GA4 y Firebase en BigQuery: cómo usar la función UNNEST para trabajar con tablas anidadas (repeated fields)

Yo no soy un experto en SQL, pero muchas veces trabajo diseñando y lanzando consultas en BigQuery. Es algo que me gusta, entre otras cosas porque me permite trabajar con el resultado de una implementación (vía Google Tag Manager o Tealium iQ) en su estado más puro: los datos recopilados en bruto. La primera vez que tuve que trabajar con los datasets de GA4 y Firebase en BigQuery (también con Universal Analytics, ojo) me di de bruces con la estructura anidada que tienen estas tablas: filas que contienen estructuras de datos repetidas. Para explotar estos repeated fields tienes que desanidarlos con la función UNNEST. Es algo que me costó un poco entender al principio, por lo que he pensado que escribir un post al respecto igual serviría de ayuda. Ojalá sea así.

Arranco enlazando a dos de los recursos que más me han ayudado a entender este concepto tan importante a la hora de trabajar con datos de GA4 y Firebase en BigQuery:

La estructura anidada de las tablas de GA4 y Firebase en BigQuery: los repeated fields

Como ya te he dicho al comienzo de este post, las tablas de GA4 y Firebase en BigQuery son anidadas. Cada fila de estas tablas representa un evento (screen_view, page_view, session_start, etc.), y cada uno de estos eventos contiene varias propiedades de usuario y varios parámetros de evento (que a su vez contienen diferentes tipos de valores posibles). Estos registros generan una estructura anidada o repetida de valores (repeated fields en inglés) que se insertan dentro de la propia fila de la tabla. Son, por así decirlo, un array de datos dentro de la estructura de datos del evento. Fíjate en el siguiente diagrama (sí, lo he hecho yo mismo) Representa de una forma gráfica la estructura anidada del parámetro event_params en los datasets de GA4 y Firebase de BigQuery.

Y ahora fíjate en la siguiente captura de pantalla. Es un evento random del dataset público de GA4 en BigQuery (todas las consultas de ejemplo que llevo a cabo en este post están realizdas sobre este dataset) ¿Ves cómo se repiten los parámetros de evento dentro de la propia fila de la tabla? Esta es la estructura anidada (el array, el repeated field) que te mostraba en el diagrama que he incluido un poco más arriba.

Hay tres tipos de repeated fields en el export de GA4 a BigQuery: los parámetros de evento, las propiedades de usuario y los items de ecommerce. Tanto los event params como los user properties tienen la misma estructura: una clave y varios posibles valores para la misma. En tanto en cuanto puede haber varios parámetros de evento, habrá varias claves con sus respectivos valores (lo mismo sucede con las propiedades de usuario). Y esto resulta en un repeated field. Esto es algo que se explica muy bien en el post GA4 BigQuery Tips: Event Parameters and Other Repeated Fields (Part Two).

En esta captura de pantalla, sacada del detalle de la tabla del dataset de GA4 en BigQuery, se aprecia bien como los user_properties y event_params de un evento tienen la misma estructura.

Para trabajar con estas estructuras de datos antes hay que desanidarlas, es decir, sacar cada elemento que forma parte de la estructura anidada para que ocupe su propia fila en la tabla. Si no haces esto vas a ver errores como el siguiente al ejecutar tu consulta en el editors de SQL de BigQuery:

La consola SQL de BigQuery te muestra este mensaje de error al estar intentando acceder a un campo del campo repetido user_properties (key en este caso) sin haber desanidado la estructura antes.

La función UNNEST al rescate

Desanidar los repeated fields de una tabla de GA4 o Firebase en BigQuery es sencillo, lo difícil es entender qué es lo que realmente está ocurriendo behind the scenes. Para llevar a cabo este desanidamiento hay que hacer uso de la función UNNEST , que toma como parámetro la estructura de datos que se quiere desanidar. UNNEST genera una tabla nueva en la que cada uno de los registros del array ocupa su propia fila. Hecho esto, hay que fusionar todas las filas de esta nueva tabla con las de la tabla original. Esto se consigue con un CROSS  JOIN 

Te voy a ilustrar esto con un ejemplo. Supón que quieres consultar todos los parámetros de evento del evento first_visit de un usuario en particular en tu tabla de GA4 en BigQuery. Para ello tendrías que preparar una query como la siguiente: 

select
  event_name,
  key as event_param_key,
  value.string_value as event_param_string_value,
  value.int_value as event_param_int_value,
  value.float_value as event_param_float_value,
  value.double_value as event_param_double_value

from 
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131` 
  cross join unnest(event_params)
where 
  event_name = 'first_visit'
  and user_pseudo_id = 'user_pseudo_id_string'

Como puedes observar, voy a lanzar la query con un filtro WHERE para devolver los resultados de un único usuario. Para ello filtraré por el parámetro user_pseudo_id, (que identifica el navegador o la app). Este es el aspecto que tiene ese evento en la tabla de GA4 en BigQuery antes de ejecutar esta consulta (recuerda que estoy trabajando en el dataset público de GA4 en BigQuery):

Esta captura de pantalla sólo muestra una parte de todos los parámetros de este evento first_visit.

Y este es el resultado que devuelve la query:

¿Ves el desanidamiento? Ahora cada parámetro de evento ocupa su propia fila, por lo que la tabla ya se puede explotar con normalidad sin necesidad de recurrir a la función UNNEST. 

BigQuery scalar subqueries: generar una columna para el valor de cada parámetro desanidado

Si has llegado a este punto en tus análisis en BigQuery probablemente necesites ir un paso más allá (aunque la tabla generada con la anterior query es perfectamente válida, ojo). Un caso típico con el que me he encontrado un montón de veces es el de tener que generar una tabla en la que cada columna recoja el valor de un parámetro de evento o propiedad de usuario concreto.

Supón ahora que quieres contabilizar el número de veces que una determinada url ha sido la página a través de la cual ha arrancado una sesión. En este caso tendrías que apalancarte en el evento session_start y ya no tendrías que desanidar toda la estructura de parámeteros de evento, sino sólo un campo: page_title (o page_location si prefieres).

Puedes conseguir esto a través de un scalar subquery, que básicamente es una query dentro de una query que te permite acceder a un parámetro concreto de una tabla. Es algo que también aprendí del gran Johan van de Werken, de ga4bigquery.com (como ya te he dicho al comienzo de este post, esta web en un recurso súper útil si trabajas configurando consultas en SQL en BigQuery)

Fíjate, en la siguiente consulta lo vas a ver bien:

with page_data as(
  select
    event_name,
    (select value.string_value from unnest(event_params) where key = 'page_title' and event_name = 'session_start') as page_title  
  from 
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
where 
  event_name = 'session_start'  
)

select
  count (page_title) as session_starts,
  page_title
from
  page_data
group by 
  page_title  

¿Lo ves? En la primera query preparo una tabla que llamo page_data. En ella recojo sólo el valor del parámetro page_title del array event_params gracias al scalar subquery que ves en la línea 4. Uso la segunda de las consultas para hacer cálculos sobre los datos que me proporciona la primera (básicamente el número de veces que cada page_title fue la página a través de la cual arrancó una sesión). El resultado de esta consulta, a continuación:

Y ahí lo tienes. No ha hecho falta desanidar todos los event_params para acceder al parámetro page_title, ha bastado con acceder a éste gracias a la scalar subquery.

Reflexiones finales

Las tablas de GA4 y Firebase en los datasets de BigQuery tienen más tipos de estructuras de datos. En este post sólo he hablado de los repeated fields, pero también están, por ejemplo, los records (los event_params y user_properties son un tipo de record, de hecho). Pero saber cómo trabajar con estos campos repetidos es, bajo mi punto de visto, un diferencial en los análisis  en BigQuery. A mi me ha ayudado un montón a seguir explorando nuevo métodos para explotar datos y, sobre todo, a seguir aprendiendo. Ojalá que este post te ayude a ti también.

pornance.net
www.fuck-videos.net
zettaporn.com