Keyword research isn’t just about search volume. Knowing which terms you should give priority when optimizing can help save you time and creative juice writing good content. But competitive analysis can be a daunting task, even intimidating for the non-savvy.
Quite the irony, I went to close my Google AdWords keyword tool window even before starting to search for the right keywords I could use for this post. You might not want to don’t do the same. I was just in a hurry.
The Original SERPs Analysis Tool
Way back in May 2011, Tom Anthony wrote a post on SEOmoz (now Moz) about quick SERPs competitive analysis using Google Docs. It was a robust yet simple-to-use tool. If you’re looking into serious competitive analysis and don’t like to use or install Excel on your machine, I recommend using his spreadsheet instead of mine.
There was this problem I had though with using the ImportXML function which powers all the SERPs scraping work. Google Docs sets request limits for it – a maximum of 50 requests per spreadsheet document – if I’m right. Often times I get errors on my end.
I understand why they had to set limits – to prevent abuse. This was probably the same reason why Tom had set a maximum of 50 keywords to be used.
I also found Google Spreadsheets to be a bit slow on updating computed values whenever changes on referenced cells are made. Not everyone has decent internet connectivity speed including me which makes cloud computing a bit painstaking than productive.
A Faster but Stripped-down Alternative
These issues led me to come up with an Excel equivalent. I’m not good with writing scripts so I had to stick with mixing up Excel functions, simple macro recording and an indispensable Excel add-in I’ve been using since knowing about its existence.
Setting up the Google Search scraper
Before anything else, for you to be able to use the spreadsheet tool make sure you have the following:
- Microsoft Excel 2007/2010 – macros must be enabled to run;
- Niels Bosma’s SeoTools for Excel Add-in – you can download it here and properly follow the installation procedure (I recommend doing Method B: Permanent Installation);
You may then download the Excel spreadsheet as linked below:
If the download link above doesn’t work or is broken, click here to download via Google Drive instead.
How to Use
I have become a fan of procedure documentation and nothing makes it easier than SweetProcess. It’ll be impractical for anyone to go through this whole blog post repeatedly (or refer someone else here) just to know how to use the spreadsheet tool so I’ve made a SweetProcess procedure linked below:
How It Works
On the Keywords sheet is a column (C) containing Google search query strings assigned to each keyword. These strings also rely on the Query String value set on cell D1.
Making use of the XPathOnURL function of the SeoTools add-in, results on every top 10 positions of the SERPs for every keyword is scraped.
The scraped results are then arranged by SERPs position.
All scraped strings are then cleaned, leaving only the website domain for each cell value.
All unique domain values are then consolidated into a single list and assigned scores according to their SERPs frequency and position. For the score computation, I multiplied the CTR rate of a position with the domain’s frequency on that same position.
With the domain list and computed scores ready, websites are then arranged in descending order by score using a simple recorded macro.
I just came up with the scoring system I used based on Dejan SEO’s 2011 SERP CTR data. I may have misused or misinterpreted their findings. If you wish to use an alternative approach, Stephen Croome suggest just using domain frequency on the SERPs.
What it Lacks
As I mentioned earlier, Tom’s version is better than mine when it comes to analysis. His spreadsheet tool is also able to do everything what my version can. I’m not well-versed with APIs so I’m not yet able to figure out how to properly fetch data from Mozscape which would have made this spreadsheet akin to the Google Docs version.
Like every other tool, there’s always room for improvement. I’m planning to rework the Keywords sheet and add a new column beside the entered keywords that would contain keyword difficulty scores based on how strong are the top X websites competing for each of those term.
Please feel free to dissect the whole Excel workbook and modify it to suit your preference. You can even refer to the following articles for ideas on making possible improvements to it:
- Competitor Analysis and Benchmarking With the New Domain and Page Authorities
- Fun With The SEOmoz API – Get Links Data Straight into Excel
Hope you enjoyed this post and the Excel tool! Don’t hesitate to share your thoughts and know what others have to say in the comments.
Yo! I have a newsletter where I share not only my latest posts, but also interesting and carefully curated finds from all over the web that will help you with your business and/or career on the web. No ads, no spam, unsubscribe anytime. Enter your nickname and email below to get started.
Newsletters — not your thing? No problemo. I’m on also on Twitter where I share mostly about UX, social media, web trends, customer service, workflow automation, and more. Self-improvement stuff and tech updates go on my Facebook page. Google+ is where I post about web design, artsy/cool/random stuff and #caturday! You're already awesome, but together we'd be awesome...er! Let's be friends!