Cómo hacer consultas en Google BigQuery – guía básica de SQL

Como muchos sabréis, Google BigQuery es la solución Big Data de Google. Se trata de un servicio web que permite almacenar y consultar grandes volúmenes de datos en pocos segundos.

BigQuery

En el caso de la analítica web, se pueden importar todos nuestros datos de Google Analytics (sólo para cuentas Premium). Pero no los datos a los que estamos acostumbrados de GA, sino gigantescas tablas que contienen absolutamente toda la actividad registrada de cada sesión durante su paso por nuestro site: desde el identificador de la visita hasta el número de hit dentro de una sesión, pasando por todas las dimensiones y métricas ya disponibles en Analytics. Así pues, cualquier información que deseemos sobre nuestras visitas está a nuestro alcance haciendo la consulta (también llamada query) adecuada, que además se procesará en pocos segundos a pesar de las miles de filas que probablemente tendrá nuestra tabla de datos.

Pero, ¿cómo se hacen las consultas en BigQuery? Pues bien, con un lenguaje de programación basado en SQL, por lo que es muy importante entender y dominar este lenguaje para poder explotar al máximo esta herramienta. Teniendo en cuenta que las consecuencias pueden ser catastróficas si tomamos acciones a partir de datos erróneos, una query equivocada no es algo que nos podamos permitir.

En este post describiremos la estructura básica de las consultas en BigQuery e intentaremos entender las principales cláusulas del lenguaje SQL para ser capaces de realizar cualquier query que necesitemos.

La estructura básica de una consulta en BQ es la siguiente:


SELECT expr1 (AS alias1), expr2 (AS alias2), …

FROM [tabla1]

([INNER | CROSS | [LEFT | RIGHT | FULL] OUTER] JOIN [tabla2] ON condicionesJoin)

WHERE condiciones

GROUP BY expr1|alias1, expr2|alias2, …

ORDER BY expr1|alias1 [DESC|ASC], expr2|alias2 [DESC|ASC], …

LIMIT n;

*Los campos entre paréntesis son opcionales y la barra vertical | indica los distintos valores posibles.


Veamos con detalle cada una de sus partes:

 

SELECT (obligatorio)

Las dimensiones y métricas que queremos extraer. Cada una de ellas será una columna de la tabla resultante. Podéis encontrar los nombres de todas las variables que están disponibles en BigQuery (para datos de Google Analytics) aquí:

También pueden ser operaciones aritméticas entre métricas y funciones agregadas sobre variables. En este último caso necesitaremos la función GROUP BY, de la cual hablaremos más adelante.

Los nombres por defecto no son cómodos para trabajar ya que suelen ser muy largos (por ejemplo, la fuente de tráfico es trafficSource.source), por lo que se puede asignar un alias a las variables mediante la cláusula AS, con el cual podremos hacer referencia a esa variable en otras funciones de la query y además será el nombre con el que aparecerá en la tabla de datos resultante.

 

FROM (obligatorio)

Todas las variables que se incluyan en el SELECT se extraerán de una tabla, a la cual haremos referencia a través de la cláusula FROM. Por defecto, Bigquery guarda tablas diarias, es decir, tenemos una tabla para cada día en la que cada fila es una visita y cada columna una dimensión o métrica. Los usos más comunes de la cláusula FROM son:

  • Datos de un día:

FROM [<Dataset>.<Table Name>]

Los nombres del dataset y de la tabla los podemos encontrar en la interfície de BigQuery:

BQinterface

 

Como podéis ver, los nombres de las tablas con datos de GA tiene la forma: ga_sessions_ seguido por la fecha correspondiente en formato YYYYMMDD.

Para este ejemplo, FROM [99999999.ga_sessions_20150428] extraería los datos para el día 28 de abril de 2015 del Dataset 99999999.

  • Datos dentro de un rango de fechas:

FROM TABLE_DATE_RANGE([<Dataset>.ga_sessions_],TIMESTAMP(‘YYYY-MM-DD’),TIMESTAMP(‘YYYY-MM-DD’))

De esta forma podremos extraer datos dentro de un rango de fechas, cosa realmente importante ya que los análisis de datos no suelen (ni deben) hacerse con datos de un solo día.

Por ejemplo, para extraer datos del 10 al 20 de abril de 2015 utilizaríamos:

FROM TABLE_DATE_RANGE([99999999.ga_sessions_],TIMESTAMP(‘2015-04-10’),TIMESTAMP(‘2015-04-20’))

  • Tablas intermedias

También se puede utilizar como tabla una que resultaría de otra query. La estructura sería la siguiente:


SELECT expr3, expr4, …

FROM (        SELECT expr1, expr2, …

                    FROM [tabla]

                    WHERE condiciones1

                    GROUP BY expr1, expr2, …

                    ORDER BY expr1 [DESC|ASC], expr2 [DESC|ASC], …

                    LIMIT n)

WHERE condiciones2

GROUP BY expr3, expr4, …

ORDER BY expr3 [DESC|ASC], expr4 [DESC|ASC], …

LIMIT n;


Como os podéis imaginar, en el primer SELECT (será el que obtendremos con la consulta) solo podremos utilizar aquellas variables que estén definidas en la tabla intermedia. Este tipo de estructuras se pueden utilizar para obtener métricas que no están predefinidas y que requieren cálculos intermedios.

 

WHERE (opcional)

Condiciones sobre los datos que queremos extraer. No es necesario que sean dimensiones o métricas incluidas en el SELECT, pero sí en la tabla con la que estamos trabajando (la del FROM, aunque sea el resultado de una query o un JOIN). Se pueden utilizar condiciones múltiples usando AND y OR.

Notas:

  • La cláusula WHERE filtra los datos que pedimos, pero no reduce la cantidad de datos procesados.
  • Para datos de tipo INTEGER se suelen usar los operadores aritméticos para definir las condiciones. Algunos ejemplos son: = (igual a), != (no igual a), > (mayor que), < (menor que), >=(mayor o igual que)…
  • Para datos de tipo STRING también se pueden utilizar = o !=, pero en este caso si se comparan con una expresión fija se tienen que poner entre comillas (‘). Por ejemplo, trafficSource.medium = ‘referral’ exige que el medio sea referral. También es común utilizar IS NULL y IS NOT NULL para exigir que el valor sea o no, respectivamente, NULL, que es el valor que se asigna a las variables cuando no hay información disponible.

 

GROUP BY

Agrupa los distintos valores de las dimensiones especificadas. Se pueden hacer agrupaciones por varias dimensiones a la vez.

Se utiliza junto a funciones agregadas (en el SELECT). Vamos a entender qué hace exactamente el GROUP BY junto a las dos funciones agregadas más frecuentes: COUNT y SUM. Podéis encontrar el resto de funciones agregadas aquí:

Imaginemos que tenemos la  tabla siguiente (supongamos en este ejemplo que tiene como nombre “tabla”):

variable1 valor1
A 10
B 15
C 12
A 10
D 8
C 11
A 9
  • COUNT

Si queremos saber en cuántas filas aparece cada una de las distintas categorías de la variable1 utilizaremos la función COUNT, la cual se tiene que utilizar con GROUP BY.

Usando:


SELECT variable1, COUNT(variable1) AS count

FROM tabla

GROUP BY variable1

ORDER BY count DESC;


Obtendríamos:

variable1 count
A 3
C 2
B 1
D 1

 

Es decir, con COUNT estamos pidiendo contar las filas, y con GROUP BY estamos haciendo que acumule el número de filas si tienen la misma categoría de la variable1. Además, hemos pedido (con ORDER BY) que la tabla aparezca ordenada según los resultados de la cuenta de forma descendiente (utilizando el alias que le hemos dado a la cuenta: “count”).

  • SUM

Si queremos saber el valor total de valor1 asociado a cada categoría utilizaremos la función SUM, la cual se tiene que utilizar con GROUP BY.

Usando:


SELECT variable1, SUM(valor1) AS total

FROM tabla

GROUP BY variable1

ORDER BY total DESC;


Obtendríamos:

variable1 total
A 29(=10+10+9)
C 23(=12+11)
B 15
D 8

 

Es decir, con SUM haremos que se sumen valores, y con GROUP BY decidimos qué valores se tienen que sumar: aquéllos que tengan la misma categoría de la variable1. Además, hemos pedido (con ORDER BY) que la tabla aparezca ordenada según el resultado total de mayor a menor.

 

ORDER BY (opcional)

Ordena los datos de forma descendente (DESC) o ascendente (ASC). Por defecto se utiliza el orden ascendente.

 

LIMIT (opcional)

Número máximo de filas de la tabla de datos resultante.

 

Unión de tablas (JOIN)

En análisis avanzados puede que necesitemos hacer varias consultas por separado pero que nos interese tener todos los datos en una única tabla. En estos casos, ¿cómo podemos unir distintas tablas? La respuesta es con la función JOIN, con la cual podremos unificar dos tablas (o más, concatenando JOINs) en una si nuestras tablas comparten alguna de las variables.

En BQ hay hasta 5 tipos de JOIN, que son:

– INNER JOIN

– LEFT OUTER JOIN

– RIGHT OUTER JOIN

– FULL OUTER JOIN

– CROSS JOIN

Notas:

1) La tabla resultante del JOIN es sobre la cual trabajaremos, así que en el SELECT solo podremos pedir variables de alguna de las tablas unidas. Una forma abreviada para pedir todas las columnas de la tabla resultante del JOIN es utilizar * (es decir, SELECT *).

2) Para hacer referencia a cualquier variable primero tenemos que indicar a qué tabla pertenece seguido por el nombre (o el alias) de la variable en esa tabla (separados por un punto “.”). Veremos un ejemplo más adelante. Esto se aplica en todas las cláusulas: SELECT, ON, GROUP BY, ORDER BY…

3) La condición para unir las tablas puede ser sobre múltiples columnas (utilizando AND y OR).

Veamos en qué se diferencian los distintos tipos de JOIN mediante un ejemplo.

Supongamos que tenemos las siguientes tablas (de nombres “tabla1” y “tabla2”, respectivamente):

variable1 valor1 valor2
A 10 1500
B 15 1800
C 12 2100
A 11 3800

 

variable1 variable2 valor3
A X 250
B Y 300
D Y 100
E Z 175

 

INNER JOINInnerJoin

Dadas dos tablas de datos, con INNER JOIN la tabla resultante tendrá únicamente las filas que cumplan la/s condición/es.


SELECT *

FROM tabla1 (AS aliasTabla1)

INNER JOIN tabla2 (AS aliasTabla2)

ON tabla1.variable1 = tabla2.variable1;


Notad cómo se hace referencia a la variable1 en la cláusula ON, que es donde se especifica la condición de la unión (ver nota 2).

La tabla resultante sería:

tabla1.variable1 tabla1.valor1 tabla1.valor2 tabla2.variable1 tabla2.variable2 tabla2.valor3
A 10 1500 A X 250
A 11 3800 A X 250
B 15 1800 B Y 300

 

LEFT OUTER JOIN

LeftOuterJoin

Dadas dos tablas de datos, con LEFT OUTER JOIN obtendremos una tabla con el mismo número de filas que la primera tabla (la que esté a la izquierda de la función LEFT OUTER JOIN). En aquellas filas donde haya coincidencia (según la condición establecida) con la segunda tabla, se añadirán los valores de esta última, y para las filas donde no haya coincidencia, las entradas de las columnas de la segunda tabla se rellenarán con NULL.


SELECT *

FROM tabla1

LEFT OUTER JOIN tabla2

ON tabla1.variable1 = tabla2.variable1;


La tabla resultante sería:

tabla1.variable1 tabla1.valor1 tabla1.valor2 tabla2.variable1 tabla2.variable2 tabla2.valor3
A 10 1500 A X 250
A 11 3800 A X 250
B 15 1800 B Y 300
C 12 2100 NULL NULL NULL

 

Es decir, a las filas donde se cumple la condición (INNER JOIN) se les añade el resto de filas de la tabla1 (porque es la que está a la izquierda del JOIN) con valor NULL en las columnas que corresponden a variables de la tabla2.

RIGHT OUTER JOIN

RightOuterJoin

Equivalente a LEFT OUTER JOIN pero conservando la tabla2 entera (la de la derecha de RIGHT OUTER JOIN).

FULL OUTER JOIN

FullOuterJoin

 

Dadas dos tablas de datos, con FULL OUTER JOIN obtendremos una tabla con todas las filas de la primera y de la segunda tabla, haciendo el “match” donde se cumple la condición y rellenando con NULL donde no.


SELECT *

FROM tabla1

FULL OUTER JOIN tabla2

ON tabla1.variable1 = tabla2.variable1;


La tabla resultante sería:

tabla1.variable1 tabla1.valor1 tabla1.valor2 tabla2.variable1 tabla2.variable2 tabla2.valor3
A 10 1500 A X 250
A 11 3800 A X 250
B 15 1800 B Y 300
C 12 2100 NULL NULL NULL
NULL NULL NULL D Y 100
NULL NULL NULL E Z 175

 

Es decir, para las filas en las que se cumpla la condición habrá el “merge” entre las variables de la tabla1 y la tabla2, y para las filas en las que no se cumpla se asignará NULL a las variables de la tabla a la que no pertenecen.

CROSS JOIN

CrossJoin2

Dadas dos tablas de datos, con CROSS JOIN obtendremos una tabla en la que cada fila de la primera tabla se habrá juntado con todas las filas de la segunda tabla, por lo que no es necesario poner ninguna condición. Por lo tanto, si tenemos una tabla de 3 filas y otra de 5, la tabla resultante del CROSS JOIN tendrá 3*5=15 filas.

Se recomienda no usar este tipo de JOIN ya que es muy poco eficiente.


SELECT *

FROM tabla1

CROSS JOIN tabla2;


La tabla resultante sería:

tabla1.variable1 tabla1.valor1 tabla1.valor2 tabla2.variable1 tabla2.variable2 tabla2.valor3
A 10 1500 A X 250
B 15 1800 A X 250
C 12 2100 A X 250
A 11 3800 A X 250
A 10 1500 B Y 300
B 15 1800 B Y 300
C 12 2100 B Y 300
A 11 3800 B Y 300
A 10 1500 D Y 100
B 15 1800 D Y 100
C 12 2100 D Y 100
A 11 3800 D Y 100
A 10 1500 E Z 175
B 15 1800 E Z 175
C 12 2100 E Z 175
A 11 3800 E Z 175

 

Vemos que las filas 1-4 corresponden a unir cada fila de la tabla1 con la primera fila de la tabla2, las filas 5-8 corresponden a unir cada fila de la tabla1 con la segunda fila de la tabla2, y así sucesivamente.

 

Llegados a este punto ya tenemos los medios para realizar cualquier tipo de consulta (o como mínimo para entender que hace cualquiera que se nos ponga delante). Para terminar con el post, dejémonos de teoría y vayamos a la práctica con un ejemplo aplicado a la analítica web:

¿Cómo sería la query necesaria para obtener el número de transacciones asociadas a cada categoría de dispositivo (desktop, mobile, tablet)?

La respuesta es:


SELECT device.deviceCategory AS CategoriaDispositivo, SUM (totals.transactions) AS TotalTransacciones

FROM TABLE_DATE_RANGE([99999999.ga_sessions_],TIMESTAMP(‘2015-04-10’),TIMESTAMP(‘2015-04-29’))

GROUP BY CategoriaDispositivo

ORDER BY TotalTransacciones DESC;


1) En el SELECT pedimos la categoría del dispositivo (device.deviceCategory), al cual hemos renombrado como “CategoriaDispositivo”, y calculamos el número total de transacciones (totals.transactions), al cual llamamos “TotalTransacciones”.

2) En el FROM pondríamos la tabla de datos para la que querríamos hacer el cálculo. En este ejemplo, el cálculo se habría hecho para el periodo comprendido entre el 10/04/2015 y el 29/04/2015.

3) Como hemos usado la función SUM necesitamos el GROUP BY. En este caso, agrupamos por categoría de dispositivo (fíjate que hemos utilizado su alias, CategoriaDispositivo) de manera que se sumarán el número de transacciones de aquellas sesiones cuya categoría de dispositivo es la misma.

4) Con el ORDER BY ordenamos en función del número total de transacciones que hemos obtenido, de mayor a menor.

Con esta query obtendríamos una tabla con aspecto parecido a éste:

EjemploBQ

¿Habrías sido capaz de hacerla tú solo? Si es que sí, enhorabuena! 😀 Y si es que no, no te desanimes, es cuestión de práctica. 😉

Podéis encontrar muchos más ejemplos de consultas para GA en el “BigQuery Cookbook” (aunque en algunos de ellos los nombres de las dimensiones y métricas no están actualizados). Y mucha más información sobre las consultas en BigQuery en la documentación oficial de Google.

 

Espero que esta pequeña guía os haya servido para entender un poco más cómo funcionan las consultas en BigQuery (y en SQL en general). Para los que aun no hayáis tenido la posibilidad de utilizar esta herramienta, os animo a que la probéis ya que tiene un potencial enorme de cara a explotar nuestros datos de Google Analytics y conocer un poco más a nuestros usuarios. Y bueno, si tenéis alguna duda relacionada con este post o estáis trabajando en una query que no conseguís resolver, podéis dejar vuestro comentario y os intentaré ayudar en la medida de lo posible. 🙂

 

 

 

8 Comments

  1. Interesante articulo, de los mas completos a la hora de hablar sobre BigQuery, direto a mis favoritos para analizarlo varias veces. Saludos

  2. Pol Ferrando

    Muchas gracias Mariana! Me alegro de que te haya gustado! 🙂

  3. Hola Pol, gracias por compartir esta valiosa info, es muy completa tu guía. Hace tiempo estoy buscando un ejemplo para ver la “raw” data de Google Analytics y no encuentro en Internet… serías tan amable de remitirme a un ejemplo de esas “gigantescas tablas que contienen absolutamente toda la actividad registrada de cada sesión durante su paso por nuestro site…”? Muchas gracias!

  4. Pol Ferrando

    Hola Gonzalo! Muchas gracias por tu comentario! Google ofrece una tabla de datos de ejemplo de Analytics en BigQuery, correspondiente a los datos de un día de un site ficticio. Puedes encontrar cómo acceder a ella paso por paso aquí. Cuando tengas acceso a esta tabla, podrás ver la “raw” data en la interfície de BQ siguiendo estos pasos:

    1) Hacer click sobre la tabla (se llama ga_sessions_20130910), lo que hará que aparezcan los detalles de la tabla (resumidos). La imagen de la interfície de BQ de este post muestra un ejemplo de una tabla resumida.
    2) En la esquina superior derecha, hacer click en “Details”.

    Espero que estas indicaciones te sean de ayuda. Un saludo!

  5. Oscar

    Hola,
    es posible hacer una consulta sql a una cuenta GA no premium?

  6. Pol Ferrando

    Hola Oscar! Por desgracia, la exportación de datos de Google Analytics a BigQuery solo está disponible para cuentas Premium actualmente. Sin cuenta Premium, de momento únicamente se pueden hacer consultas de prueba en la tabla de datos de muestra que ofrece Google y de la que hablé en comentarios anteriores. Pero quizá cambie algún día… Saludos!

  7. Hola Pol
    Tienes documentación o material de ayuda de como conectar información desde un sitio HTTP o SQL Server, para exportarlo y dejarlo conectado en tiempo real a Google BigQuery?

    Gracias por la información

    Pedro B.

  8. Pol Ferrando

    Hola Pedro,

    Muy interesante la conexión de la que hablas. No he tenido la necesidad de hacerlo nunca, pero imagino que puedes encontrar esta información en la documentación oficial de Google sobre BigQuery.

    Si lo encuentras aquí o en cualquier otro sitio, sería un placer que lo compartieras en otro comentario. 🙂

    Un saludo!

Leave Comment

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