Trucos SEO con Spreadsheet y Excel

6 noviembre, 2015

Extraer la URL del primer resultado de Google

En mi trayectoria profesional me he encontrado más de una vez con migraciones web con cambios de plataformas o arquitecturas que implican cambio de URLs y donde no se han redirigido las URLs antiguas a las nuevas. Muchas veces no se hace por desconocimiento del impacto SEO que esto tiene y no contar desde el inicio con un profesional SEO que te acompañe desde las fases iniciales del proyecto, en este caso la migración.

Casi seguro que no te libres de tener que hacer la relación de algunas portadas, portadillas o URLs de secciones principales totalmente a mano, correspondencia A > B para pintar los redirects 301. Sin embargo, cuando estamos hablando de webs relativamente grandes, resulta humanamente imposible pintar la correspondencia A > B de todos los detalles como noticias, fichas, fotos, etc de forma manual. En estos casos lo conveniente es que durante el proceso de exportación / importación, se genere y se guarde también la información de la correspondencia URL Antigua > URL Nueva.

Sin embargo, como hemos comentado anteriormente, no siempre esto se hace. ¿Qué hacer en estos casos? ¿Qué hacer si la web ya se ha lanzado, nos hemos dado cuenta de la caída SEO debido a la ausencia de redirecciones y ahora IT dice que es muy complicado generar esa tabla de correspondencias o redirecciones A > B?

El truco que voy a explicar a continuación, sirve para arquitecturas de URLs “viejas” que tuviesen las keywords en la URL. En caso contrario tendríamos que extraer títulos o contenidos de la cache de Google o de los sistemas de analítica web y la cosa se complica un poco más, quizá para otro post.

Lo que vamos a ver tiene como objetivo emparejar el listado de “TOP URLS DETALLE” que podemos descargar de nuestra herramienta de analítica web, de Google Search Console o de cualquier otra fuente, con la Nueva URL, siempre y cuando Google ya haya rastreado el nuevo sitio por completo (o en su mayoría) siempre podremos hacer búsquedas a mano o en el propio buscador interno de la nueva plataforma para completar allá donde Google no haya llegado todavía.

Mi buen amigo Antonio Parra ha preparado este Google Spreadsheet, donde mediante XPATH e IMPORTXML, extramos la primera URL que Google devuelve para una determinada Query, en nuestro caso la query sería la keywords de todas las URLs viejas que queremos redireccionar, en mi caso haría un MIX entre TOP URLs posicionadas y TOP URLs que están dando un error tras la migración.

Si a la query le agregamos “site:nuestrodominio.com” incluso “inurl:algo_que_diferencie_urls_viejas_nuevas” obtendremos (si ya ha indexado todo el nuevo sitio) las URLs a las que tendremos que redirigir las del origen.

Las 2 funciones de la hoja de cálculo son:

  • =CONCATENATE(“http://www.google.es/search?hl=es&source=hp&biw=&bih=&q=”;A2)
  • =REGEXEXTRACT(IMPORTXML(B2;”(//*[@class=’r’]/a[1]/@href)[1]”);”q=(.*)&sa”)

Para buscar en nuestro sitio y con algún parámetro en la URL la query del CONCATENATE debería ser:

  • =CONCATENATE(“http://www.google.es/search?hl=es&source=hp&biw=&bih=&q=site%3Adominio.tld%20inurl%3Ahtml5”;A2)

Actgualización, con Excel SEO Tools. Gracias a Estela Franco!

=XPathOnUrl(“https://www.google.es/search?q=wikipedia”;”//h3[@class=’r’]/a”;”href”)

Y si necesitas sacar el top 10, por ejemplo, haces matriz seleccionando rango, Ctrl+Shift+Intro y apañado.

Tarda, pero lo saca. 🙂

Número de resultados en Google en Bulk

Si alguna vez te has visto en la tesitura de comprobar el número de resultados de Google de unas determinadas querys de forma automatizada, ¡estás de enhorabuena!. Es relativamente fácil hacerlo con el Scraper Google.Results de Excel SEO Tools, un plugin para Excel. Escribo este post porque verdaderamente está muy poco explicado toda la potencia de esta función y hasta ahora no he encontrado la fórmula para referenciar a URLs o keywords de forma relativa a una celda con el connector “GoogleResults” o “GoogleIndex“. Sin embargo, pero mediante reemplazos y concatenar, podemos extraer la fórmula que debemos poner en el Excel para que nos devuelva los resultados esperados.

Para ver el número de resultados en Google para una determinada keyword, simplemente ponemos:

=Connector(“Offpage.GoogleResults”;”adolfo suárez”;”google.com”)

Si lo que queremos es saber el número de URLs indexadas en un determinado dominio o directorio (tal y como si hiciésemos un site:domain.tld), ponemos:

=Connector(“Offpage.GoogleResults”;”adolfo suárez site:elpais.com”;”google.com”)

E igual de fácil es el número de resultados indexados de una determinada URL, lo podemos hacer con la query anterior:

=Connector(“Offpage.GoogleResults”;”site:elpais.com”;”google.com”)
=Connector(“Offpage.GoogleResults”;”site:elpais.com/tag/”;”google.com”)

O con GoogleIndex, quedando:

=Connector(“Offpage.GoogleIndex”;”elpais.com”;”google.com”)
=Connector(“Offpage.GoogleIndex”;”elpais.com/tag/”;”google.com”)

Cambiando obviamente las keywords o URLs y el el ccTLD del Google local donde queramos buscar.

Lo que no he conseguido que vaya fino son las referencias relativas a celdas en el campo “keywords” o “URLs”, es decir, teniendo en A las querys:

=Connector(“Offpage.GoogleResults”;A1;”google.com”)
=Connector(“Offpage.GoogleIndex”;A1;”google.com”)

En las últimas versiones del plugin parece que ya funciona, en caso contrario, tendremos que constuir las fórmulas que finalmente le enviaremos a los connectores mediante pasos intermedios, concatenando.

  1. Supongamos que tenemos en A los dominios donde buscar.
  2. En B las keywords (si queremos buscar dentro de un dominio, agregaremos el site:domain.tld quedando: =CONCATENAR(“keyword que queramos site:”;A3)
  3. En C tenemos que poner ‘=Connector(“Offpage.GoogleResults”; (importante el apóstrofe delante) para que se muestre igual. En D incluimos “;”google.com”) o el ccTLD que queramos.
  4. En E pondremos la fórmula para concatenar todo: =CONCATENAR(C3;B3;D3).

En una sesión de excel sin el plugin SEO Tools activo no pasará nada, pero con el plugin, deberían comenzar a mostrarse el número de resultados en D.

 

Nº de URLs Indexadas en un dominio directorio mediante Google Spreadsheet

También se puede hacer con las hojas de cálculo de Google y por lo tanto gratis. El código sería:

=importXml("https://www.google.com/search?q=site:www.yourdomainnamehere.com&num=100&start=1"; "//cite")

 

=importXml("https://www.google.com/search?q=site:www.yourdomainnamehere.com&num=100&start=100"; "//cite")

 

Así hasta 1000

 

Bonus track: Antonio Parra está preparando el script para obtener el número de resultados de una query o URL sin necesidad de andar mirando el total. 🙂

 

Monitorizar posiciones en Google con Excel

Pese a no ser nada amigo de monitorizar keywords, hay veces que por diversas razones no te queda más remedio. Existe una infinidad de fórmulas en el mercado, herramientas en la nube, con proxis con IPs de todo el mundo, histórico, rankings diarios, etc. Sin embargo, lo cierto es que hay poca documentación para tratar de hacerlo de forma simple y gratis, pero algo más automatizado que ir mirando con crtl + mayusc + n y buscar a pelo 😉

Una opción puede ser Free Monitor for Google de Clever Stats, que sigue funcionando igual de bien que hace años para pocas querys. Sin embargo, quería algo más simple todavía, que tirase en Google Drive o mejor todavía con Excel.

Gracias de nuevo a Estela Franco que me ha ayudado a screapear con Excel SEO Tools las URLs de la serp en busca de un determinado dominio dada una keyword dada, para ello:

Concatenamos https://www.google.es/webhp?gws_rd=ssl + la Query que queremos.

Ahora la maravilla para Excel SEO Tools (no funciona en Drive): =XPathOnUrl(A2;”//h3[@class=’r’]/a”;”href”) (así hasta 10 veces en 10 filas, para sacar el TOP10)

La posición, la sacamos con: =COINCIDIR(“TRUE”;B3:B12;0)

Y la URL con =INDICE(A3:A12;D3)

Regex para SEO

Las Expresiones Regulares, comúnmente conocidas como “Regex” son al SEO como el Excel es a la Analítica Web.

Ejemplos Regex o Expresiones regulares

Ejemplos Regex o Expresiones regulares

Dominarlas te permitirá entre otras cosas pelearte sin miedo con .htaccess así como sacar el máximo partido a herramientas como DeepCrawl, Screaming SEO Frog o Botify, entre otras muchas. Podrás usarlas también en Google Spreadsheet y en ciertas funcionalidades de Excel como buscar y reemplazar. Respecto a esto último de Excel, tengo que comprobar si en Office 2016 puedes usarlas en los filtros, tal y como permiten Libre Office u Open Office. En definitiva, creo que conocer las expresiones regulares es algo importante para cualquier perfil SEO, ya sea técnico o analista. Para entender su funcionamiento no es necesario saber programar para controlarlas, ni mucho menos, pero si tener cierta base sobre como funcionan los algoritmos o conjuntos de instrucciones. Seguro que si te pones con ello no te costará mucho 🙂

Algunos enlaces interesantes que te ayudarán:

Manual de Regex en Español: http://www.desarrolloweb.com/
Generador de Expresiones Regulares: https://regex101.com/
Comprobador de expresiones Regulares: http://www.metriplica.com/

regex en el .htaccess

No debemos olvidar que cualquier sentencia en el .htaccess o httpd.conf afectará a nuestro WPO. Para ello tendremos que tratar de usar las expresiones lo más livianas posibles, por ejemplo se suele usar erróneamente (.*) cuando se podría hacer simplemente con ([a-zA-Z0-9_-]+). A menos comprobaciones, menos carga. Una tilde en una URL puede ser perfectamente [aáâàä].

Por otro lado, en el caso de webs extremadamente grandes, a veces puede resultar mejor cargar las redirecciones en ficheros php en lugar de directamente en el .htaccess. Esto hace que su ejecución sea más lenta, pero por contra la carga de las máquinas lo agradece. Pero eso ya si que es cuestión de los programadores 🙂

Expresiones regulares útiles:

Termina con, por ejemplo .html: ^/(.).html$

Expresiones regulares en los filtros de Spreadsheet | Excel | Open Office | Libre Office

Cuando estoy haciendo una auditoria me gusta saber cuántas URLs hay de cada tipología posicionadas, cuánto tráfico tiene cada una, etc. Para ir quitando “elementos” lo que hacia hasta ahora era filtrar en Excel las URLs posicionadas, que no contengan “noticias”, copiar & pegar, aplicar otro filtro, tampoco fotos, tampoco videos. Así hasta que más o menos tenía una idea de todas las tipologías de contenidos o temáticas (basado en URLs) que podía tener una web, un proceso bastante manual vamos.

Si quieres ir más deprisa, puedes hacerlo con expresiones regulares en Open Office, no he conseguido hacerlo en Excel ni Google Drive. En un filtro, podemos concatenar cuantas condiciones queramos (realmente no se si habrá límite, pero yo no he llegado tan lejos). Por ejemplo podemos darle a “Datos > Filtro > Estándar”, ¡recuerda activar “REGEX” en opciones avanzadas!

En nombre de campo tenemos que poner la columna que contiene las keywords que queremos filtrar y en condición y hay que poner “contiene” o “no contiene..” de luego para que no contenga ninguna keyword podemos poner en condición simplemente, sin las comillas “(keyword1)|(keyword2)|otra_key”, etc.. También podemos usar comodines, etc. Por ejemplo:

(noticias)|(news)|(nachrichten)|(nouvelles)|(notizie)|(photos)|(fotos)|(videos)|(video)

(noticias)|(news)|(nachrichten)|(nouvelles)|(notizie)|(photos)|(fotos)|(videos)|(video)

Para Google Analytics, también podemos incluir este segmento a la hora de agrupar URLs con regex

(^/$)|(^/es$)|(^/es/$)|(^/en$)|(^/en/$)|(^/it$)|(^it/$)|(^/fr$)|(^/fr/$)|(^/ja$)|(^/ja/$)|(^/pt$)|(^/pt/$)

 

Leave a comment: