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

About the Author: Yoav Ezer

30 Comments

  1. Tom July 21, 2010 at 3:44 am

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

    • Yoav Ezer July 21, 2010 at 7:29 am

      Tom,
      I’ve fixed the link. Let me know if it works for you

  2. Marjory Meechan July 21, 2010 at 4:04 am

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

    • Marjory Meechan July 21, 2010 at 4:05 am

      Actually, that wasn’t me that posted Google Me or Not – sorry should have unchecked the Luv button.

  3. DazzlinDonna July 21, 2010 at 5:05 am

    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 July 21, 2010 at 7:30 am

      Donna, it’s fixed now, so try it again.
      And please let me know what you think about it 🙂

  4. BruB July 21, 2010 at 5:41 am

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

    • Yoav Ezer July 24, 2010 at 11:34 am

      BruB, Be sure to try again. it works now.

  5. Sheila Walker July 21, 2010 at 6:18 am

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

    • Yoav Ezer July 24, 2010 at 11:35 am

      Sheila, the link is fixed so try again.

  6. Tom July 21, 2010 at 7:57 am

    Working fine now, thanks Yoav!

    …also thanks for the well-commented code 🙂

  7. Netwave Interactive July 21, 2010 at 11:14 am

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

  8. Nitin July 22, 2010 at 1:16 am

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

    • Yoav Ezer July 24, 2010 at 11:36 am

      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?

  9. Budget Hotels In kanyakumari July 22, 2010 at 1:34 am

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

  10. Andy Symonds July 22, 2010 at 2:49 am

    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 July 24, 2010 at 11:38 am

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

  11. Rob July 22, 2010 at 7:05 am

    There is a ‘compile error’ in the macro

    • Yoav Ezer July 24, 2010 at 11:39 am

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

  12. Glynn July 22, 2010 at 7:14 am

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

  13. Cory July 22, 2010 at 9:19 am

    Awesome tool – thanks for sharing this!

  14. Jun July 22, 2010 at 4:49 pm

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

  15. Lyena Solomon July 24, 2010 at 12:11 pm

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

  16. Tony July 26, 2010 at 3:14 am

    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 July 28, 2010 at 9:11 am

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

  17. Andy July 29, 2010 at 8:09 am

    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 July 29, 2010 at 8:12 am

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

  18. yosef August 24, 2010 at 10:01 am

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

    Any help?

    Thanks!

  19. Richard September 2, 2010 at 12:57 pm

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

  20. Gareth January 13, 2011 at 12:42 pm

    Nice work on this tool. Works great

Comments are closed.