Análisis de tráfico con Sheets, SEMRUSH y Ahrefs | SEO

Para cualquier proyecto nuevo que entra en una agencia SEO, uno de los primeros pasos es realizar un audit detallado de su cuenta Google Analytics para entender cuáles son las secciones más potentes de la web, las que están captando más o menos tráfico que el año anterior.

En función del tamaño de la página web de nuestro cliente, esta tarea puede hacerse en menos de una hora o llevar días. 

En este post, os quiero compartir la técnica que usamos en SEOCOM para analizar el tráfico de los nuevos clientes para poder entender mejor su proyecto a nivel de captación y saber cuál será el foco principal en el desarrollo de la auditoría. 

Usaré varios ejemplos cuyos datos se han tenido que esconder por motivos obvios, sin embargo no descarto en un próximo artículo hacerlo con un proyecto sin ningún vínculo con SEOCOM, así podré enseñar los datos sin problema.

¿Por qué es importante analizar el tráfico en detalle?

Aunque el primer análisis pueda ser complejo y requiere bastante tiempo, es una de las tareas más importantes de una auditoría web. En efecto, por muy potentes que sean, las herramientas de seguimiento que usamos nunca dan resultados 100% fiables y tenemos que ser capaces de detectar problemas de captación con los datos de Google Analytics, ya que son los únicos datos reales que tenemos. 

Cada vez que se detecte una bajada fuerte en Sistrix, se suele comprobar si ha tenido impacto sobre el tráfico real. En efecto, los motivos pueden ser varios: 

1. Cambio de la manera de contabilizar las posiciones en la SERP (ver este hilo de Twitter) 
2. Actualización de la base de datos de keywords (ver esta noticia de Sistrix aunque en este caso no haya tenido impacto sobre el índice como lo explican)  
3. Bajadas en palabras clave fuera del Top 10 

Y más de una vez hemos visto índices de visibilidad subir o bajar sin impacto sobre el tráfico real de un cliente.

Análisis de tráfico con Google Sheets

Google Sheets es una de las herramientas más potentes de la suite de Google. Existen varias fórmulas que veremos a lo largo de este post y que no existen en Excel y que nos permiten ahorrar bastante tiempo y trabajar de manera más eficiente. También, algunas extensiones como la de Google Analytics  o de Search Console que permitirán recuperar los datos que os interesan sin salir de vuestra hoja de cálculo. 

Recuperación de los datos con la extensión de Analytics

La primera etapa para nuestro análisis es obtener los datos de tráfico. En nuestro primer ejemplo, se analizarán los datos desde el 1ro de Enero del 2018. 

Los pasos son los siguientes: 

• Instalar la extensión de Google Analytics yendo a Complementos > Descargar complementos

• Una vez instalado, se puede mostrar la barra lateral de configuración de los informes yendo a Complementos > Google Analytics > Create New Report

• En la barra lateral, tenéis que configurar: 
  • El nombre del reporte que será el nombre de la hoja de cálculo que creará la extensión
  • La vista que queréis usar para extraer los datos 
  • Las métricas / dimensiones que queréis extraer. Tened en cuenta que algunas dimensiones no se pueden combinar pero si esto es el caso , la extensión os avisará. 

En nuestro ejemplo, queremos obtener las sesiones orgánicas por página de destino y por mes. La configuración sería la siguiente:

Cuando le damos al botón “Create Report”, una nueva pestaña se crea como el ejemplo que mostramos a continuación:

Varios comentarios: 

1. Cada columna a partir de la columna B corresponde a un informe diferente. En el caso que haga falta, podéis crear más de un informe copiando y pegando la información creada de manera automática por la extensión sin tener que volver a realizar el paso a paso que acabamos de detallar. 
2. Por defecto, la extensión genera un informe usando los 30 últimos días como periodo, pero obviamente lo podéis cambiar como lo hemos hecho. Acordaos de usar el formato americano para las fechas (AAAA-MM-DD). 
3. Si queréis usar más de una dimensión o métrica, las tenéis que separar por una coma(,). Podéis encontrar los nombres usando la herramienta de exploración de dimensiones y métricas de Google. 
4. Para los filtros, mirad la sección de filtros de esta página 
5. Como lo podéis ver, el formato de los segmentos es un poco extraño. Para conocer el ID de un segmento, usad el Query Explorer.

Finalmente, podéis cambiar el límite en función del número de URLs que pensáis tener. Podéis dejar el número por defecto para la primera ejecución ya que cada pestaña incluirá el número de líneas que puede devolver la API. Si veis que hay una diferencia grande entre el límite y este número, se puede aumentar sin problema.

• Una vez se tengan  el o los informes configurados en la pestaña de “Report Configuration”, podéis lanzar la ejecución:

La ejecución puede tardar un poco en función del número de líneas por informe y del número de informes que habéis configurado, pero en general el proceso dura menos de un minuto.

Manipulación de los datos 

Si estáis analizando el tráfico de una web pequeña, podéis crear un solo informe con las dimensiones y métricas que mencionaba al inicio (página de destino, mes y año y sesiones), pero en la gran mayoría de los casos si se  analizará el tráfico de más de un año, tendréis un problema de muestreo que os indicará la extensión en la parte superior del informe. 

Este muestreo es un problema, ya que Analytics estima cuántas sesiones generan nuestras páginas en base a 43% del tráfico únicamente. Más bajo es el porcentaje  de muestreo, más probabilidad tenemos de tener datos incoherentes. 

En este caso, tenemos que dividir nuestros informes por mes (o más/menos en función de vuestro muestreo) como lo mostramos a continuación:

El problema principal con esta configuración, es que acabamos generando varias pestañas cuando queremos obtener una sola pestaña con todos los datos. Sin embargo, existe una fórmula potente que podemos usar para solventar este problema: QUERY. 

A continuación, os explico el paso a paso: 

• Para simplificar el uso de esta fórmula, aconsejamos usar intervalos con nombres. Lo que tenéis que hacer es seleccionar el rango con datos (a partir de la línea 15): 

• Y después, yendo a Datos > Intervalos con nombre, podréis asignar un nombre a este intervalo.

• Una vez hecho para cada uno de las pestañas que tenéis, cread una nueva pestaña y poner la siguiente fórmula en la celda A1. 

=QUERY({intervalo_1;intervalo2;…};”select *”;-1)

En detalle: 

• Query: es una de las fórmulas más potentes de Sheets y que usa una versión simplificado de SQL para manipular datos. No entraré en detalle sobre sus usos ya que de esto no se trata este post, pero os aconsejo que os la miréis porque os ahorrará mucho tiempo 🙂 
• {intervalo_1;intervalo_2;…): es la lista de intervalos. Por eso os aconsejo de asignarles un nombre, es más simple crear la fórmula si tenéis muchas pestañas por fusionar. 
• “select *”: código SQL que os permite seleccionar todos los datos de vuestros intervalos 
• -1: indica que la primera línea de nuestros intervalos es la cabecera 

Una vez aplicada la fórmula, deberéis acabar con todos los datos. Si el número de líneas disponibles en la pestaña no es suficiente, os saltará un error y Sheets os indicará cuantas líneas tenéis que agregar. Yendo abajo, lo podréis hacer sin problema para que la fórmula se ejecute y que ya no salte el error.

Una vez hecho esto, casi hemos  terminado la parte de manipulación de datos y podremos empezar el análisis. Si os fijáis, tenéis varias dimensiones de fechas disponibles en la API de Analytics: 

• ga:Month (10 por ejemplo)
• ga:Year (2019 por ejemplo) 
• ga:yearMonth (201910) por ejemplo

Para que Sheets detecte que los valores de esta columna son fechas, aconsejamos manipular un poco las columnas que habéis decidido incluir en vuestros informes para acabar con el formato “2019-10” (AAAA-MM). 

Lo podéis hacer con una nueva columna en la última pestaña que habéis creado o haciendo un buscar y reemplazar en las diferentes pestañas generadas por la extensión.

Hecho esto, tenéis que crear una tabla dinámica, para finalmente poder analizar los datos, con los siguientes pasos: 

• Crear una tabla dinámica seleccionando todos los datos de la última pestaña:

• Configurar la tabla: 
  • Filas: columna de URLs  
  • Columnas: columna de fechas 
  • Valores: Sesiones

Una vez generada la tabla, ya podréis finalmente empezar con la parte divertida: el análisis de los datos.

Análisis del tráfico 

Aunque el análisis del tráfico URL por URL pueda ser interesante, no es viable ya que son bastantes y tenemos que agruparlas. Una manera de hacerlo es por carpeta, usando las URLs. Existe una manera muy rápida de hacerlo: 

1. En vuestra pestaña que se usa de fuente para la tabla dinámica que hicimos al final, crear una nueva columna 
2. Agregar la siguiente fórmula: 

=SPLIT(URL;”/”)

Lo que os permitirá extraer las carpetas de vuestras URLs. Un ejemplo para que se entienda mejor: 

Con esta información adicional, podréis hacer un análisis por carpeta e ir un poco más allá del análisis global. En efecto, para el proyecto que uso de ejemplo en esta sección del post, si mirais los datos globales del canal orgánico, podríamos tener una lectura positiva:

Sin embargo si hago el mismo análisis por carpeta, veo que la situación es muy diferente, con las páginas de producto tirando los resultados hacia arriba cuando las categorías captan más o menos lo mismo desde un año.

En el caso que no se pueda extraer esta información de la URL (algunos dominios usan una estructura a raíz, es decir que ninguno de los contenidos incluye la carpeta en la URL), podemos extraer la información de los breadcrumbs, un elemento que casi siempre encontramos ahora. 

Usando la página http://www.leroymerlin.es/fp/82237934/cajonera-retro-2-cajones como ejemplo, vamos a ver cómo extraer esta información. Como lo podéis ver, la información sobre la categorización se encuentra en los Breadcrumbs:

Podemos extraer esta información de dos maneras diferentes: 

Con Google Sheets (en el caso que tengamos pocas URLs por clasificar): con la fórmula IMPORTXML. 

• Hacer clic derecho en el elemento que queremos recuperar y darle a “Inspeccionar”
• Hacer clic derecho en la sección del código fuente que se ha resaltado y seleccionar Copy > Copy Xpath

Sin embargo no siempre os servirá y a veces tendréis que modificar un poco el Xpath para que sea más genérico. En nuestro caso, usaremos el siguiente: 

//ol/*[@class=’home’]/following-sibling::li[2]

• Usar la siguiente fórmula:

=IMPORTXML(A1;»//ol/*[@class=’home’]/following-sibling::li[2]»)

Como lo comentaba anteriormente esta técnica se adapta si tenéis pocas URLs, ya que  aplicarla a miles de líneas podría tardar horas. En el caso que tengáis más, mejor usar la herramienta favorita de los SEOs: Screaming Frog.

Con Screaming Frog 

• Copiar el listado de URLs que queréis rastrear 
• Abrir Screaming Frog y activar el modo lista

• Configurar la Custom Extraction con el mismo Xpath visto en la explicación del IMPORTXML

• Lanzar el crawl y esperar que termine
• Incluir los datos en Sheets con un BuscarV

Una vez incluida esta información en vuestra tabla, podréis modificar vuestra tabla dinámica para que use este valor en las filas en vez de las URLs. 

Podréis acabar con una gráfica como la siguiente, segundo ejemplo que usaremos para esta sección del post: 

Sin tener el sector del cliente ni el detalle sobre las tipologías de contenido que produce, un análisis rápido parece demostrar que las 3 carpetas que más tráfico generaban han sufrido una caída muy fuerte a lo largo del año 2019.

Ahora, al momento de analizar los datos de este cliente, tenemos que tener en cuenta que una bajada del tráfico  casi siempre se debe a dos motivos principales: 

1. Bajada del posicionamiento 
2. Cambios en las SERPs 

Para ver cuál es el problema, vamos a mirar los datos Search Console, que nos permite tener los datos tanto de impresiones como de clicks. Si nos enfocamos por ejemplo a la carpeta azul:

Vemos que a partir de Marzo del 2019, las impresiones de esta carpeta se han más o menos mantenido pero los clicks se han ido para el suelo. Para entender mejor lo que ha podido pasar, podemos usar SEMRUSH y sus datos históricos para algunas palabras clave de esta carpeta. Si miramos los datos de Febrero 2019 usando un ejemplo de palabra clave, vemos que el portal se posicionaba en el primer lugar. 

Para la misma palabra clave unos meses después, vemos que la bajada no ha sido tan brutal (-2) pero Google ha decidido mostrar un Featured Snippet en el cual no aparecemos. Estos bloques de resultados enriquecidos captan una parte importante del tráfico o hasta pueden dar la respuesta directa al usuario sin que salga de Google, aunque en el caso de esta búsqueda no es lo que sucede ya que se trata de un tema complejo. 

Este ejemplo, extendido a todas las palabras clave de esta carpeta, podría explicar la bajada fuerte del tráfico. Sin embargo, tenemos que comprobar que este se aplique a todas las palabras clave de la carpeta (o por lo menos una parte importante). 

Esta conclusión se puede obtener usando la herramienta Ahrefs y su funcionalidad de búsqueda orgánica. 

• Entrar en Ahrefs y introducir el nombre de la carpeta 
• Entrar en la sección de “Palabras clave orgánicas” 

• Activar el filtro de “Featured Snippet”, filtrar por las posiciones entre 0 y 10 y poner el número de palabras clave que indica la herramienta

• Activar el filtro de “Link to target” para el mismo informe y  poner el número de palabras clave que indica la herramienta

En nuestro caso, de más de 1.700 palabras clave con Featured Snippet para los cuáles el proyecto está en el Top 10, únicamente en 25 casos aparece en el bloque de Featured Snippet. Parece que hemos encontrado cuál es el problema principal y lo que podría explicar una bajada fuerte del tráfico sin bajada de las impresiones. Cuando llegáis a este punto, es importante chocar cinco con sí mismo.

Sin embargo, tenemos que validar esta teoría con todas las carpetas y los contenidos principales que han bajado antes de enviar nuestras conclusiones. Si revisamos nuevamente la gráfica inicial, nos damos cuenta que una carpeta pierde tráfico más rápido que las demás: la verde. 

De hecho, los datos en Search Console no se parecen en nada a lo que hemos indicado para la otra carpeta: tanto las impresiones como los clicks se desploman. En este caso, es probable que no sea la aparición de un bloque de Featured Snippet que haya provocado esta situación, sino otro problema que tenemos que identificar.

Entrando más en detalle y dado que se trata de una carpeta incluyendo  contenidos generados por los usuarios (UGC), no es extraño que Google haya decidido potenciar otros contenidos. Google simplemente ha decidido valorar otros contenidos, más completos y con mayor valor agregado para los usuarios. 

Para el cliente, la recomendación sería de identificar los contenidos UGC que generaban más tráfico antes de la bajada y crear contenidos de mejor calidad para las búsquedas asociadas.  

Yendo un poco más allá 

La metodología que explicamos en este post es escalable para portales de tamaño pequeño o medio, sin embargo para portales muy grandes no sirve. Igual que para Excel, Google Sheets tiene sus límites en cuanto a número de filas. Para estos casos, aconsejamos usar una mezcla de Big Query y Python para extraer y analizar los datos de manera más rápida y eficiente. 

Pero se trata de otro post, que espero poder compartir con vosotros en otra ocasión. Pero mientras, no dudéis en poneros en contacto conmigo en los comentarios, para darme vuestra opinión o sugerencias sobre esta metodología.