How to Perform SEO Competitor Research with Excel

by Yoav Ezer July 21st, 2010 

Any SEO will tell you that the hard part of the job is not just working on your own efforts but also keeping an eye on what your competitors are up to. With that in mind, here is a way you can do routine competitor research for your SEO using only a Microsoft Excel spreadsheet!

What our solution does is goes to Google and returns the top results for a search query. Why is this important?

  • This is useful if you want to know who your competitors are, and how much better than you they are doing in the search results.
  • It's also good for monitoring your SEO success over time, and the success of other players in your playfield.
  • Lastly, this can be useful to identify newcomers, so you are never caught by surprise when a new player enters your playfield.

How to use it

  1. Download the file competitor-research.xls and open it.
  2. Enter your website's URL in cell A2
  3. Enter the various search terms you compete in, in column B
  4. Press Ctrl-Shift-U

The script will scan all the Google result pages for the terms you specified, and will collect all the results that aren't your site.

Competitor Research Tool Spreadsheet

It will also count the total times each competitor site appears above your site, and below your site.

The result list is ordered by competitor strength, the stronger first.

How it works

The main routine, CompetitorResearch, starts by retrieving our website URL and the search terms, and goes on to process the terms one by one using the utility subroutine ProcessTerm.

'' Retrieve my url
Dim myurl As String
myurl = sheet.Cells(2, 1).Text

'' Work through the list of search terms and process each one
row = 2
While (sheet.Cells(row, 2).Text  "")
    Dim term As String
    term = sheet.Cells(row, 2).Text
    ProcessTerm term, myurl
    row = row + 1
Wend

After all the data is computed, it is also sorted by the "Above me" column and then by "Below me". This way, the site that appears above me in most search term results, is considered the strongest competitor.

The other important subroutine, ProcessTerm, scans search result pages for a specific term:

For page = 1 To PagesToScan
    url = BuildSERPURL(term, start)
    contents = FetchPage(url)

The result pages are searched for organic result links, identified by the string <h3 class="r"><a href="…">

If a result link is a link to my URL, then a flag is raised to indicate that any further links are below me, not above.

If InStr(url, myurl) > 0 Then
    '' This my URL. Everything from here on is below me
    foundMyUrl = True

For all other links, the host name is extracted from the URL. For the sake of this tool, the host name is what differentiates between sites. If this is not the case in your niche (for example if there are plenty of competitors using various subdomains), then you may want to change this code.

hostname = GetHostname(url)

Then the existing list of competitors is scanned to see if (and in which row) this host name already exists. This is done with the Match formula function, which might fail with an error if the host name does not already exist.

row = -1
'' Locate this competitor URL in the existing list
On Error Resume Next
row = Application.WorksheetFunction.Match(hostname, sheet.Columns(3), 0)
On Error GoTo 0

Then, if this competitor site does not already exist in the list, it is added into a new row.

In any case this appearance in the results is counted as either "Before me" or "After me" and the code moves on to the next result

'' Count this appearance either below or above me
If foundMyUrl Then
    sheet.Cells(row, 5).Value = sheet.Cells(row, 5).Value + 1
Else
    sheet.Cells(row, 4).Value = sheet.Cells(row, 4).Value + 1
End If

Configuring the Macro

There are two easily configurable parameters at the top of the script:

  1. GOOGLE_WEBSERVER is the host name for the web server you wish to query. As with our previous tool, this allows you to research your competitors in a region-specific Google data center and server, like www.google.co.uk for the UK.
  2. PagesToScan is the amount of default-sized result pages to scan. This may be useful if you don't currently rank on the first result page, or if you want to research your weaker competition.

Summary

Knowing who to beat and analyzing how they got there can be a great way to improve your own SEO performance. This spreadsheet is a free and simple way to keep a track of your competitors over time. Is this something you might use for your own SEO efforts?

P.S.

I've updated the file so it now works on Office versions 2000 and up.

More SEO Spreadsheets

Yoav Ezer

Yoav co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to XLS conversion software. For more Excel tips, join him on Facebook or Twitter

Codswallop blog

You May Also Like

34 Responses to “How to Perform SEO Competitor Research with Excel”

  1. Tom says:

    Is it me or does the link to the XLS file not work? I'd love to see how this works…

  2. Nope – it's not just you. It would be nice to see how it works.
    .-= Marjory Meechan recently posted: Google Me or Not =-.

  3. DazzlinDonna says:

    Yoav, the spreadsheet link leads to a 404. I'd love to be able to download it as this looks excellent. Please fix. :)
    .-= DazzlinDonna recently posted: How To Quickly Assess The Competition In Any Niche =-.

    • Yoav Ezer says:

      Donna, it's fixed now, so try it again.
      And please let me know what you think about it :)

  4. BruB says:

    Sounds like a great tool, would love to try it, but like the comments before, Can't access the xls file.
    .-= BruB recently posted: Oui je le veux …mon mariage Hollywoodien =-.

  5. Yep looks like that link is DOA. Leads back to the page root. Too bad though, looks like a very handy application.

  6. Tom says:

    Working fine now, thanks Yoav!

    …also thanks for the well-commented code :-)

  7. Wow, seems like a great tool to check up on your competitors. Thanks for sharing!

  8. [...] How to Perform SEO Competitor Research with Excel, Search Engine People [...]

  9. Nitin says:

    I go through your given process but not showing any data can you please give the right process how does it work

    • Yoav Ezer says:

      Nitin, you may have to enable macros when you open the file. The procedure for enabling macros is different for each version of Excel. What version do you use?

  10. that is very nice idea,and coding is also help for us as an examplwe.thanx for that

  11. Andy Symonds says:

    This looks interesting but upon hitting Ctrl Shift U gives me the followingg error:

    Compile error;

    Method or data member not found

    " Order the result list by "Above me" and then by "Below me", so stronger competitors appear first
    With sheet.Sort

    • Yoav Ezer says:

      Andy, what version of Excel are you using?
      Also, maybe try downloading the file again and see if it works now.

  12. Rob says:

    There is a 'compile error' in the macro

    • Yoav Ezer says:

      Rob, you seem to get the same error like Andy above.
      What version of Excel are you using?

  13. Glynn says:

    Very cool!!! And very useful, thanks
    .-= Glynn recently posted: How to SEO a Domain =-.

  14. Cory says:

    Awesome tool – thanks for sharing this!

  15. Jun says:

    Yoav, this tool is awesome. The choice of webserver to use makes this very useful for people like me who does geo-targeting. Thanks a lot for sharing.
    .-= Jun recently posted: I Love CommentLuv =-.

  16. I get a runtime error: "Overflow" (Excel 2007) after I get 2 results returned.

    Otherwise, it is pretty nice tool.
    .-= Lyena Solomon recently posted: How Not To Treat Your Prospects =-.

  17. Tony says:

    Hey Yoav,
    Thanks for this but I haven't had any luck so far trying to use it.
    I'me getting the same error described above: 2Method or data memeber not found."
    It's within the "Sub CompetitorResearch()" and specifically is the "sheet.Sort" after the comment
    " Order the result list by "Above me" and then by "Below me", so stronger competitors appear first

    i'm running Excel 2003 SP3. Any idea of what might be causing this?

    • Yoav Ezer says:

      Tony, can you please re-download and give it another go?
      I've fixed the code and tested it on older Office versions.

  18. [...] How to Perform SEO Competitor Research with Excel – SEP [...]

  19. Andy says:

    Works for me now in Excel 2003 Yoav – thanks!

    Would be great to have a cell in there to put in which version of Google you want to match against such as http://www.google.co.uk :)

    • Andy says:

      P.S I know I can do this manually via the macro editor – it would just be easier for the Macro / scrupt virgins.

  20. yosef says:

    Getting this error:
    System Error &H80072EE2 (-2147012894). The operation timed out.

    Any help?

    Thanks!

  21. Richard says:

    Hi,

    I just found your site and this Excel program. Thanks for posting it!

    I'm not able to get it to run for me though. My problem may be that I am running a Mac with Office 2008 (Macros are enabled.) Any suggestions?

    Thanks,

    Richard
    .-= Richard recently posted: BlueSheepdog Podcast Episode 4 =-.

  22. [...] SEO Competitor Research [...]

  23. Gareth says:

    Nice work on this tool. Works great