Faced with a new client, and having established a list of keywords they need to target, you want to evaluate the competition to find out what sites are dominating the SERPs for these keywords. However… being an SEO you’re a busy guy (or gal), and you need it done right now. I’ve built a Google Docs tool to automagically do exactly that and this post will walk you through it.
The basis for this tool comes from a report in this linkbuilding post on YOUmoz which contained a neat little ‘SERP Saturation’ report. I don’t know how Stephen made his snazzy looking report (he’s now shared a few details in this comment), but in response to a few people asking about his I thought I’d put together a tool. Here is Stephen’s report:
Cool, eh? We are going to produce something very similar, albeit not as pretty. We will automatically pull ranking data and tie into the Linkscape API to pull in some helpful metrics.
1. What does the report show?
So, what’s the report all about? It is a pretty standard report, and most SEOs will have put together similar reports in their time. It shows which domains are dominating the results pages for the specified list of keywords. It is an excellent way to quickly see who the main players are, and see a few metrics for them.
Ours will be sorted by the cumulative number of times a subdomain has appeared in the top 10 of the search results over all the keywords we specify, and will display the mozRank, Domain Authority and Linking Root Domains for each. We’ll show just the top 10 competitors in our report.
You can just duplicate the Google Docs spreadsheet I provide below, and change almost any of this to add, modify or take away as per your needs.
2. How do you configure it?
You must configure it the first time you use it:
1) If you’ve not yet done so, get a SEOmoz API key. Its free!
2) Open the Google Docs spreadsheet. In File menu select ‘Make a copy’ so you have a version you can edit (call it “Report Template” or such).
3) Go to the ‘Config’ sheet at the bottom, and enter your SEOmoz API details.
4) If you’d like to change the template for which Google URL to do (it defaults to UK for me), you can do that here too.
3. How do you use it?
Open your report template spreadsheet you just made.
1) On the config tab, paste up to 50 keywords, one per row, starting at cell B7 (its indicated).
2) Open the ‘Report’ sheet.
3) Now select ‘Make a copy’ and give it a name (“Client X Report” or whatever). This step is *essential* or the fields will not update properly (I’m working on making this not necessary – any clues?).
4. What should you see?
You should see a snazzy little report:
It shows everything I promised, and more even:
A colourful and interactive, albeit it slightly wonky, graph! What more could you want?!
5. Under the hood
You don’t need to read this section if you are neither interested in how it works or need to edit it at all. Besides which, I’m mostly just going to refer you elsewhere! A big shout out to Tom Critchlow, whose prior work contributed heavily to this little tool. Firstly, you need to read:
How To Build Agile SEO Tools Using Google Spreadsheets
Which introduces how to scrape the SERPs for ranking data. I modified what Tom did slightly as I wanted a list of subdomains, rather than pages, so there is a bit of string cropping (and fudging!).
Next you need to read Ian Lurie’s post (which Tom also helped with):
Linkscape + Google Spreadsheets. Together, at last.
Again, this I also edited. I changed the code around quite a bit, which you can see in the script editor. You end up with a function you can enter into a cell:
=getLinkscapeData(A1, 1)
The A1 is a cell reference to a URL, and the 1 is a dummy parameter to prevent annoying caching issues.
For a look at the full code for the Linkscape API interface, and some pointers on how to modify it to suit your needs I’ve put up a separate post on Using the Linkscape API with Google Docs, which includes a simpler example spreadsheet to try the code out with.
The rest of the spreadsheet is a few simple bits to filter and cumulate the necessary bits and pieces, along with a few tricks to try to sidestep some bugs in Google Apps. Nothing in the sheet is protected (there are a fed hidden columns) so you can take a look at the workings. If you have specific questions, post them in the comments and I’ll try my best to answer.
This was my first real foray into Google Docs, so it might not be particularly elegant. Also the document seems to have trouble updating sometimes – if anyone has a solution that would be great. In the meantime, if you just ‘Make a copy’ it seems to force an update.
6. Wrap up
Ok, it isn’t in depth analysis, but if you have a keyword list, and want a very quick peek at what domains are players, and their general stats, this tool gives you a quick and dirty look. Most importantly – it is free and open, so you can tweak it to your hearts content.
Questions, comments or suggestions are very welcome – post below and I’ll get back to you.
