En el sector del SEO llevamos mucho tiempo en un estado permanente de “herramientitis” en el que constantemente somos bombardeados por herramientas pensadas (en principio) para ahorrarnos horas de trabajo en nuestro día a día pero… ¿hasta qué punto no podemos ahorrarnos alguna y obtener los mismos datos con una pequeña inversión de tiempo en vez de hacer una inversión económica?

Como persona adicta a seguir aprendiendo cosas nuevas cada día, hoy comparto una sencilla forma de montarte tu propio Log Analyzer con unos cuantos comandos de consola y BigQuery + Google Data Studio.

Antes de empezar

Para poder seguir este tutorial, debes tener en cuenta esto:

  • Los comandos utilizados son de Linux, por lo que si no usas Linux o MacOS tendrás que apañártelas para lograr un archivo de salida como el que muestro más adelante para poder seguir la parte de BigQuery y Data Studio.
  • El formato de logs que utilizo es el siguiente: “%h %l %u %t \”%r\” %>s %b \”%{Referer}i\” \”%{User-agent}i\”” donde:
    • %h: dirección IP que realiza la petición
    • %l: identidad de usuario (identd)
    • %u: user name
    • %t: fecha y hora de la petición
    • %r: petición
    • %>s: código de respuesta HTTP
    • %b: tamaño en bytes de la respuesta
    • Referer: Cabecera del referer de la petición
    • User-agent: identificación del navegador/bot

Limpiando datos con cat, grep, sed y alguna cosa más

Primero de todo, partimos de la base de tener un archivo que se llame logs.txt con registros como estos:
66.249.89.60 - - [01/Feb/2018:02:33:11 +0000] "GET /landing2.html HTTP/1.1" 200 22000 "-" "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
66.249.89.60 - - [01/Feb/2018:02:33:11 +0000] "GET /landing2.html HTTP/1.1" 200 22000 "-" "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
66.249.70.1 - - [01/Feb/2018:02:33:11 +0000] "GET /landing3.html HTTP/1.1" 200 23000 "-" "AdsBot-Google (+http://www.google.com/adsbot.html)"
66.249.70.28 - - [01/Feb/2018:02:33:11 +0000] "GET /image1.jpg HTTP/1.1" 404 40000 "https://midominio.com/landing3.html\" "Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
66.249.89.60 - - [01/Feb/2018:08:15:40 +0000] "GET /font.woff2 HTTP/1.1" 200 20000 "https://midominio.com/landing4.html\" "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
66.249.83.144 - - [01/Feb/2018:08:15:40 +0000] "GET /script1.js HTTP/1.1" 301 5000 "https://midominio.com/landing1.html\" "Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
66.249.89.34 - - [01/Feb/2018:12:51:54 +0000] "GET /landing6.html HTTP/1.1" 404 40000 "-" "Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
66.249.89.34 - - [01/Feb/2018:14:15:40 +0000] "GET /landing1.html HTTP/1.1" 200 21000 "-" "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"

Ahora toca convertir este txt en un archivo csv para poder tener cada dato por separado. Para ello, puedes usar este script teniendo en cuenta que si el formato de los logs no es el mismo que he utilizado yo no lograrás el resultado esperado:

  1. #!/bin/sh
  2. cat logs.txt | grep "Googlebot" | sed 's/"//g' | sed 's/\[//g' | sed 's/\]//g' | sed 's/Mozilla\/5\.0 (compatible; Googlebot\/2\.1; \+http:\/\/www\.google\.com\/bot\.html)/Googlebot-Desktop/' | sed 's/Mozilla\/5\.0 (Linux; Android 6\.0\.1; Nexus 5X Build\/MMB29P) AppleWebKit\/537\.36 (KHTML\, like Gecko) Chrome\/41\.0\.2272\.96 Mobile Safari\/537\.36 (compatible; Googlebot\/2\.1; \+http:\/\/www\.google\.com\/bot\.html)/Googlebot-Smartphone/' | sed 's/Googlebot-Image\/1\.0/Googlebot-Image/' | sed 's/Googlebot-Video\/1\.0/Googlebot-Video/' | sed 's/Googlebot\/2\.1 (\+http:\/\/www\.google\.com\/bot\.html)/Googlebot-Desktop/' | tr ' ' '\t' | cut -f 1,4,7,9,10,11,12 > logs-clean.txt
  3. for IP in `cut -f 1 logs-clean.txt`
  4.     do
  5.         if [ `host $IP | grep "google" | wc -l` -eq 0 ]
  6.         then
  7.              echo "GOOGLE-NOK";
  8.         else
  9.              echo "GOOGLE-OK";
  10.         fi
  11. done > ips-ok.txt
  12. for DATE in `cut -f 2 logs-clean.txt`
  13.     do
  14.         LC_ALL=en_US.utf8 date -j -f "%d/%b/%Y:%k:%M:%S" $DATE +%Y-%m-%d
  15. done > dates-ok.txt
  16. paste logs-clean.txt ips-ok.txt dates-ok.txt > data-ok.txt
  17. cat data-ok.txt | cut -f 3,4,5,6,7,8,9 | tr '\t' ',' > logs-filtered.txt
  18. (echo "Path,Response,Bytes,Referral,UserAgent,IsGoogle,Date" && cat logs-filtered.txt ) > logs-final.csv
  19. rm logs-clean.txt ips-ok.txt dates-ok.txt data-ok.txt logs-filtered.txt

A tener en cuenta para que todo salga bien

  • Estoy más que segura de que mi script se puede mejorar y optimizar hasta el infinito y más allá (se aceptan sugerencias, mis conocimientos de shell scripting son bastante básicos), pero cumple con su función y mi objetivo es que pueda servirte de base para crear tu propio script adaptado a tus logs y a los bots que quieres monitorizar. Además, si nunca has cacharreado con la consola, te puede servir para animarte a jugar, probar, equivocarte y aprender.
  • Tu archivo de logs debe llamarse logs.txt (o cambia el nombre del archivo en el script con el nombre de tu archivo de logs)
  • Si lo que tienes son archivos de logs por día y no sabes cómo unirlos en un único archivo, Lino te cuenta en este post cómo unirlos (además de muchas otras cosas ;) )
  • Guarda este script como clean-logs.sh en la misma carpeta en la que tengas el archivo logs.txt
  • Para ejecutarlo, desde consola en la carpeta en la que tengas el script y ejecuta:
    ./clean-logs.sh
  • Dependiendo del número de líneas que tenga tu archivo de logs puede tardar más o menos en ejecutarse el script. Cuando acabe, encontrarás en la carpeta donde tienes tus logs y el script un nuevo archivo que se llama logs-final.csv con datos como estos:
    Path,Response,Bytes,Referral,UserAgent,IsGoogle,Date
    /landing4.html,200,24000,-,Googlebot-Smartphone,GOOGLE-OK,2018-02-04
    /landing6.html,404,40000,-,Googlebot-Desktop,GOOGLE-OK,2018-02-05
    /landing1.html,200,21000,-,Googlebot-Desktop,GOOGLE-OK,2018-02-04
    /landing2.html,200,22000,-,Googlebot-Desktop,GOOGLE-OK,2018-02-01
    /landing6.html,404,40000,-,Googlebot-Desktop,GOOGLE-OK,2018-02-03
    /landing2.html,200,22000,-,Googlebot-Desktop,GOOGLE-OK,2018-02-06
Así pues, en este csv tendremos información de:
  • Path: URL sobre la que se realiza la petición.
  • Response: Código de respuesta HTTP.
  • Bytes: Peso en bytes de la respuesta.
  • Referral: En caso de haberla, ruta desde la que el bot accede al Path.
  • UserAgent: Bot que realiza la petición. Disclaimer: he separado Googlebot Desktop de Mobile para poder visualizar las peticiones a cada tipo de contenido. Si prefieres no hacer esta distinción, cambia en el script "Googlebot-Desktop" y "Googlebot-Mobile" por "Googlebot" y ya lo tendrías arreglado.
  • IsGoogle: Resultado de hacer un reverse DNS para comprobar que la IP desde la que se realiza la petición es de Google. Esto lo hago para poder detectar si estamos recibiendo peticiones "en nombre de Google" que no lo son y, si fuera preciso, bloquear determinados rangos de IPs que nos estuvieran friendo a peticiones fake.
  • Date: Fecha en la que se realiza la petición con formato AAAA-MM-DD, que es el que reconoce Google Data Studio en las fuentes de datos que le conectamos.

Bienvenidos al maravilloso mundo de Google BigQuery

Ya estás mucho más cerca de poder tener tu propio Log Analyzer. Ahora que ya tenemos la fuente de datos a analizar, sólo nos falta tenerlo disponible para consultar desde Google Data Studio. Si tu archivo no es muy grande y te cabe en un Google Sheets, es una excelente (y sencilla) opción subirlo allí. Pero si vas a exceder las 57.000 filas necesitarás otra opción. Y aquí es donde entra en juego Google BigQuery.
Creando un nuevo proyecto

Para empezar a usar BigQuery, primero necesitaremos crear un nuevo proyecto. Si no has usado antes Google Cloud, tendrás que configurar la cuenta para poder activarlo pero tiene recompensa: Google te ofrece una Lino te cuenta en este post cómo unirlos para gastar en 12 meses en sus servicios en la nube ;)

BigQuery - Crear nuevo proyecto
Creando un nuevo dataset

Ahora que tenemos el proyecto, podemos crear un nuevo dataset:

BigQuery - Crear un dataset
Creando una tabla

Y en este dataset podremos crear una tabla con los datos de nuestro archivo logs-final.csv:

BigQuery - Crear tabla

Y ya tenemos nuestra tabla de datos cargada en BigQuery:

BigQuery - Tabla creada

Cómo conectar Google Data Studio con BigQuery

¡Por fin! Este es el momento al que queríamos llegar desde el principio del post. Si has usado antes Data Studio, ya sabrás cómo elegir BigQuery como fuente de datos para un nuevo proyecto, pero si no estás familiarizad@ con esta herramienta, vamos por partes:
Conectando con BigQuery

Al seleccionar la opción "Create new data source" podremos escoger entre diferentes conectores.

Data Studio - BigQuery connector

Buscaremos la opción de BigQuery, que nos permitirá seleccionar proyecto, dataset y tablas a importar al report de Data Studio:

Data Sutdio - Seleccionar tabla BigQuery

Y una vez seleccionemos la tabla ya nos aparecerán los campos disponibles en la misma para poder agregarlos al report:

datastudio2

Campos calculados

Y con estos datos cargados en BigQuery, podemos crear nuevos campos que se basen en cálculos sobre los campos ya disponibles. Por ejemplo, teniendo en cuenta el formato de los datos de muestra que he utilizado en capturas anteriores, he creado un campo llamado "ContentType" en base a la extensión del campo "Path" (url) para determinar el tipo de contenido que está recibiendo los hits:

Data Studio - Campos calculados

Diseñando tu propio dashboard

Ahora sí. Cuando hayas creado todos los campos calculados que creas oportunos, puedes ponerte manos a la obra y diseñar tu propio dashboard con sus filtros y demás parafernalias para dejarlo con todo lo que necesites para analizar el comportamiento de Googlebot en tu web. Como ejemplo, te dejo esta pequeña propuesta con 4 visualizaciones básicas que podrías añadir en tu propio Log Analyzer: Data Studio - Ejemplo dashboard Y si has llegado hasta aquí y decides poner en práctica todo esto, no dudes en compartir en los comentarios las dudas que te puedan surgir así como propuestas de visualizaciones o de mejoras que puedan ayudar a otros usuarios ;)