Posted on March 23, 2011 by Jordan Knox
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.
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.
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.
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.
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.
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.
Related Posts
- Keyword Analysis by Number of Terms (and the RegEx that helps)
- Who loves GA Secondary Dimensions and Pivot Functions? I do, And so will you!
- Slash Pay-Per-Click Costs Using Negative Keywords
- The 30,000 Foot View and Success Metrics
- The Great Anchor Text Debate
by Jordan Knox | Categories: Search Engine Optimization
Leave a Reply
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?
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.
-Jordan