Analizar de lo más general a lo más particular, el mínimo minimísimo para enterarte de qué van los datos que tienes delante. En este artículo no vamos a usar R, Python, ni siquiera vamos a usar ¡regex! Nos hemos propuesto crear un google sheets que pueda clasificar de forma ágil un listado de keywords, tan solo usando fórmulas en la hoja de cálculo, ¡a lo loco!
¿Por qué Categorizar keywords?
Cuando nos encontramos ante una lista de palabras clave y la queremos clasificar teniendo en cuenta varios criterios o atributos que ya conocemos de antemano, podemos desbloquear oportunidades que no sabíamos que estaban ahí.
Categorizando esas palabras por distintas categorías y haciendo un recuento, tal y como si hiciéramos una tabla dinámica, nos puede ayudar a tener una visión y percepción de esta lista, que nos puede ayudar mucho. Esto se puede aplicar a cientos de casos de uso, pero para este post, nos vamos a centrar en el caso de uso típico: hacer un keyword research.
¿Cómo elegimos los atributos de categorización?
Es importante que dediques tiempo a entender el producto o servicio que tienes ante ti, antes de usar herramientas de keyword research y volverte loca.
Entender qué problemas resuelve el producto, qué características tangibles tiene, qué otros atributos intangibles se puede encuadrar.
También es interesante hacer un research de competidores y ver qué tipo de características inherentes, poseen sus productos o servicios, de esa forma te meterás a fondo en el sector y entenderás aún más cómo listar las características que luego serán tus categorías.
El ejemplo que traemos hoy es el sector de los cursos de inglés, hemos ideado 4 atributos de referencia para que se entienda:
- Atributo precio: barato, económico, precio, mejor precio, lo que sea…
- Atributo destinatario o quién va a recibir el curso: adultos, niños, empresas, autónomos, etc.
- Atributo nivel: a1, b1, c2, etc.
- Atributo skills o qué áreas de aprendizaje se quieren adquirir: speaking, listening, conversación, etc.
- Atributo longitud de la keyword: si son long tail, mid, short, en base a la longitud (cadena de 1, 2, 3, 4 términos)
- Atributo volumen de la keyword: hacer un escalado en base a volumen, si es más bajo de 100, es bajo, si es superior a 10000, es alto, el escalado que cuadre con el mínimo y máximo volumen de tu muestra.
Con esto definimos qué keywords queremos clasificar, tan solo quedaría poner un nombre a los grupos de esos atributos, para saber luego cuántas keywords hay de cada y tener una visión más segmentada de todo el conjunto de datos.
El Google Sheets
Si nos vamos a nuestro maravilloso y manual Google Sheets, tan solo hay que rellenar la palabra clave a capturar y el nombre del grupo en el que queremos clasificarla.
Las fórmulas que hemos usado
En esta pestaña apenas hay que usar fórmulas, porque es la pestaña que ha de rellenar quien use el documento, pero para los que entren al doc y vean que hay varias columnas ocultas, ahá, habéis sido muy sagaces.
Usamos un concatenar para hacer que la palabra que se intruduce como keyword a clasificar, se convierta en *keyword* (asterisco keyword asterisco), para que las fórmulas hagan una búsqueda \»contiene\» en vez de una búsqueda \»exacta\».
¿De dónde sacar los datos de keyword?
Cuando hablamos de herramientas, podemos pensar en SISTRIX, SEMRUSH, Keyword Planner, Keywordtool.io, Ahrefs, y un larguísimo etcétera, así que en este post no te recomendamos una cosa u otra, dado que es indiferente para lo que queremos explicar.
El Google Sheets
El Google Sheets recoge dos campos: keyword y volumen. Pero como estaréis pensando en vuestras cabecitas, si, se puede adaptar para que sea un export directo de cualquiera de las herramientas mencionadas, lo hemos hecho así por simplificar 🙂
Por tanto, pegando los datos en la pestaña de keywords los duendes que se esconden tras las fórmulas de Google Sheets empezarán a trabajar de forma invisible sin que tu ni yo nos demos cuenta.
(En esta pestaña, no hay fórmulas)
La categorización
Pues aquí viene la gracia. Esta pestaña ya muestra todos los cálculos, trayéndose los atributos y la tabla de keywords, además de calcular una columna con la clasificación de los atributos, por si alguien quiere filtrar y ver al detalle.
A la derecha las tablas con el recuento de todo todísimo, para tener una foto clara de la composición de ese listado de keywords, por recuento y por suma del volumen
El Google Sheet
Así queda toda esta película manual
Las fórmulas que hemos usado
Aquí nos hemos hartado, y vuelvo a repetir, el placer de practicar, aprender y ver múltiples opciones de resolver algo, pues nos ha llevado a esto 😀
- Traer datos de otra pestaña
=\"celda\"
- Contar número de palabras de una celda
=LARGO(ESPACIOS(A12))-LARGO(SUSTITUIR(ESPACIOS(A12);\" \";\"\"))+1
- Clasificar palabras en short, mid, long tail
=SI(Y(0<=C12;C12<2);\"short\";SI(Y(2<=C12;C12<4);\"mid\";SI(Y(4<=C12;C12<6);\"long\";SI(Y(6<=C12;C12<15);\"extreme_long\";\"otras\"))))
- Clasificar volumen en rangos bajo, medio, alto
=SI(Y(0<=C12;C12<51);\"0-50\";SI(Y(51<=C12;C12<101);\"51-100\";SI(Y(101<=C12;C12<501);\"101-500\";SI(Y(501<=C12;C12<1001);\"501-1000\";SI(Y(1001<=C12;C12<2001);\"1001-2000\";SI(Y(2001<=C12;C12<3001);\"2001-3000\";SI(Y(3001<=C12;C12<5001);\"3001-5000\";SI(Y(5001<=C12;C12<10001);\"5001-10000\";SI(Y(10001<=C12;C12<15001);\"10001-15000\";SI(Y(15001<=C12;C12<20001);\"15001-20000\";\"0\"))))))))))
- Clasificar en base a coincidencia contra una lista de criterios (contra la pestaña de atributos, si no encuentra coincidencia, escribe \»otras\»)
=SI.ERROR(INDICE(\'1-añade atributos\'!$D$16:$D$26;COINCIDIR(VERDADERO;ESNUMERO(HALLAR(\'1-añade atributos\'!$C$16:$C$26;A12));\"\"));\'1-añade atributos\'!$D$26)
- Recuentos por criterio
=CONTAR.SI.CONJUNTO(A:A;M12)
- Sumatorios por criterio
=SUMAR.SI.CONJUNTO(B:B;D:D;\"FALSE\";G:G;M13)
¡Y ya estaría!
Acceso al documento
Lo liberamos en abierto para que os hagáis una copia, lo rompáis, lo mejoréis, lo adaptéis, hagáis una fogata o lo que os apetezca, pero molaría que permanezca la esencia y el espíritu manual (nada de no regex, app script, python, ni machines learnings), solo fórmulas my friends.
Esperamos que os haya gustado o aportado algo, enjoy!
https://docs.google.com/spreadsheets/d/1mNFJHgZAiPXAimsLhxRA_au3YWCU1L9RjLlXyF6eXrU/copy