Friday, June 7, 2013 — Spreadsheets

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.

This was supposed to be a followup or a supplementary piece to the do-it-yourself keyword research guide I’ve recently started drafting for my DIY Online Marketing – From Zero to Something series which you might want to subscribe to.

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:

If you’d like to get updated on the changes to this tool as soon as I publish them, you can subscribe to my DIY Online Marketing series. I barely write, but when I do I make it a point to make the piece worth reading (despite my limited English vocabulary – I get stressed when I write *lol*).

Hope you enjoyed this post and the Excel tool! You can subscribe to my feed. I’m also on Twitter and if you feel like asking something. I also secretly want to be your friend.



Want to receive updates like these weekly? Click here to know more.

  • Modesto Siotos

    Great work Bibiano. Thanks for sharing!

    • http://bibiano.io/ Bibiano Wenceslao

      Thanks for sharing this with others, good sir! Glad to connect.

  • http://www.HireYourVirtualAssistant.com Owen McGab Enaohwo

    Thanks @bibianowenceslao:disqus for giving a shout out to SweetProcess. (<<<— You ROCK b***s!)

    • http://bibiano.io/ Bibiano Wenceslao

      Nothing sweeter than a well-documented process! :)

  • Herman Rosa

    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

    • http://bibiano.io/ Bibiano Wenceslao

      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! :)

  • Herman Rosa
    • http://bibiano.io/ Bibiano Wenceslao

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

  • Spook SEO

    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.

About

Hey! Bibiano here. I write about a range of stuff - user experience, social media, web marketing, CRO, customer support, workflows, productivity hacks and life (mostly) - and this blog is a repository of my thoughts on all those.

I'm on also on Twitter and Facebook where I share mostly about the abovementioned topics. Google+ is where I post about web design, artsy stuff, cool/funny/weird things/news/updates and #caturday!

Looking for my work-related posts? Clicky here.

Subscribe

I have a newsletter, but I'm not just hurling everything I write into your inbox (yah, no personal stuff). I pick the good ones as soon as they're published (rants and scrawls stay here), plus relevant tips and materials I got from outside sources.

No ads, no spam. Enter your nickname and email below to get started.