Joins (SQL) en BigQuery: cómo fusionar datos de Google Analytics 4 (GA4) con datos del tiempo

BigQuery es el data warehouse de Google. Forma parte de la suite de herramientas de Google Cloud Platform y te permite alojar -y explotar- bases de datos de gran tamaño a muy buen precio y con muy buen rendimiento (en realidad BigQuery se puede usar para otras muchas cosas, como por ejemplo para elaborar modelos de Machine Learning).

Google Analytics (tanto GA360 como GA4) tiene una integración nativa con BigQuery para exportar tus datos de analítica en bruto a este warehouse. Puedes hacer lo mismo con otras fuentes como por ejemplo un CRM como Salesforce o una plataforma como Google Ads. En todos estos casos puedes trabajar estos datos en bruto. Pero ojo, no sólo se trata de explotar cada base de datos por separado, sino de establecer relaciones entre todas ellas. Este es, bajo mi punto de vista, uno de los potenciales de trabajar con un data warehouse como BigQuery: establecer relaciones entre todas tus fuentes de información, acabar con los silos de datos. En el argot del SQL (el lenguaje de programación para explotar BBDD) esto es lo que se conoce como joins (uniones en castellano).

Pues bien, en este post te voy a enseñar a hacer un join entre dos datasets públicos que puedes encontrar en el proyecto bigquery-public-data: el dataset de Google Analytics 4 (GA4) (recoge datos de GA4 del ‘site’ https://shop.googlemerchandisestore.com/) y el dataset con datos del clima de todo el planeta de la organización National Oceanic and Atmospheric Administration (NOAA)

Para que veas el nivel de análisis que puedes lograr con BigQuery, voy a intentar averiguar si la tendencia de visitas a https://shop.googlemerchandisestore.com/ cambió en función de si llovió o no en enero de 2021.

Cómo se organizan los datos en BigQuery: proyectos, datasets y tablas

Es importante entender cómo se organizan los datos en BigQuery, tanto a la hora de alojarlos como de explotarlos. La jerarquía que se sigue es la siguiente:

Proyecto > Dataset > Tabla

Un proyecto puede englobar muchos datasets, y un dataset puede contener muchas tablas. Si navegas a BigQuery y despliegas el proyecto bigquery-public-data verás todos los datasets que ‘cuelgan’ de él. Así mismo, si despliegas un dataset (el de GA4, por ejemplo), verás todas las tablas que cuelgan de dicho dataset.

El proyecto bigquery-public-data, desplegado. En la foto puedes ver algunos de los datasets que 'cuelgan' de este proyecto.
El dataset público de Google Analytics 4 (GA4) en BigQuery. De él cuelga la tabla events_, una tabla particionada por fecha.

Es muy importante que tengas en cuenta en qué ubicación geográfica están alojados tus datos. Sólo podrás hacer joins entre tablas que estén alojadas en la misma región, de lo contrario tendrás que transferir los datos a la región correcta. Y recuerda que BigQuery es un producto de pago, pagas por almacenar los datos y por explotarlos. El rango de precios es atractivo, pero te aconsejo que lo tengas en cuenta.

El dataset público de Google Analytics 4 (GA4) en BigQuery

Para el ejercicio que voy a hacer en este post voy a usar el dataset público de GA4 en BigQuery: ga4_obfuscated_sample_ecommerce. Como te decía un poco antes, este dataset recoge datos del ‘site’ https://shop.googlemerchandisestore.com/

La estructura de los datos de GA4 en BigQuery es compleja, y es además diferente a la estructura de los datos de Universal Analytics (GA360) en BigQuery. Fíjate en los siguientes pantallazos:

El esquema del dataset público de GA4 en BigQuery...
...y la vista previa de la tabla particionada de GA4.

Cada fila de la tabla hace referencia a un único evento (session_start, page_view, first_visit, etc.), y cada una de las columnas hace referencia a un parámetro de ese evento. La complejidad de la tabla de GA4 en BigQuery viene dada porque es una tabla anidada, es decir, cada celda puede contener varios valores. Parámetros como el parámetro geo son anidados. Parámetros como event_params son anidados pero además repetidos. Conocer esto es muy importante de cara a poder explotar los datos de GA4 en BigQuery.

Detalle del campo anidado de GA4 en BigQuery geo.
Detalle del campo anidado de GA4 en BigQuery event_params.Este campo, además de anidado, es un campo repetido.

Si recuerdas de lo que has leído al principio, me he propuesto averiguar si la tendencia de sesiones en enero de 2021 cambió en función de si llovió o no. Bien, para eso voy a necesitar los siguientes datos del dataset de GA4 en BigQuery:

  • event_date →  La fecha del evento.
  • user_pseudo_id → Identificador de usuario (navegador) de GA4.
  • event_name → Nombre de evento
  • event_param de valor ga_session_number → Parámetro necesario para hallar si una visita es la primera de un usuario, o no.
  • geo.country → País desde el que tiene lugar el evento de GA4.

No tengo sitio en este post para entrar a explicar en detalle el resto de campos que están disponibles en BigQuery para GA4. Si quieres informarte con más detalle, te recomiendo que consultes la documentación oficial del Google al respecto.

Listo, esta es la consulta que he preparado para obtener los anteriores datos:

with ga4_data as (
    select
        --Converting event_date string into date format
        parse_date("%Y%m%d", event_date) as fecha, 
        user_pseudo_id,
        event_name,
        case 
            when (select value.int_value from unnest(event_params) where key = 'ga_session_number' and event_name = 'session_start') = 1 then 'visita nuevo usuario'
            else 'visita usuario recurrente' 
        end as tipo_de_visita,    
        geo.country
    from 
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    where
        _table_suffix between '20210101' and '20210131'
        and event_name = 'session_start'
        and geo.country = 'Spain'
    group by
        fecha,
        user_pseudo_id,
        event_name,
        tipo_de_visita,
        country
),

Ten en cuenta que la anterior es una subquery, es decir, es una query que genera una tabla de datos que usaré en otra query más adelante. Esto es algo que ya hice en el post ‘BigQuery y Google Analytics (Universal): hallar la secuencia de páginas visitadas en una sesión’.

El resultado de la consulta es el siguiente:

Una muestra de los resultados que devuelve la anetrior query.

Como ves, he generado una tabla en la que puedo ver a nivel de usuario (user_pseudo_id) cuántas sesiones han tenido lugar en España durante enero de 2021, tanto las acometidas por usuarios nuevos como por usuarios recurrentes. He filtrado los resultados para ver sólo datos de España con el objetivo de economizar la consulta. Recuerda que ejecutar consultas en BigQuery tiene un coste asociado.

El dataset del tiempo en BigQuery

Bien, ahora toca generar una tabla con datos del tiempo. Para ello voy a usar el dataset noaa_gsod, un conjunto de datos público del organismo National Oceanic and Atmospheric Administration (NOAA). Esta organización lleva desde 1929 recabando datos del clima de todo el planeta a través de estaciones ubicadas en diferentes puntos del mundo. El dataset tiene una tabla por cada año y yo voy a usar la correspondiente a 2021.
Detalle del esquema de una tabla del dataset de NOAA en BigQuery.

De todos los campos que hay disponibles, me interesan tres:

  • stn → El id de la estación climática
  • date → La fecha
  • rain_drizzle → Si llovió, o no

Voy a usar el campo stn para filtrar los datos y ver sólo los correspondientes a las estaciones de Madrid, así consigo economizar aún más la consulta. Pero en Madrid hay varias estaciones, y no todas han tomado mediciones en 2021, por lo que también hay que tomar en consideración este factor. Usaré los datos de la estación 082210, situada en Barajas, cuyo último es de febrero de 2022 (tienes toda esta información en la tabla stations del dataset noaa_gsod de BigQuery, aunque también puedes consultarla en el informe Station History Reports de NOAA).

Bien, esta es la query que ejecuto para conseguir esta información (date cuenta de que de nuevo es una subquery, es decir, una query que uso para generar una tabla sobre la que lanzaré una consulta más adelante):

madrid_weather_data as (
    select 
        date,
        rain_drizzle  
    from 
        `bigquery-public-data.noaa_gsod.gsod2021`
    where 
        stn = '082210' 
),

Y estos son los resultados:

Detalle de parte de los resultados de la anterior consulta. La columna 'date' recoge la fecha, la columna 'rain_drizzle' si llovió (1), o no (0).

El join entre las dos tablas: GA4 y los datos del tiempo

Ya tengo los datos de visitas de GA4 y los datos del tiempo de Madrid, ahora toca fusionarlos para poder explotarlos en su conjunto. Para ello voy a hacer un join, es decir, voy a fusionar ambas tablas en una sola. Aquí hay varias cosas que tienes que tener en cuenta:

  • Cuando haces un join entre tablas debe haber un elemento en común entre ellas. Puede ser un id, un valor booleano, un número, etc. En este caso usaré el campo date, es decir, la fecha. De esta forma podré ver el dato de visitas (sacado del dataset de GA4) en la misma fila que los datos del tiempo (sacado del dataset de NOAA)
  • Hay varios tipos de joins, y en este caso voy a hacer un left join. En el ejercicio que hago en este post esto quiere decir que uniré los datos del tiempo a los de GA4, y no al revés. Sólo se añadirán a los datos de GA4 los datos del tiempo que cumplan la condición del join, que en este caso es el de la fecha. El resto se descartan.
  • Cuando haces un join estás seleccionando columnas de ambas tablas. Es importante que en tu query referencies la tabla en la que están cada una de estas columnas. Como verás en la siguiente query, yo uso los pseudónimos a. y b.

Venga, fíjate en la query que he configurado para hacer el join. Fíjate también en las dos subqueries que preceden a la consulta del join, son justo las que he creado en los anteriores apartados de este post. Ahora las puedes ver en acción. ¿Ves cómo simplifican la consulta? En lugar de configurar una única query, creas varias y simplificas el trabajo.

with ga4_data as (
    select
        parse_date("%Y%m%d", event_date) as fecha, 
        user_pseudo_id,
        event_name,
        case 
            when (select value.int_value from unnest(event_params) where key = 'ga_session_number' and event_name = 'session_start') = 1 then 'visita nuevo usuario'
            else 'visita usuario recurrente' 
        end as tipo_de_visita,    
        geo.country
    from 
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    where
        _table_suffix between '20210101' and '20210131'
        and event_name = 'session_start'
        and geo.country = 'Spain'
    group by
        fecha,
        user_pseudo_id,
        event_name,
        tipo_de_visita,
        country
),

madrid_weather_data as (
    select 
        date,
        rain_drizzle  
    from 
        `bigquery-public-data.noaa_gsod.gsod2021`
    where 
        stn = '082210' 
)

select
    a.fecha,
    case
        when (a.tipo_de_visita = 'visita usuario recurrente' and b.rain_drizzle = '1') then count(user_pseudo_id)
        else 0
    end as visitas_usuarios_recurrentes_con_lluvia,
    case
        when (a.tipo_de_visita = 'visita nuevo usuario' and b.rain_drizzle = '1') then count(user_pseudo_id)
        else 0
    end as visitas_nuevos_usuarios_con_lluvia,
    case
        when (a.tipo_de_visita = 'visita usuario recurrente' and b.rain_drizzle = '0') then count(user_pseudo_id)
        else 0
    end as visitas_usuarios_recurrentes_sin_lluvia,
    case
        when (a.tipo_de_visita = 'visita nuevo usuario' and b.rain_drizzle = '0') then count(user_pseudo_id)
        else 0
    end as visitas_nuevos_usuarios_recurrentes_sin_lluvia,
from
    ga4_data as a
    left join madrid_weather_data as b
    on a.fecha = b.date
group by
    fecha,
    tipo_de_visita,
    rain_drizzle 
Y aquí los resultados de la consulta:
Detalle de los resultados de la anterior consulta. Es una tabla que muestra datos transformados: se cuentan los usuarios nuevos y recurrentes que visitaron el 'site' en los días en los que llovió y en los que no.
¿Ves algo raro en los resultados? Si te fijas, cada día aparece por duplicado. Esto es así porque en cada fecha hubo usuarios nuevos y recurrentes, tanto si llovió como si no. La consulta probablemente se pueda optimizar un montón, pero yo lo resuelvo convirtiendo la anterior query en una nueva subquery. De esta forma puedo operar sobre la anterior tabla y agregar los resultados para que sea más fácil trabajar con ellos. Fíjate:
with ga4_data as (
    select
        parse_date("%Y%m%d", event_date) as fecha, 
        user_pseudo_id,
        event_name,
        case 
            when (select value.int_value from unnest(event_params) where key = 'ga_session_number' and event_name = 'session_start') = 1 then 'visita nuevo usuario'
            else 'visita usuario recurrente' 
        end as tipo_de_visita,    
        geo.country
    from 
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    where
        _table_suffix between '20210101' and '20210131'
        and event_name = 'session_start'
        and geo.country = 'Spain'
    group by
        fecha,
        user_pseudo_id,
        event_name,
        tipo_de_visita,
        country
),

madrid_weather_data as (
    select 
        date,
        rain_drizzle  
    from 
        `bigquery-public-data.noaa_gsod.gsod2021`
    where 
        stn = '082210' 
),


final_data as(
    select
        a.fecha,
    case
        when (a.tipo_de_visita = 'visita usuario recurrente' and b.rain_drizzle = '1') then count(user_pseudo_id)
        else 0
    end as visitas_usuarios_recurrentes_con_lluvia,
    case
        when (a.tipo_de_visita = 'visita nuevo usuario' and b.rain_drizzle = '1') then count(user_pseudo_id)
        else 0
    end as visitas_nuevos_usuarios_con_lluvia,
    case
        when (a.tipo_de_visita = 'visita usuario recurrente' and b.rain_drizzle = '0') then count(user_pseudo_id)
        else 0
    end as visitas_usuarios_recurrentes_sin_lluvia,
    case
        when (a.tipo_de_visita = 'visita nuevo usuario' and b.rain_drizzle = '0') then count(user_pseudo_id)
        else 0
    end as visitas_nuevos_usuarios_recurrentes_sin_lluvia,
from
    ga4_data as a
    left join madrid_weather_data as b
    on a.fecha = b.date
group by
    fecha,
    tipo_de_visita,
    rain_drizzle    
)

select 
    fecha,
    sum(visitas_usuarios_recurrentes_con_lluvia) as visitas_usuarios_recurrentes_con_lluvia,
    sum(visitas_nuevos_usuarios_con_lluvia) as visitas_nuevos_usuarios_con_lluvia,
    sum(visitas_usuarios_recurrentes_sin_lluvia) as visitas_usuarios_recurrentes_sin_lluvia,
    sum(visitas_nuevos_usuarios_recurrentes_sin_lluvia) as visitas_nuevos_usuarios_recurrentes_sin_lluvia
from
    final_data
group by 
    fecha         
Los resultados de la anterior consulta:
Detalle de los resultados de la anterior query. Para los más atentos: se me olvidó ordenar los resultados por fecha, es cierto.

Reflexiones finales

Cada vez me gusta más trabajar con BigQuery. Me doy cuenta de que explotar los datos de Google Analytics (ya sean los de GA360 o GA4) a través de este warehouse permite hacer análisis muy elaborados. Explotar tus datos de analítica en bruto, calcular tus propias métricas, objetivos, agrupaciones por dimensiones… BigQuery posibilita hacer análisis adhoc con los datos de tu negocio.

Pero donde realmente veo el potencial de esta herramienta es en la posibilidad de establecer relaciones entre bases de datos. Ya no analizas tus datos de analítica por un lado y los de tu CRM por otro, los relacionas en origen y generas tablas de datos conjuntas.

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