Como ya sabéis, Google BigQuery utiliza las sentencias en Standard SQL para presentar los datos que nosotros solicitemos a los diferentes bases de datos con las que estemos trabajando. Este lenguaje comparte gran parte de su sintaxis con otros lenguajes SQL, por lo que es muy fácil de aprender si ya has tratado con Oracle o MySQL, por ejemplo.
El artículo que escribo hoy trata de hacer un pequeño repaso por las sentencias básicas que se suelen utilizar en Google BigQuery para indagar en los datos disponibles. Me gustaría, más adelante, hacer otros tantos para tratar las funciones, procesos y otra serie de utilidades que nos ofrece BigQuery que nos permiten moldear los datos para sacar esas conclusiones, pero paso a paso.
Hoy es el turno del SELECT y todo lo que depende de él. Voy a hacer un repaso por aquellas sentencias que nos permiten hacer consultas simples y complejas a base de condiciones o estableciendo datos previos. El mejor comienzo para trabajar con datos en Google Big Query que, como ya sabéis, podéis conectarlo gratuitamente con Google Analytics 4.
Para los ejemplos voy a trabajar con la tabla ga_sessions_ del dataset público de google_analytics_sample, dentro del proyecto bigquery_public_data.
Vamos a hablar de...
SELECT, SELECT DISTINCT, FROM y GROUP BY
La sentencia SELECT es aquella que nos permite realizar una consulta directa de información a una base de datos. En su creación podemos utilizar diferentes instrucciones que nos permiten acotar dicha consulta estableciendo condicionales que nos ofrezcan los datos que buscamos. Por ejemplo, podremos solicitar el número de sesiones por canal de adquisición entre determinadas fechas o cuando la página de aterrizaje sea una en concreto, para hacernos una idea. Al final, nos permite obtener toda la información que nos ofrece Google Analytics, eso sí, de una manera manual, e ir un poco más allá calculando lo que necesitemos.
En el proceso de construcción de una sentencia SELECT juega un importante papel la instrucción FROM, pues indica la base de datos de la que queremos extraer la información. Nunca encontraréis un SELECT sin un FROM. Vamos a ver algún ejemplo:
#standardSQL
SELECT
*
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`;
Como se puede ver, la consulta es muy sencilla. Estamos solicitando todas las columnas con el asterisco (*) a la tabla que está entre comillas, que en este caso pertenece al proyecto público de prueba de BigQuery. La consulta más sencilla está formada por esta estructura de SELECT <qué queremos> FROM <de dónde lo queremos>.
Ahora bien, podemos ir añadiendo mayor complejidad:
#standardSQL
SELECT
geoNetwork.city AS City,
SUM(totals.transactionRevenue) AS Revenue
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
GROUP BY
City;
En esta sentencia SELECT estamos llamando al parámetro geoNetwork.city de la tabla ga_sessions_20170801, que muestra la ciudad, y creando un ALIAS para él con la instrucción AS, lo mismo que con totals.transactionRevenue, que muestra los ingresos. Es decir, queremos que Google BigQuery nos devuelva los ingresos por ciudad, por ello, también usaremos el GROUP BY siempre que queramos obtener información agrupada por una dimensión (en este caso la ciudad). Por último, he añadido la función de agregación SUM() para que sume los valores que se repitan para la dimensión de ciudad (puede haber varias instancias en las que una misma ciudad se repita, así agrupamos).
Finalmente, el uso del DISTINCT nos permite eliminar duplicidades de datos. Imagina que queremos obtener el número total de usuarios únicos que nos visitan por canal, para ello trabajaremos sobre el parámetro fullVisitorId de la siguiente forma:
#standardSQL
SELECT
channelGrouping,
COUNT(DISTINCT fullVisitorId) AS Users
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
GROUP BY
channelGrouping
ORDER BY
Users DESC;
En esta sentencia SELECT estamos solicitando el número de usuarios únicos por canal, como decía. Lo más destacable sería la fila 4, donde contamos con la función de agregación COUNT() los distintos fullVisitorId que tenemos para la tabla a la que apuntamos con FROM. Si quitamos el DISTINCT, verás que el número es diferente. Esto se debe a que el parámetro fullVisitorId muestra el ID único del visitante, viene a ser una versión hasheada del clientId y, en esta tabla, tenemos instancias repetidas de ese fullVisitorId. Finalmente usamos el ORDER BY para ordenar de manera descencente (DESC) por la columna Users solicitada en el SELECT.
UNION ALL
La instrucción UNION ALL permite unir dos sentencias SELECT en un mismo resultado. Esto quiere decir que, a continuación de las filas del primer SELECT, se mostrarán las del segundo, eso sí, se debe respetar el mismo número de columnas y de tipo de datos en ambas consultas. Vamos a ver un ejemplo:
#standardSQL
SELECT
channelGrouping,
COUNT(DISTINCT fullVisitorId) AS Users
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
GROUP BY
channelGrouping
UNION ALL
SELECT
channelGrouping,
COUNT(DISTINCT fullVisitorId) AS Users
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170720`
GROUP BY
channelGrouping
En este caso, hemos unido el número de usuarios únicos por canal de las tablas ga_sessions_20170801 y ga_sessions_20170720, esto lo que hará es mostrar los usuarios de cada canal de un día y a continuación los de otro. Es una manera rápida de comparar datos.
INTERSECT DISTINCT
La instrucción INTERSECT DISTINCT es lo contrario a UNION ALL, se utiliza para extraer los datos comunes de dos consultas SELECT. Al igual que el anterior, debe respetar las columnas y tipos de datos, y nos sirve para resolver cuestiones como la siguiente:
#standardSQL
SELECT
fullVisitorId AS Users
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
WHERE
channelGrouping = "Direct"
INTERSECT DISTINCT
SELECT
fullVisitorId AS Users
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
WHERE
channelGrouping = "Organic Search"
Aunque en este ejemplo ya he incluido la cláusula WHERE, que explicaré un poco más adelante, sirve para establecer el ejemplo. En este caso, lo que he hecho es solicitar el parámetro fullVisitorId de la misma tabla pero que aparece por duplicado procedente del canal Direct y del canal Organic Search. De este modo, puedo conocer qué usuarios han accedido por estos dos canales.
REPLACE
La instrucción REPLACE sirve, más que nada, para presentar correctamente tablas de información. De esta manera, su objetivo no es otro que sustituir un valor repetido por un número o cadena que nosotros queramos y que dé mayor sentido a la información que estamos viendo. Vamos con el ejemplo:
#standardSQL
SELECT
REPLACE(trafficSource.campaign,"(not set)","Sin campaña") CampaignName,
COUNT(DISTINCT fullVisitorId) AS Users
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
GROUP BY
CampaignName;
En este ejemplo verás que tras la instrucción SELECT, en lugar de poner la columna que quiero, directamente he añadido la función REPLACE. Esto lo que hará es, para todos los resultados (not set) de trafficSource.campaign, los renombrará a “Sin campaña”. Como veis, simplemente nos permite mostrar los datos de una manera más ordenada o aclaratoria para analizarlos o mostrarlos.
Esta instrucción tiene una versión para utilizar REGEX bastante más apañada, pero el uso de expresiones regulares en consultas SQL lo trataré en un futuro artículo.
WHERE, AND, OR y BETWEEN
La cláusula WHERE es la condicional por excelencia. En un ejemplo anterior ya la he usado, y es la que nos va a permitir solicitar aquellos datos que buscamos a través de una única condición o de varias utilizando el AND, el OR y el BETWEEN, entre otras instrucciones que veremos en los puntos siguientes. Vamos con el ejemplo:
#standardSQL
SELECT
date,
channelGrouping AS Canal,
COUNT(DISTINCT fullVisitorId) AS Users
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
channelGrouping = "Organic Search"
AND _TABLE_SUFFIX BETWEEN '20170701'
AND '20170801'
GROUP BY
date,
Canal
ORDER BY
date;
En el ejemplo superior he utilizado el tratamiento de fechas que expliqué en los Primeros pasos con GA4 y BigQuery, en el que usábamos el comodín * para poder hacer comparaciones entre fechas. En Este caso, estamos mostrando una tabla agrupada por fecha y canal donde vemos los usuarios únicos que han visitado nuestra página, siempre procedentes de Organic Search y entre el 1 de julio de 2017 y el 1 de agosto de 2017. Luego ordenamos por fecha de manera ascendente.
Como veis, en la instrucción WHERE he utilizado un =, pero también podemos utilizar cualquier comparador lógico del tipo mayor que, menor que, etc. Después he puesto un AND, pero si queremos que no sea excluyente y tenga en cuenta ambas condiciones, podría haber puesto un OR. Finalmente el BETWEEN siempre va acompañado de un AND entre ambas condiciones.
LIKE y NOT LIKE
LIKE y NOT LIKE es otra forma de establecer condicionales, solo que en este caso podremos jugar con coincidencias aproximadas del tipo empieza por, acaba con, etc. Si tuviésemos un listado de productos de una tipología cuyo nombre siempre comenzase por Bota, sería una forma de poder extraer datos. Por ejemplo:
#standardSQL
SELECT
prod.v2ProductName AS ProductName,
COUNT(prod.v2ProductName) AS Quantity
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
UNNEST(hits) AS hits,
UNNEST(hits.product) AS prod
WHERE prod.v2ProductName LIKE "E%"
GROUP BY
ProductName;
Este ejemplo lo he complicado un poquito más utilizando el UNNEST(), que nos sirve para desagrupar arrays y que explicaré más a fondo en un artículo dedicado solo a dicha función. En cualquier caso, lo importante de esta consulta es el LIKE, que siempre irá acompañado de un WHERE previo. La estructura, por tanto, será la de WHERE <parámetro> LIKE <condición>.
Las condiciones se suelen aplicar en string, como en el ejemplo y van acompañadas del característico % si fuese necesario. Podría haber puesto un nombre de producto completo, pero con el símbolo % especificamos si debe empezar, acabar, o estar entre cualquier cadena.
- E%: empieza por la letra E mayúscula.
- %e: termina por la letra e minúscula.
- %e%: contiene la letra e minúscula.
La negación de LIKE, que sería NOT LIKE, funciona exactamente igual, solo que en este caso es una condición de exclusión, mientras que el LIKE a secas es de inclusión.
IN y NOT IN
IN y NOT IN también irán acompañados siempre de un WHERE previo, solo que en este caso, obtendremos resultados dentro de una agrupación de posibilidades. Es decir, “quiero saber X siempre que Y sea Z1, Z1 o Z3“. Vamos con el ejemplo:
#standardSQL
SELECT
prod.v2ProductName AS ProductName,
COUNT(prod.v2ProductName) AS Quantity
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
UNNEST(hits) AS hits,
UNNEST(hits.product) AS prod
WHERE prod.v2ProductName IN('Google Flashlight','Keyboard DOT Sticker','Google 2200mAh Micro Charger')
GROUP BY
ProductName;
Siguiendo con el ejemplo de los productos y sus cantidades con hits, en la fila resaltada he utilizado el condicional IN y, como veis, he especificado una serie de nombres de producto de los cuales obtendré las cantidades solicitadas. Si hubiera indicado NOT IN, obtendría el resultado de todos los productos que no fuesen los indicados.
WITH
La última instrucción de este artículo y, en ocasiones, la más útil. El WITH nos permite establecer una especie de tabla temporal donde almacenar información. Es decir, podremos incluir con WITH los valores de un SELECT previo sobre el que luego realizar otras consultas; fijamos la información y nos introducimos en una capa más profunda de detalle para analizar.
#standardSQL
WITH product_quantity AS
(
SELECT
prod.v2ProductName AS ProductName,
COUNT(prod.v2ProductName) AS Quantity
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
UNNEST(hits) AS hits,
UNNEST(hits.product) AS prod
WHERE prod.v2ProductName IN('Google Flashlight','Keyboard DOT Sticker','Google 2200mAh Micro Charger')
GROUP BY
ProductName
)
SELECT * FROM product_quantity;
En este ejemplo he utilizado el ejemplo de IN y NOT IN como esa tabla temporal mencionada, a la que he llamado product_quantity. A continuación he hecho un SELECT de todos los parámetros directamente sobre la tabla temporal. De este modo, podríamos, por ejemplo, crearnos tablas temporales de ventas por día y luego trabajar sobre ellas. Al final, se trata ordenar la forma de trabajar, con una estructura adecuada y que la información sea comprensible.
Del mismo modo, el contenido de un WITH no se ejecuta hasta que llamamos a la tabla temporal con un SELECT, por lo que es, también, una manera eficiente de trabajar a la hora de tener en cuenta los tamaños de procesamiento de las consultas.
Y con esto llego al final de este artículo de sentencias SQL básicas para Google BigQuery. El mundo del SQL y su aplicación en BigQuery es muy extenso, por lo que he decidido dividir este tipo de artículos en varias entregas. Así pues, en los próximos profundizaré más en otros tipos de sentencias (creaciones de tabla, actualizaciones, etc.), así como en funciones que facilitan la vida a la hora de tratar con los datos o cómo podemos utilizar las expresiones regulares como condicionales.
Espero que el contenido os haya servido. ¡Nos leemos!
