Automating Keyword Research with Google Colabs & Python-Semrush.

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

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.

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.

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:





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.

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.

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.
