En consultoría SEO uno de los análisis que realizamos con más frecuencia es el de los exports de Screaming Frog. Ya sea durante el desarrollo de una auditoría SEO o de forma cíclica en una consultoría mensual en función del estado del proyecto y la implementación de cambios es normal que crucemos datos de rastreo con analytics, Ahrefs, etc. para montarnos cuadros de mando, presentar informes y, lo más importante, revisar implementaciones y tomar decisiones de cambio.
- Por qué crear un maestro de datos
- #1 Preparación de la información de rastreo
- #2 Pasar datos a Google Sheets
- #3 Creación de Maestro de Datos
- Aplicaciones prácticas
- Limitaciones
Por qué crear un maestro de datos
Automatizar todo lo posible este proceso repetitivo es de gran ayuda ya que conseguimos finalmente un importante ahorro de tiempo de preparación de tablas, recuento de datos para sacar KPIs técnicos y otros cruces de datos, pero en ocasiones la información puede no coincidir y la configuración que podíamos tener se nos trastoca. En los exports de Screaming Frog tenemos una cantidad ingente de información ordenada por columnas pero este orden puede variar de un rastreo a otro.
Basta por ejemplo con que exista una URL que tenga más de una etiqueta H1 para que el último rastreo ya no cuadre con el anterior.
Para solventar esta situación tenemos la opción de normalizar los datos y conseguir que, independientemente de qué web rastreemos y cual sea la situación de esa web, las columnas con la información de rastreo y el cruce de datos siempre coincidan en el orden que deseamos. A esto es a lo que podemos llamar MAESTRO DE DATOS.
En este artículo te voy a explicar paso a paso cómo montar este maestro de datos exportado de Screaming Frog con mis queridas Hojas de Cálculo de Google 
.
#1 Preparación de la información de rastreo
Este paso es el más sencillo de todos y el que, probablemente, habrás realizado en más de una ocasión por lo que voy a obviar cosas como la configuración de Screaming Frog, campos a extraer, etc. Al finalizar el rastreo de la web que estamos analizando deberemos tener seleccionada la pestaña «Internal», desplegar el selector «Filter», escoger «HTML» para quedarnos sólo con datos de páginas y finalmente darle a «Export».
En cuanto al documento de Google Sheets que vayamos a usar de plantilla para los proyectos deberemos crear en él una pestaña llamada «Export Screaming» -por ejemplo- para volcar la información y otra que podemos llamar «Maestro Screaming».
Importante : Al exportar desde Screaming Frog puedes hacerlo en 3 tipos distintos de fichero .csv separador por comas, .xls y .xlsx. Para esta tarea en cuestión te recomiendo que uses .xls o .xlsx para ahorrar pasos intermedios.
#2 Pasar datos a Google Sheets
Otro paso bien sencillo ya que aquí tan sólo tenemos que abrir el .xls o .xlsx exportado, seleccionar todos los datos, copiarlos y pegarlos en la pestaña «Export Screaming» que tenemos en nuestro documento-plantilla.
#3 Creación de Maestro de Datos
Aquí viene «la chicha» del artículo y lo primero que voy a hacer es mostrarte un ejemplo de la fórmula que usaremos para montar el maestro en este caso para traernos las URLs del export (columna Address):
=QUERY(‘Export Screaming’!A:AAC;»SELECT «&SUSTITUIR(DIRECCION(1;COINCIDIR(«Address»;’Export Screaming’!A1:AAC1;0);4);1;»»))
La clave está en la función «Query» que será la responsable de hacer la consulta de cada columna en base a los criterios que vayamos especificando. El ejemplo que voy a usar para desgranar esta fórmula es para la columna «Address» -la de las URLs-, para todas las demás bastará con que cambiemos el «Address» por el nombre de la cabecera de la columna que nos queramos traer.
Para que sea más sencillo explicaré de dentro hacia fuera qué es lo que hace cada una de las funciones incluidas en la fórmula.
COINCIDIR
A esta función le indicamos un texto que debe buscar «Address» en la primera fila y un rango de celdas en el que debe buscar dicho texto ‘Export Screaming’!A:AAC lo que hará que nos devuelva el valor del número de columna en la que se encuentra ese texto en la primera fila. Para «Address» será 1 para «Status Code» por defecto será 3. El valor «0» al final de la función nos ayudará a que este número de columna sea secuencial contando columnas de izquierda a derecha.
DIRECCIÓN
Esta función lo que hará es devolvernos el identificador o referencia de celda que coincide con el texto de cabecera que estamos buscando. Siguiendo con el ejemplo de «Address«, al pasarle el dato resultante de la función COINCIDIR nos devolverá «A1» y si fuese «Status Code» nos devolvería «C1«. El número 4 que indicamos al final de la función nos sirve para especificar que queremos la referencia de celda en modo relativo, si por ejemplo quisiésemos una celda en modo absoluto pondríamos un 1 en lugar del 4 y nos devolvería «$A$1» en lugar de «A1«.
SUSTITUIR
Esta función la concatenamos en cierto modo al texto del SELECT que necesitará la función QUERY para indicarle qué letra de columna es la que debemos usar para hacer nuestra consulta. En el caso de «Address» nos devolverá «A» y en el caso de «Status Code» nos devolvería «C«. Básicamente lo que hacemos es coger la referencia de celda que nos da la función DIRECCION, es decir, «A1» y eliminar el valor numérico. El SELECT resultante para el ejemplo de «Address» sería «SELECT A«
QUERY
Finalmente a la función QUERY le pasamos un rango de datos ‘Export Screaming’!A:AAC, una consulta muy similar al lenguaje SQL «SELECT A» y una indicación opcional de si queremos cabeceras o no que en nuestro caso dejamos en blanco.
Repitiendo esta fórmula por columnas para cada dato que con el que deseemos contar conseguiremos tener un maestro de datos normalizado en la pestaña «Maestro Screaming» de nuestro Sheets.
Apliaciones prácticas
A partir de aquí entran en juego tus necesidades y tu creatividad para trabajar toda la informacion como quieras. Si te interesa el tema y quieres ver una aplicación práctica de esto te recomiendo que leas este hilo de Carlos Ortega.
Limitaciones
Por desgracias para nosotros Google Sheets no es un pozo sin fondo al que le puedas volcar toda la información que desees por lo que estaremos sujetos a las limitaciones inherentes de esta herramienta.
En el momento de publicar este post tenemos un límite de 5000000 celdas de no más de 256 columnas por hoja.
Para que te hagas una idea… Habiendo conectado las APIs de Analytics, Ahrefs y PageSpeed te puedes ir a más de 100 columnas fácilmente (A:DD) por lo que si por ejemplo el sitio web a rastrear tiene unas 8000 URLs (filas) estaremos hablando de más de 800000 celdas más las «duplicadas» que hayas seleccionado en el Maestro de Screaming Frog.
Si quieres ver otro tipo de maestros de datos de Screaming Frog te recomiendo que le eches un vistazo a mi add-on para Sheets «Crawl Overview Importer«.
Hasta aquí el post, como siempre espero que te resulte interesante y de utilidad. Son bienvenidas tus dudas, sugerencias y comentarios.
