Google BigQuery es una base de datos en la nube popular utilizada por empresas de todo el mundo. Es especialmente conveniente para trabajar con datos brutos de Google Analytics: en GA 360, la integración con BigQuery se configura en unos pocos clics, y para la versión gratuita hay scripts y módulos de terceros.
En los datos sin procesar de Google Analytics, cada registro (fila) corresponde a una sesión. Dentro de dicha entrada hay campos anidados que corresponden a los hits de la sesión:
A menudo, esta estructura de datos anidados es confusa para los usuarios que no entienden cómo trabajar con ellos y descargar dichos datos.
Usando los parámetros personalizados de Google Analytics como ejemplo, intentaré explicar "con los dedos" cómo se almacenan los datos incrustados en Google BigQuery y cómo se pueden cargar.
Los códigos de consulta dados funcionan, se pueden usar para resolver problemas, sustituyendo los nombres de sus tablas y los números de índice de Dimensiones personalizadas requeridos.
• Líneas de descarga• Descarga mientras se mantiene la estructura de anidamiento• Ejemplo de reemplazo de valores de parámetros de usuarioLos fundamentos
BigQuery admite 2 dialectos de SQL: Legacy y Standard. Google recomienda utilizar el estándar SQL más nuevo, en el que escribiremos consultas para descargar.
Todos los que han trabajado con SQL al menos un poco conocen la construcción de consulta estándar:
SELECT * * FROM * * WHERE * *
Este diseño funciona si la estructura de la tabla es simple, sin otros campos anidados en las celdas:
Consideramos tablas con campos anidados. La estructura de dicha tabla (por ejemplo, parámetros de usuario de Google Analytics):
Opciones de GA personalizadas en BQEn Google BigQuery, dicha tabla tendrá los siguientes nombres de columna (el separador "." Muestra la estructura de anidación):
Entonces, ¿cómo descargamos datos de campos anidados?
Descargar filas
Regrese a la
tabla con un ejemplo de parámetros personalizados de GA en BQ.
Las columnas
customDimensions.index y
customDimensions.value son los índices y valores de la
sesión y
las Dimensiones personalizadas
definidas por el usuario .
Las columnas
hits.customDimensions.index y
hits.customDimensions.value son los índices y valores de las Dimensiones personalizadas de
hit .
En Google BigQuery hay otro nivel de acción de parámetros de usuario: el producto. Los nombres y valores de las Dimensiones personalizadas del
producto en Google BigQuery se encuentran en las columnas
hits.product.customDimensions.index y
hits.product.customDimensions.value . Se descargan por analogía con parámetros de usuario exitosos, solo necesita tener en cuenta otro nivel de anidamiento.
Opciones de usuario de sesión y nivel de usuario
¿Qué debemos hacer si necesitamos descargar los valores de las Dimensiones personalizadas de sesión (personalizadas) para cada fecha sin guardar la estructura anidada (es decir, línea por línea)?
Para responder la pregunta, echemos un vistazo más de cerca a la
tabla con los parámetros de usuario de GA en BQ.
Muestra que los valores de las celdas en la columna customDimensions
son otra tabla :
Es suficiente hacer una subconsulta a esta tabla en la consulta principal:
SELECT
El resultado es una tabla:
Si necesitamos agregar una columna con el valor de otro parámetro de usuario, hacemos otra subconsulta:
SELECT date, (SELECT value FROM t.customDimensions WHERE index = 1) AS customDimensions1,
Obtenemos lo siguiente:
Opciones personalizadas de nivel de éxito
Los parámetros de usuario de hit se descargan de la misma manera que los de sesión (usuario), excepto que la subconsulta debe realizarse en la tabla de hits anidada. En otras palabras, los valores de las celdas de la columna de aciertos en la tabla de datos sin procesar de Google Analytics son una tabla anidada en la que se anida la tabla customDimensions:
La solicitud para descargar parámetros de usuario de hit línea por línea será la siguiente:
SELECT
El resultado de la consulta será una tabla:
Puede descargar varios parámetros de usuario de hit y agregar el parámetro hitNumber (el número de secuencia del hit en la sesión):
SELECT date, h.hitNumber AS hitNumber, (SELECT value FROM h.customDimensions WHERE index = 3) AS customDimensions3,
Consigue la mesa:
Sesión (usuario) + parámetros de usuario de hit
Si en una consulta queremos descargar la sesión y presionar los parámetros del usuario, solo necesita hacer las subconsultas necesarias para las tablas principales y anidadas:
SELECT date, h.hitNumber AS hitNumber,
La tabla que se obtendrá como resultado de la consulta:
Descarga mientras se mantiene la estructura de anidación
Tal descarga puede ser necesaria al reemplazar los valores de un parámetro de usuario en Google BigQuery.
Ejemplo
En Google Analytics, el nombre del país en formato completo se transfiere al parámetro de usuario de sesión con el índice 12 y al parámetro de usuario afectado con el índice 25 para usuarios de Rusia: RUSIA. Es necesario cambiar el formato del país a abreviado: RUS.Para hacer esto, debe reemplazar los valores requeridos del parámetro del usuario con el país del usuario para todo el historial de datos en Google BigQuery.
El procedimiento para resolver el problema:
- Descargue todos los datos mientras mantiene la estructura de anidamiento
- Reemplace el valor del parámetro de usuario con el país
- Reescribe la tabla
Para cargar datos manteniendo la estructura de anidamiento, debe usar la función ARRAY y la construcción SELECT AS STRUCT. Averigüemos qué es.
La sintaxis de la función ARRAY es la siguiente:
ARRAY(**)
Devuelve una matriz de elementos.
Comparación de una matriz con grabación línea por línea:
A la izquierda hay una matriz, a la derecha hay una grabación de línea.Si queremos guardar una estructura anidada y descargar una matriz con varias columnas, debemos usar
ARRAY (SELECT AS STRUCT ...) :
Matriz AnidadaOpciones de usuario de sesión y nivel de usuario
Para descargar mientras se mantiene la estructura de dimensiones personalizadas de sesión (personalizadas), utilizamos la consulta:
SELECT date,
Como resultado de su ejecución, se obtiene una tabla en la que se guarda la estructura de anidación de los datos "brutos" de Google Analytics:
Opciones personalizadas de nivel de éxito
Para descargar los valores de los parámetros de usuario de hit de Google BigQuery mientras se mantiene la estructura de anidación, es importante tener en cuenta que la tabla customDimensions está anidada en la tabla de hits. En otras palabras, debe hacer la subconsulta ARRAY 2 veces (SELECT AS STRUCT ...): primero a la tabla de resultados anidados, luego a la tabla customDimensions anidada en ella:
SELECT date,
El resultado de esta consulta será una tabla:
Sesión (usuario) + parámetros de usuario de hit
Al igual que con la descarga línea por línea, necesitamos combinar las subconsultas ARRAY (SELECT AS STRUCT ...) en la misma consulta a las tablas anidadas deseadas:
SELECT date,
¿Cuál es el resultado?
Ejemplo de reemplazo de valores de parámetros de usuario
Volvamos a nuestro
ejemplo .
En la sección anterior, recibimos una solicitud para cargar sesión (usuario) y atacamos los parámetros de usuario de Google Analytics mientras mantenemos la estructura de anidamiento.
Complementamos esta consulta con construcciones SELECT * REPLACE para descargar con reemplazo y CASE para actualizar los valores de los parámetros de usuario necesarios:
Como resultado de esta solicitud, obtendremos la tabla original con los datos sin procesar de Google Analytics. Ella retendrá por completo la estructura de anidación original, pero los valores de los parámetros de usuario necesarios se cambiarán por otros nuevos.
El tema de trabajar con una estructura de datos anidados en Google BigQuery no es fácil.
Espero haber logrado aclarar este problema. Pero, permíteme recordarte que la mejor manera de aprender a hacer algo es practicar más.