Análisis en Google BigQuery

Para el desarrollo de un dashboard de análisis de la conversación sobre #BlackFriday y #CyberMonday recurrí al uso de Google BigQuery para el desarrollo de cálculos avanzados, antes de comenzar con la representación visual del análisis.

En este post te cuento el proceso desde la creación del proyecto hasta el desarrollo de las diferentes queries de consulta.

Crear un proyecto

La estructura del proyecto es clave para no perdernos buscando dónde están los datos. Almacenar las tablas y vistas en diferentes proyectos, es muy útil cuando manejas información que corresponde a diferentes ideas, trabajos o clientes.

Dentro de cada proyecto puedes albergar diferentes tablas y vistas, procedentes de fuentes de datos distintas o consultas específicas que desarrolles.

Subir un documento a una tabla

Las conexiones de Google BigQuery son múltiples. Puedes subir un archivo desde diferentes fuentes, pero la opción más útil es hacer una conexión a Google Cloud Platform. Desde aquí se genera una tabla con el archivo.

Creación de una vista

Para agilizar la consulta de información recurrente, es muy útil guardar vistas en base a queries ya desarrolladas. Estas vistas pueden conectarse, al igual que las tablas, a Google Data Studio para la visualización de los datos.

Cálculos avanzados con SQL

Haciendo uso del lenguaje SQL, con algunas adaptaciones para Google BigQuery, es fácil desarrollar cálculos avanzados y guardarlos en una vista. Esta vista puede cargarse más tarde como fuente de datos, por ejemplo en Google Data Studio.

Google BigQuery implementa por defecto SQL estándar. Aquí tienes la documentación para el desarrollo de queries en su plataforma.

Tratamiento de los datos en Google BigQuery

💻 En este repositorio de GitHub puedes encontrar el código para la descarga de contenido de Twitter, los archivos csv y la exploración de datos para este proyecto.

Para comenzar hay que tener en cuenta los datos de los que dispongo, las características del dataset y que para extrapolar estas métricas a un análisis de Social Media hay que hacer algunas suposiciones.

• Carezco del número de impactos; pero el número de seguidores puede ser una alternativa.
• Por cada retweet que se comparte, se muestra el total de interacciones del tweet original, por lo que para el cálculo del engagement considero solo las interacciones del tweet original y los seguidores del usuario con la comunidad más grande.
• Si un usuario participa más de una vez en la conversación, aparece varias veces en el dataset.

Teniendo en cuenta estas consideraciones, es más fácil la ordenación y tratamiento de datos para un análisis coherente.

Tratamiento de las fechas: días y meses

El formato de fecha del dataset era timestamp, así que para posibles agrupaciones de datos, generé un campo ‘day’ y un campo ‘month’ con la función EXTRACT().

select
  extract(day from date) as day,
  extract(month from date) as month
from `BF.bfcm`

Hashtags

El dataset original descarga los hashtags que tiene cada tweet en el siguiente formato:

[{‘text’: ‘BlackFriday’, ‘indices’: [53, 65]}]

Es un JSON en el que cada diccionario indica el texto y en qué posición del tweet se encuentra. Pueden extraerse todos los hashtags en un solo campo o cada hashtag en un campo diferente. Yo he optado por la segunda opción.

Para trabajar con el JSON, hago uso en Google BigQuery la una función JSON_EXTRACT(). Con la siguiente query extraigo el primer y el segundo hashtag.

select
  JSON_EXTRACT(hashtags, '$[0].text') AS hashtag1,
  JSON_EXTRACT(hashtags, '$[1].text') AS hashtag2
from `BF.bfcm`

Esta query la incluí en la query general, para hacer una vista básica de los resultados.

Tipo de tweet

Aunque no aparece en el dataset original, inferí el tipo de tweet en función de cómo empieza: Retweet, Respuesta y Original.

select 
  text,
  case
    when text LIKE "RT%" THEN "Retweet"
    when text like "@%" THEN "Reply"
    else "Original"
  end as type
from `BF.bfcm`

Y con éste generé una vista para implementar a Google Data Studio.

Interacciones

Puesto que cada retweet que se incluye en el dataset, refleja el total de interacciones del tweet original, realizo un cálculo de interacciones solo en base a los tweets originales.

select 
  count(text) as mentions,
  count(distinct text) as unique_mentions
from `BF.bfcm`

Con esta query veo que hay 177K menciones, de las cuales 46,7K son únicas.

Para calcular más adelante el Engagement Rate, necesitaré también el número de seguidores del usuario que mayor comunidad tenga del tweet compartido.

with win as
  (select text,
     followers,
     retweets,
     favorites,
     row_number() over (partition by text order by followers desc) as rn
  from `BF.bfcm`)
select
  win.text,
  win.retweets,
  win.favorites,
  win.followers,
  win.rn
from win
where win.rn=1

Y con esta query, generé una vista llamada engagement que utilizo más adelante para el cálculo del engagement rate.

Cálculos que desarrollé en Google BigQuery

Los cálculos más complejos que necesité para el análisis, los desarrollé en Google BigQuery, los guardé como vistas y los conecté como fuentes de datos a Google Data Studio.

Alcance Potencial

Para el cálculo del alcance potencial, considero el número de seguidores que tiene cada uno de los usuarios que ha participado en la conversación. Reduzco el dataset solo a los distintos usuarios que participan y sumo los seguidores.

select 
   sum(fol.followers) as reach
from
  (select 
     distinct(user),
     max(followers) as followers
   from `BF.bfcm`
   group by user) fol;

Puesto que hay usuarios que participan más de una vez y que el número de seguidores va variando, selecciono por cada usuario el número máximo de seguidores que tiene en el dataset.

Engagement Rate

Para el cálculo del Engagement Rate o ratio de interacción, por lo general se consideran el número de interacciones y el número de impresiones de cada publicación (o el alcance). En este caso, solo tengo el número de seguidores de cada usuario, por lo que lo tomaré como el impacto.

Por la parte de las interacciones, como indicaba antes, cada retweet replica el número de interacciones del tweet original, así que solo selecciono un tweet, sus interacciones y el número de seguidores del usuario que mayor comunidad tenga de este tweet.

Para calcular el Engagement Rate de cada tweet (incluidos los duplicados) utilizamos interacciones/followers.

select
  text,
  retweets+favorites as interactions,
  followers,
  CASE WHEN followers > 0 THEN ((retweets+favorites)/followers)*100
  ELSE 0
  END as eng_rate
from `BF.engagement`
order by eng_rate DESC

Y para el cálculo del Engagemente Rate general y por cada uno de los hashtags, utilizo las interacciones de los tweets originales (eliminando los duplicados) y el alcance potencial con la función ROLLUP().

select
  eng.query,
 (sum(eng.engagement)/sum(fol.followers)) * 100 as engagement_rate
from 
(select
  distinct(text),
  query,
  favorites,
  retweets,
  retweets + favorites as engagement
 from `BF.bfcm`) eng
 join
 (select 
    distinct(user),
    max(followers) as followers
  from `BF.bfcm`
  group by user) fol
 on 1=1
 group by rollup(eng.query)

Tablas y Vistas a conectar a Google BigQuery

Después de este desarrollo, había generado:

• Una vista base con la información filtrada y tratada (añadiendo día y mes, hashtags y tipo de contenido).
• Una vista con el alcance potencial de la conversación.
• Una vista con las interacciones de los tweets únicos y los seguidores del usuario con mayor comunidad correspondiente a cada tweet.
• Una vista con el engagement rate.

A partir de aquí la representación visual en muy fácil porque ya tenemos todos los datos disponibles para el dashboard. ¡Solo falta pintarlos!

Happy Coding!