Automating Keyword Research with Google Colabs & Python-Semrush.

admin November 24, 2020 Technology No Comments

In late 12th century England, teams of agricultural workers using the heavy plough could expect to harvest 250 kilos of grain from 1 acre of land. With its ability to turn over the fertile clay laden soils of northern europe at unprecedented speed, the medieval heavy plough was praised as a high technology of its time. Alexander Nequam, Abbot of Cirencester Abbey, called it ‘a divine piece of work, discovered by some lofty genius, whose utility transcends the power of writing to express’.

12th century English plough team with oxen.

Efficiency was on the increase, but few could dream of what would become possible. Today, a farmer working the same land can expect to harvest 3,500 kilos of wheat per acre – that’s 14 times as much.

Ploughing through web interfaces

Keywords are the lifeblood of search, so harvesting keywords is a pretty important and commonplace daily activity for the team at Found. Up until now, I had to find keywords using the slow multi-step interfaces of the major keyword research tools such as Google Ads Keyword Planner, SemRush and Ahrefs, for which 2-factor authentication, slow web page loads, and the management of .csv exports are part and parcel. Switch-tasking and delays such as these not only take time but disrupt focus and flow.

The web interfaces of the major SEO tools are amazing development achievements. But they have to serve all possible needs at once, which makes them slow.

There is an alternative

Automating keyword research using a Google Colabs notebook built to harness the semRush API is a frictionless method which saves time and effort. It’s particularly effective for ad-hoc conversion focused keyword research (especially when responding to ad-hoc queries) and a useful starting point for comprehensive keyword research projects with great opportunities to scale.

Automated keyword harvesting with Google Colabs & SEMRush

It’s hard to get good quality conversion focused keyword research quickly

By conversion focused keyword research, I mean research where I only want keywords searched by people who are definitely interested in a specific topic. So for the topic ‘christmas dinner in london’, I want keywords like ‘best christmas dinner in london’ but not ‘christmas dinner menu’ which is related but not closely. Getting this tightly focused keyword research from the interfaces of the major keyword tools is fiddly and settings intensive. I have found that third party keyword research tools’ related keywords functions within the interface tend to return a high proportion of keywords which are only broadly relevant for any input phrase if I just put in that phrase on its own. They usually have functions for narrowing down by only including certain words, but these are not as powerful or easy to use as they should be. If I could get a list of just closely related keywords automatically, I wouldn’t need to spend time going through and separating the wheat from the chaff. Now, I can.

Keyword Harvester

Keyword Harvester is part of a Colabs notebook I built to do automated keyword research without having to go into a third party tool interface and wait for it to load.

I give it a list of seed phrases and it gets closely related keywords for each one, along with the search volume. It takes one second per seed phrase. It gives me a sheet with these columns:

Seed Phrase
Keyword (closely related to the phrase)
Search Volume in a specified market.

What is a Google Colabs Notebook?

A place to run code in the cloud, with simple ways to input data for processing and export it for use. I put something into it like keywords or urls, either directly into a text form, or from a Google Sheet, press Play, and it runs code I put there earlier on whatever data I have input. Then it makes a new Google Sheet and puts the results of applying that code to my input data in the sheet.

Go to the notebook (it has a link, just like a Google Sheet)
Authenticate by copy pasting a code
Press play going through each step

Step by step:

1. I select my market and specify how many keywords I want to get per seed, and press play. I am given a link to a newly created G Sheet to put this data in.

2. This is the sheet that was just created. I then put in my seed keywords, for which I want to get closely related keywords. By avatar, I mean avatar the last airbender.

3. After putting the seed keywords in that sheet, I go back to Colabs and estimate the API usage by pressing play. This is not big scale research! An estimate is needed because the number of keywords SEMRush returns is variable for different seeds.

4. I press play on the next cell to harvest the keywords. It takes a couple of seconds, tells me how many API units were used, and creates the output G sheet with the harvested keywords!

5. The harvested keywords in the newly created G sheet, grouped by seed phrase, with search volumes. The keywords are very closely related to the seed phrases, which is what we want. I didn’t have to wait for any loading, do 2 factor authentication, or play with filters.

Benefits of using a Colabs notebook vs. a tool interface:

No more .csv exports and imports – Google Colabs connects seamlessly with Google Sheets, giving me a sheet to paste my keywords into, and making another sheet with the results.
No more waiting for webpages to load – The single specific purpose eliminates the need for a slow web interface, which has to cater for everybody’s needs.
No more 2 factor authentication delays – All I need to do is click a link and paste in the code to authenticate the connection to Google Sheets.

The API

Behind a keyword research tool like semrush is a series of tables with data. When you log into SemRush and research keywords, the SemRush webpage is making calls to these tables and they are sending back what is being asked for and displaying it. An API call is just like that, except you don’t have to be on the SemRush page, you can do it from anywhere.

Use case: A trip to the city

Our client, a farmer, has become so successful that they are expanding into the luxury hotel business. They want to understand the conversion opportunity around luxury hotels in london. That is the seed phrase.

The Rosewood Hotel in Holborn, London (I used to go to the Fitness First just up the road)

To approach this, I would like to first know how many people search for keywords directly on this topic. I need a clean list of keywords that are strictly about the topic in question, and their search volumes for the relevant market. On being fed this phrase for the UK market, SemRush’s related keywords database returns the following:

What is first apparent is that several of the keywords returned do not contain ‘luxury’ or ‘london’ even through the seed phrase was ‘luxury hotel in london’, such as ‘booking com london’. This has happened because SemRush does not know that we are conducting conversion focused keyword research. These are all related keywords, and sometimes, getting a list of keywords which are broadly relevant to the seed phrase is useful, such as when gathering content ideas for high funnel awareness building activity. But for our purposes of performing conversion focused keyword research, we need tightly related keywords to answer our specific need to find out how many people are interested in staying in a luxury hotel in london. To get this, we will use constraints – specified words which must be included in all related keywords returned.

Constraints are generated automatically to refine the keywords

There is a function in Keyword Harvester called autoconstraint, which makes it possible to do automated keyword research that’s narrowly related to a topic. When on, it will guess the constraints (the include-words to specify to SEMRush) by parsing up the seed phrases and including each one in the API call. It makes things a lot quicker, but they can still be added manually for that extra level of precision.

Back to the use case. For starters, I only want keywords about hotels! So that will be my first constraint.

That made things better. But ‘luxury’ isn’t included in several of the keywords. This will be the second constraint.

Better, but we’re not there yet. I would like to only have keywords which specify ‘london’ (Google’s searcher location detection for generics notwithstanding, for the purposes of example):

That’s more like it! That’s just for one seed phrase. I can feed in multiple phrases and this process will be automatically applied.

Time elapsed: < 1 minute
Mental state: Unrattled
Focus of mind: ‘How can this information be used to achieve our objectives?’ rather than ‘Why is this tool so slow’

The Tech

The SemRush API reports ‘phrase_this’ and ‘phrase_related’ are called from Google Colabs using the python-semrush package, with the request parameters modified to reference the user input values for the seed phrase, market and constraint parameters. The data is transformed in Pandas and is read from / written to Google Sheets with Gspread.

API calls with python-semrush in Keyword Harvester

Freedom

By allowing for better field drainage, access to the most fertile soils, and saving of labor time, the heavy plough stimulated food production and, as a consequence, population growth, specialization of function, urbanization, and the growth of leisure. It made a lot of people more free to do other things.

Using Keyword Harvester to part-automate my keyword research has saved me time by taking the friction out of the soil, thanks to the versatility of the SemRush API and the speed and simplicity of Google Colabs.