SQL estándar en Google BigQuery: ventajas y ejemplos de uso en marketing

En 2016, Google BigQuery introdujo una nueva forma de comunicarse con tablas: SQL estándar. Hasta entonces, BigQuery tenía su propio lenguaje de consulta estructurado llamado BigQuery SQL (ahora llamado Legacy SQL).

A primera vista, no hay mucha diferencia entre Legacy y Standard SQL: los nombres de las tablas se escriben un poco diferente; Estándar tiene requisitos gramaticales ligeramente más estrictos (por ejemplo, no puede poner una coma antes de DE) y más tipos de datos. Pero si observa detenidamente, hay algunos cambios de sintaxis menores que brindan muchas ventajas a los especialistas en marketing.

En este artículo, obtendrá respuestas a las siguientes preguntas:

• ¿Cuáles son las ventajas de SQL estándar sobre SQL heredado?
• ¿Cuáles son las capacidades de SQL estándar y cómo se utiliza?
• ¿Cómo puedo pasar de Legacy a Standard SQL?
• ¿Con qué otros servicios, características de sintaxis, operadores y funciones es compatible SQL estándar?
• ¿Cómo puedo usar consultas SQL para informes de marketing?

SQL estándar admite nuevos tipos de datos: ARRAY y STRUCT (matrices y campos anidados). Esto significa que en BigQuery ahora es más fácil trabajar con tablas cargadas desde archivos JSON/Avro, que a menudo contienen archivos adjuntos de varios niveles.

Un campo anidado es una mini tabla dentro de una más grande:

En el diagrama anterior, las barras azules y amarillas son las líneas en las que están incrustadas las minimesas. Cada línea es una sesión. Las sesiones tienen parámetros comunes: fecha, número de cédula, categoría de dispositivo del usuario, navegador, sistema operativo, etc. Además de los parámetros generales de cada sesión, se adjunta a la línea la tabla de aciertos.

La tabla de resultados contiene información sobre las acciones del usuario en el sitio. Por ejemplo, si un usuario hace clic en un banner, hojea el catálogo, abre una página de producto, coloca un producto en la cesta o realiza un pedido, estas acciones se registrarán en la tabla de aciertos.

Si un usuario realiza un pedido en el sitio, la información sobre el pedido también se ingresará en la tabla de resultados:

• transaccionId (número que identifica la transacción)
• transactionRevenue (valor total del pedido)
• transacciónEnvío (gastos de envío)

Las tablas de datos de sesión recopiladas con OWOX BI tienen una estructura similar.

Suponga que desea saber el número de pedidos de los usuarios de la ciudad de Nueva York durante el último mes. Para averiguarlo, debe consultar la tabla de aciertos y contar la cantidad de ID de transacción únicos. Para extraer datos de tales tablas, Standard SQL tiene una función UNNEST:

Si la información del pedido se registró en una tabla separada y no en una tabla anidada, tendría que usar JOIN para combinar la tabla con la información del pedido y la tabla con los datos de la sesión para averiguar en qué sesiones se realizaron los pedidos.

Si necesita extraer datos de campos anidados de varios niveles, puede agregar subconsultas con SELECT y WHERE. Por ejemplo, en las tablas de transmisión de sesiones de BI de OWOX, otra subtabla, producto, se escribe en la subtabla de visitas. La subtabla de productos recopila datos de productos que se transmiten con una matriz de comercio electrónico mejorado. Si se configura un comercio electrónico mejorado en el sitio y un usuario ha mirado la página de un producto, las características de este producto se registrarán en la subtabla del producto.

Para obtener estas características del producto, necesitará una subconsulta dentro de la consulta principal. Para cada característica del producto, se crea una subconsulta SELECT separada entre paréntesis:

Gracias a las capacidades de SQL estándar, es más fácil crear lógica de consulta y escribir código. A modo de comparación, en Legacy SQL, necesitaría escribir este tipo de escalera:

Con SQL estándar, puede acceder a las tablas de BigQuery directamente desde Google Bigtable, Google Cloud Storage, Google Drive y Google Sheets.
Es decir, en lugar de cargar toda la tabla en BigQuery, puede eliminar los datos con una sola consulta, seleccionar los parámetros que necesita y cargarlos en el almacenamiento en la nube.

Si necesita usar una fórmula que no está documentada, las funciones definidas por el usuario (UDF) lo ayudarán. En nuestra práctica, esto sucede rara vez, ya que la documentación de SQL estándar cubre casi todas las tareas de análisis digital.

En SQL estándar, las funciones definidas por el usuario se pueden escribir en SQL o JavaScript; SQL heredado solo admite JavaScript. Los argumentos de estas funciones son columnas, y los valores que toman son el resultado de manipular columnas. En SQL estándar, las funciones se pueden escribir en la misma ventana que las consultas.

En Legacy SQL, las condiciones de JOIN se pueden basar en igualdad o nombres de columna. Además de estas opciones, el dialecto SQL estándar admite JOIN por desigualdad y por expresión arbitraria.

Por ejemplo, para identificar socios de CPA desleales, podemos seleccionar sesiones en las que la fuente se reemplazó dentro de los 60 segundos posteriores a la transacción. Para hacer esto en SQL estándar, podemos agregar una desigualdad a la condición JOIN:

La única limitación de Standard SQL con respecto a JOIN es que no permite semi-join con subconsultas de la forma WHERE column IN (SELECT...):

Algunas funciones en Legacy SQL devuelven NULL si la condición es incorrecta. Por ejemplo, si la división por cero se ha infiltrado en sus cálculos, la consulta se ejecutará y aparecerán entradas NULL en las filas resultantes de la tabla. Esto puede enmascarar problemas en la consulta o en los datos.

La lógica de SQL estándar es más sencilla. Si una condición o dato de entrada es incorrecto, la consulta generará un error, por ejemplo “división por cero”, para que puedas corregir rápidamente la consulta. Las siguientes comprobaciones están integradas en SQL estándar:

• Valores válidos para +, -, ×, SUM, AVG, STDEV
• División por cero

Las consultas JOIN escritas en SQL estándar son más rápidas que las escritas en SQL heredado gracias al filtrado preliminar de los datos entrantes. Primero, la consulta selecciona las filas que coinciden con las condiciones de JOIN y luego las procesa.
En el futuro, Google BigQuery trabajará para mejorar la velocidad y el rendimiento de las consultas solo para SQL estándar.

Las funciones del lenguaje de manipulación de datos (DML) están disponibles en SQL estándar. Esto significa que puede actualizar tablas y agregar o eliminar filas de ellas a través de la misma ventana en la que escribe consultas. Por ejemplo, al usar DML, puede combinar datos de dos tablas en una sola:

Con SQL estándar, las consultas complejas se pueden iniciar no solo con SELECT sino también con WITH, lo que hace que el código sea más fácil de leer, comentar y comprender. Esto también significa que es más fácil prevenir los propios y corregir los errores de los demás.

Es conveniente trabajar con el operador CON si tiene cálculos que se realizan en varias etapas. Primero, puede recopilar métricas intermedias en subconsultas y luego hacer los cálculos finales.

Google Cloud Platform (GCP), que incluye BigQuery, es una plataforma de ciclo completo para trabajar con big data, desde organizar un almacén de datos o una nube de datos hasta ejecutar experimentos científicos y análisis predictivos y prescriptivos. Con la introducción de SQL estándar, BigQuery amplía su audiencia. Trabajar con GCP es cada vez más interesante para los analistas de marketing, los analistas de productos, los científicos de datos y los equipos de otros especialistas.

En OWOX BI, a menudo trabajamos con tablas compiladas utilizando la exportación estándar de Google Analytics 360 a Google BigQuery o OWOX BI Pipeline. En los ejemplos a continuación, veremos los detalles de las consultas SQL para dichos datos.

Si aún no recopila datos de su sitio en BigQuery, puede intentar hacerlo de forma gratuita con la versión de prueba de OWOX BI.

En Google BigQuery, los datos de comportamiento de los usuarios de su sitio se almacenan en tablas comodín (tablas con un asterisco); se forma una tabla separada para cada día. Estas tablas tienen el mismo nombre: solo el sufijo es diferente. El sufijo es la fecha en el formato AAAAMMDD. Por ejemplo, la tabla owoxbi_sessions_20190301 contiene datos sobre las sesiones del 1 de marzo de 2019.

Podemos referirnos directamente a un grupo de dichas tablas en una solicitud para obtener datos, por ejemplo, del 1 de febrero al 28 de febrero de 2019. Para hacer esto, necesitamos reemplazar AAAAMMDD con un * en DESDE, y en DONDE, necesitamos especificar los sufijos de la tabla para el inicio y el final del intervalo de tiempo:

No siempre conocemos las fechas específicas para las que queremos recopilar datos. Por ejemplo, cada semana podríamos necesitar analizar los datos de los últimos tres meses. Para hacer esto, podemos usar la función FORMAT_DATE:

Después de BETWEEN, registramos el sufijo de la primera tabla. La frase FECHA_ACTUAL(), INTERVALO 3 MESES significa «seleccionar datos de los últimos 3 meses a partir de la fecha actual». El sufijo de la segunda tabla se formatea después de AND. Es necesario marcar el final del intervalo como ayer: CURRENT_DATE (), INTERVALO 1 DÍA.

Los parámetros de usuario y las métricas en las tablas de exportación de Google Analytics se escriben en la tabla de resultados anidados y en las subtablas customDimensions y customMetrics. Todas las dimensiones personalizadas se registran en dos columnas: una para la cantidad de parámetros recopilados en el sitio, la segunda para sus valores. Así es como se ven todos los parámetros transmitidos con un solo golpe:

Para desempaquetarlos y escribir los parámetros necesarios en columnas separadas, usamos la siguiente consulta SQL:

Así es como se ve en la solicitud:

En la siguiente captura de pantalla, seleccionamos los parámetros 1 y 2 de los datos de demostración de Google Analytics 360 en Google BigQuery y los llamamos page_type y client_id. Cada parámetro se registra en una columna separada:

Dichos cálculos son útiles si planea visualizar datos en Google Data Studio y filtrar por ciudad y categoría de dispositivo. Esto es fácil de hacer con la función de ventana COUNT:

Suponga que recopila datos sobre pedidos completados en varias tablas de BigQuery: una recopila todos los pedidos de la tienda A y la otra recopila los pedidos de la tienda B. Desea combinarlos en una tabla con estas columnas:

• client_id: un número que identifica a un comprador único
• ​transaction_created — hora de creación del pedido en formato TIMESTAMP
• id_transacción — número de pedido
• is_approved — si el pedido fue confirmado
• transaction_revenue — monto del pedido

En nuestro ejemplo, los pedidos desde el 1 de enero de 2018 hasta ayer deben estar en la tabla. Para hacer esto, seleccione las columnas apropiadas de cada grupo de tablas, asígneles el mismo nombre y combine los resultados con UNION ALL:

Cuando los datos ingresan a Google Analytics, el sistema determina automáticamente el grupo al que pertenece una transición en particular: Directa, Búsqueda orgánica, Búsqueda paga, etc. Para identificar un grupo de canales, Google Analytics analiza las etiquetas UTM de las transiciones, a saber, utm_source y utm_medium. Puede obtener más información sobre los grupos de canales y las reglas de definición en la Ayuda de Google Analytics.

Si los clientes de OWOX BI quieren asignar sus propios nombres a grupos de canales, creamos un diccionario, cuya transición pertenece a un canal específico. Para hacer esto, usamos el operador CASE condicional y la función REGEXP_CONTAINS. Esta función selecciona los valores en los que se produce la expresión regular especificada.

Recomendamos tomar nombres de su lista de fuentes en Google Analytics. Aquí hay un ejemplo de cómo agregar tales condiciones al cuerpo de la solicitud:

Si aún no ha cambiado a SQL estándar, puede hacerlo en cualquier momento. Lo principal es evitar mezclar dialectos en una sola solicitud.

El SQL heredado se usa de forma predeterminada en la antigua interfaz de BigQuery. Para cambiar entre dialectos, haga clic en Mostrar opciones debajo del campo de entrada de consulta y desactive la casilla Usar SQL heredado junto a Dialecto SQL.

La nueva interfaz utiliza SQL estándar de forma predeterminada. Aquí, debe ir a la pestaña Más para cambiar de dialecto:

Si no ha marcado la configuración de la solicitud, puede comenzar con el prefijo deseado (#standardSQL o #legacySQL):

En este caso, Google BigQuery ignorará la configuración de la interfaz y ejecutará la consulta con el dialecto especificado en el prefijo.

Si tiene vistas o consultas guardadas que se inician de forma programada con Apps Script, no olvide cambiar el valor de useLegacySql a false en el script:

Si trabaja con Google BigQuery no con tablas sino con vistas, no se puede acceder a esas vistas en el dialecto SQL estándar. Es decir, si su presentación está escrita en SQL heredado, no puede escribirle solicitudes en SQL estándar.

Para transferir una vista a SQL estándar, debe volver a escribir manualmente la consulta por la que se creó. La forma más fácil de hacerlo es a través de la interfaz de BigQuery.

1. Abra la vista:

2. Haga clic en Detalles. El texto de la consulta debería abrirse y el botón Editar consulta aparecerá a continuación:

Ahora puede editar la solicitud de acuerdo con las reglas de SQL estándar.
Si planea continuar usando la solicitud como una presentación, haga clic en Guardar vista después de que haya terminado de editar.

Gracias a la implementación de SQL estándar, puedes acceder directamente a los datos almacenados en otros servicios directamente desde BigQuery:

• Archivos de registro de almacenamiento en la nube de Google
• Registros de transacciones en Google Bigtable
• Datos de otras fuentes
  

Esto le permite usar los productos de Google Cloud Platform para cualquier tarea analítica, incluidos los análisis predictivos y prescriptivos basados ​​en algoritmos de aprendizaje automático.

La estructura de consulta en el dialecto estándar es casi la misma que en Legacy:

Los nombres de las tablas y la vista están separados por un punto (punto) y toda la consulta está encerrada en acentos graves: `nombre_proyecto.nombre_datos_nombre.nombre_tabla``bigquery-public-data.samples.natality`

La sintaxis completa de la consulta, con explicaciones de lo que se puede incluir en cada operador, se compila como un esquema en la documentación de BigQuery.

Características de la sintaxis SQL estándar:

• Se necesitan comas para listar campos en la instrucción SELECT.
• Si usa el operador UNNEST después de FROM , se coloca una coma o JOIN antes de UNNEST.
• No puedes poner una coma antes de FROM.
• Una coma entre dos consultas equivale a CROSS JOIN, así que ten cuidado con eso.
• JOIN se puede hacer no solo por columna o igualdad sino por expresiones arbitrarias y desigualdad.
• Es posible escribir subconsultas complejas en cualquier parte de la expresión SQL (en SELECT, FROM, WHERE, etc.). En la práctica, aún no es posible usar expresiones como WHERE column_name IN (SELECT ...) como en otras bases de datos.

En SQL estándar, los operadores definen el tipo de datos. Por ejemplo, una matriz siempre se escribe entre corchetes []. Los operadores se utilizan para la comparación, haciendo coincidir la expresión lógica (NOT, OR, AND) y en cálculos aritméticos.

SQL estándar admite más funciones que Legacy: agregación tradicional (suma, número, mínimo, máximo); funciones matemáticas, de cadenas y estadísticas; y formatos raros como HyperLogLog ++.

En el dialecto estándar, hay más funciones para trabajar con fechas y TIMESTAMP. Se proporciona una lista completa de características en la documentación de Google. Las funciones más utilizadas son para trabajar con fechas, cadenas, agregación y ventana.

1. Funciones de agregación

COUNT (DISTINCT column_name) cuenta el número de valores únicos en una columna. Por ejemplo, supongamos que necesitamos contar la cantidad de sesiones desde dispositivos móviles el 1 de marzo de 2019. Dado que un número de sesión se puede repetir en diferentes líneas, queremos contar solo los valores de número de sesión únicos:

SUM (column_name) — la suma de los valores en la columna

MIN (nombre_columna) | MAX (column_name) — el valor mínimo y máximo en la columna. Estas funciones son convenientes para verificar la distribución de datos en una tabla.

2. Funciones de ventana (analíticas)

Las funciones analíticas consideran valores no para toda la tabla sino para una determinada ventana, un conjunto de filas que le interesan. Es decir, puede definir segmentos dentro de una tabla. Por ejemplo, puede calcular SUM (ingresos) no para todas las líneas sino para ciudades, categorías de dispositivos, etc. Puede convertir las funciones analíticas SUM, COUNT y AVG, así como otras funciones de agregación, agregándoles la condición OVER (PARTITION BY column_name).

Por ejemplo, debe contar la cantidad de sesiones por fuente de tráfico, canal, campaña, ciudad y categoría de dispositivo. En este caso, podemos utilizar la siguiente expresión:

OVER determina la ventana para la cual se realizarán los cálculos. PARTITION BY indica qué filas deben agruparse para el cálculo. En algunas funciones, es necesario especificar el orden de agrupación con ORDER BY.

Para obtener una lista completa de las funciones de la ventana, consulta la documentación de BigQuery.

3. Funciones de cadena

Estos son útiles cuando necesita cambiar texto, formatear el texto en una línea o pegar los valores de las columnas. Por ejemplo, las funciones de cadena son excelentes si desea generar un identificador de sesión único a partir de los datos de exportación estándar de Google Analytics 360. Consideremos las funciones de cadena más populares.

SUBSTR corta parte de la cadena. En la solicitud, esta función se escribe como SUBSTR (string_name, 0.4). El primer número indica cuántos caracteres saltar desde el principio de la línea y el segundo número indica cuántos dígitos cortar. Por ejemplo, supongamos que tiene una columna de fecha que contiene fechas en el formato STRING. En este caso, las fechas se ven así: 20190103. Si desea extraer un año de esta línea, SUBSTR lo ayudará a:

CONCAT (column_name, etc.) pega valores. Usemos la columna de fecha del ejemplo anterior. Suponga que desea que todas las fechas se registren así: 2019-03-01. Para convertir fechas de su formato actual a este formato, se pueden usar dos funciones de cadena: primero, corte las partes necesarias de la cadena con SUBSTR, luego péguelas a través del guión:

REGEXP_CONTAINS devuelve los valores de las columnas en las que aparece la expresión regular:

Esta función se puede utilizar tanto en SELECT como en WHERE. Por ejemplo, en DONDE, puede seleccionar páginas específicas con él:

4. Funciones de fecha

A menudo, las fechas en las tablas se registran en formato STRING. Si planea visualizar los resultados en Google Data Studio, las fechas en la tabla deben convertirse al formato de FECHA usando la función PARSE_DATE.

PARSE_DATE convierte una STRING del formato 1900-01-01 al formato DATE.
Si las fechas en sus tablas se ven diferentes (por ejemplo, 19000101 o 01_01_1900), primero debe convertirlas al formato especificado.

DATE_DIFF calcula cuánto tiempo ha pasado entre dos fechas en días, semanas, meses o años. Es útil si necesita determinar el intervalo entre el momento en que un usuario vio publicidad y realizó un pedido. Así es como se ve la función en una solicitud:

Si desea obtener más información sobre las funciones enumeradas, lea Funciones de Google de BigQuery: una revisión detallada.

El dialecto SQL estándar permite a las empresas extraer la máxima información de los datos con una segmentación profunda, auditorías técnicas, análisis de KPI de marketing e identificación de contratistas desleales en las redes de CPA. Estos son ejemplos de problemas comerciales en los que las consultas SQL sobre los datos recopilados en Google BigQuery lo ayudarán.

1. Análisis ROPO: evaluar la contribución de las campañas online a las ventas offline. Para realizar un análisis de ROPO, debe combinar datos sobre el comportamiento de los usuarios en línea con datos de su CRM, sistema de seguimiento de llamadas y aplicación móvil.

Si hay una clave en una y la segunda base, un parámetro común que es único para cada usuario (por ejemplo, ID de usuario), puede realizar un seguimiento:
qué usuarios visitaron el sitio antes de comprar productos en la tienda
cómo se comportaron los usuarios en el sitio
cuánto tiempo tardaron los usuarios en tomar una decisión de compra
qué campañas tuvieron el mayor aumento en las compras fuera de línea.

2. Segmente a los clientes por cualquier combinación de parámetros, desde el comportamiento en el sitio (páginas visitadas, productos vistos, número de visitas al sitio antes de comprar) hasta el número de tarjeta de fidelización y los artículos comprados.

3. Averigüe qué socios de CPA están trabajando de mala fe y reemplazando las etiquetas UTM.

4. Analizar el progreso de los usuarios a través del embudo de ventas.

Hemos preparado una selección de consultas en dialecto SQL estándar. Si ya recopila datos de su sitio, de fuentes publicitarias y de su sistema CRM en Google BigQuery, puede usar estas plantillas para resolver sus problemas comerciales. Simplemente reemplaza el nombre del proyecto, el conjunto de datos y la tabla en BigQuery por los tuyos. En la colección, recibirá 11 consultas SQL.

Para los datos recopilados mediante la exportación estándar de Google Analytics 360 a Google BigQuery:

• Acciones del usuario en el contexto de cualquier parámetro
• Estadísticas sobre acciones clave de los usuarios
• Usuarios que vieron páginas de productos específicos
• Acciones de los usuarios que compraron un producto en particular
• Configurar el embudo con los pasos necesarios
• Eficacia del sitio de búsqueda interna

Para los datos recopilados en Google BigQuery mediante OWOX BI:

• Consumo atribuido por fuente y canal
• Coste medio de atraer a un visitante por ciudad
• ROAS de beneficio bruto por fuente y canal
• Número de pedidos en el CRM por forma de pago y forma de entrega
• Tiempo medio de entrega por ciudad

Si tiene preguntas sobre la consulta de datos de Google BigQuery para las que no encontró respuestas en este artículo, pregunte en los comentarios. Intentaremos ayudarte.