Ben’s Keyword Research Comparison Tool
This week I developed this Google Doc spreadsheet to speed up my keyword research.
You enter the following data:
- The Page Authority and Domain Authority for a page you want to rank (from OpenSiteExplorer)
- The total monthly searches (phrase-matched, from Google AdWords Keyword Tool)
- And the stats on the top 10 competitors (which you can get from OpenSiteExplorer, but 50 at a time from SEOMoz Pro)
The spreadsheet then shows you which target SERP positions are the most attractive, i.e. will give you the most traffic for the least work.
Try it Yourself
All open in new windows.
- OpenSiteExplorer.org
- Google AdWords Keyword Tool
- SEOMoz Pro (my affiliate link / regular link)
- My latest Master Google Spreadsheet (updated November 9th 2011) - You must be logged in to Google in order to make a copy of this (Do “File > Make a Copy”)
Update (30 September): I’m continuing to adapt this tool. I’ve used it today to do a complete keyword strategy for one client, a Real Estate Appraiser, with a couple of slight tweaks. I’ll update this post.
25 Comments Leave a comment
Leave a comment
Hi Ben… for some reason, when I go to paste the data I copied from the Keyword Difficulty Report in my SEOmoz Pro account (for the term “digital dictation”, when I click on the rectangle immediately beneath “PA” on the Google Docs spreadsheet – instead of populating laterally and vertically (downwards) like yours does in your video, it only populates vertically downwards (within the column I pasted into).
So, instead of having all that great data populate all the relevant spaces on the spreadsheet, it only populates the vertical colum under PA!
I’ve tried everything to make it work just like you do in your video, but it won’t work for me
What the hell am I doing wrong? Or is their a glitch in the tool that causes this sometimes? I have tried Google Chrome, Firefox and IE9, but to no avail. I cannot get beyond this step in the process
Hi Bruce. Sorry, I don’t know what may be going wrong. It works fine for me in Firefox and Chrome.
Thanks for replying, Ben. The only thing I DON’T quite understand is… “how does clicking on one single cell immediately beneath “PA” (and then pasting the data from the Keyword Difficulty stats) into it, supposedly manage to populate the three columns to the right of it as well? (as per your video tutorial). All that ever happens for me is that the ‘pasted data’ populates down the column I pasted in – nowhere else.
Bruce, yes, someone else found the same effect. That’s how it works for me in Chrome and in Firefox.
Good stuff Ben! We’ve been tweaking our forecast spreadsheets for a while now to try and incorporate some more metrics and make things a little less dependent on estimates.
From a linkbuilder’s perspective, I wonder if it could be improved by using the metrics to generate some kind of ‘quality score’ for each of the websites in the top ten and for your own website. This could be a distinct metric, different from the overall score in the green column… then perhaps you could tell at a glance which of your rivals in the top ten you’re likely to be able to beat. That way it would give an impression of the likelihood of you being able to get to a certain position in the rankings, as well as the profitability of getting to that position. (I hope I’ve explained that alright!)
Anyway, it looks really useful and I look forward to giving it a try.
Cheers,
Ed
Hi Ed. The SEOMoz Page Authority and Domain Authority are pretty good indicators of link value as well as “rankability”. I hope it works for you.
Thanks for the tool, seems useful and timesaving. But I can’t understand how the score figure works.
In your video example why is it easier for you to beat someone at position 2 than someone at position 5 which looks to be the hardest to beat?
To get to position 2 your still have to beat 10, 9, 8, etc. to get there. It’s not like you can just target someones site and swap position with them.
Hi Peter.
When you see sites lower down the rankings that look like they should be stronger, it will usually be because they are not very well optimised around the term.
So you can often get pages with lower Domain / Page Authority higher up the rankings, because they have keywords in the domain, or better on-page optimisation.
The tool is only a guide, but it’s the best one I’ve come across so far. You should still examine the other SEO factors for any candidate target competitor.
The “score” doesn’t tell you how easy it is to beat a competitor. It indicates the likely return on your effort. So it takes into account the traffic you’re likely to get at each position, combined with the difficulty of beating the competitor who’s at that position today.
- Ben
Hi Ben, thanks for the quick reply.
OK, so the score is more a way to spot low authority sites that rank well because of great on page SEO.
In a way. There are lots of factors, like domain age, keywords in domain name, on-page SEO, or just plain relevance to the search.
The higher the score, the better the likely return for your effort is likely to be. You’ll have to match a target’s overall strength though, which may include making your landing page more specific (domain name, on-page factors, internal and external link text) as well as matching it for Page Authority (mainly driven by high-quality inbound links).
Great it makes more sense now.
Thanks a lot
Hi Ben, my adwords keyword tool used to look like the one you used in your tutorial, i.e. with category listing on the left hand side. I found the use of categories very useful. Now I get the one with no category listing. Also, I’ve noticed many other tutorials with recent dates that have the same view that you get. Now I can only get the category view within the placement tool.
Have you any suggestions please?
Hi Mike. They have just updated the UI. I don’t tend to use categories, so I can’t advise on that.
Hi Ben. Thanks for the quick reply. Because you are an experienced web designer I trust that you have heard of concept of Vertical Market Analysis (VMA)? This describes a parent / child relationship of keywords. I was using the categories to give me a rough idea of VMA.
Have you any alternative ideas?
Many regards
Mike
Hi Mike. No, that’s not a term I’ve ever heard in use.
Note – I’ve updated the “Score” calculation, so it gives a clearer indication of ROI.
Not sure if i’m doing something wrong, but i follow along with your tutorial but the #’s on the page rank column never change. (problem with spreadsheet?).
It may be. I can’t make the Google Doc read-only, because if I do I don’t think you can copy it. I’ve made some updates recently, so will share the new version soon.
Actually, I think that – as long as you’re logged in to Google Docs – you can now do File > Make a Copy. So I have made the doc read-only.
i have a question ? i found a niche that has an average page rank of 2.8 and the the listing for google number one has 24 back links… the # 2 spot comes from the same website but it doesnt contain the keyword .. to get number one on this keyword im using … should i try the niche because i can beat the #1 page… but the 2nd and like 5th page have over 1000 back links
Hi Kenneth.
Look at the Page Authority and Domain Authority using OpenSiteExplorer or SEOmoz Pro – for all the top 10.
If those numbers are far ahead of yours, and the pages are optimised, you’re unlikely to be able to catch them. Look for a term for which you can get into the top 10 (then perhaps sub-terms with more traffic for which you can get into the top 10 later).
I am confused between the tool shown in the video and what is on google docs. Can you briefly explain the NOW, Plus 5, Plus 10, Plus 15, Plus 20?
Hi Michael.
I have updated the Google Doc a few times. In fact, I have a much more advanced version now, which I’m going to explain in a new video series (“SEO from Scratch”) which I’m planning to launch next week.
The “Now” column indicates the situation now, with your page’s current strength (PA & DA). Numbers in that column indicate potential “low-hanging fruit”. The +5/10… columns are projections, for when you build more links to your page over time. They tell you what traffic may become available in the future, when both your PA and DA go up by 5 points, 10 points, etc.
(Of course, this comparison is only “on paper” – it doesn’t take into account the context of any particular search phrase.)
Hi Ben
How does this tool compare to something simple like using Market Samurai to generate some terms, get the SEOT (Phrase), Title Comp, the ratio of SEOT/TC then pick strategic and tactical targets?
Tim
Hi Tim. This tool is far more useful than Market Samurai, Wordtracker, or any other keyword research tool I’ve seen.
It’s all explained in “SEO From Scratch”, but briefly, data like SEOTC or KEI only give an indication of the competitiveness of a market for a keyword. They cannot tell you what really matters, which is: How much would you have to do to get in the top 10?