Una vez que conectamos nuestra propiedad de GA4 con BigQuery, podemos comenzar a procesar los datos y ejecutar consultas para generar tablas preparadas para informes personalizados.
Después de saber como desanidar los event_params, vamos a ver como utilizar otras funcionalidades de esta herramienta.
Dado que los datos se actualizan constantemente (diariamente o en tiempo real si usas una conexión en streaming), no es eficiente actualizar manualmente las tablas e informes cada día.
Aquí es donde BigQuery ofrece una funcionalidad clave: la programación de consultas. Esta herramienta te permite automatizar la actualización de las tablas generadas, asegurando que siempre trabajes con datos actualizados de manera automática.
Después de saber como desanidar los event_params, vamos a ver como utilizar otras funcionalidades de esta herramienta.
Dado que los datos se actualizan constantemente (diariamente o en tiempo real si usas una conexión en streaming), no es eficiente actualizar manualmente las tablas e informes cada día.
Aquí es donde BigQuery ofrece una funcionalidad clave: la programación de consultas. Esta herramienta te permite automatizar la actualización de las tablas generadas, asegurando que siempre trabajes con datos actualizados de manera automática.
Índice:
Antes de avanzar, es importante entender qué es una tabla en BigQuery.
Básicamente, una tabla es un conjunto de datos que puede provenir directamente de una fuente conectada, como Google Analytics o Search Console, o bien de datos que han sido extraídos, procesados y refinados desde estas fuentes originales.
En otras palabras, una tabla puede contener datos brutos o ya trabajados para facilitar su análisis y reporte según las necesidades de tu proyecto.
Básicamente, una tabla es un conjunto de datos que puede provenir directamente de una fuente conectada, como Google Analytics o Search Console, o bien de datos que han sido extraídos, procesados y refinados desde estas fuentes originales.
En otras palabras, una tabla puede contener datos brutos o ya trabajados para facilitar su análisis y reporte según las necesidades de tu proyecto.
Imaginemos que queremos crear una tabla que incluya ciertos datos específicos de Google Search Console.
Para ello, podemos realizar una consulta simple que extraiga los datos que necesitamos de esa fuente. Este tipo de consulta permite seleccionar únicamente los parámetros que son relevantes para nuestro análisis, organizándolos en una tabla que estará disponible para futuras consultas o informes.
Esta técnica es útil cuando queremos procesar grandes volúmenes de datos y obtener insights de manera más eficiente y automatizada.
Pero, es posible que queramos que esa consulta se ejecute de forma periódica sin tener que repetir el proceso manualmente.
Imagina que tienes una query como la siguiente:
Para ello, podemos realizar una consulta simple que extraiga los datos que necesitamos de esa fuente. Este tipo de consulta permite seleccionar únicamente los parámetros que son relevantes para nuestro análisis, organizándolos en una tabla que estará disponible para futuras consultas o informes.
Esta técnica es útil cuando queremos procesar grandes volúmenes de datos y obtener insights de manera más eficiente y automatizada.
Pero, es posible que queramos que esa consulta se ejecute de forma periódica sin tener que repetir el proceso manualmente.
Imagina que tienes una query como la siguiente:
/*
Search console: querys, impresiones y clics*/SELECTquery,impressions,clicksFROM`tu_web.searchconsole.searchdata_site_impression` Si queremos que nuestra consulta se actualice de forma periódica, diariamente, por ejemplo, BigQuery lo permite de manera sencilla.
Solo necesitamos hacer clic en el botón 'Programación':
Programar Consulta Sin Particionar
Al hacerlo, aparecerá a continuación una barra lateral en la que podremos seleccionar la periodicidad de la actualización, en conjunto de datos y tabla de destino, la ubicación de los datos y si la consulta va a agregar datos o a reemplazarlos con cada ejecución. Este punto es importante ya que son comportamientos muy distintos.
Si elegimos la opción de "agregar datos", cada día que se ejecute ira añadiendo nuevos datos a la tabla. Si elegimos la opción de reemplazar los datos, cada día que se ejecute la consulta programa, borrará los datos existentes en la tabla y los reemplazará con los nuevos.
Esto nos permitirá configurar la frecuencia con la que se ejecutará la consulta, automatizando la creación y actualización de la tabla sin intervención manual.
Si el volumen de tus datos no es muy grande esta opción puede ser valida. Pero si el volumen de datos de tu pagina es muy grande, esta opción te dará problemas a la larga: si metes todos tu datos en la tabla, esta cada vez será mas grande, por lo que el volumen de datos a ejecutar cada día será más grande, y no tenemos que olvidar que BigQuery es una herramienta de pago, y uno de los costes que tiene es el relacionado con el procesado de datos. Si no tenemos cuidado, cada día cada consulta nos costará más y más dinero.
La solución en este caso será "particionar".
Si elegimos la opción de "agregar datos", cada día que se ejecute ira añadiendo nuevos datos a la tabla. Si elegimos la opción de reemplazar los datos, cada día que se ejecute la consulta programa, borrará los datos existentes en la tabla y los reemplazará con los nuevos.
Esto nos permitirá configurar la frecuencia con la que se ejecutará la consulta, automatizando la creación y actualización de la tabla sin intervención manual.
Si el volumen de tus datos no es muy grande esta opción puede ser valida. Pero si el volumen de datos de tu pagina es muy grande, esta opción te dará problemas a la larga: si metes todos tu datos en la tabla, esta cada vez será mas grande, por lo que el volumen de datos a ejecutar cada día será más grande, y no tenemos que olvidar que BigQuery es una herramienta de pago, y uno de los costes que tiene es el relacionado con el procesado de datos. Si no tenemos cuidado, cada día cada consulta nos costará más y más dinero.
La solución en este caso será "particionar".
Particionar una tabla en BigQuery consiste en dividir una tabla grande en partes más pequeñas y fáciles de manejar.
Imagínatelo como un libro. Cada día escribes una hoja y la metes dentro del libro, con el paso de los días tendrás un libro un poco más grande, pero cada hoja solo contendrá lo que escribiste un día. Fácilmente podrías ir a buscar lo que escribiste un día concreto, encontrar y leer solo esa hoja.
Imagina que en lugar de un libro, toda la información estuviera en un papiro. Cada día, al añadir más datos, el papiro se haría más grande, lo que dificultaría su transporte, lectura y acceso a un punto específico. Para encontrar un dato concreto, tendrías que desenrollar el papiro completo, lo que complicaría aún más el proceso.
Para evitar esta situación, lo que hacemos con las tablas es particionarlas.
Lo habitual será particionar por la fecha, pero según tus necesidades puedes tener que particionar por meses o años. El procedimiento será similar en cada caso.
El procedimiento a seguir constara de dos pasos.
Imagínatelo como un libro. Cada día escribes una hoja y la metes dentro del libro, con el paso de los días tendrás un libro un poco más grande, pero cada hoja solo contendrá lo que escribiste un día. Fácilmente podrías ir a buscar lo que escribiste un día concreto, encontrar y leer solo esa hoja.
Imagina que en lugar de un libro, toda la información estuviera en un papiro. Cada día, al añadir más datos, el papiro se haría más grande, lo que dificultaría su transporte, lectura y acceso a un punto específico. Para encontrar un dato concreto, tendrías que desenrollar el papiro completo, lo que complicaría aún más el proceso.
Para evitar esta situación, lo que hacemos con las tablas es particionarlas.
Lo habitual será particionar por la fecha, pero según tus necesidades puedes tener que particionar por meses o años. El procedimiento será similar en cada caso.
El procedimiento a seguir constara de dos pasos.
La consulta la vamos a estructurar de manera habitual, seleccionando las variables y métricas necesarias.
En este caso, usaremos los datos exportados de Google Analytics 4, pero con una pequeña variación: en lugar de especificar una fecha concreta en el nombre de la tabla, reemplazaremos la fecha final con un '*' (comodín). Esto permite que la consulta abarque todos los datos sin necesidad de indicar una fecha específica, facilitando la creación de informes automatizados y actualizados con cada ejecución.
En este caso, usaremos los datos exportados de Google Analytics 4, pero con una pequeña variación: en lugar de especificar una fecha concreta en el nombre de la tabla, reemplazaremos la fecha final con un '*' (comodín). Esto permite que la consulta abarque todos los datos sin necesidad de indicar una fecha específica, facilitando la creación de informes automatizados y actualizados con cada ejecución.
/*
Particionar por fecha*/SELECT DISTINCTevent_date,event_name,(SELECT value.sting_value FROM UNNEST(event_params) where KEY = "variable") as variableFROM´miproyecto.mi_google_analytics_4.events_*´WHERE _table_suffix = FORMAT_DATE('%Y%m%d', DATE_SUB(@run_date, INTERVAL 1 DAY))Con esta acción, estamos indicando que vamos a utilizar cualquier fecha presente en el conjunto de datos de GA4. El asterisco (*) actúa como un comodín.
Ahora, debemos definir el valor de ese comodín. Esto se hace en la parte del filtrado de datos, en la cláusula WHERE. Allí debemos especificar que la tabla tiene un comodín al final, el cual definiremos. El nombre de este comodín será _table_suffix. Para asignarle un valor, lo haremos de la siguiente manera:
Ahora, debemos definir el valor de ese comodín. Esto se hace en la parte del filtrado de datos, en la cláusula WHERE. Allí debemos especificar que la tabla tiene un comodín al final, el cual definiremos. El nombre de este comodín será _table_suffix. Para asignarle un valor, lo haremos de la siguiente manera:
- FORMAT_DATE: Con esta función, definimos que una fecha tiene un formato determinado. Puedes consultar la referencia de esta función en este enlace Date functions.
- '%Y%m%d': Aquí definimos el tipo de fecha que vamos a recibir. En este caso, el formato es YYYYMMDD. Puedes consultar todos los posibles valores de formato en este enlace Format elements.
- DATE_SUB(@run_date, INTERVAL 1 DAY): DATE_SUB Es una función que resta un intervalo de tiempo a una fecha. @run_date, es una variable que contiene una fecha. INTERVAL 1 DAY especifica el intervalo de tiempo que se va a restar. En este caso, es un día. Entonces, DATE_SUB(@run_date, INTERVAL 1 DAY) devuelve la fecha que es un día antes de la fecha almacenada en @run_date.
- Haz clic en 'Programación'.
- Asigna un nombre a la consulta programada.
- Elige la periodicidad de la ejecución.
- Selecciona el conjunto de datos de destino.
- Al nombrar la tabla, asignaremos el valor al parámetro @run_date que hemos definido en la consulta.
Programar Consulta Particionada
Al incluir {run_time-24h|"%Y%m%d"}, estamos indicando que el parámetro @run_time toma la fecha de ejecución, resta 24 horas y formatea la fecha resultante en YYYYMMDD. Puedes modificar este parámetro según tus necesidades y objetivos.
Además, al seleccionar "Reemplazar tabla", justo debajo del nombre de la tabla, si cometemos un error y necesitamos corregirlo, simplemente debemos volver a ejecutar la consulta para la fecha deseada.
Cada vez que se ejecute la programación, se creará una nueva tabla con la fecha correspondiente a los datos. Estas tablas se apilarán una encima de otra y se particionarán por fecha para facilitar, acelerar y hacer más eficiente el acceso a los datos.
Además, al seleccionar "Reemplazar tabla", justo debajo del nombre de la tabla, si cometemos un error y necesitamos corregirlo, simplemente debemos volver a ejecutar la consulta para la fecha deseada.
Cada vez que se ejecute la programación, se creará una nueva tabla con la fecha correspondiente a los datos. Estas tablas se apilarán una encima de otra y se particionarán por fecha para facilitar, acelerar y hacer más eficiente el acceso a los datos.
Con este procedimiento, ya estás en condiciones de generar consultas y programarlas de manera eficiente para que se ejecuten automáticamente.
Esto significa que no solo podrás obtener datos actualizados sin intervención manual, sino que también podrás personalizar la frecuencia y el alcance de las consultas según los requerimientos específicos de tu proyecto. Al automatizar este proceso, optimizas el tiempo y los recursos, asegurando que siempre trabajes con la información más reciente y relevante.
Además, al particionar las tablas, facilitas el manejo de grandes volúmenes de datos, lo que resulta en un acceso más rápido y eficiente a la información necesaria para tomar decisiones informadas.
En resumen, este enfoque te proporciona una herramienta poderosa para gestionar y analizar datos de manera continua y adaptativa, mejorando así la calidad y la precisión de tus informes y análisis.
