Post on 03-Aug-2015
SPREADSHEET
avanzadoPara análisis y reporting
SEO
Año 1991-1996 1997-2002 2003-2008 2008-2009
Empresa DANONE, ROYNE…
GENERAL DE FILTROS
AURUM CREATIVOS COBRISUR
Sector Varios Suministros industriales
Publicidad y MKT online
Suministros industriales
Rol Técnico Copropietario Responsable de MKT online
Experiencia
Desarrollo de aplicaciones de
escritorio (VB/VBA), web
(ASP) y SysAdmin
Desarrollo web (ASP), SysAdmin y
SEO-SEM
Desarrollo web (ASP),
SysAdmin y SEO-SEM
Acerca de José B. Moreno Suárez
INDICE 1. LA herramienta2. “Vitaminando”
SPREADSHEET3. Ejemplos de datos que
‘cobran vida’ en GOOGLE SPREADSHEET
4. Análisis más allá de la hoja de cálculo
5. Ayuda, ideas y recursos útiles
•Herramientas preferidas, BOTIFY (patrocinador)…y las hojas de cálculo•Lenguaje de consultas de datos, Xpath, Expresiones regulares (Regex), JAVASCRIPT•¿Qué tráfico, evolución y tendencias?, ¿Cómo evolucionamos con respecto al mercado?, ¿Quiénes son los competidores?, ¿en qué es mejor la competencia?, Ideas de palabras clave, ¿por qué palabras clave?, ¿Qué KW sonNon Brand y qué patrón siguen las Brand terms?, ¿Cómo evolucionan las posiciones de las palabras clave agrupadas?•SQL+SPREADSHEET
15/04/2023SPREADSHEET avanzado para análisis y reporting SEO 3
LA HERRAMIENTA
“El hombre es un animal que hace herramientas.”
Benjamin Franklin
Herramientas preferidas
¿Para qué? Auditoría ‘on page’ Keyword Research Visibilidad Velocidad de carga Analítica Web Backlinks…
¿Cuáles? SCREAMING FROG, WEBCEO ,
GOOGLE SEARCH CONSOLE, XENU’S LINK SLEUTH…
Planificador de palabras clave de GOOGLE, SEMRUSH, SISTRIX, ÜBBERSUGGEST…
ADVANCED WEB RANKING, RANK TRACKER, SEMRUSH, SISTRIX, WEBCEO…
PINGDOM, GOOGLE PAGESPEED… GOOGLE ANALYTICS, YANDEX
METRICA, HOTJAR, INSPECTLET… MAJESTIC SEO, AHREFS, OPEN SITE
EXPLORER (MOZ), SISTRIX……y…
SPREADSHEET avanzado para análisis y reporting SEO
BOTIFY (patrocinador)
Botify Analytics: servicio Web (SaaS) basado en información obtenidad con el crawling
Botify Log Analyzer: aplicación basada en datos obtenidos de logs de servidor
15/04/2023SPREADSHEET avanzado para análisis y reporting SEO 7
…y las hojas de cálculo
Recolectar datos
Procesarlos
AnalizarInstruir
Optimizar
15/04/2023SPREADSHEET avanzado para análisis y reporting SEO 8
Por qué SPREADSHEET
¿Cuales? CALC (OPENOFFICE) o
CALC (LIBREOFFICE) EXCEL (OFFICE) SPREADSHEET
(GOOGLE DOCS)
¿Por qué? Gratuita Muy potente (sobretodo por lo fácil
de conectar a BBDD externas, proceso y cantidad de datos y VBA)
Gratuita, muy potente y nativa para datos en la nube
15/04/2023SPREADSHEET avanzado para análisis y reporting SEO 9
“VITAMINANDO” SPREADSHEET
“Sabemos muy poco, y sin embargo es sorprendente que sepamos tanto, y es todavía mas sorprendente que tan poco conocimiento nos de tanto poder.”
Bertrand Russell
Lenguaje de consultas de datos
¿Qué es? Es un subconjunto de
SQL con algunas características propias y es fundamental para poder sacar provecho a la función Query
¿Para qué se puede usar? Podemos obtener
respuestas concretas, usando consultas avanzadas como filtros, de cualquier área de datos (ya sean celdas o contenido obtenido externo usando –por ejemplo- ImportXml o ImportHML, etc.)
SPREADSHEET avanzado para análisis y reporting SEO
XPath
¿Qué es? Es un lenguaje que
permite construir expresiones que recorren y procesan un documento XML y es fundamental para sacar provecho a la función ImportXML
¿Para qué se puede usar? Obtención de datos
publicados en páginas de sitios externos (ImportXML, ImportHMT o ImportData forman un ‘combo’ VITAL para hacer scrapping de contenidos usando GOOGLE SPREADSHEET)
SPREADSHEET avanzado para análisis y reporting SEO
Expresiones regulares (Regex)
¿Qué es? Se utilizan para
comprobar si hay un patrón en una cadena alfanumérica y son fundamentales para poder sacar provecho a las funciones: RegexMatch, RegexExtract o RegexReplace
¿Para qué se puede usar? Podemos analizar las
palabras clave que obtenemos de GOOGLE SEARCH CONSOLE (antes GWT) y saber si son expresiones ‘Brand Terms’ o ‘Non Brand Terms’, o si contienen expresiones que indican localidad, son transaccionales, etc.
SPREADSHEET avanzado para análisis y reporting SEO
JAVASCRIPT
¿Qué es? Es el lenguaje principal
del servicio GOOGLE APPS SCRIPT y es fundamental para poder generar funciones personalizadas que no existen de manera nativa en GOOGLE SPREADSHEET
¿Para qué se puede usar? Para conectar GOOGLE
SPREADSHEET con otros servicios externos como: APIS externas, BBDD de MYSQL (por ejemplo la de los contenidos de WP…)
15/04/2023SPREADSHEET avanzado para análisis y reporting SEO 15
EJEMPLOS DE DATOS QUE ‘COBRAN VIDA’ EN GOOGLE SPREADSHEET
“Treguna mecoides trecorum satisdi.”
“Boliches y Escobas”, 1971(“La Bruja Novata en España”)
¿Qué tráfico, evolución y tendencias?
=sparkline(query($B$3:D$16;"SELECT D WHERE NOT B LIKE '%(%'";0))
=round(forecast(13;D$3:D$14;$A$3:$A$14);0)
¿Cómo evolucionamos con respecto al mercado?
15/04/2023SPREADSHEET avanzado para análisis y reporting SEO 19
¿Quiénes son los competidores?
=Regexreplace(concatenate(query(importxml(concatenate("http://www.google";Brief!$B$4;"/search?q=";substitute(lower(Brief!
$B$1);" ";"+");"&pws=0");"//h3/a/@href");"SELECT Col1, '|' WHERE Col1<>'' AND NOT (Col1 LIKE 'http://www.google.%' OR Col1 LIKE 'https://maps.google.%' OR Col1 LIKE '/images?q=%' OR Col1 LIKE '/search?q=%') LIMIT 10 LABEL '|' ''";0));"(\/url\?q=|(&sa=[^|]
+|))";"")
ImportXML(concatenate("http://www.google";Brief!$B$4;"/search?q=related:";substitute(regexreplace(Brief!$B$2;"(http(s|)://
(www\.|))";"");" ";"+");"&hl=es&pws=0");"//cite")
SPREADSHEET avanzado para análisis y reporting SEO
¿En qué es mejor la competencia?
=ImportHTML(concatenate("https://es.majestic.com/reports/comparator?
q1=";regexreplace(regexreplace(trim(lower($A2));"(https?:\/\/|)(www\.|)";"");"\/$";"");"&IndexDataSource=F");"table";0)
=ImportHTML(concatenate("http://www.mysiteview.net/d/";regexreplace(lower($A2);"https?:\/\/|
www\.|\/";""));"table";0)=getLinkscapeData(regexreplace(lower($A2);"\/$";""))
=importjson(concatenate("http://api.aljtmedia.com/social/?url=";$A2);"";"noHeaders")
SPREADSHEET avanzado para análisis y reporting SEO
¿Por qué palabras se posicionan?
=ImportHTML(concatenate("http://www.keyrow.com/data/?s=";regexreplace(lower($A1);"https?:\/\/|www\.|\/
$";"");"&more=1000");"table";0)
SPREADSHEET avanzado para análisis y reporting SEO
Ideas de palabras clave
15/04/2023SPREADSHEET avanzado para análisis y reporting SEO 23
=ImportXML(concatenate("http://google.es/complete/search?output=toolbar&q=";$A2;"&hl=es&lr=lang_es");"//@data")
¿Qué KW sonNon Brand y qué patrón siguen las
Brand terms?
15/04/2023SPREADSHEET avanzado para análisis y reporting SEO 24
=arrayformula(if($A2:$A<>"";REGEXMATCH(lower($A2:$A);Brief!$B$13);""))
=arrayformula(IF($A2:A<>"";REGEXREPLACE(REGEXREPLACE(lower($A2:A);Brief!B13;"[MARCA]");"opini(o|ó)n(es)?";"");""))
¿Cómo evolucionan las posiciones de palabras
clave agrupadas?
15/04/2023SPREADSHEET avanzado para análisis y reporting SEO 25
ANÁLISIS MÁS ALLÁ DE LA HOJA DE CÁLCULO
“La única posibilidad de descubrir los límites de lo posible es aventurarse un poco más allá de ellos, hacia lo imposible.”
Arthur C. Clarke
SPREADSHEET avanzado para análisis y reporting SEO
MySQL
MySQL + SPREASHEETfunction connectToMySqlDB(Query,Column,Cell){ // Obtiene las variables de conexión a la base de datos de la hoja SETUP var address = String(SpreadsheetApp.getActive().getRange('SETUP!$B$1').getValue()); var user = String(SpreadsheetApp.getActive().getRange('SETUP!$B$2').getValue()); var userPwd = String(SpreadsheetApp.getActive().getRange('SETUP!$B$3').getValue()); var db = String(SpreadsheetApp.getActive().getRange('SETUP!$B$4').getValue()); var dbUrl = 'jdbc:mysql://' + address + '/' + db; // Ejecuta la conexión var connection = Jdbc.getConnection(dbUrl, user, userPwd); // Ejecuta la consulta pasada como parámetro Query var SQLstatement = connection.createStatement(); var result = SQLstatement.executeQuery(Query); // Escribe los valores obtenidos a partir de la celda pasada como parámetro Cell var ss = SpreadsheetApp.getActiveSpreadsheet(); var cell = ss.getRange(Cell); // Vuelca los datos obtenidos de la consulta en el nº de columnas pasadas como Column var row = 0; while(result.next()) { for(var i=0; i<Column; i++) { cell.offset(row, i).setValue(result.getString(i+1)); } row++ } // Cierra el objeto result.close(); SQLstatement.close(); connection.close();}
15/04/2023SPREADSHEET avanzado para análisis y reporting SEO 29
AYUDA, IDEAS Y RECURSOS ÚTILES
“Dame seis horas para cortar un árbol y pasaré las primeras cuatro afilando el hacha.”
Abraham Lincoln
SPREADSHEET avanzado para análisis y reporting SEO
¡Afila el hacha!
SPREADSHEET avanzado para análisis y reporting SEO
¿Para qué? • Función personalizada Getlinkscapedata (API MOZ)
• Función personalizada Importjson
• Función estándar Query• Función estándar
ImportXML y Guía de Xpath
• Ejemplo de expresiones regulares
• Javascript para GOOGLE APPs y funciones avanzadas para el agrupado de palabras clave
• Mozrank, Domain Authority• Formatear datos JSON• Hacer consultas a datos de la
hoja de cálculo• ‘Escrapear’ páginas• Análisis avanzados de palabras
clave• Creación de funciones
avanzadas
SPREADSHEET avanzado para análisis y reporting SEO
¡GRACIAS!¿Alguna pregunta,
duda o insulto?;-)
• 629 200 821• jose@jbmoreno.es
• @jbmoreno• josebmorenosuarez