A step by step keyword research guide for SEO in 2020

This process document will cover:

  • How to find research and discover keywords
  • How to choose the right types of keywords
  • How to use each keyword tool listed
  • How to collate all the data
  • How to make all the data presentable
The Tools and websites used in this guide are:
 
  • SEMRush
  • Answer the Public
  • Google Keyword Planner
  • Reddit
  • Quora
  • And arguably an SEO’s best tool… Microsoft Excel
 

Quick links to blog content:

1. Research & Data Pulls

The first step of any keyword research task is to compile data dumps of relevant topics matching your business niche. This is obviously a very easy thing to do, however, this process can go quite granular.

A full, unfiltered export is likely to not give you much much insight at all. 

I’ve seen many keyword research documents throughout my years in SEO and sadly most seem to be just that, a giant data dump with no real insight.

That said, research and pulling data is the starting point, so here is how I do it.

 

1.1 SEMrush

Inputting a website into SEMrush is a great place to start as SEMrush shows you the exact keywords that a website already ranks for. It also includes historical data and comparative keyword metrics against competitors.

This is my go to tool as it takes much of the guess work out from the get go. You can even drill down by folder path, enabling you to see every single keyword (along with SERP features) that you or a competitors URL ranks for.

This is particularly helpful when you want trimmed keyword sets of subtopics from domains that may cover a whole range of niches. Department stores for example:

 

From here you can export all keywords with search volume, SERP features, competition levels, CPC’s and even the associated ranking URL. 

This can then be split out by country and you can use to advanced filters to remove branded queries and go more granular into sub-topic keyword sets.

 

There is a heap of information here that is useful for much more than just SEO.

This export will give you a top-level understanding of where the volume and ranking opportunity is for non-branded topics. As you can see below, with a few basic filters I can already see at a glance what the top, non-branded traffic driving keywords are for Nike:

 

SEMrush also has a “Topic Research” function that can be used to discover content ideas, additional keywords, questions and headlines that competitors are using.

1.2 Answer the Public

Answer the Public combines Google’s Autosuggest with a question scraper. 

This makes it the ideal tool for learning what questions your target audience is asking about a topic. It is the best tool out there (in my opinion) for generating quick lists of informational queries.

Set your country, type in a top-level keyword and then you’ll be given a hefty list of:

  • Questions
  • Prepositions
  • Comparisons
  • Alphabetical matches
answer the public

In this list we are mainly concerned with the list of questions and prepositions, as those are the keywords that trigger SERP features and are often (surprisingly so) ignored by competing brands.

These informational queries are generally low in volume but also low in competition. However, as a topic set, you can create a sizeable keyword list with a decent search volume total.

Once the data for your keyword has loaded, you will see a wheel of questions, like below:

answer the public cheap flights example

Ok now we have our list, the next steps are to:

  • Export the CSV file and filter all “questions” and “prepositions” keywords so those are the only types that remain
  • Delete any keywords that are not relevant or malformed

1.3 Google Keyword Planner

The Google Keyword Planner is the most reliable source of keyword data online, as it comes directly from Google.

This tool makes it very easy to export a bulk list of keywords that you input, complete with historical metrics, or discover new keywords with their keywords ideas tool.

 

google keyword planner

Firstly, to get the search volume for your Answer the Public list, do the following:

  • Click on “Get search volume and forecasts”
  • Paste in your list of informational keywords and click “GET STARTED”
  • Click on “Historical Metrics” and then export the data from Google Keyword Planner in a CSV file

Secondly, if you want an additional start point for some extra research, then click on the “Find new keywords” tab and past in your core keyword(s). 

You will get a list of related terms as well as complete historical volume metrics, which is very handy!

 

cheap flights keywords

1.4 General Brain Storming

Aside from the dedicated keyword research tools, sometimes the old fashioned method of manual searching can be a great way to discover topics, keywords and questions that your target audience has already shown interest in.

The auto suggest feature of both Google and YouTube is a great place to start.

 

 

cheap flights google suggest
youtube cheap flights example

Both of the above examples work the same way, where Google and YouTube are showing you related keywords that users have searched for.

This can be a quick and easy way to find relevant terms to a core topic that might otherwise be missed when using tools like SEMrush, Answer the Public and the Google Keyword Planner.

Finding keywords on websites like Reddit, Quora, or any other popular online forum is another great way to find topic ideas. Using Reddit as an example:

  • Enter any broad topic that your audience may be interested in and then choose a popular subreddit on that topic
reddit-cryptocurrency-example
  • Browse through all the current popular threads and just keep and eye out for any posts that may prompt addition questions and keyword ideas. You can also search within the subreddit itself.

This thread below might make you think of topics and questions such as:

  • What’s the cheapest way to buy Bitcoin?
  • What are the buying fees on Binance?
  • Can I buy crypto with a credit card?
reddit post example crypto

I find both Reddit and Quora great platforms to do this kind of research on.

 

2. Formatting the Data

Now that we have our data dumps, we need to clean and format everything.

This is not only to remove irrelevant or no volume keywords, but to also create a presentable document that clients can easily understand and interact with.

The latter point is very important as this provides something of value to a client. It’s a resource they can continuously reference and easily navigate. This makes internal sharing on their end much easier.

 

2.1 Trimming the dead weight

The first step in formatting is to get rid of all useless keywords.

To begin, paste in all your exported keywords and create a filter. We want to filter volumes containing the following:

  • 0
  • (blanks)
keyword filter example

Delete all of these terms with 0 or blank volume.

Next, create a new column to filter keywords by topic or question type and apply a “Group” to every keyword.

keyword group filter

Apply the group tag to all keywords.

From the small sample size above, you can see how granular you could go here. In that set alone you could group by:

  • brand
  • location
  • product type
It’s also very easy to create an additional group in a new column if you wish to keep the core group “Kids” and then create more granular keyword sets.
 
Continue to create a group for every single keyword. There may be a bit of manual editing involved as I often find certain keywords are left over that are missed when filtering. Every export is bound to have a few terms that are just not relevant.

 

2.2 Creating and formatting the tables

Now that all the keywords have been assigned a group, highlight all the data on columns A, B and C, then create a pivot table in a new worksheet.

If you’re not sure how to create a pivot table, here is how to do it:

  • Ensure all data is highlighted 
  • Click on “Insert” in the top navigation bar
  • On the upper left hand side, click on “Pivot table”, then click “OK”
We want this to be created in a new spreadsheet.

The new spreadsheet will open. We now need to sort the data into segments. In the “PivotTable Fields”, do the following:

  • Drag “Group” to “Rows”
  • Drag “Keyword to “Rows” below “Group”
  • Drag “Volume” to “Values”
You will then be left with this:
pivot table formatted excel

The next step is to insert a slicer.

Here are the steps to do so:

  • Click on the pivot table
  • Click on “Insert” in the top navigation and select “Slicer”
  • Select “Group” in the pop-up
This will give you the slicer filtered by keyword group. There is still a bit more formatting to do before the data is presentable. Next we need to:
 
  • Click on the volume of the first keyword (most likely cell B5)
  • Click on “Data” in the top navigation and then press Z -> A
This will filter all keywords by descending volume. You will be left with this:
pivot slicer volume filter

There is one last bit of formatting that needs to be done for the pivot and slicer. Here’s what you need to do:

  • Right click on the pivot table and go to “PivotTable Options”
  • Click on the “Layout” tab
  • Un-tick “Autofit column widths on update”. This prevents the size of the pivot table changing
You may want to also format the slicer, to do so:
 
  • Right click on the slicer and go to “Size and Properties”
  • Click on the “Position Layout” tab dropdown
  • Change the number of columns to suit how many keyword groups you have
Change the look and format all you want. Lastly, I also like to do the following:
 
  • Turn off gridlines. Under “View”, untick “Gridlines”
  • Rename the pivot table and slicer headers. “Keyword” and “Volume” for the pivot and “Group” for the slicer
  • Add a title box for each keyword pivot & slicer set
Note: You can add multiple pivots and slicers to the same sheet. This is handy when wanting to split out different keyword groups, such as generic, brand, informational and transactional.
 
That’s it! You’ll be left with something like this:
 
keyword document complete

Lastly, to take this one step further, it’s very easy to add charts to your pivot tables and slice those too. This is a great way to present a group of keywords and showcase their ranking movement month on month, or the historical search volume.

Quick example of how that can look: 

All of the above can also been done in Google Sheets.

Note that you will have less control over slicers in Google Sheets and it doesn’t suit keyword lists as well as Excel does, however, if you are just wanting to showcase some numbers and graphs, like the above image, it can be a great option as it’s much easier to share around.

Google has a quick guide on how to slice data in sheets. It’s basically the same process as it is in Excel. Check it out here:

https://support.google.com/docs/answer/9245556?hl=en

I hope this guide has helped you in your efforts to research and compile keywords for SEO and/or even paid search!

Please leave a comment below with your thoughts and let me know if there are any more specifics you would like covered.