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

 



 


Comentarios

Dani Pinilloshace Hace más de 5 años y 297 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 5 años y 295 días

Aupa Dani!

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

Errioxahace Hace más de 5 años y 289 días

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

Deja un comentario


Posts anteriores en Google Analytics


Space Needle
Publicado el 24 de mayo del 2017, by Lino Uruñuela Muchas veces los SEOs usamos determinados protocolos y/o directivas para dar determinadas señales a los Bots de los buscadores y otros servicios. Así por ejemplo, podemos usar un meta para indicarles en qué fecha

Seguir leyendo
Space Needle
Publicado el 13 de abril del 2015 por Lino Uruñuela Hace tiempo ya comentamos que podemos saber cómo y cuándo acceden los robots de los buscadores a cada url de nuestro site por medio de los logs. Es muy importante tener visibilidad sobre qué y cómo crawlean

Seguir leyendo
Space Needle
Publicado el jueves 11 de septiembre por Lino Uruñuela Hoy voy a cambiar un poco de tema para variar, pero no me desviaré mucho. Si hay algo que me ha quedado claro en más de 10 años que llevo trabajando entorno a los negocios online , y que se viene cumpliendo co

Seguir leyendo
Space Needle
Publicado el 19 de agosto del, 2014 Hace ya "bastante" que salió a la luz Universal Analytics y ya nos ha dado tiempo a descubrir sus pros y sus contras con respecto a la anterior versión de Google Analytics. Y sí, también tiene contras, bastantes ademá

Seguir leyendo
Space Needle
Publicado por Lino Uruñuela (Errioxa) el 30 de junio del 2014 Hoy me he llevado una sorpresa cuando me he puesto a analizar el tráfico directo a raíz de que Rodrigo Hernandez me comentase su preocupación con el tráfico de un site , y es que normalmente suelo t

Seguir leyendo
Space Needle
Publicado por Lino Uruñuela (Errioxa) el 23 de abril del 2014 Como dijimos el otro día, vamos a crear una serie de post sobre cómo utilizar determinadas APIs que Google nos ofrece, ya que en ocasi

Seguir leyendo
  • 0000-00-00 00:00:00

Publicado el lunes  7 de octubre del 2013, by Lino Uruñuela Hoy me siento optimista, no sé si será porque ya estoy casi recuperado de la operación de rodilla a la que me sometí hace un mes, pero estoy optimista. Hace poco Google anunci&oacu

Seguir leyendo
Space Needle
Publicado el 4 de abril del 2013 by Lino Uruñuela Como todos sabemos desde hace más de un año el Not Provided cada vez aglutina más keywords en nuestras estadísticas y aunque aun podemos trabajar casi igual que hace dos años, no creo que podamos hacerlo d

Seguir leyendo
Space Needle
Publicado el 27 de diciembre del 2011 El otro día desde el blog para webmasters de Google nos anunciaban que habían creado un script para poder descargar los datos de impresiones, clicks, CTR, etc desde WMT. La verdad lo acogí con gran ilusión pero de momento no le

Seguir leyendo
Space Needle
Publicado el miércoles 17 de agosto del 2011 , by Lino Uruñuela, Recordemos que con el nuevo método de Google Analytics de medir las visitas ahora para ver que impacto ha tenido la actualización de Google Panda, no puedes comparar visitas post-Panda con visitas pr

Seguir leyendo
Space Needle
Publicado el 8 ed agosto del 2001 El otro día ya comentamos que Google Analytics había cambiado la forma en que trataba el tráfico que llegaba desde Google Images y ya no lo tomaba como referencia sino como tráfico orgánico. Para mi es importante saber el

Seguir leyendo
Space Needle
Publicado el 27 de julio del 2011 Cualquier día la OMS va a declarar Google como posible factor de riesgo de infarto entre las personas que viven de internet. Y es que ahora desde el sábado Google no muestra las búsquedas desde Google Images como referencia y parece que lo ha

Seguir leyendo

Datos de contacto
  • 637167218
  • wachynaky@gmail.com

Dónde encontrarme

FunnelPunk.com


Blog Seo de Lino Uruñeula
Co-Fundador



Lánzate y pregunta!

Nos gustan los robots, pero no tanto
7 + 3

Últimos posts

Últimos comentarios


Mateo Bohorquez

Hola hermano, lo he intentado y no me funciona. para acotar problemas lo que hize fue usar ejemplo que nos redirige a otra pagina. este:
Post: Ofuscando enlaces para mejorar Link Juice

Juan M.

¿Esto lo has probado en Safari o Firefox? No pilla bien el preload para css
Post: Cómo cargar css y js y no bloquear la carga de contenido

Luis Salazar Jurado

Buenas Lino He hecho un test en mi site: www.seotecnico.com y tras hacer test en Iphone, Chrome y Firefow parece que funciona Sería a
Post: Cómo cargar css y js y no bloquear la carga de contenido

Luis Salazar Jurado

Gracias Lino por avisar. Solo he aplicado las mejoras de CSS y va como un tiro. Debido a que la web en la que lo he implementado cargo
Post: Cómo cargar css y js y no bloquear la carga de contenido

Tomás

...la línea ---?php wp_head()--- Gracias.
Post: Cómo cargar css y js y no bloquear la carga de contenido

Tomás

Hola Lino. En WordPress, ¿de qué manera se podría editar una línea de código CSS y JS para añadir las modificaciones en verde que menc
Post: Cómo cargar css y js y no bloquear la carga de contenido

Juan M.

Para mí, esa forma de cargar los estilos donde todo se ve sin estilo y los carga a los segundos me da una muy mala sensación del site. Tan
Post: Cómo cargar css y js y no bloquear la carga de contenido

Errioxa

@Luis actualizado! Si te da algún problema avisa!
Post: Cómo cargar css y js y no bloquear la carga de contenido

Luis Salazar Jurado

Lo he implementado y va como la seda Espero la actualización que has comentado en Twitter para ver el Javascript, pero solo con el CSS y
Post: Cómo cargar css y js y no bloquear la carga de contenido

Errioxa

@Antonio solo Google sabe si se pasa o no lj con nofollow, en principio la url de destino no se vería beneficiada porque no lo contaría, p
Post: ¿Cómo ejecuta, interpreta e indexa Google el contenido cargado mediante javascript?

Categorias