google spreadsheet avanzado para análisis y reporting seo

34
SPREADSHEE T avanzado Para análisis y reporting SEO

Upload: jose-b-moreno-suarez

Post on 03-Aug-2015

1.127 views

Category:

Marketing


0 download

TRANSCRIPT

Page 1: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

SPREADSHEET

avanzadoPara análisis y reporting

SEO

Page 2: GOOGLE SPREADSHEET avanzado para 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

Page 3: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

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

Page 4: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO
Page 5: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

LA HERRAMIENTA

“El hombre es un animal que hace herramientas.”

Benjamin Franklin

Page 6: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

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

Page 7: GOOGLE 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

Page 8: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

…y las hojas de cálculo

Recolectar datos

Procesarlos

AnalizarInstruir

Optimizar

15/04/2023SPREADSHEET avanzado para análisis y reporting SEO 8

Page 9: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

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

Page 10: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO
Page 11: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

“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

Page 12: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

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

Page 13: GOOGLE 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

Page 14: GOOGLE 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

Page 15: GOOGLE 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

Page 16: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO
Page 17: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

EJEMPLOS DE DATOS QUE ‘COBRAN VIDA’ EN GOOGLE SPREADSHEET

“Treguna mecoides trecorum satisdi.”

“Boliches y Escobas”, 1971(“La Bruja Novata en España”)

Page 18: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

¿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)

Page 19: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

¿Cómo evolucionamos con respecto al mercado?

15/04/2023SPREADSHEET avanzado para análisis y reporting SEO 19

Page 20: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

¿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

Page 21: GOOGLE 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

Page 22: GOOGLE 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

Page 23: GOOGLE 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")

Page 24: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

¿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)?";"");""))

Page 25: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

¿Cómo evolucionan las posiciones de palabras

clave agrupadas?

15/04/2023SPREADSHEET avanzado para análisis y reporting SEO 25

Page 26: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO
Page 27: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

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

Page 28: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

MySQL

Page 29: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

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

Page 30: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO
Page 31: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

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

Page 32: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

¡Afila el hacha!

SPREADSHEET avanzado para análisis y reporting SEO

Page 33: GOOGLE 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

Page 34: GOOGLE SPREADSHEET avanzado para análisis y reporting SEO

¡GRACIAS!¿Alguna pregunta,

duda o insulto?;-)

• 629 200 821• [email protected]

• @jbmoreno• josebmorenosuarez