Primeros pasos con GA4 y BigQuery

Para los que, como yo, no hemos tenido demasiadas oportunidades de trabajar con BigQuery hasta ahora, la salida de Google Analytics 4 y su vinculación gratuita con esta herramienta de tratamiento de datos abre un nuevo camino por recorrer. Como seguro sabéis, la sincronización de Universal Analytics con la plataforma de datos de Google solo era posible si se tenía la versión 360, es decir, tenías que pasar por caja. Ahora ya no es así, pero con matices, claro.

La conexión entre Google Analytics 4 y BigQuery se hace en apenas unos clics y, a partir de ese momento, la segunda comenzará a almacenar diariamente los datos de la primera en diferentes tablas, una por día, tal y cómo hacía UA. Ahora bien, BigQuery sigue siendo de pago, lo que no quita que tengamos margen para poder utilizarla con proyectos pequeñitos o para hacer nuestras pruebas.

BigQuery acepta de manera gratuita hasta 10 gigabytes de almacenamiento de datos y 1 terabyte mensual de consultas que se renuevan al mes siguiente. No es un pozo sin fondo pero, como he dicho, no está mal para empezar. Además, la herramienta no es demasiado cara, ya que cada terabyte de consulta extra son $6.25 y cada gigabyte extra de almacenamiento unos $0.020, según indican en sus condiciones. Vamos, que para los que estamos acostumbrados a trabajar con pymes y microempresas, no nos va a salir demasiado caro.

Ahora bien, BigQuery tiene ciertas particularidades que debemos tener en cuenta, como que debes tener alguna noción de SQL para utilizarlo, pues utiliza el lenguaje Standard SQL para desarrollar las consultas sobre los datos en bruto de las tablas. Las consultas SQL son el motor de BigQuery, sin ellas solo tenemos muchas dimensiones y métricas desperdigadas en tablas.

Antes de entrar en materia…

¿Qué es Google BigQuery?

No voy a saber explicarlo mejor que Google, pero sí con mis palabras. Google BigQuery es un almacén de datos, así de simple. Es el lugar donde podemos construir nuestro datalake sin necesidad de estar consultando los datos desde diferentes plataformas y, con lenguaje SQL, realizar las peticiones necesarias para extraer información relevante que nos permita sacar conclusiones y tomar decisiones.

La verdadera ventaja de Google BigQuery es que nos ofrece una gran capacidad de almacenamiento y una velocidad brutal de consultas de información por muy poco.

Cómo conectar GA4 con BigQuery

Conectar Google Analytics 4 con BigQuery es lo más sencillo del mundo, en apenas un par de clics se queda hecho. Ahora bien, primero deberemos tener cuenta en Google Cloud y haber metido nuestros datos de facturación en la plataforma. Pero tranquilos, que si no nos pasamos de los límites gratuitos que he mencionado antes, no pasa ná.

Pues bien, dentro de nuestra cuenta de GA4, deberemos dirigirnos a la configuración de la Propiedad, donde veremos las opciones de conexión con Google Ads y Google BigQuery.

Al pulsar sobre la opción de Vinculación con BigQuery se nos abrirá una ventana donde aparecerá el listado de las vinculaciones existentes. Si no hay ninguna, veremos la siguiente pantalla, en la que tendremos que pulsar sobre el botón azul de Vincular.

Al pulsar comenzaremos el proceso de vinculación, para el cual ya deberemos tener nuestro proyecto de BigQuery creado previamente. Pulsaremos sobre Elegir un proyecto de BigQuery, donde aparecerá el listado de dichos proyectos, debiendo seleccionar al que queremos vincular la cuenta de GA4.

Una vez confirmado el proyecto de BigQuery al que queremos vincular GA4, continuaremos con la configuración seleccionando el flujo de datos que queremos conectar y algunas opciones más. La primera de ellas se encuentra en el check que hay inmediatamente después de seleccionar los flujos de datos que queremos vincular, y trata de incluir los identificadores publicitarios en flujos de aplicaciones móviles. Posteriormente seleccionaremos la frecuencia de actualización de los datos, en mi caso con tener los datos una vez al día me va perfecto.

En el caso de la exportación en tiempo real o en streaming hará que tengamos los datos pocos minutos después de que se generen en GA4, lo que creará dos tablas diferentes en BigQuery, una con esa información en streaming y otra con los datos diarios. Hay que tener en cuenta que, en la primera, tendremos datos sin consolidar, por lo que no será demasiado fiable.

Finalmente, os dejo un vídeo donde podréis ver paso a paso cómo realizar esta vinculación:

Partes importantes de la interfaz de BigQuery

Trabajar en Google BigQuery es bastante fácil en lo que a manejar la herramienta se refiere. Deberemos tener en cuenta algunas partes de su interfaz que nos ayudarán a conseguir los resultados que buscamos, y es que, una vez aprendidos, solo nos quedará perfeccionar nuestro SQL.

Así pues, cuando entramos en BigQuery, dentro de la Google Cloud Platform, lo primero que veremos será un espacio dividido entre las bases de datos dentro de nuestro proyecto y otro donde se visualizan las mismas y el editor SQL.

Si hemos conectado previamente Google Analytics 4 como he explicado, y han pasado algunos días, al desplegar nuestra base de datos veremos un conjunto llamado analytics_num y, en su interior, events_, que contendrá las tablas necesarias para realizar las consultas divididas por días:

En este punto, es importante tener en cuenta las pestañas Esquema, preseleccionado por defecto y que nos permite ver los datos que se almacenan y su tipo; la pestaña Detalles, donde veremos lo que ocupa esa tabla, la cantidad de filas que almacena en ese momento, el ID o la descripción que le pongamos entre otras cosas; y la Vista previa, que nos sirve para ver cómo se almacena la información en el esquema que hemos visto en la pestaña anterior.

Si miramos arriba a la derecha, veremos más pestañas.

• Consultar tabla: será el inicio de cualquier consulta para analizar nuestros datos, y nos abrirá el editor de SQL con un SELECT por defecto apuntando a esa tabla con el que empezar a trabajar.
• Compartir tabla: nos permite gestionar los permisos para compartir la tabla con nuevos usuarios.
• Copiar tabla: nos permite crear una réplica de la tabla actual en el mismo proyecto o en uno diferente.
• Borrar tabla: su propio nombre lo indica.
• Exportar: nos permite visualizar la tabla directamente en Data Studio creando una fuente de BigQuery, guardarla en Google Cloud Storage o examinarla con Google Data Loss Prevention.

Pero en este caso, lo que nos importa es analizar datos, así que nos quedaremos con la primera opción, que es la de Consultar tabla. Al pulsar, como decía, se abrirá el editor directamente:

En la parte superior de la consulta por defecto, veremos el botón Ejecutar, que es al que pulsaremos cuando tengamos nuestra consulta SQL terminada. A la derecha tenemos Guardar, que nos permite guardar la propia consulta o una vista; luego Programación, para la que tendremos que habilitar las consultas programadas, lo que nos permitirá almacenar el contenido de la consulta a la que podemos asignarle una ejecución diaria a una hora determinada; finalmente tenemos el Más, donde podremos dar formato a la consulta con saltos de línea y entrar en la configuración de la misma.

Y ahora, vamos a lo que realmente mola, que es empezar a navegar por los datos almacenados en nuestra BD de Google Analytics 4.

Ejemplo de consultas SQL en BigQuery

Realizar consultas SQL en BigQuery es verdaderamente sencillo, lo único que tendrás que saber manejarte con este lenguaje. Vaya por delante que no me considero ningún experto, pero con poco que sepas, ya puedes sacar resultados interesantes. Lo primero que hay que hacer es comprender la sintaxis que utiliza BigQuery y, a partir de ahí, lanzarse a la aventura de sumergirte en los datos.

En este artículo voy a poner algunos ejemplos de SELECT, y más adelante escribiré otro con más detalles de las diferentes posibilidades que tenemos en BigQuery para tratar los datos. Así pues, vamos con algunos ejemplos interesantes.

Tratamiento de fechas de GA4 en BigQuery

Antes de nada, deberemos tener en cuenta que cuando queremos explorar datos de Google Analytics 4 en BigQuery, nos interesa navegar por diferentes fechas para las cuales tenemos información. Incluso generalmente, querremos ver datos entre dos fechas, por lo que resulta indispensable saber como se manejan en BigQuery. Para ello hay tres formas, pero tenemos que conocer como funciona _TABLE SUFFIX.

SELECT
  *
FROM
  `base_de_datos.analytics_xxxxxx.events_*`
WHERE
  _TABLE_SUFFIX ='20210101';

Si os fijáis en la consulta, veréis que en la base de datos después del FROM, he añadido un asterisco o wildcard (*) donde debería ir la fecha. Esto es una especie de comodín cuyo contenido se almacena en _TABLE_SUFFIX, parámetro que podremos utilizar después del WHERE para establecer condiciones de fechas, tal y como en el ejemplo.

Ahora bien, existen tres opciones para tratar con fechas. La primera es similar a la de arriba y, simplemente, utilizamos la instrucción BETWEEN del WHERE para establecer una fecha inicial y otra final para la que querremos extraer los datos:

SELECT
  *
FROM
  `base_de_datos.analytics_xxxxxx.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20210101'
  AND '20210131';

La segunda opción es tratar las fechas de manera flexible, por ejemplo, desde ayer que tengamos la información de los 30 días anteriores:

SELECT
  *
FROM
  `base_de_datos.analytics_xxxxxx.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
  AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY));

En este caso, lo importante es, de nuevo, la condición tras el WHERE. En este caso especificamos que _TABLE_SUFFIX esté entre una fecha cuyo formato sea YYYYMMDD y sea exactamente la de hace 30 días. Esto último lo hacemos extrayendo la fecha inicial con DATE_SUB, especificando que a la fecha de hoy hay que restarle 30 días. DATE_SUB nos da la posibilidad de trabajar con diferentes intervalos, algo muy útil si queremos ver datos con cuatrimestres, años, semanas, etc. Después del AND volvemos a utilizar la misma estructura pero indicando que la fecha final sea la de ayer (INTERVAL 1 DAY).

La tercera forma es mezclando las dos anteriores:

SELECT
  *
FROM
  `base_de_datos.analytics_xxxxxx.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20210101'
  AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY));

Aquí simplemente hemos indicado que la fecha inicial sea fija, poniendo cuál es, y la final sea variable hasta el día de ayer.

Consulta simple con agrupación de dimensión

SELECT
  traffic_source.name AS Canal,
  COUNT(DISTINCT(user_pseudo_id)) AS Usuarios
FROM
  `base_de_datos.analytics_xxxxxx.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20210101'
  AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY
  Canal
ORDER BY
  Usuarios;

Algunas cuestiones interesantes de la consulta de arriba:

• traffic_souce.name: es el parámetro que contiene el nombre de los canales, para conocer los parámetros de GA4 bien podemos analizar un poco la tabla en la que estamos, o echar un vistazo a la documentación.
• COUNT(): simplemente es la instrucción que sirve para contar elementos.
• DISTINCT(): extrae valores diferentes, es decir, si hay alguno repetido, solo lo cuenta 1 vez.
• user_pseudo_id: otro parámetro de GA4, en este caso sería el equivalente al Client ID de UA, es decir, el usuario. Contando los user_pseudo_id diferentes estamos contando el número de usuarios.
• AS: sirve para establecer un alias, es decir, darle un nombre más amigable a la información.
• GROUP BY: agrupamos por la dimensión, en este caso los canales. Acabaréis acostumbrados a agrupar, ya que es necesario para listar datos por dimensión.
• ORDER BY: establecemos un orden a la información, ASC o DESC, por defecto es DESC.

El resultado sería el siguiente:

Para acabar el ladrillo que os acabo de soltar, no me queda otra cosa que recomendaros ga4bigquery.com, una web sobre consejos para trabajar con GA4 en BigQuery bastante guay que os puede servir para aprender. Además, por supuesto, de la documentación oficial de BigQuery.

Espero que el contenido os sirva. ¡Nos leemos!