Categorizar keywords para SEO con SQL

Hello! and thank you for dropping by! If you would like to take a peek of project in English, please click in the next buttons:

Las palabras clave o “Keywords” son la columna vertebral de la arquitectura SEO de una página web así como también de una estrategia de tráfico con anuncios o SEM.

Las keywords se consiguen de diversas fuentes y es normal que terminemos con cientos o incluso miles de keywords potenciales.

Categorizar manualmente no es una opción si tienes cientos o miles de palabras clave y distintos archivos de varios clientes.

La solución no es sencilla si piensas en una herramienta como Excel. Sin embargo, con SQL podemos cumplir con esta tarea fácilmente.

Proceso de limpieza y transformación de datos con SQL

Para cumplir con esta tarea, tenemos dos dataframe (df) que contienen información complementaria sobre las palabras clave.

En la imagen podemos apreciar las columnas y el estado de una de las tablas. Tenemos que categorizar keywords con SQL, pero antes debemos limpiar y organizar las df.

limpiar y categorizar palabras clave con SQL en Ecuador

Algunas columnas están vacías en ambos df. Vamos a crear 2 nuevos df que contengan únicamente las columnas con la información que necesitamos.

--Some of the columns are empty or have info that we don't need, so we create two tables with the data we are interested on

SELECT Keyword, [Avg# monthly searches], [Competition (indexed value)]
INTO monthly_search_clean
FROM PortfolioProject..monthly_search

SELECT Keyword, [Estimated CTR], [Estimated Clicks]
INTO avg_CTR_clean
FROM PortfolioProject..avg_CTR

Eliminamos las filas que tiene valores NULL en ciertas categorías como las búsquedas mensuales y el CTR estimado.

--Now we have to drop the rows with Null or 0 values. 
DELETE 
FROM avg_CTR_clean
WHERE [Estimated CTR] IS NULL

DELETE 
FROM monthly_search_clean
WHERE [Avg# monthly searches] IS NULL

Aún tenemos algunos valores NULL que no fueron eliminados, estos NULL restantes los vamos a rellenar con valor 0.

Si te preguntas ¿por qué no las eliminamos en el paso anterior? se debe a que contienen palabras clave importantes, es decir con volumen de búsqueda, pero tienen un valor vacío en un parámetro no relevante como CTR.

--We have to fill the empty values with 0
UPDATE monthly_search_clean
SET [Competition (indexed value)]= ISNULL([Competition (indexed value)], 0)

Ahora vamos a unir las dos tablas de acuerdo a las palabras clave que tenga volumen de busque. Para esto, primero creamos las columnas en el df monthly_search_clean y luego las rellenamos con los valores de la segunda table, utilizando las palabras clave como referencia.

-First, we create 2 new columns in the avg_monthly_search table, to fill with the clicks and CTR values of each keyword
ALTER TABLE monthly_search_clean
ADD Estimated_Clicks float

ALTER TABLE monthly_search_clean
ADD Estimated_CTR float

--Then we fill the new columns with the data from the avg_CTR_clean table

UPDATE a
SET a.[Estimated_Clicks] = b.[Estimated Clicks]
FROM monthly_search_clean a
	left join avg_CTR_clean b
	on a.Keyword=b.Keyword

UPDATE a
SET a.[Estimated_CTR] = b.[Estimated CTR]
FROM monthly_search_clean a
	left join avg_CTR_clean b
	on a.Keyword=b.Keyword

Algunos valores NULL se generan luego de realizar la unión, esto se debe a que no todas las keywords con volumen de búsqueda tienen un CTR estimado.

Rellenamos los valores NULL con 0. Lo más importante es el volumen de búsqueda.

--The NULL values generated should be fill with 0.

UPDATE monthly_search_clean
SET [Estimated_CTR]= ISNULL([Estimated_CTR], 0)

UPDATE monthly_search_clean
SET [Estimated_Clicks]= ISNULL([Estimated_Clicks], 0)

Categorizamos las keywords con SQL

Finalmente vamos a categorizar keywords con SQL.

El criterio que se utiliza dependerá de la estrategia y la arquitectura que se le quiera dar a la web.

Para este ejemplo, se categorizaron las keywords con 4 criterios que tenían las mayores búsquedas y relevancia para la arquitectura del sitio.

El quito criterio “otros” se uso para colocar palabras que tienen poco volumen pero que podrían ser incluidas posteriormente en artículos o publicaciones.

--Finally, we have to categorise the keywords in groups. 

SELECT *,
CASE
	WHEN Keyword LIKE '%adiestra%' THEN 'adiestramiento'
	WHEN Keyword LIKE '%escuela%' THEN 'escuela'
	WHEN Keyword LIKE '%entrena%' THEN 'entrenamiento'
	WHEN Keyword LIKE '%educa%' THEN 'educación'
	ELSE 'otros'
END as Categoria
FROM monthly_search_clean

El resultado final lo puedes ver en la siguiente imagen:

categorizar keywords con sql proyecto ecuador

¿Buscas un analista de datos? Contáctame

Whatsapp: +593 97 907 2682
Email: contact@ivan-condor.com