By Cory Collins
16 Jun 2015

Keyword Research Quick Wins with SEMrush – Tutorial Tuesday

Advanced SEO     Keyword Research

Note: the process covered in this week’s tutorial will require a paid subscription to SEMrush, which I highly recommend.

Hello and welcome to another Tutorial Tuesday, where we break down a digital marketing tool, strategy, tactic, process, etc. each and every week.

Tutorial Tuesday Banner

This week I’ll be covering how to effectively and efficiently analyze a domain’s ranking keywords with SEMrush. Specifically, I’ll highlight how to find opportune keywords. Keywords that are right in the sweet spot:

  1. High-traffic keywords the domain is already ranking for. BUT:
  2. The domain isn’t quite ranking on page one – garnering low, if any CTR.

What does this mean? It means Google has already decided your page is a good result for this high-traffic keyword. Unfortunately, there are a small handful of other webpages that are better. The difference is often intentional optimization. Look at title tag, header tags, meta description, URL, and keyword usage throughout the content.

The only way to really determine opportunity is analysis. You’ll have to look at the pages ranking on page one, determine why your page specifically is ranking (compared to every other page on your site), and whether or not your page really deserves to be ranking on the first page.

If you decide:

  1. Your page is a good match for the keyword
  2. The page has opportunity to assist or direct conversions
  3. It’s feasible and possible to improve the page’s optimization for that keyword.

Then you’ve successfully found a high-opportunity page to directly impact site revenue. Congratulations!

keyword opportunities

Depending on circumstance, minor tweaks can go a long way to improve both ranking and optimization, especially if the page was poorly optimized to start. It’s incredible to me as an SEO how often I can find sites that are unintentionally close to ranking for important keywords.

Of course, it might take considerable more work to rank for a high-traffic phrase. Nevertheless, knowing an opportunity exists puts you in a stronger position than you were previously. Ignorance is rarely bliss in marketing.

SEMrush makes the process of finding these “near miss keyword opportunities” incredibly easy.

Let’s jump into the actual process of finding high-opportunity keywords with SEMrush.

The Example Site: NRS

First for the purpose of this tutorial I need to determine which site to use as an example. I want a site rife with opportunity, healthy competition, a large market, and an established presence.

For this example I’ll use, an Idaho-based rafting gear company. NRS stands for “Northwest River Supply”.

NRS is a manufacturer as well as a competitive e-commerce website, with an established brand, dealing in the Northwest but also the international market.

They have a strong company culture as well as my admiration as a company. My Dad has been known to send me links to their community articles, dubbed the Duct Tape Diaries. Should be a fun example.

Step One: Entering the Domain into SEMrush

Head into SEMrush.

Navigate to SEMrush’s dashboard:

SEMrush dashboard

Where you’ll put your domain into the top bar.

Step Two: Navigate to Organic Research

Since we’re primarily concerned with finding opportunities within organic search, we should delve into the organic research tab with SEMrush’s dashboard.

Click on “Positions” in the left side nav:

organic positions SEMrush

This will take you into SEMrush’s organic position overview for the selected domain.

The primary feature we're interested in here is the organic keywords traffic graph, located at the bottom. This graph will show what keywords, specifically, are sending NRS the most organic traffic.

Step Three: Quick Keyword Analysis by Traffic and Ranking

As long as we’re in the dashboard, we ought to glance at the organic keyword traffic graph.

SEMrush organic keyword traffic graph

That’s what it looks like for NRS.

Let’s highlight the obvious opportunities:

SEMrush organic keyword traffic graph opportunities

This graph is sorted by the amount of traffic the SERP is sending to NRS. Let's look for any page not ranking within the top 10 results, which is generally page one in search. This is represented under the “Pos” column, short for position.

We can quickly see that there are a variety of phrases already sending significant traffic to NRS, but not ranking on page one. These phrases include:

  • Pfd (likely short for “personal flotation device”)
  • Oars
  • Kayak accessories
  • Kayak air
  • 303 aerospace protectant.

5 opportunities in about ~30 seconds. Not bad, eh?

Of course more analysis would need to be done here to really suss out the quality of the opportunity. How feasible is it, really? Here's what we'd need to do to qualify the opportunity:

  • Analysis of the sites ranking page one for the given keyword
  • Analysis of the SERP landscape:
    • Video results?
    • Knowledge graph results?
    • Ads?
    • Rich snippets?
  • Analysis of NRS’s page that is ranking:
    • Is it a good fit?
    • Should it outrank the other results?
    • How optimized is it?
    • Would minor fixes improve rankings, or would substantial work be needed?
    • Etc.

That would be the true analysis - here we're just looking for potential opportunities. Let’s dig a bit deeper by exporting the data into Excel.

Step Four: Downloading SEMrush Organic Keyword Data into Excel

At the top of SEMrush’s organic keyword traffic chart is an export button. We’re going to click that and choose to export the data into an Excel file:

export SEMrush organic keyword data

Make sure to choose Excel:

Export to excel SEMrush

And open your Excel file when it’s complete.

Step Five: Formatting Organic Keywords in Excel

You should now have an Excel file that looks like this:

SEMrush keyword data in excel

Now comes the fun part – formatting our data in Excel.

This can be taken to a variety of levels depending upon your general Excel wizardry. For the sake of this post, let’s assume you know very little about Excel. Truly, you don't need to be a wizard to format Excel into something visually pleasing. No formulas required.

I’m going to demonstrate a dead simple way to format the data in order to highlight outstanding opportunities in your keyword data.

First, let’s bold the top row and add color so it stands out:

bold and color top row Excel

Then we’ll want to freeze the top row so we can keep our data headers as we scroll through.

Freeze top row Excel

Next we want to delete unnecessary data, which will make it easier to find opportunities. Delete column H, K, and L. Simply right click the column you wish to delete and choose “delete”:

Delete column

This will leave you with a spreadsheet that looks like this:

Excel with columns deleted

A bit better, but still pretty overwhelming.

Let’s hide the information we don’t absolutely need, but which might be helpful when we’ve established opportunities and need more information for analysis. Let’s hide columns C, E, G, H, and I.

The only information we want to see is Keyword, Position, Search Volume, and URL.

Right click on the column you want to hide and click “hide”:

Hide column in excel

This should leave you with an Excel spreadsheet that looks like this:

Excel columns deleted and hidden

Not bad, not bad. Certainly less information to overwhelm. We can do better though.

Let’s flip over to the data tab and filter by search volume. After all, we want the keywords with the most opportunity, correct? Click the "Filter" button and then choose "Sort Largest to Smallest":

Filter largest to smalles Excel

If you’ve only highlighted column D—Search Volume—make sure you choose the menu option “Expand the selection”. This will keep your data grouped together appropriately rather than just sorting the data in column D.

Excel filtering by search volume

This will leave you with your keywords sorted by search volume.

Last but not least, let’s do some conditional formatting on column B and D—position and search volume—to better highlight opportunity. It will be somewhat redundant on search volume since we’ve already sorted by that, but the visual aid might still be helpful.

Click the column you wish to conditionally format and then choose “Conditional Formatting”, choose “Color Scales”, and let’s choose green to red:
Conditional formatting in Excel

The column should now look like this:

contional formatting applied in Excel

Now let’s do the exact same thing for column B, position (ranking). This is where the magic really happens.

This is what you should see:

Excel fully formatted

Anything green within column B (position) will mean page two or beyond ranking, signaling opportunity. If it matches with green in column D (search volume) that means you have a great opportunity for improved traffic, by improving the ranking.

Conveniently filtered by search volume, the further you scroll down the sheet the less traffic opportunity you’ll find.

Summary: Formatted SEMrush Data in Excel to Find Traffic Opportunities

At this point you should have an easy-to-use Excel worksheet which visually highlights keywords that are already driving traffic, but could drive more with a bit of ranking improvement.

This is admittedly the first step in keyword analysis. You’ve found opportunity. To take this further, you’ll need to:

  1. Analyze the page receiving traffic for the keyword:
    1. Does it match intent?
    2. Is it a good match?
    3. Has it been properly optimized?
  2. Analyze the page one results for the keyword:
    1. Are they a better match than your page?
    2. What sorts of results are returned?
      1. Informational?
      2. Video?
      3. Knowledge graph?
    3. What appears to be the intent?
  3. Analyze opportunity:
    1. Are there easy optimization steps to take? Title, h1, on-page?
    2. Internal linking?
    3. Duplication issues?
    4. Etc.

On a site as large as NRS there will be issues with keyword cannibalization, page duplication, URL structure, internal linking, poorly optimized headers, title tags, alt tags, etc.

This tutorial will help you find SEO opportunities with SEMrush. What comes next is the real work – optimizing for search.


Cory Collins

Cory Collins is the Business Development Manager at Page One Power and has been with the agency since 2012. Cory is an SEO strategist, writer, runner, and outdoor enthusiast residing in Boise, Idaho, with his wife, daughter, and (too) many pets.