SERPs Competitor Scraper – Quick Raw Analysis Using Excel

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.

ImportXML function limit on Google Docs" alt="ImportXML function limit on Google Docs

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.

Setup

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:

Download SERPs Competitor Scraper (~350 KB)

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:

Procedure: Using the SERPs Competitor Scraper

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.

Google search query for each keyword

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.

Search results are scraped

The scraped results are then arranged by SERPs position.

Scraped results are grouped by SERPs position

All scraped strings are then cleaned, leaving only the website domain for each cell value.

URLs are cleaned

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.

Unique URLs are consolidated and scored according to SERPs position

With the domain list and computed scores ready, websites are then arranged in descending order by score using a simple recorded macro.

URLs are listed and arranged in descending order by score

Scoring

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.

Possibilities

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:

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 and Facebook where I share mostly about user experience, social media, web marketing, CRO, customer support, workflows, productivity hacks, and more. Google+ is where I post about web design, artsy stuff, cool/funny/weird things/news/updates and #caturday! You're already awesome, but together we'd be awesome...er! Let's be friends!

Need a WordPress website?

Whether you need a blog or website of your own with the domain of your choice (for example: yourname.com), or you're a local business looking to boost your online presence and make it easy for your potential customers to find you — I can help you get set up with one this weekend. Click here to contact me.

Not familiar with WordPress? No worries. Click here to learn more about it and why it's the choice for millions of websites, worldwide.

9 Comments

  1. Hey Bibiano,

    Cant wait to use it. Getiting this error: run time error “1004”
    unable to set the formulaArray property of the range class

    One thing that I was not able to notice on the during the set up process was that I was not to see the ‘seotools’ in the add-ins section. Please advice thnx

    1. Hi Herman! Glad to connect.

      Before you can use the spreadsheet tool, you’ll have to configure your excel installation first by allowing macros to run as well as installing the SeoTools plugin properly http://nielsbosma.se/projects/seotools/download/.

      Once you have those requirements covered, you should be good to go. Could you also post a link to the screenshot of the error and also tell me when does it appear (e.g. when keywords are pasted, when list is generated, etc.)?

      Would be happy to help! :)

    1. Hmmm. I am not able to access the doc you’ve shared (maybe you can set it to public or anyone with the link). Could you please send it to me@bibianowenceslao.com instead? Would be happy to help.

      Also, what version of Excel are you using? Maybe I can save another copy for backwards compatibility with older versions (I’m using 2010).

  2. Another way of performing Seo on where you are going to start. Through this kind of technique, you can directly target those sites where you are going to link on as it is the site where your competitor drove bakclinks from it. In that idea, you can easily eliminate your competitor in a legal way.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>