19 Ago Cómo crear funnels con BigQuery, Google Analytics 4 y LookerStudio
Una de las preguntas que te vas a hacer cuando empiezas a usar BigQuery con Google Analytics 4 es cómo demonios vas a poder construir un funnel con la información en bruto que te aporta la conexión con BigQuery.
En este post vamos a ver un ejemplo con el dataset de pruebas bigquery-public-data.ga4_obfuscated_sample_ecommerce
Funnel de ecommerce con BigQuery y GA4
Este tipo de funnel que solemos encontrar en las exploraciones de GA4 te va a servir para analizar el proceso de compra genérico desde que se aterriza en una página hasta que se finaliza la compra.
También te va a servir para medir la efectividad de las campañas, comparar canales de adquisición , tipos de usuario y mucho más.
Básicamente vamos a añadir 4 pasos:
-Visita de la página home.
-Visita de una página de producto.
-Añadir un producto al carrito.
-Compra.
Este funnel , para que sea realista debe ser de tipo secuencial. Un funnel secuencial implica que los usuarios completan los pasos del funnel en el orden correcto. Lo que estamos haciendo en este caso es un funnel cerrado. Si quieres saber la diferencia entre un funnel abierto y uno cerrado lee este post que te enlazo.
Construiremos algo parecido a esto:
Vamos a extraer los datos de GA4 a través de BigQuery y utilizaremos LookerStudio para visualizar los datos.
Guía paso a paso para extraer los datos de ecommerce de BigQuery GA4
Puedes incluir tantos pasos como quieras, pero principalmente lo que haremos será utilizar la métrica de usuarios y con distintas queries sacar los pasos que completan en el funnel de compra que hemos establecido.
El identificador que usaremos es user_pseudo_id. Esto nos asegura que sea el mismo usuario el que «atraviesa» los 4 pasos del funnel.
Atención, la métrica que usaremos, como he dicho será usuarios , pero las sesiones nos «darán igual» contabilizaremos usuarios independientemente de cuantas sesiones hayan necesitado para completar los pasos del funnel. Además con este funnel aislaremos a los usuarios que completaron la compra en el mismo dia.
Define los pasos del funnel
Antes de escribir nada de SQL tendremos que encontrar los nombres de los eventos.
- Ver un producto: – event_name = ‘view_item’
- Añadir un producto al carrito – event_name = ‘add_to_cart’
- Empezar el chekout – event_name = ‘begin_checkout’
- Completar la compra – event_name = ‘purchase’
Extraer el identificador y las tablas indispensables
Vamos a necesitar si o si las siguientes tablas, user_pseudo_id, event_name, event_date y event_timestamp
1. Para esto utilizaremos un WITH. Esta parte del SQL será transversal a todos los eventos.
WITH dataset AS ( SELECT user_pseudo_id, event_name, PARSE_DATE(‘%Y%m%d’, event_date) AS event_date, TIMESTAMP_MICROS(event_timestamp) AS event_timestamp FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*` WHERE event_name IN (‘view_item’, ‘add_to_cart’, ‘begin_checkout’, ‘purchase’) ),
2. Añadiremos la lista de usuarios que han hecho un view_item
view_item AS (
SELECT
user_pseudo_id,
event_date,
event_timestamp
FROM dataset
WHERE event_name = ‘view_item’
),
y repetiremos lo mismo con el resto de eventos.
Veréis que la estructura del SQL es la misma pero cambiando el nombre del evento.
Después aplicaremos un LEFT JOIN para hacer la unión de todos los usuarios. La haremos con varias condiciones.
funnel AS ( SELECT vi.event_date, COUNT(DISTINCT vi.user_pseudo_id) AS view_item, COUNT(DISTINCT atc.user_pseudo_id) AS add_to_cart, COUNT(DISTINCT bc.user_pseudo_id) AS begin_checkout, COUNT(DISTINCT p.user_pseudo_id) AS purchase FROM view_item vi LEFT JOIN add_to_cart atc ON vi.user_pseudo_id = atc.user_pseudo_id AND vi.event_date = atc.event_date AND vi.event_timestamp < atc.event_timestamp LEFT JOIN begin_checkout bc ON atc.user_pseudo_id = bc.user_pseudo_id AND atc.event_date = bc.event_date AND atc.event_timestamp < bc.event_timestamp LEFT JOIN purchase p ON bc.user_pseudo_id = p.user_pseudo_id AND bc.event_date = p.event_date AND bc.event_timestamp < p.event_timestamp GROUP BY 1 )
Calcular la progresión
Es muy importante que se añada esta parte al final para que se calcule el porcentaje de usuarios que pasan de un punto al otro.
SELECT event_date, view_item, add_to_cart, begin_checkout, purchase, 1 AS view_item_rate, ROUND(COALESCE(add_to_cart / NULLIF(view_item,0), 0), 2) AS add_to_cart_rate, ROUND(COALESCE(begin_checkout / NULLIF(view_item,0), 0), 2) AS begin_checkout_rate, ROUND(COALESCE(purchase / NULLIF(view_item,0), 0), 2) AS purchase_rate FROM funnel ORDER BY 1 ASC
El SQL final
WITH dataset AS ( SELECT user_pseudo_id, event_name, PARSE_DATE(‘%Y%m%d’, event_date) AS event_date, TIMESTAMP_MICROS(event_timestamp) AS event_timestamp FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*` WHERE event_name IN (‘view_item’, ‘add_to_cart’, ‘begin_checkout’, ‘purchase’) ),
view_item AS ( SELECT user_pseudo_id, event_date, event_timestamp FROM dataset WHERE event_name = ‘view_item’ ),
add_to_cart AS ( SELECT user_pseudo_id, event_date, event_timestamp FROM dataset WHERE event_name = ‘add_to_cart’ ),
begin_checkout AS ( SELECT user_pseudo_id, event_date, event_timestamp FROM dataset WHERE event_name = ‘begin_checkout’ ),
purchase AS ( SELECT user_pseudo_id, event_date, event_timestamp FROM dataset WHERE event_name = ‘purchase’ ),
funnel AS ( SELECT vi.event_date, COUNT(DISTINCT vi.user_pseudo_id) AS view_item, COUNT(DISTINCT atc.user_pseudo_id) AS add_to_cart, COUNT(DISTINCT bc.user_pseudo_id) AS begin_checkout, COUNT(DISTINCT p.user_pseudo_id) AS purchase FROM view_item vi LEFT JOIN add_to_cart atc ON vi.user_pseudo_id = atc.user_pseudo_id AND vi.event_date = atc.event_date AND vi.event_timestamp < atc.event_timestamp LEFT JOIN begin_checkout bc ON atc.user_pseudo_id = bc.user_pseudo_id AND atc.event_date = bc.event_date AND atc.event_timestamp < bc.event_timestamp LEFT JOIN purchase p ON bc.user_pseudo_id = p.user_pseudo_id AND bc.event_date = p.event_date AND bc.event_timestamp < p.event_timestamp GROUP BY 1 )
SELECT event_date, view_item, add_to_cart, begin_checkout, purchase, 1 AS view_item_rate, ROUND(COALESCE(add_to_cart / NULLIF(view_item,0), 0), 2) AS add_to_cart_rate, ROUND(COALESCE(begin_checkout / NULLIF(view_item,0), 0), 2) AS begin_checkout_rate, ROUND(COALESCE(purchase / NULLIF(view_item,0), 0), 2) AS purchase_rate
FROM funnel
ORDER BY 1 ASC
El resultado será algo así.
Ojo! Antes de pasar a LookerStudio deberás guardar esta tabla resultado como una tabla BigQuery
Conectar BigQuery con LookerStudio
A partir de aquí todo es cuestión de conexiones y ajustes. En LookerStudio añadirás la tabla que te has guardado desde las fuentes de datos. Como sueles hacer normalmente con GA4 pero con BigQuery en este caso.
Para la visualización en forma de funnel deberás hacer lo siguiente.
1. Añadir un gráfico de columnas.
2.Añadir esta configuración de dimensiones y métricas.
3. Asegúrate de cambiar el formato de las métricas a porcentaje para poder ver el porcentaje de usuarios que completan cada paso.
4. Añade un control de fechas para ver los días que quieres seleccionar y ya lo tendrás listo.
A partir de aquí ya puedes añadir todos los formatos que quieres y «jugar» con las visualizaciones de LookerStudio.
