Step-By-Step Keyword Research (Free Tool and Video Guide)

• No Time to Read? Watch the Video Tutorial
• How This Works
• Adding Potential Keywords
• Selecting Keywords
• Keyword Value
• Take Action!

No Time To Read? Watch Instead!

How This Works

Essentially, you’re going to sort through all of the keywords that your competitors already rank for. 

Why?

They’ve already done the legwork of creating relevant content. Now, we can use tools to spy on what’s driving customers to their business so that we can focus our efforts on the most important terms.

Adding Potential Keywords

The first thing that we need to do is find out who our search competitors are. To do this, add your “vanity” or “primary” keyword into Google.

This should be the term that all of your direct competitors would want to be #1 for. Some examples are:

• Boston Roofer
• Tampa car accident attorney
• Commercial HVAC Austin

Next, open the websites for all of the businesses that appear on page 1. Don’t open directories like Yelp, HomeAdvisor, FindLaw, etc.

If you’re in a more competitive area, then you can grab all the competitors on the top 3 pages.

Then, copy all of the websites into a text document. You can use this chrome extension to copy the URL of all of your open tabs and just remove any irrelevant ones. 

After installing the plugin, make sure that one of your format options says “URL”

If you don’t see “URL” in any of your 3 format slots, open the extension settings and change one of your formats to “URL”

With all of your competitors pages open, use the “URL” option from Copy Tab to copy all of the URLs. Then, paste them into a text document.

NOTE: You can follow this process for as many keywords as you want. You’ll get more data, but you’ll also make more work for yourself.

Once you’ve gone through all your main keywords, found your competitors ranking pages, and added them to a text document – We now want to remove the duplicates.

You can use a tool like this one to remove all the duplicate lines from your list.

Once all of your duplicates are removed, paste the entire list under the “Competitor Page” header on the “Add Competitors” sheet.

Downloading Links

For each competitor page, click the link under “keywords link” to open Ahrefs. You’ll be sent to the Organic Keywords report for that URL, showing all keywords Ahrefs has on that page ranking within the top 50.

On the “Organic Keywords” page, click the “Export” button. If there are less than 1,000 keywords, then you can use the “Quick export”. If there are more than 1,000, choose “Full Export”.

Download the CSV file.

Repeat this process for all of the competitor websites you found earlier.

Once you have all of your CSV files, we need to combine them to make importing them into the Google sheet easier.

You can use this tool to merge all of your CSV files.

Once you have your merged CSV file, it’s time to import it.

On the “Import Sheet” sheet select cell A1. Then, go to File > Import > [Select where you stored the merged CSV file] and select “Replace data at selected cell.”

Selecting Keywords

Before going any further, we need to create our target page sheets.

Create a list of each “money page” or pages that you want to rank. These will most likely be your service pages.

Make a duplicate of the “Homepage” sheet and name each new sheet according to your “money pages.” Example sheet names could be

• Homepage
• Repair
• Car accident
• Lawn care

n the “Research” sheet, we now have a list of all of our competitors keywords (duplicates are automatically removed) as well as their corresponding monthly search volume.

For each keyword, add a “Yes” or “No” to the “Target” column to indicate whether or not you want to rank for that keyword.

Use filters on the “Keyword” column to parse through the list much faster.

After all of the keywords have been gone through, filter “Target” by “Yes” so that you only see the keywords that you want to target.

For every keyword, add the name of the “money page” that keyword will be associated with to the “Target Page” column. Make sure that it matches the sheet names we made earlier!

On your “Money page” sheets, you’ll see that the keywords from the research sheet that have this sheet as the target page have been copied over here.

The “money page” sheets allow you to see keywords on a page-by-page basis.

Along with the keywords, we can also see the monthly search volume, click value, Keyword Difficulty (KD), and Ranking Pages.

Monthly Search Volume – How many people search for this keyword per month (on average). Note that this is an estimate, usually it’s higher than what Ahrefs shows.

Click Value – This is how much it would cost to buy every click for this term on Google Ads. This is a good way to measure how much money is in a keyword (that’s a whole post on its own).

KD – Don’t take this at face value. Read Ahrefs article on how KD is calculated. https://ahrefs.com/blog/keyword-difficulty/

Ranking Pages – This column checks to see what competitor webpages are ranking for that keyword. By default, the formula is only in cell E2, you’ll need to drag the anchor down to the rest of the cells.

If you’re working with a lot of competitor websites, then the “Ranking Pages” column can become quite overwhelming. I suggest hiding this column until you need it.

You may encounter a “money page” sheet that looks like this:

This is because the sheet uses a script based on the name of the sheet.

When duplicating the Homepage sheet, the sheet name is “Copy of Homepage.” It can take a bit for the ‘system’ to recognize the new sheet name.

If you run into this issue, copy cell A1 and paste it back into A1. This seems to get the ‘system’ to recognize the change.

Keyword Value

Navigate to the “Stats” sheet. 

There’s a couple of automated cells and a couple where you’ll need to make edits.

Total Imported Terms – How many keywords were imported from Ahrefs.

Total Selected Terms – How many keywords you chose to target.

Total Search Volume – The total monthly search volume for your selected terms.

Est. CTR – What percent of searchers will click through to your website. If you ranked #1 for all of your selected keywords then you can assume roughly 20-30% CTR. The more keywords you have the lower your avg. CTR will be. Unless you know what you’re doing, I recommend leaving it at 5%.

Est. Website Conv. Rate – The percent of visitors to your website that become a lead. If you’re currently tracking this, then you’ll be able to add your numbers here. Since we normally focus on MOFU (buyer intent) keywords for this sheet, the default is 20%. Or “1 in 5 consumers who come to your website after searching for your services will become a lead.”

Lead Value – This is how much a lead is worth to you.

Est. Monthly Traffic – Total search volume multiplied by the CTR.

Est. Monthly Leads – Monthly traffic multiplied by conversion rate.

Est. Monthly Value – Monthly leads multiplied by lead value.

SERP Features

The last sheet will tell you which SERP (Google results) features Ahrefs found for that keyword. This includes things like featured snippets, and YouTube videos.

The other columns will show an “X” if that particular feature appeared.

This is a great way to expand your visibility through what Rand Fishkin calls “On SERP” SEO.

Want to know what keywords you should try and rank your YouTube videos on Google for? Here you go!

Which of your keywords have featured snippets? No reason to manually check hundreds or thousands of terms. 😄

Take Action!

This process allows you to spy on what’s already working for your competitors, while automating the keyword mapping process and evaluating the estimated value of your keywords.

Download the spreadsheet (link above) and get to work!

Compared to doing this manually, you’ll save hours of work.