Blog

How to Perform SEO Competitor Research with Excel

by  Yoav Ezer | Jul. 21, 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

Written by 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

Share This!


31 fascinating comments

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

Reply
Tom  #

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

Yoav Ezer  #

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

Reply
Marjory Meechan  #

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

Marjory Meechan  #

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

Reply
DazzlinDonna  #

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

Yoav Ezer  #

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

Reply
BruB  #

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

Yoav Ezer  #

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

Reply
Sheila Walker  #

Sheila, the link is fixed so try again.

Yoav Ezer  #

Working fine now, thanks Yoav!

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

Reply
Tom  #

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

Reply
Netwave Interactive  #

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

Reply
SearchCap: The Day In Search, July 21, 2010  #

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

Reply
Nitin  #

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?

Yoav Ezer  #

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

Reply
Budget Hotels In kanyakumari  #

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

Reply
Andy Symonds  #

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

Yoav Ezer  #

There is a 'compile error' in the macro

Reply
Rob  #

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

Yoav Ezer  #

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

Reply
Glynn  #

Awesome tool – thanks for sharing this!

Reply
Cory  #

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

Reply
Jun  #

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

Reply
Lyena Solomon  #

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?

Reply
Tony  #

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

Yoav Ezer  #

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

Reply
Weekly Search & Social News: 07/27/2010 | Search Engine Journal  #

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

Reply
Andy  #

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

Andy  #

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

Any help?

Thanks!

Reply
yosef  #

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

Reply
Richard  #

Leave a Reply

Call Us Today