How To Do Keyword Research Using Semrush and Google Sheets

Background to Keyword Research

What is keyword research?

Keyword research is the process of understanding the language your target customers use when searching for your products, services, and content.

It then involves analyzing, comparing, and prioritizing the best keyword opportunities for your website.

Exciting stuff. To do this, I am using Semrush and Google Sheets.

In today’s SEO tutorial you will learn:

• How to check what keywords the pages on your site are currently ranking for, using SEMrush Organic Search Positions Report
• How to use the Keyword Magic Tool to find keyword opportunities
• How to filter a column of broad match keywords, using a REGEX match filter formula to return question-type long-tail keywords
• How to use the VLOOKUP formula to easily find and match the keyword difficulty of your keywords in your final spreadsheet

Let’s dive in.

1. Extract the keywords that your site currently ranks for, using the Organic Search Positions Semrush menu

The Semrush Organic Search Positions menu allows us to check what keywords the content on the website is already ranking for. This is, in my humble opinion, a great starting point for a keyword research project.

In order to export these keywords:

1. Go to the Dashboard > Domain Analytics > Organic Research
2. Navigate to the Pages tab
3. Click on Export

Not all of these will be relevant to the business aims, so consulting with the client is crucial at this stage.

2. Create a Keyword Research Google Sheet

Organize the keywords based on topics, as demonstrated below.

You will need three column pairs:

• Keywords we currently rank for + KD (keyword difficulty)
• Keyword Opportunities + KD
• Questions + KD

Start pinpointing the main topics (e.g. natural language processing, machine learning, AI), then organize the keywords from the export we did in step 1 per topic.

3. Run your topics through the SEMrush Keyword Magic Tool.

What I refer to as topics in the context of the keyword research are the main keywords you created via grouping in the previous step. These are referred to as seed keywords as well.

Most often these are primary keywords, that are seeded in their main form (e.g. machine learning, natural language processing, artificial intelligence) or via an abbreviation (e.g. ml, nlp, ai) in the majority of keywords in a group.

Let’s find their broad matches, using the Keyword Magic Tool.

1. Go to SEMrush > Keyword Overview > Keyword Magic Tool
2. Enter your primary keyword
3. Export the sheet for each of the main keywords, leaving the settings at all, broad match.

4. Add the exported sheets in your main keyword research Google Sheet file

Ensure to keep the keyword, volume, and keyword difficulty columns.

You can color-code the latter two fields via conditional formatting. This will help you with the next steps.

You can also make a copy of this dashboard I built for keyword search intent classification in Data Studio.

Click here to get your free copy of the Search Intent Keyword Classifier (Data Studio Template)

5. Identify keyword opportunities from the exported sheets

In the words of Tim Soulo:

Keyword research is the only way to figure out what people are typing into search engines. You need to know this to avoid creating content about things that nobody is searching for.

Go through the sheet for each of the main keywords and identify keyword opportunities, based on the search volume and keyword difficulty. Keywords with higher search volumes and low/medium keyword difficulty are good targets but don’t stay shy from more niche ones, too.

This is a good stage to involve your client for feedback, as they would know specific aims and business needs for the near future. This can help streamline the keyword selection process.

Once identified, paste your selection in the relevant section of your main sheet, in the column Keyword Opportunities.

6. Use Regexmatch in Filter criteria in Google Sheets for finding question-type keywords

Question-type keywords and long-tail keywords that contain the seed keyword in any order, plus a “question word” like “how,” “what,” “where,” “when,” or “why.”

It is important to have a look at these keywords as it indicates almost directly the type of content users are searching for in a given topic. Make sure to pay attention to the language they use when doing this. It will often differ from the language you might use.

In order to fill the Questions column, we need to use a regex match formula in the filter criteria, referencing the list of keywords exported via the Keyword Magic Tool from SEMrush.

To do this, use the following formula, where you are referencing from the sheet Machine Learning (where the export from the keyword magic tool is hosted; see step 4), column A, containing the keywords:

=filter(‘Machine learning’!A:A,regexmatch(‘Machine learning’!A:A, “who|what|where|why|how|which”))

This formula filters the targetted column from the referenced sheet and applies a regex match filter, using a pattern that leaves in question-type keywords.

This is inspired by this tutorial for using regex match in a filter and this tutorial for regex in Google Search Console.

7. Use VLOOKUP to match the keyword difficulty column to existing keywords

We now have three columns that are filled in for each topic, however, we have not yet matched the keyword difficulty to each one.

In order to fill in the keyword difficulty, use the VLOOKUP function.

The VLOOKUP function finds things in a table or range by row. The secret is to organize your data so that the value you look up is to the left of the value (trust me this will save you a lot of hassle).

Paste the following formula, using your relevant cells

=vlookup(C3, ‘Machine learning’!A:C, 3, false)

where:

• C3 — represents the search value (in our case — the keyword)
• ‘Machine learning’!A:C — represents where this value will be searched for (columns 1–3 in our Machine learning export from step 4)
• 3 — is the index of the column returned (i.e. the third column from the range we’ve provided)
• false — shows our values are not sorted.

Copy the formula down the column, once done to auto-populate it across the column.

Keyword Research is the process of analyzing, comparing, and prioritizing the best keyword opportunities for your website.

Final Thoughts

We have created what is the first step from the content planning process. Next up is topic research with the aim of content idea discovery, as well as the creation of a content calendar.

Most importantly, always ensure to involve your client in the different steps of the keyword and topic research processes, as their input for the content strategy’s aims from a business standpoint is paramount for making great SEO research.