SERPs Competitor Scraper Tool – 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.

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:

Download SERPs Competitor Scraper Tool (~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 Tool

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.

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.

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


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:

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.

Published by

Bibiano Wenceslao

Father of one. Works from home. Interested about UX, social media, web trends, customer service, workflow automation, technology and life.

11 thoughts on “SERPs Competitor Scraper Tool – Quick Raw Analysis Using Excel”

  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

      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 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.

  3. Great work Bibiano!!
    I’m trying to download “serps competitor scrapers tool” but I’m getting this error on Dropbox: Error (429)
    This account’s public links are generating too much traffic and have been temporarily disabled!

    Do you have another link where to download that file?
    Thank you very much!!


    1. Hey Geno – Appreciate the feedback and the heads-up! My public Dropbox links have been mostly useless at the moment (temporarily disabled for sending too much traffic) so I’ve just uploaded it on Google Drive as an alternative download link. It’s added on the post above, and you can also download it here. Glad to help!

Leave a Reply