Si eres alguien que usa constantemente Google Analytics, Adobe Analytics o algún programa de Clickstream similar, en algún momento debes hacer pasado por la pregunta ¿Cómo llego al siguiente nivel con esta herramienta? Ya sabes el comportamiento del usuario en el sitio web, pero ¿Podría usar modelos estadísticos para crear segmentos (Clusters) de usuarios?, ¿Podría usar otro modelo para combinar las compras realizadas y realizar recomendaciones?

Bueno, este artículo es para ti: Bigquery, R y Tableau te permitirán ir más allá de los análisis que normalmente realizas y comenzar a trabajar con modelos estadísticos. Ahora, es bueno mencionar que este es un artículo avanzado. Si conoces SQL, sabes algo de programación y te encanta el análisis de datos, estás en el lugar indicado, sino estás tan familiarizado con lo mencionado anteriormente es probable que necesites leer con muuuucha paciencia.

BigQuery

Big query es la solución de Google Analytics Premium (De la cuál somos partners) que permite ver la data de Google Analytics sin procesar y realizar consultas con el estándar SQL. Además, Tableau y R cuentan con conectores para BigQuery.

Cómo acceder a BigQuery

Para tener acceso a esta herramienta puedes entrar a su versión demo en https://cloud.google.com/bigquery/. Cabe destacar que deberás colocar tu tarjeta de crédito y que en esta versión no podrás ver la data de Google Analytics sin procesar.

Si usas la versión Premium de Google Analytics (ahora llamada Google Analytics 360) debes habilitar el uso de Bigquery en https://bigquery.cloud.google.com

Una vez ahí debes dar clic en View my console
Google Cloud Platform

Al realizar el paso anterior te va a mostrar la siguiente pantalla donde debes dar clic en Create a Project.

Welcome BigQuery

Una vez hecho esto te pedirá el nombre del proyecto. En este caso le puse proy01.
Nuevo Proyecto

Al hacerlo tendrás la siguiente respuesta:
Google APIS

Lo cual confirma que ya estás en habilitado para probar Bigquery 🙂

Para empezar a hacer las consultas debes volver a: https://bigquery.cloud.google.com/

Una vez ahí en la parte superior derecha verás un botón llamado: Compose query

A continuación un primer query para probar:

SELECT author, text FROM [bigquery-public-data:hacker_news.comments]
where author is not null
LIMIT 1000

Esta consulta arroja el siguiente resultado:
Compose Query

Lo que acabo de hacer es perdirle a BigQuery me brinde los primeros mil comentarios de la tabla bigquery-public-data:hacker_news donde tenga autor, es decir, no sea nulo (NULL)

Bigquery y SQL

Si ya conoces SQL, puedes ver acá las funciones que puedes utilizar:

https://cloud.google.com/bigquery/query-reference#stringfunctions

Básicamente el mismo set de palabras reservadas que cualquier manejador de base de datos:

Query Syntax

Si no estás tan familiarizado con SQL aquí algunos ejemplos:

  1. Conocer volumen de transacciones por día y por hora. Esta es una tarea que normalmente podrías realizar en Google Analytics, pero qué sucede si tienes que sacar las transacciones por hora de un mes pero día por día. Hacerlo en Google Analytics requeriría demasiado tiempo, pero con Big Query se puede realizar en un SQL sencillo:

    SELECT DAYOFWEEK( date )  as dia ,
    hits.hour as hora,
    sum( totals.transactions ) total_transacciones
    FROM TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2016-05-01'), TIMESTAMP('2016-05-31'))
    group by dia, hora
    New QueryAl ejecutar el query nos da el siguiente resultado:
    Bigquery First query
    Al tener el resultado puedes bajar la tabla a un archivo CSV directamente o a un Google Sheet (hoja de cálculo de google docs). Con ello puedes armar un mapa de calor interesante donde puedes ver las horas pico por día de la semana.Google Sheets

    Simple, pero potente, te muestra que días de las semana y a que horas se tienen picos de transacciones ( Pueden ser ventas, pedidos o como hayas decido etiquetar).

  2. Las 10 primeras páginas vistas de todo el mes: Al igual que el ejemplo anterior si tengo que hacer el total de un mes en Google Analytics, no es tanto problema. Si tuviera que hacerlo día por día es donde el tema se complica. Nuevamente Big Query puede simplificarnos la tarea:

    SELECT date, hits.page.pagePath,  visitas , ranking
    FROM
    (
    SELECT date, hits.page.pagePath,  visitas ,
    RANK() OVER (PARTITION BY date  ORDER BY visitas DESC) ranking
    FROM
    (
    SELECT date, hits.page.pagePath, sum( totals.visits )  as visitas
    FROM TABLE_DATE_RANGE([37801020.ga_sessions_],
    TIMESTAMP('2016-04-01'), TIMESTAMP('2016-04-30'))
    GROUP BY date,  hits.page.pagePath
    )
    )
    WHERE ranking <= 10
    El resultado sería una tabla simple de doble entrada que te dice tus páginas principales día por día:
    Tabla simple

¡Ya! ¿Pero todo eso ya lo puedo hacer con Google Analytics?
Sí, pero aparte de ser mucho más tedioso, con Bigquery puedes:

  1. Llevar la data desde scripts en R y automatizar algunas tareas.
  2. Llevarlo a Tableau o Excel (como el ejemplo) y armar un Dashboard con una visualización bastante amigable.
  3. Y principalmente realizar modelos de Data Mining a partir de lectura de grandes volúmenes de información. (Esto lo veremos en el siguiente post)

BigQuery: Sub-queries y funciones más avanzadas

A continuación algunos problemas comunes en BigQuery y como resolverlos:

  1. Acceder a dimensiones personalizadas en conjunto con otros campos. No pueden ser accedidas como un campo adicional como si se puede normalmente en Google Analytics. Habrá que usar subqueries y alguna función de agregación como  max() o  min () .En Google Analytics se puede tienes una vista como esta:SubqueriesDonde se puede acceder al Rendimiento de la lista de productos:Rendimiento de la lista de productosEsto en query , es muy complicado obtenerlo.  En este sentido, es más práctico usar Google Analytics, a no ser que quieras tener una tabla con esta información diaria y quieras cruzarla con otros datos adicionales más para entender mejor la información.Para poder hacerlo, podrías usar una consulta como esta:


    Select productlist.date as fecha,
    productlist.hits.product.productListName as plName,
    productlist.hits.eventInfo.eventCategory as eventCategory,
    productlist.hits.eventInfo.eventAction as eventAction1,
    productlist.hits.eventInfo.eventLabel as eventLabel,
    productlist.hits.eCommerceAction.action_type as actiontype,
    productlist.hits.eCommerceAction.step as step,
    dimensiones.tipo_linea as tipo_linea,
    ( CASE WHEN eventAction1 = 'List View' THEN   EXACT_COUNT_DISTINCT(productlist.fullVisitorId)
    ELSE count(*)  END ) as  total
    FROM
    ( SELECT date, fullVisitorId, visitId, hits.product.productListName,
    hits.eventInfo.eventCategory, hits.eventInfo.eventAction, hits.eventInfo.eventLabel,
    hits.eCommerceAction.action_type, hits.eCommerceAction.step
    FROM TABLE_DATE_RANGE([9999999999.ga_sessions_], TIMESTAMP('2016-04-01'), TIMESTAMP('2016-04-30'))
    WHERE hits.product.productListName = 'operaciones recarga') productlist
    LEFT OUTER JOIN EACH
    ( SELECT date, fullVisitorId, visitId, MAX(IF(hits.customDimensions.index=9, hits.customDimensions.value, NULL)) WITHIN RECORD AS tipo_linea,
    FROM TABLE_DATE_RANGE([90234794.ga_sessions_], TIMESTAMP('2016-01-01'), TIMESTAMP('2016-04-30')) )  dimensiones
    ON productlist.date = dimensiones.date
    and productlist.fullVisitorId = dimensiones.fullVisitorId
    and productlist.visitId =  dimensiones.visitId
    GROUP BY fecha ,
    plName  ,
    eventCategory ,
    eventAction1 ,
    eventLabel ,
    actiontype,
    step,
    tipo_linea

    El Resultado sería:

    Resultado

  2. Rango de Fechas. Bigquery almacena una tabla de Google Analytics por día.  Entonces ¿Cómo hago para acceder a información de un rango de fechas?Para poder acceder a varios días debes usar la función TABLE_DATE_RANGE, en la cual puede establecer un rango de fechas. Son 2 parámetros: fecha inicial y fecha final. Aquí como establecer el SQL:FROM TABLE_DATE_RANGE([90234794.ga_sessions_], TIMESTAMP(fecha Inicial), TIMESTAMP(fecha final))

    Es decir:
    FROM TABLE_DATE_RANGE([90234794.ga_sessions_], TIMESTAMP('2016-04-01'), TIMESTAMP('2016-04-30'))

    Si quieres acceder a los últimos 30 días, últimos 7 días,  últimos N días,  puedes usar las siguientes funciones:

    Rango fijo conocido

    FROM TABLE_DATE_RANGE([999999999.ga_sessions_], TIMESTAMP('2016-04-01'), TIMESTAMP('2016-04-30'))

    Últimos 30 días

    FROM (TABLE_DATE_RANGE([99999999.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -30, 'DAY'),  DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')   ))

    Últimos 7 días

    FROM (TABLE_DATE_RANGE([99999999.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'),  DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')   ))

    Conteos únicos o como se conoce en Google Analytics como Unique Events

    EXACT_COUNT_DISTINCT, te permite hacer el clásico SELECT DISTINCT del set de palabras reservadas del SQL Standard

    Nota: En el ejemplo ,  99999999 , es el identificador de tu tabla en Bigquery. Cuando tengas tu cuenta de bigquery, podrás ver tus cuantas con esos números al principio del nombre de la tabla. También le puedes referir a él como  default_dataset

Cuando no es mucha la cantidad de registros, biquery te permite exportar  directamente al tipo de archivo .CSV  o incluso si te parece mejor,  a Google Sheet ( Google Docs)

Google Docs

Pero cuando tenemos mucha información, es mejor usar R o Tableau, dependiente del tipo de actividad que quieras hacer. R para análisis usando algoritmos de minería de datos y pedirle a los datos que te hablen un poco de ellos.  Con Tableau para hacer grandes visualizaciones. En los 2 siguientes post, vamos a revisar como usar BigQuery desde R y Tablaeu, con algunas rutinas interesantes para descubrir patrones de búsqueda.