Introducción a BigQuery para Analizar Tráfico

En mi post de análisis de tráfico que publiqué hace poco, os explicaba cómo analizar el tráfico de un portal pequeño o mediano usando SEMRUSH, Google Sheets & Ahrefs. Sin embargo, os decía que este método tenía sus límites para portales grandes. En este nuevo post, os detallaré la metodología que usamos para dichos portales.

Table of Contents

¿Qué es BigQuery?

Tal y como lo define el propio Google, BigQuery es una herramienta para almacenar datos sin servidor (propio) y de gran escalabilidad. Al final lo que hacemos es enviar datos que queremos utilizar a un servidor externo (en este caso de Google) para aprovechar su potencia y, de este modo, ejecutar operaciones sobre una gran cantidad de datos en un tiempo récord.

Tened en cuenta que, aunque usemos BigQuery en SEOCOM, existen empresas con bases de datos que no se pueden comparar a las nuestras (con millones de millones de líneas), por lo tanto es bastante probable que esta herramienta se adapte a vuestras necesidades sin ningún problema.

¿Cómo crear una cuenta BigQuery?

A diferencia de la gran mayoría de los productos de Google, esta herramienta no es gratis. Sin embargo, como podéis ver en la documentación oficial, el precio es más que reducido, y podréis montar vuestras bases de datos por menos de 5€ al mes sin ningún problema. El caso es que, de nuevo, la herramienta se ha desarrollado para empresas que gestionan una cantidad de datos superior a lo que vamos a hacer, por tanto, es bastante lógico que no sea muy cara para nosotros.

Para crear una cuenta, entrad en https://cloud.google.com/bigquery/ y haced clic en “Empezar gratis”:

Deberéis seguir los pasos para activar el servicio y tendréis que introducir un número de tarjeta de gratis. No os preocupéis, ya que deberíais poder empezar con un saldo (que podéis usar durante 1 año) de 300$ (unos 275€ al tipo de cambio de hoy), que es más que suficiente para el uso que tendréis.

Una vez hecho, deberéis crear un proyecto. Podéis usar el nombre que queráis, ya que no es demasiado relevante:

Una vez creado (puede tardar un minuto), buscad BigQuery y dadle a “Ir a Big Query”:

Una vez aquí, ya tenéis vuestra cuenta BigQuery lista para que empecemos a usarla.

Estructura de datos en BigQuery

Antes de empezar a explicar cómo cargar datos en BigQuery, un pequeño hincapié sobre la estructura de los datos en esta herramienta, que usa tres niveles:

El proyecto asignado por BigQuery (que al final es el nombre del proyecto con un ID al final en algunos casos)
Conjuntos de datos que podemos ver como una agrupación de tablas
Tablas individuales.

Para no entrar en una explicación demasiado técnica, al final podéis imaginar que un conjunto de datos es una hoja de cálculo y que cada tabla es una pestaña de este archivo.

Una vez en vuestro proyecto, deberíais poder crear un conjunto:

En el caso que haga falta, os aconsejo cambiar la ubicación de los datos, ya que la ley os puede obligar a dejar los datos en Europa (la legislación que aplica es diferente de la de los EEUU) pero, en referencia a este tema, os aconsejo mirarlo bien con vuestro departamento legal.

Aquí nos quedamos, ya que nuestro proceso de subida de datos se encarga de crear las tablas solo 🙂

Subida de datos a BigQuery

En nuestro caso y como queremos analizar el tráfico de un portal, queremos usar un sistema para exportar datos de Google Analytics directamente a BigQuery. Nos permitirá:

Extraer datos día por día con el objetivo de evitar problemas de muestreo con cuentas grandes
Recuperar de forma automática el histórico de cualquier cliente en menos de 1h

Para hacerlo, vamos a usar Python. Primero el código, después las explicaciones, por partes:

Importación de las librerías


import pandas as pd

import numpy as np 

from google.oauth2 import service_account

import pandas_gbq

from google2pandas import *

import time

from datetime import datetime, timedelta



conn = GoogleAnalyticsQuery(
     secrets='ga-creds/client_secrets.json', token_file_name='ga-creds/analytics.dat')

credentials = service_account.Credentials.from_service_account_file(
     'key.json',
 )


# TODO: Set project_id to your Google Cloud Platform project ID.

project_id = "XXXXXX"



#date for further use

today = datetime.today()

La primera parte del código incluye todas las reglas de import de las librerías que usaremos en nuestro código:

Las más importantes son las dos últimas, ya que nos permiten:

Recuperar datos desde la API de Google Analytics
Enviar datos a BigQuery

Para realizar ambas acciones, tendréis que seguir las instrucciones de las librerías, pudiendo así usar las API de ambos servicios. En caso que no os funcione, no dudéis en poneros en contacto conmigo para que os ayude: la primera vez puede costar un poquito ya que la documentación de Google no siempre es la más simple.

Recuperar datos de Analytics


    query = {

        'ids': ga_id,

        'metrics': ['xx','xx','xx'],

        'dimensions': 
['channelGrouping','landingPagePath','year','month','day','country'],

        'start_date': start_date,

        'end_date': end_date,

        'max_results': 10000,

        'sort': ['-sessions']

     }

    df, metadata = conn.execute_query(**query)

    

    df['yearMonth'] = df['year']+'-'+df['month']

    df['mainFolder'] = df['pagePath'].str.split('/',expand=True)[1]



    df['year'] = pd.to_numeric(df['year'])

    df['month'] = pd.to_numeric(df['month'])

    df['day'] = pd.to_numeric(df['day'])

    df['bounceRate'] = pd.to_numeric(df['bounceRate'])

    df['avgSessionDuration'] = pd.to_numeric(df['avgSessionDuration'])

    df['transactionRevenue'] = pd.to_numeric(df['transactionRevenue'])




    return df

Con la siguiente función, podréis recuperar los datos de Google Analytics sin ningún problema. En la parte de métricas, podéis incluir lo que queráis (en general por lo menos el número de sesiones) y en la parte de dimensiones aconsejamos incluir:

Dimensiones de fecha
Ubicación para poder filtrar el informe por país en el caso que haga falta
Ruta para poder analizar los datos por carpeta por ejemplo

Esta lista de dimensión depende del tipo de análisis que queráis hacer y de lo que tenéis disponible en vuestra cuenta.

Subir los datos a BigQuery


mydate = datetime.strptime('2017-01-01','%Y-%m-%d')

mydate_str = mydate.strftime('%Y-%m-%d')

total = mydate-today

print(str(total))




while mydate 
    mydate_str = mydate.strftime('%Y-%m-%d')

    print(mydate_str)

    x = GetAnalyticsData('XXXXX', mydate_str, mydate_str)

    pandas_gbq.to_gbq(x, 'raw_data.google_analytics', 
project_id=project_id,if_exists='append')
    mydate += timedelta(days=1)

Lo que este código hace es muy simple:

Empezando a partir del primero de enero del 2017, extrae los datos (día por día) de Google Analytics y los sube en la tabla que se llama “google_analytics” en el conjunto de datos llamado “raw_data”. Si la tabla no existe, la creará, pero el conjunto de datos tiene que existir antes, sino el script no funcionará.
La subida se hace día por día y no al final del proceso de extracción de los datos, para evitar reanudar todo desde cero en caso que hubiera algún corte de conexión.

Se subirán todos los datos hasta el día de hoy (incluido) a partir de la fecha indicada sin que tengáis que hacer nada más. Ahora que tenemos los datos subidos, podemos finalmente empezar a analizar datos.

Análisis de datos de BigQuery

Opción 1: Usar Google Sheets

Una manera simple de analizar datos es con el conector de Sheets. Lo único que tenéis que hacer es escribir vuestro código SQL para incluir los datos en vuestra hoja de cálculo:

Por ejemplo, podéis usar el siguiente código para saber cuál es el número de sesiones por mes de vuestra tabla.

El código SQL que podéis usar:

SELECT SUM(sessions) as sessions, yearMonth
FROM `my_project.raw_data.google_analytics`
GROUP BY yearMonth

Deberíais obtener un resultado como el siguiente:

resultados-importacion-bigquery-en-sheets

¿Cuál es la ventaja? Estos datos se extraen (y se pueden actualizar en un solo clic) desde BigQuery, por lo tanto, si actualizamos los días en BigQuery de manera diaria, lo que tendremos en Google Sheets se actualizará también si le damos al botón “Actualizar” en la parte inferior de la pestaña:

Podéis montar un dashboard de seguimiento un poco más potente de lo que os permite Analytics, porque con SQL podéis cruzar y modificar datos, ya que se actualiza solo sin que tengáis que volver a reconstruir todo. También podéis montar una plantilla para analizar proyectos nuevos y, lo único que tendréis que cambiar, es la segunda línea del código que os indico más adelante en este artículo.

Qué chulo, ¿no? El único problema es que tenéis que dominar un poco SQL, pero es un lenguaje simple si queréis hacer análisis de datos básicos. En el caso que queráis montar un código más complejo (con condiciones, fusión de datos, etc…), os aconsejo que habléis con un miembro de vuestro equipo que domine un poco más el lenguaje. La gran ventaja es que es un lenguaje bastante común en el mundo del desarrollo web (muchas bases de datos funcionan con SQL), por lo que podéis hablar con ellos para que os ayuden con los códigos que necesitáis.

Opción 2: Jupyter Notebook

Algunas veces, analizar datos en Google Sheets tampoco es una opción porque, como Excel, tiene sus límites. Por tanto, tenemos que usar Jupyter Notebook, que propone un espacio de trabajo interactivo para desarrollar código en Python (o en R, pero como no lo domino, me quedo con Python).

¡Mejora tu SEO!

Si tenéis instalado Python en vuestro sistema, abrid el Terminal e introducid las siguientes instrucciones (línea por línea):

python3 -m pip install –upgrade pip
pip3 install jupyter
jupyter notebook

Una vez hecho, se debería abrir una ventana en vuestro navegador y deberíais poder visualizar lo siguiente:

Ahora, si agregamos el siguiente código en la primera celda, podréis recuperar la misma información que vimos hace un momento (la tabla con el tráfico por mes):


import pandas as pd

import numpy as np 

from google.oauth2 import service_account

import pandas_gbq

from google2pandas import *

import time

from datetime import datetime, timedelta

import matplotlib.pyplot as plt 



conn = GoogleAnalyticsQuery(
     secrets='ga-creds/client_secrets.json', token_file_name='ga-creds/analytics.dat')

credentials = service_account.Credentials.from_service_account_file(
     'key.json',
 )



# TODO: Set project_id to your Google Cloud Platform project ID.

project_id = “xxxxxx"

sql = """

SELECT SUM(sessions) as sessions, yearMonth

FROM `xxxxxxx.raw_data.google_analytics`

GROUP BY yearMonth

"""

df = pandas_gbq.read_gbq(sql, project_id=project_id)

df

Si ejecutáis el código, deberíais ver algo similar a lo que os muestro a continuación:

importar-datos-bigquery-jupyter-notebook

Una vez los datos estén disponibles, podéis después usar el módulo que deseáis para visualizar los datos y sacar conclusiones. Por ejemplo, podemos saber sin problema cuál es la repartición de los ingresos a lo largo del año entre los diferentes canales de adquisición del portal.

¿Se podría hacer también en Sheets? Tal vez sí, pero Jupyter Notebook (o más bien Python, que es el lenguaje que usamos en este caso), nos permite gestionar datos con millones (sí sí, millones) de líneas sin ningún problema.

¿Es más complicado que en Sheets? Tal vez la primera vez que lo hagáis, pero la verdad es que, como el mismo código se puede usar para varios proyectos, ganaréis en eficiencia. Además, permite manipular datos después de extraerlo desde BigQuery sin ningún problema (agregar una condición por ejemplo). Por otro lado, no hemos de olvidar que, si agregamos muchas fórmulas, muchas tablas dinámicas y por mucho que Sheets funcione en los servidores de Google, la actualización de los datos puede tardar un tiempo. Este problema pasará en Jupyter únicamente si tenéis una cantidad impresionante de datos, algo que no pasará en 99% de los casos.

Y voilà, espero que os haya gustado y, como siempre, podéis enviarme vuestras dudas o sugerencias en la sección de comentarios.