The Keyword Detox: How to Refocus Keyword Research to look for Gaps and Opportunities


Several days ago, I was asked to review a list of keywords for a site and created a nice little layout to provide gaps and opportunities for established keywords. Everyone thought it was nifty, here it is so that you can benefit, too.
Beet Header for Keyword Gaps and Opportunities

Our Problem: Bob’s Beets.

Let’s say you’ll be reviewing a site about beets. Bob, the owner of Bob’s Beets, has a pretty nice layout of keywords. He had a previous SEO campaign done, but it’s been awhile and he really wants to make sure his website is doing as well as it could. Pretty cut and dry. Before you run off and get excited across the interwebs finding new keywords to throw at Bob and his beet goodness, you should fire up Excel. As you can see I like to open up with some basic things such as URL, Keywords, both national and local ranking, keyword difficulty, and exact match search volume.

First Image for Keyword Gaps and Opportunities

Make Color Coding your Friend

I need everyone to know first that I love color coding things (see the start above). I find it easier if you can quickly scroll through huge Excel sheets and find highlighted problems with ease. You can go with any color you decide. I went with a manly pastel blue. For the sake of me writing less we are going to assume that Bob kept around a handy dandy list of previous targeted keywords, title, and Meta descriptions. Bob is a really awesome client.

Break up the Excel file by URL and subfolder

So we’ll break up each page and their subsections, in order, down through the keyword list. I like to break up each section with a color line for easier viewing. I include a short description of the page followed by the title for a header. This helps as a nice reference when looking at the ranking for the keywords selected for each specific page. Below is a sampling of the initial process to give you a general idea.

Second Image for Keyword Gaps and Opportunties

Think about difficulty and volume before rank

Once I’ve filled the fields to cover your desired area of the site, I tend to skip over ranking into grabbing data for the difficulty and search volume for each keyword. Why did I decide to jump over ranking? I find it much easier to look at the competitive field for specific keywords coupled with search volume, which will help to highlight specific keywords out of each section. I focus on the keywords that
1. I feel the site could compete for based on difficulty and
2. Have enough traffic to be worth working on.

Tools to help you find these gaps

There are several ways you can aggregate data to come up with a difficulty model for each keyword, I highly suggest trying out SEOmoz Pro Tools specifically for their keyword difficulty tool. If you’re not ready to commit to a paid tool set, I still suggest grabbing SEOmoz’s free SEO Toolbar which calculates a read out of each sites Page Authority and Domain Authority. If you wish to go the toolbar route it will be much more time intensive (albeit free), as you will have to physically visit each of the top 10 ranking sites for each keyword. Record each Page Authority and Domain Authority, add the numbers up and divide by two and multiply by .10 to receive a baseline difficulty score. You can use a number of tools for volume search such as WordTracker; pick one you’re most comfortable with.

Third Image detailing Keyword Gaps and Opportunties

As you can see from the above graph above, I’ve been able to find a sampling from my, totally made up, results. I use purple to distinguish a potential opportunity but with a higher competition for ranking; orange denotes a perfect blend of medium competition and high enough search traffic. The easiest way to find these opportunities is to use the sort feature for the volume row and cross reference that with the difficulty row. From there I check to see where each keyword ranks. If you’re pressed for time you could focus only on the highlighted words, but I suggest filling everything out to provide a fuller picture of the site’s performance.

Fourth Image for Keyword Gaps and Opportuntites

Looking at the finished example sheet there are several areas Bob’s keywords are severely behind in the SERP’s. Ouch. Still, by providing this data, we can really dig into these areas and go over what is wrong to correct these problems. Bob could definitely do better for beet cake, beet lover, and beet juice. Personally, I would focus on the faltering areas in the orange and think about more intensive strategies for trying to enter a purple zone. I realize that organizing a sheet like this may be inconvenient for some, but this provides a quick numerical guide of how your site is performing in relation to your targeted keywords, which I think is invaluable. Let me know if you have a different way or suggestions for the improvement of my methods in the comments below.

Jordan is a former LunaMetrician and contributor to our blog.

  • Winooski

    Hey Jordan, thanks for the description and spreadsheet setup!

    Here’s a dumb question: When you write about distinguishing “Rank (National)” from “Rank (Local)”, what method are you using to arrive at the “Rank (Local)”? Does that amount to, e.g., logging into Google, then performing the searches to see how the results work for your current location?

    • Jordan Knox

      Hey Winooski,

      Thanks for the comment! No question is dumb. You are correct that I manually search through Google for the local terms. I suggest using the &pws=0 at the end of the search URL if you are logged into Google. It may be slightly more time intensive to manually search, but I feel the data you collect is much more robust.


  • thanks for description.

  • Hello Winooski

    Thanks for sharing, i will check this product.

  • Way cool! Some extremely valid points! I appreciate you
    penning ths post plus the rest off the site is very

Contact Us.

Follow Us



We'll get back to you
in ONE business day.
Our Locations
THE FOUNDRY [map] LunaMetrics

24 S. 18th Street
Suite 100

Pittsburgh, PA 15203


4115 N. Ravenswood
Suite 101
Chicago, IL 60613


2100 Manchester Rd.
Building C, Suite 1750
Wheaton, IL 60187