Cómo cruzar los datos de Analytics, WMT, rankings, etc

Publicado el 26 de enero del 2012 by Errioxa

En el anterior post comenté cómo se podría sacar más provecho si cruzamos los datos que tenemos desde la distintas herramientas como Analytics, WMT, monotorización de posiciones.

Así obtendríamos un excel como este

excel datos cruzados


Os dije lo que hacía pero no el cómo, y lo prometido es deuda.


Pasos para cruzar los datos como yo lo hago
  1. Descargar y descompimir el script en phyton,

  2. Ejecutar el script con la siguiente orden, yo lo ejecuto en Ubuntu pero creo que con ws también se puede sin problema.
    python /ruta-del-script/posiRank.py /ruta/palabras-a-monotorizar.txt tudominio.com 2 > /ruta/resultados.txt

    Parámetros del comando

    • /ruta-del-script/posiRank.py es la ruta donde has descomprimido el script

    • /ruta/palabras-a-monotorizar.txt es un archivo txt donde tendremos las KW que queremos, una por cada línea

    • tudominio.com el dominio a monotorizar

    • 2 tiempo de espera entre consultas para que no te pille Google, a mi con 2 me va muy bien

    • /ruta/resultados.txt es donde guardaremos los resultados, no tiene por que existir.



  3. Vamos a Analytics, o lo hago con la versión nueva, Fuentes de tráfico -> Búsqueda -> Orgánico
    Y filtramos desde el select de abajo de la tabla, por 100 por ejemplo

    filtro analytics

    Y ahora vamos a la barra de direcciones y buscamos este parámetro en la UR, table.rowCount%3D100/ cambiamos el 100 por 50.000 por ejemplo.
    Esto nos mostrará las 50.000 KW que más tráfico te traen o si queires algunas en especial puedes aplicar cualquier filtro y luego hacer esto.

    Le damos a exportar como CVS para Excell y lo guardamos


  4. Vamos a Webmaster Tools a "Tu sitio en la web" -> "Consultas de búsqueda" y descargamos la tabla

  5. Vamos a la herramienta de palabras clave de AdWrods, y ahí vamos obteniendo datos de todas las palabras que queramos y las vamos añadiendo a un único excel. Esto es lo más laborioso ya que sólo te deja de 100 en 100 y si quisieras hacerlo para las 50.00 KW sacadas de analytis podría ser eterno... Así que las que queráis.


  6. Abrir el fichero de resultados (/ruta/resultados.txt) con Excel,Te quedarán 5 columnas

    •   Columna 1: posición en Google

    •   Columna 2: KW

    •   Columna 3: URL mostrada en las serps. Si no está ninguna de tu dominio se pondrá la primera en los resultados de Google

    •   Columna 4: Titulo que muestra Google en las serps de tu resultado

    •   Columna 5: Descripcin que muestra Google en la serp

  7. Abrimos un nuevo documento Excel
    Cogemos las dos primeras columnas del fichero de resultados (punto 6), posición en Google, KW  y lo pegamos en nuestra nueva hoja y vamos a llamar a la hoja "Datos" por ejemplo. Luego la columa 3 del punto 6 la copiamos, pero en la columa D de nuestro nuevo excel

  8. Creamos una hoja nueva y la renombramos con "Analytics". En ella pegamos los datos sacados del punto 3

  9. Creamos otra hoja nueva, la llamamos "WMT" y pegamos en ella los datos sacados del punto 4

  10. Creamos ota hoja y la llamamos "AdWords" donde pegamos los datos del punto 5

  11. Ahora en la columna C, fila2 (la uno son las cabeceras) ponemos esta fórmula
    =VLOOKUP(B2;$Analytics.$A$1:$C$50000;3;0)

    La aplicamos a toda la columna
    Esto lo que hace es buscar el valor de la casilla B2 en la hoja de Analytics desde la columa A fila 1 hasta la column C fila 50000 y obtiene los datos de esa hoja pero 3 columnas más allá

    Hacemos lo mismo en la columna E (Impresiones) pero esta vez la fórmula será
    =VLOOKUP(B2;$WMT.$A$1:$K$50000;2;0)

    Ya que ahora esos datos los cojemos de la hoja de WMT y no de Analytics

    Os pongo aquí las fórmulas para cada columna
    • Rank/19-12-11 - sin fórmula

    • Palabra - sin fórmula

    • Vistas al Mes
      VLOOKUP(B2;$Analytics.$A$1:$C$50000;3;0)

    • URLs en las serps - sin fórmula

    • Impresiones
      =VLOOKUP(B2;$WMT.$A$1:$K$50000;2;0)

    • Clicks
      =VLOOKUP(B2;$WMT.$A$1:$K$50000;4;0)

    • %CTR
      =CONCATENATE(100*(VLOOKUP(B2;$WMT.$A$1:$K$50000;6;0));"%")

    • Posición media
      =VLOOKUP(B2;$WMT.$A$1:$K$957;8;0)

    • Páginas vista
      =VLOOKUP(B2;$Analytics.$A$1:$H$957;4;0)

    • Promedio de tiempo en el sitio
      =VLOOKUP(B2;$Analytics.$A$1:$H$957;5;0)

    • Rebote
      =VLOOKUP(B2;$Analytics.$A$1:$H$957;7;0)

    • tiempo
      =VLOOKUP(B2;$Analytics.$A$1:$H$957;6;0)

    • Competencia
      =VLOOKUP(B2;$AdWords.$A$1:$H$957;2;0)

    • Búsq Globales
      =VLOOKUP(B2;$AdWords.$A$1:$H$957;3;0)

    • Búsq Locales
      =VLOOKUP(B2;$AdWords.$A$1:$H$957;4;0)

    • Title en las serps - las pegamos si queremos desde el punto 7

    • Descripción en las serps - las pegamos si queremos desde el punto 7

     

  12. Aplicamos formato condicional a las celdas si queremos, yo tengo puesto que si el valor de A2 < 6 lo pinto verde, si A2 > 5 lo pinto rojo y si es 0 lo pinto azul. Eso ya cada uno como quiera.

Os dejo aquí un ejemplo del excel, sin datos por supuesto, pero para que veáis más claro cómo hacelo. Ojo, en este en concreto creo que tengo en alguna fórmula que sólo busque unas 900 ya que no me hacía falta más, lo suyo es poner un número como 50.000 para asegurarte que busca en todas las filas y así lo puedes usar como plantilla para los siguientes que hagas.


Sacar el primer excel os parecerá algo tedioso, pero en cuanto hagas 3 verás como no se tarda más de 15 mintos, por supuesto sin tener en cuenta el tiempo que tardes en monotorizar las KW con el script, yo hago 50.000 en unas 10 horas.


El próximo post intentaré que sea de cómo sacar provecho de esto

 



 


Dani Pinilloshace Hace más de 7 años y 90 días

Pero que grande eres ;-)
De todas formas aunque no me lo expliques en directo no te libras de mi para hechar unas birras.
Gracias por compartirlo.
Con conversiones en la pestaña de GA ya sería la reostia.
Un abrazo!

Errioxahace Hace más de 7 años y 88 días

Aupa Dani!

Le puedes meter cualquier columna que quieras de analytics, prueba está tirado :)

Errioxahace Hace más de 7 años y 82 días

Lo hago sin proxies ni VPN, conexión directa.



Lea otros artículos de Google Analytics

Últimos posts

Últimos comentarios


Lino Urnuela

@Emirodgar gracias! Pero parece que en tema de imágenes las pilla lo hagas cómo lo hagas parece, eso sí, siempre que no tengas un fall
Post: Indexar imágenes en Google usando Lazy Load

Emirodgar

Muy interesante el experimento. Yo estaba probando con los nuevos formatos webp y pero al final, como eran pocas imágenes y usaba Masonry,
Post: Indexar imágenes en Google usando Lazy Load

Lino Uruñuela

Completamente de acuerdo :) Pero en este experimento solo quería comprobar el método usado para hacer lazy load, en este caso con xmlht
Post: Indexar imágenes en Google usando Lazy Load

Francisco Morales

Lino muy interesante las distintas formas de cargar la imagen. Pero no crees que lo realmente interesante de aplicar Lazy Loading es cargar
Post: Indexar imágenes en Google usando Lazy Load

javier

Buenas , esto del onclik ha cambiado actuamente en algunas web que tengo las lee y sigue enlaces
Post: ¿Cómo ejecuta, interpreta e indexa Google el contenido cargado mediante javascript?

David Girona

Antes de Nada muchas gracias por la aportación. Estoy probando de poner en marcha este procedimiento y me surgen un par de dudas. En
Post: Cómo añadir el valor del meta Robots a Google Analytics via Google Tag Manager

Javier Espinoza

Gracias por la informacion!! Este tipo de blogs me parecen muy importantes, esto lo estudio en la universidad. gracias por la informacion. h
Post: Atacados por los .cn .cz .pl

juan

Hola Lino Uruñuela, una duda ¿aun funciona? porque no lo logro. Mira, en un index.php tengo este codigo: Camuflados
Post: Ofuscando enlaces para mejorar Link Juice

DUQUEredes

Google pasa del canonical bastante :-(
Post: Comprobando comportamiento de Google con meta canonical

Marinette

Gracias por la información!
Post: Nuevo Google Search Console ¿qué información nos ofrecerá?

Contacta

Lánzate y pregunta!


He leído y acepto la política de privacidad

Mecagoenlos.com te informa que los datos de carácter personal que nos proporciones rellenando el presente formulario serán tratados por Lino Uruñuela. como responsable de esta web.

La finalidad de la recogida y tratamiento de los datos personales que te solicitamos es para enviar un correo con los datos que introduzcas, sin guardarse en ninguna base de datos.

Legitimación: Al marcar la casilla de aceptación, estás dando tu legítimo consentimiento para que tus datos sean tratados conforme a las finalidades de este formulario descritas en la política de privacidad.

Como usuario e interesado te informamos que los datos que nos facilitas estarán ubicados en los servidores de Linode.com (proveedor de hosting de Mecagoenlos.com) cumpliendo la ley de protección de datos. Ver política de privacidad de Linode.com.

Podrás ejercer tus derechos de acceso, rectificación, limitación y suprimir los datos en info@mecagoenlos.com, así como el derecho a presentar una reclamación ante una autoridad de control. Más información aquí.