Processing keywords for SEO with SQL

Keywords are the pillar of the SEO architecture of a website, and so it is for SEM strategies.

When we do keyword research, they may come from several sources. It is not unusual to end up with hundreds or even miles of potential keywords.

Manually categorizing is not an option if you have hundreds of miles of keywords for different products, industries, and from multiple sources.

The solution is not straightforward if you think of a tool like Excel. However, with SQL we can easily accomplish this task.

Cleaning and processing the data with SQL

To accomplish this task, we have two dataframe (df) that contain complementary information about the keywords.

In the image, we can see the columns and the state of one of the tables. We have to categorize keywords with SQL, but first, we must clean and organize the df.

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

We have to drop the rows with NULL values in columns like Estimated CTR and Avg monthly searches.

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

We still have some NULL values. We need to fill up these remaining NULLs with 0.

If you wonder why we didn’t drop them in the previous step? is because they contain important keywords with search volume, but have an empty value in a non-relevant parameter such as CTR.

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

Now we are going to join the two tables according to the keywords that have search volume.

For this, we first create two columns in the monthly_search_clean df and then fill them with the values from the second table, using LEFT JOIN, and the keywords as a reference.

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

Some NULL values were generated after the join of both tables. This happened because not all keywords with search volume have an estimated CTR. We fill the NULL values with 0. The most important thing is the search volume.

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

Let’s categorize the keywords with SQL

The criteria to categorize the keywords depends on the SEO strategy.

For this example, I used4 criteria to categorize the keywords, taking into account the highest searches volume and relevance to the website positioning.

The category “others” was defined to place words with little volume but could be included later in articles or publications.

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

Check out the final result in the image below. Categories make each keyword easier to find when developing an SEO strategy.

prosessing keywords for SEO with SQL

Are you looking for an analyst? Contact me

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