Bigquery Cookbook: 5 claves para extraer datos de GA4, por D2B

En D2B estamos trabajando en apropiarnos de BigQuery con los datos de GA4 lo más pronto posible, para brindarle una mejor experiencia a nuestros clientes. Pero antes de compartir los detalles, queremos darte un poco de contexto:

En el 2020 se lanzó GA4 con la promesa de brindar análisis más detallados y profundos, pero esta promesa llegó con varias sorpresas, entre ellas, el retiro de Google Universal Analytics (su versión anterior) a partir del pasado 01 de julio de 2023.

Esto obligó a los usuarios de Google Analytics a migrar sus datos a la nueva interfaz, y de esta misma forma, a familiarizarse con los cambios estructurales que propone GA4. 

Google Analytics 4 y Bigquery

El cambio de estructura radica principalmente en el formato en que se miden sitios, ya que ahora se hará un seguimiento por eventos y no por sesiones o visualizaciones de páginas, como solía suceder en Google Analytics Universal. 

Estos cambios consisten en que nosotros, como usuarios, debemos reestructurar la forma en que construimos nuestros análisis, lo que implica aprender nuevas herramientas y formas de procesar la información (BigQuery).

Por este motivo, compartiremos a continuación 5 queries genéricas para extraer datos que son imprescindibles para cualquier persona que trabaje en marketing digital, por medio de nuestro recetario de consultas en MySQL: “GA4 Bigquery Cookbook”. 

Explorando datos en Bigquery

Todas las consultas están escritas, y a su vez, consultan el dataset público, pero puedes cambiar la ruta a tus propias rutas.

1. Análisis de sesiones por fuente y medio en BigQuery

Con la estructura de datos de GA4 se puede calcular el número de sesiones de diferentes maneras. Sin embargo, creemos que hay dos formas principales: de manera directa o mediante una aproximación. 

La manera directa es mucho más costosa computacionalmente, es por eso que la aproximación resulta un método más eficiente pero inexacto a la vez. 

Para calcular dicha aproximación usamos el algoritmo de hyperloglog++. Este algoritmo permite estimar la cantidad de elementos diferentes en grandes conjuntos de datos.

En BigQuery tenemos dos comandos que facilitan la aplicación de dicho algoritmo: HLL_COUNT.EXTRACT y HLL_COUNT.INIT. 

El segundo tiene un parámetro de precisión que varía entre 10 y 24. En D2B estudiamos este parámetro y fue el valor que produjo los resultados más cercanos al número de sesiones calculadas de manera directa.

La forma de comparar estos resultados contra la interfaz de GA4 es ocupando el primero de estas fórmulas (sin el hiperloglog). 

Sin embargo, es importante destacar que existe la probabilidad de que el resultado sea exacto debido a que, si tenemos dos o más usuarios que ingresan al mismo tiempo, estos compartirán el ID que contamos para calcular la sesión, y por lo tanto, tendremos menos sesiones en la query de BigQuery que en la interfaz:

SELECT
 CONCAT(MIN(traffic_source.source),'/',MIN(traffic_source.medium)) AS sourcemedium
 ,COUNT(DISTINCT(CONCAT((
   SELECT DISTINCT e.value.int_value
   FROM UNNEST(event_params) as e
   WHERE e.key = "ga_session_id" AND event_name = 
'session_start'),'/',user_pseudo_id))) as session_sin_hiperlog
,HLL_COUNT.EXTRACT(HLL_COUNT.INIT(CONCAT(
       user_pseudo_id,
       (SELECT `value`
       FROM UNNEST(event_params)
       WHERE key = 'ga_session_id').int_value),12)) AS session_hiperlog
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

2. Análisis de eventos totales en BigQuery

Cuando queremos saber la cantidad de eventos totales únicos, debemos utilizar la función ‘Distinct’  para contar la cantidad de veces que aparece el evento:

SELECT
    DISTINCT(event_name),
    COUNT(event_name) as total_events
 FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
 GROUP BY event_name

3. Análisis de productos y categorías vendidas en BigQuery

Debido a la estructura de datos de GA4, sólo se puede responder a esta pregunta para aquellos usuarios que sean e-commerce, porque son los únicos que reportan el evento ‘purchase’:

SELECT
  items.item_id AS item_id
  ,items.item_name AS item_name
  ,items.item_category AS category
  ,SUM(items.quantity) AS item_quantity
  ,COUNT(event_timestamp) AS compras
  ,SUM(items.item_revenue) AS item_revenue
FROM (SELECT
  event_name, items, event_timestamp
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, 
UNNEST (items) AS items
  WHERE event_name = 'purchase')
GROUP BY item_id, item_name, category

4. Análisis por landing page

Para obtener el landing page de cada sesión recurrimos al evento llamado ‘session_start’. De él extraemos el parameter de page_location para poder identificar la página por la cual ingresa el usuario:

SELECT
 landing_page
,COUNT(DISTINCT user_pseudo_id) AS user_count
,COUNT(DISTINCT concat(user_pseudo_id, session_id)) AS sessions
FROM(
 SELECT
  user_pseudo_id
 ,(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 
'ga_session_id') AS session_id
 ,MAX((SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE event_name = "session_start" AND key = 'page_location')) AS 
landing_page
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  GROUP BY user_pseudo_id,session_id)
GROUP BY landing_page

5. Análisis de tiempo de página

Para calcular el tiempo que estuvo cada cliente en la página, debemos restar la hora a la que éste inició una sesión con la hora en la que realizó el siguiente hit, y luego hacer un loop por cada ID diferente:

SELECT
 user_pseudo_id
 ,event_timestamp
 ,ga_session_id
 ,page_location
 ,page_title
 ,next_hit_in_the_same_session
 ,(next_hit_in_the_same_session - event_timestamp)/1000000 AS 
time_on_page_in_seconds
FROM (
 SELECT
  user_pseudo_id,event_timestamp
 ,(SELECT value.int_value FROM UNNEST(event_params)
 WHERE key = 'ga_session_id') AS ga_session_id
 ,(SELECT value.string_value
 FROM UNNEST(event_params)
 WHERE key = 'page_location') AS page_location
 ,(SELECT value.string_value
 FROM UNNEST(event_params)
 WHERE key = 'page_title') AS page_title,
 LEAD(event_timestamp) OVER (PARTITION BY (SELECT value.int_value FROM 
UNNEST(event_params)
 WHERE key = 'ga_session_id')
 ORDER BY event_timestamp ASC) AS next_hit_in_the_same_session
 FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` AS 
tableAlias
 WHERE event_name = 'page_view'
 ORDER BY user_pseudo_id,ga_session_id,event_timestamp ASC)

Ejemplos 

Ejemplo personalizado

Muchas veces queremos graficar nuestros resultados (en D2B usamos LookerStudio), pero agregar dimensiones que vienen muy desagregadas puede entregar resultados incorrectos, es por esto que nosotros trabajamos en una query tipo para que podamos crear una tabla en Bigquery y luego crear las métricas directamente en LookerStudio.

Con la query que se presenta a continuación, podemos fácilmente calcular las sesiones, usuarios, nuevos usuarios, rebotes y cualquier tipo de evento que tengamos creado. Además, podremos desagregar estos valores por nombre de campana, fuente, medio, stream ID, ciudad y/o PagePath:

SELECT event_date
  ,stream_id
  ,traffic_source.source as traffic_source
  ,traffic_source.medium as traffic_medium
  ,traffic_source.name as traffic_name
  ,user_pseudo_id
  ,event_name
  ,device.category as device_category
  ,geo.city as city
  ,event_timestamp
  ,(SELECT value.int_value FROM UNNEST (event_params) where key ="engaged_session_event") as 
value_int_value_engaged_session_event
  ,(SELECT value.int_value FROM UNNEST (event_params) where key ="ga_session_number") as 
value_int_value_ga_session_number
  ,CONCAT(user_pseudo_id,(SELECT value.int_value FROM UNNEST (event_params) AS e WHERE 
key="ga_session_id")) as session_id
  ,(select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 
'page_location') as page
  ,CONCAT(user_pseudo_id,(SELECT value.int_value FROM UNNEST (event_params) AS e WHERE key="ga_session_id")) as session_id
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

Algunos de los parámetros que podemos calcular en Looker con base en la query podrían ser los siguientes:

1. Bounces: (COUNT_DISTINCT( CASE WHEN (value_int_value_engaged_session_event=1) THEN session_id ELSE null END)/COUNT_DISTINCT(session_id))
2. Sesiones: count_distinct(IF(event_name = ‘session_start’, concat(value_int_value_ga_session_id,’/’, user_pseudo_id),null))
3. Paginas Vistas: sum(if(event_name = “page_view”,1,0))
4. Usuarios: COUNT_DISTINCT(user_pseudo_id)
5. Nuevos Usuarios: count_distinct(IF(value_int_value_ga_session_number= 1,user_pseudo_id,null))

Para concluir, podemos ver que GA4 llegó con muchos cambios y debemos comenzar a acostumbrarnos a esta nueva forma de trabajar e interpretar los datos. 

A través de internet, podemos encontrar múltiples CookBooks y formas de llegar a los datos por medio de cálculos, que muchas veces nos entregarán conclusiones opuestas o poco similares para el mismo valor, si es que ocupamos un sistema de medición u otro.

Es por esto que en compañías como la nuestra, debemos comenzar a decidir qué métricas usar y cómo las vamos a calcular a lo largo del tiempo, para que sean lo más compatibles posibles entre GA4, BigQuery, GA4 interfaz e idealmente con Universal y así poder entregar el máximo de continuidad posible al trabajo que todos realizamos gracias a Google Analytics.

Para más información, puedes revisar este material: 

https://www.sisense.com/blog/hyperloglog-in-pure-sql/   https://static.googleusercontent.com/media/research.google.com/es//pubs/archive/40671.pdf 

Autores: Orlando Ibáñez y Juan David Cárdenas