Google BigQuery: Vinculación con Analytics 360 y exportación de datos

En este post daremos una visión global acerca de la herramienta Google BigQuery.  Enlazar BigQuery a Google Analytics 360 y conocer la infraestructura y tipología de las tablas exportadas serán algunos de los temas que abordaremos.

Google BigQuery es el almacén de datos en crudo de Google Analytics. En su interfaz será posible hacer consultas de datos históricos mediante lenguaje SQL, cargar y exportar datos. Además, y yendo más allá de Google Analytics, se podrán unificar en las queries datos de origen puramente web junto a otros datasets externos: datos offline o incluso información explícita de los usuarios web que únicamente tenemos alojada en los CRM internos.

VINCULACIÓN CON GOOGLE ANALYTICS 360. OPCIONES DE EXPORTACIÓN.

Comenzamos por la propia vinculación con Analytics, resumida en estos sencillos pasos:

  1. Creamos un proyecto en la consola de Google Cloud, al cual se asociará todo el histórico de la facturación.
  2. En la administración de APIs, activar la de BigQuery.
  3. Habilitar la facturación del proyecto.
  4. Entre las opciones de Administrador de Analytics, a nivel de propiedad, activar el link con BigQuery dentro de Todos los productos.

Link Analytics 360

Seleccionamos el proyecto creado en el paso 1. A continuación seleccionamos la vista a través de la cual se hará la exportación de los datos a BigQuery, siendo recomendable utilizar la vista más limpia de filtros (en BigQuery siempre estarás a tiempo de añadir filtros). Finalmente, seleccionamos el modo de exportación: streaming data (opción añadida recientemente) o batch data.

Link Analytics 360

Con la opción en streaming, los datos serán exportados a BigQuery cada 10 o 15 minutos. Sin embargo, existen ciertas limitaciones al activar esta opción, como la exportación de los datos que provienen de Adwords o DoubleClick.

Para más información, consúltese estos dos enlaces: crear y administrar proyectos, configurar exportaciones a BigQuery y cuotas y límites de facturación.

INFRAESTRUCTURA DE LAS TABLAS DE DATOS EN GOOGLE BIGQUERY

Como apunte preliminar os recordamos, en el siguiente orden jerárquico, la ubicación o el “directorio” al cual se exportarán las tablas de datos en Google BigQuery.

BigQuery

Éstas se organizan en datasets dentro del proyecto seleccionado.

Tras completar la acción del linkado, únicamente veremos dentro del proyecto un único dataset (por defecto tendrá el nombre del ID de la vista linkada) compuesto del histórico de tablas diarias cerradas ga_sessions_YYYYMMDD. Sin embargo, al activar en Analytics 360 la opción streaming, nos encontraremos con un par de tablas más:

BigQuery - Analytics

¿En qué se diferencian? En ambas tablas los datos se refrescan cada 10 o 15 minutos, sin embargo las tablas ga_realtime_sessions_view tienen un poco más de delay para consolidarse, pues requieren de cierto procesamiento en los datos. El procesamiento consiste en hacer un pequeño filtrado de aquellas sesiones que fueron afectadas en el momento del refresco de la tabla, es decir, deduplicación de hits en el caso de que los hubiera.

CRUCE CON OTRAS FUENTES DE DATOS

Existen diferentes alternativas para combinar los datos de Analytics con otras fuentes dentro de Google BigQuery. Podríamos pensar en Google Drive, y hacer una consulta donde combinemos campos comunes de las tablas ga_sessions_* y el archivo(s) de Drive. Sin embargo, esta tarea no es la más eficiente, pues consultar datos almacenados en Google Cloud Storage es más rápido.

Nota importante 1: Google BigQuery no admite trabajar en una misma consulta SQL con dos fuentes cuya ubicación geográfica es distinta. Por lo que es obligatorio comprobar la ubicación geográfica de todas las fuentes de datos, tanto las procedentes de Analytics como fuentes externas (por defecto, tienen la de EE.UU).

Nota importante 2: al igual que las tablas ga_sessions_* tienen como sufijo la fecha de la tabla, la nomenclatura a seguir para hacer la importación de otra fuente de datos externa ha de seguir la misma estructura (en el supuesto de que se importen CSVs diarios, semanales, trimestrales, etcétera).

Como consultora, pongámonos en la situación de que un cliente quiere combinar sus datos de Google Analytics con los datos de su CRM. Un posible proceso a seguir para hacer el cruce de ambas fuentes de datos en una única consulta, podría ser el siguiente:

  1. Elaborar un script en R o en Python.
  2. Conectar con un FTP donde el cliente dejará todos los días el archivo diario correspondiente.
  3. Descargar el último fichero.
  4. Hacer la subida de 3. vía API a la storage de Google Cloud.

INTEGRACIÓN DE AMBOS DATASETS CON GOOGLE BIGQUERY

Con las tablas de Analytics y las del CRM para la semana anterior cerrada, se podría utilizar el wildcard _TABLE_SUFFIX para escoger únicamente las tablas de interés. Un pseudocódigo de la consulta SQL Standard a emplear podría ser el siguiente:

SELECT tabla1.VariableCruce, tabla1.metricX, tabla2.metricY, tabla2.dimensionX
FROM(

SELECT VariableCruce, metricX
FROM `Project.dataset.ga_sessions_*` BETWEEN
REGEXP_REPLACE(CAST(DATE_SUB(CURRENT_DATE(), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) + 5 DAY) AS STRING), ‘-‘, ”)
AND
REGEXP_REPLACE(CAST(DATE_SUB(CURRENT_DATE(), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) – 1 DAY) AS STRING), ‘-‘, ”)

) AS tabla1
JOIN(

SELECT VariableCruce, metricY, dimensionX
FROM ` Project.dataset.fuentededatos_*` BETWEEN
REGEXP_REPLACE(CAST(DATE_SUB(CURRENT_DATE(), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) + 5 DAY) AS STRING), ‘-‘, ”)
REGEXP_REPLACE(CAST(DATE_SUB(CURRENT_DATE(), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) – 1 DAY) AS STRING), ‘-‘, ”)

) AS tabla2
ON tabla1.VariableCruce = tabla2.VariableCruce

 

Decidir el tipo de JOIN que se debe hacer, corre a cargo del analista.

Desde luego, Google BigQuery es una buena alternativa para gestionar diversas fuentes de datos. Representar todas éstas conjuntamente en un dashboard puede dar mucho juego, ¡y muchos insights!

 

Autor:

Digital Analytics Consultant at Metriplica. Mathematician // M.Sc. Data Analysis and Statistics

3 Comments

  1. semmejames

    Mario Martínez, thank you for this post. Its very inspiring.

Leave Comment

Your email address will not be published. Required fields are marked *