¿Cómo trabajar con la estructura anidada de Google Analytics 4 en BigQuery?

En un artículo anterior vimos como vincular una propiedad de Google Analytics 4 con BigQuery para comenzar a almacenar los datos en bruto recogidos por GA4 en el servicio de almacenamiento de Google Cloud Platform. Una vez que los datos empiecen a llegar a la herramienta y trates de trabajar con ellos, te darás cuenta de que su estructura es diferente.

Como veremos a continuación, entre los datos que GA4 exporta a BigQuery se encuentran tres campos con valores repetidos: parámetros de evento, propiedades de usuario y los ítems del ecommerce. Esto optimiza el almacenamiento de la información en las tablas de BigQuery, pero nos obliga a aprender a desanidar todos estos datos para poder trabajar con ellos.

Estructura de las tablas de GA4 en BigQuery

Las tablas de Google Analytics 4 en BigQuery tienen una estructura anidada. Cada fila se corresponde con un único evento de GA4, pero cada uno de estos eventos puede contener más de un parámetro de evento o propiedad de usuario. En estos casos, BigQuery, en lugar de crear una nueva fila para cada parámetro, almacena todos los valores dentro de un campo repetido. Se trata, por tanto, de un array de datos dentro de la propia fila del evento.

Para comprender mejor la estructura, lo mejor es acceder a BigQuery desde Google Cloud Platform, entrar a tu conjunto de datos y observar el esquema de una de las tablas. Como puedes ver en la siguiente imagen, el campo event_params es un campo repetido de tipo RECORD y cuenta con una flecha que, al pulsarla, despliega la estructura anidada.

También es posible identificar los campos repetidos de la tabla de GA4 dentro de la pestaña de vista previa, donde el campo event_params cuenta con más de un valor.

Si abres la consola y tratas de hacer una consulta SQL en la que se incluye un campo anidado, te encontrarás con un error al ejecutar la consulta, ya que no es posible acceder a un campo de tipo array. Para poder realizar tu consulta sin errores, necesitarás la función UNNEST.

Utilizar la función UNNEST para desanidar las tablas de Google Analytics 4 en BigQuery

La función UNNEST de BigQuery nos permite aplanar una tabla y extraer los valores de los campos anidados. Cuando utilizamos la función, la consulta nos devuelve una tabla en la que cada uno de los elementos del campo repetido tiene su propia fila. Esto nos permitirá acceder a los parámetros como si se tratasen de campos normales.

Para utilizar la función para extraer parámetros de evento, lo primero que tienes que hacer es identificar el tipo de datos del parámetro que quieres extraer, ya que los valores pueden ser almacenados como string, integer, float y double. Cuando sepas que parámetro quieres extraer, tan solo tienes que seleccionarlo en tu consulta. Un ejemplo de consulta sería este:

select
  distinct (select value.string_value from unnest(event_params) where key = 'page_location') as page,
from
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
where
  event_name = 'page_view'

La consulta anterior nos devolverá una tabla con todas las páginas diferentes que vieron los usuarios en el día seleccionado. En la consulta se usa uno de los datasets públicos de BigQuery, por lo que puedes introducirla en la consola y ver los resultados.

También se puede utilizar la función UNNEST dentro de la cláusula FROM. Como ejemplo, vamos a trabajar en esta ocasión con los campos repetidos de los ítems de ecommerce.

select
  item.item_name,
  sum(item.quantity) as quantity,
  sum(item.item_revenue) as item_revenue
from
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`,
  unnest(items) as item
where
  event_name = 'purchase'
group by 1
order by 2 desc

En este caso, estamos obteniendo una tabla con todos los productos comprados durante el periodo de tiempo seleccionado, junto con la cantidad y el revenue de cada uno de ellos. Como puedes ver, en lugar de usar la función UNNEST en cada uno de los valores seleccionados, la utilizamos una única vez en la cláusula FROM.

Debes tener en cuenta que desanidar variables desde la cláusula FROM genera un mayor número de filas que si lo haces desde la instrucción SELECT y el coste de procesamiento de datos también es mayor, por lo que tendrás que elegir en cada caso el método que más te convenga.

Aunque al principio parece difícil trabajar con los campos repetidos y la estructura anidada de las tablas de Google Analytics 4 en BigQuery, la exploración de los datos se vuelve mucho más sencilla una vez comprendes su funcionamiento.