Toll Free: 1-877-695-7388

GTA: (647) 699-2838

Search Engine People
  • SEO
  • SEM
  • CRO
  • Display
  • Blog
  • Why Us
  • Contact
  • Join Our Team
  • Get A Quote

Toll Free: 1-877-695-7388

GTA: (647) 699-2838

How to Perform SEO Competitor Research with Excel

Yoav Ezer | July 21st, 2010
Tweet55
Share9
Share
Pin
64 Shares

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

  • How to Automatically Track Your Google Positions in Microsoft Excel
  • A Search Engine Keyword Variation Generator in Excel
  • How to Create a Link Checker with Microsoft Excel
  • How to Check HTTP Redirects with Microsoft Excel
Tweet55
Share9
Share
Pin
64 Shares
Posted in SEOTagged competition, Google, Keyword Research, spreadsheet

About the Author: 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

30 thoughts on “How to Perform SEO Competitor Research with Excel”

  1. Tom says:
    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…

    1. Yoav Ezer says:
      July 21, 2010 at 7:29 am

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

  2. Marjory Meechan says:
    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 =-.

    1. Marjory Meechan says:
      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 says:
    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 =-.

    1. Yoav Ezer says:
      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 says:
    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 =-.

    1. Yoav Ezer says:
      July 24, 2010 at 11:34 am

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

  5. Sheila Walker says:
    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.

    1. Yoav Ezer says:
      July 24, 2010 at 11:35 am

      Sheila, the link is fixed so try again.

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

    Working fine now, thanks Yoav!

    …also thanks for the well-commented code 🙂

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

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

  8. Nitin says:
    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

    1. Yoav Ezer says:
      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 says:
    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 says:
    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

    1. Yoav Ezer says:
      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 says:
    July 22, 2010 at 7:05 am

    There is a ‘compile error’ in the macro

    1. Yoav Ezer says:
      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 says:
    July 22, 2010 at 7:14 am

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

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

    Awesome tool – thanks for sharing this!

  14. Jun says:
    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 says:
    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 says:
    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?

    1. Yoav Ezer says:
      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 says:
    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 🙂

    1. Andy says:
      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 says:
    August 24, 2010 at 10:01 am

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

    Any help?

    Thanks!

  19. Richard says:
    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 says:
    January 13, 2011 at 12:42 pm

    Nice work on this tool. Works great

Comments are closed.

Recent Posts

  • Maximizing Your E-Commerce Sales:
    A CRO Audit Guide
  • Movin’ On Up! Why Migrating to Google Analytics 4 (GA4) Should be a Priority
  • A Year in Review: The Digital Marketing Trends That Defined 2021
  • The Basics of Video Marketing
  • Just How Much Do Google Reviews Impact Your SEO Ranking?

Categories

  • Analytics & ROI Analysis
  • Company News
  • Content
  • Conversion Optimization
  • CRO
  • Display Advertising/RTB
  • Email Marketing
  • En Español
  • En Français
  • Inbound Marketing
  • Lead Nurture & Marketing Automation
  • Local Search
  • Marketing
  • Mobile
  • Partnership Marketing
  • PPC
  • PR
  • SEO
  • Social Media Marketing
  • Web Design

Additional Posts

Summertime And The Blogging Is Easy

July 20th, 2010 | by Barry Welford

What Is A KEI?

July 20th, 2010 | by Gabriella Sannino

How to Do Online Research in 3 Easy Steps

July 19th, 2010 | by Ann Smarty

LET'S TALK

Need more information or want to get in touch?

Get in touch!
  • SEO
  • SEM
  • Display
  • Blog
  • Why Us
  • Join Our Team
  • Contact Us
  • Local SEO
  • Small Business SEO
  • Enterprise SEO
  • International SEO

LOCATION

1305 Pickering Parkway,
5th Floor Pickering, L1V 3P2

PHONE

Toll Free: 1-877-695-7388
Greater Toronto Area: (647) 699-2838

Social

© Search Engine People Inc. 2023 – Canada’s Top Digital Agency
© SEP 2023 – A Search Engine People Company | Privacy Policy

Search Engine People