Competitor Link Prospecting in Microsoft Excel

17 comments

You all know I’m a big fan of Microsoft Excel, and I love building Excel templates that help you do your work better. You’re in luck, because today I’m releasing to you an Excel template that I love.

I call this one the Competitor Backlink Prospector. Now, SEOmoz has the Labs Competitive Link Finder, but this tool has never quite worked the way I’ve wanted it to. So, I took it upon myself (inspired by my old coworker Ethan Lyon, who is now at SEER) to create this spreadsheet.

How to Use It

This spreadsheet was a huge pain to put together, and it’s pretty quick dirty, but it works and does what you need it to do.

Download the sheet

The sheet has four worksheets:

  • New Prospects
  • Your Backlinks
  • Competitor 1
  • Competitor 2
It’s mostly self-explanatory, but I want to explain it to you here so you can tell me if I am doing something wrong. It’s been working for me pretty well though, so I think it’s sound.
What you need to do involves the following:
  • Download Competitor 1’s backlinks from OpenSiteExplorer.
  • Download Competitor 2’s backlinks from OpenSiteExplorer

First, copy Competitor 1’s backlinks into the “Competitor 1” worksheet, starting in cell B2:

Then, copy Competitor 2’s backlinks into the “Competitor 2” worksheet, once again in cell B2:

What happens then is magic. Or, if you want to know, I am looking to see if the clean URL in the ‘Competitor 1’ worksheet, or in the ‘Competitor 2’ worksheet (in column M) is already in the clean URL list in the ‘Your Backlinks’ sheet. If it is, you will have a “Links” value returned in column A of the same sheet.

Then, on the ‘New Prospects’ sheet, I do a VLOOKUP to pull the URL to the right of any cell in Column A where the value is ‘Prospect’. This means that the URL linking to your competitor is not already linking to you, and you need to try to get that URL!

*One quirk to note* – I then use a Pivot Table to allow you to get just the clean URLs (without duplicates) for Competitor 1 or Competitor 2. Consult the below screenshots to see how. When you enter in new competitors into the Competitor sheets, you will have to right click and ‘Refresh’ the Pivot table for it to show the right data.

Here is how you refresh the Pivot Table:

All there’s left for you to do now is:

Download the sheet

Finishing Up

I hope this spreadsheet is useful to you. I’m also totally open to ways to improve it, if you are more of an Excel ninja than I am. In fact, if you do so, I’ll gladly put your sheet as the one to download here and will give you a link as credit.

Download the sheet

17 thoughts on “Competitor Link Prospecting in Microsoft Excel”

  7. Hi John,

    Another nice post and thanks for sharing this. Testing it with two different verticals and I am tweaking it a bit to add some extra columns.

    I run the CSV through Eppie’s LinkDetective first. This way I can have the link type in the spreadsheet as well. Having this data right on your sheet helps me decided where/how to focus my efforts. It takes some tweaking, since the CSV comes out different from LD, but once you adjust the columns, it’s helpful.

    Thanks for sharing

  8. Hi John!

    what a spreadsheet of hell 🙂 you built! awesome.

    You can do the same with up to 10 competitors in our
    Link Research Tools – “Missing Link Tool” FYI and get up to 66 different SEO metrics along with every prospect you identify.

    cheers, christoph

  9. Nice tool there, sir!

    Just curious, is there a reason you use a pivot table to eliminate duplicates instead of the “Remove Duplicates” function under the Data tab?

    1. Hey Mitch –

      Yeah, I wanted to automate it and keep all the data the same. With the Pivot Table, people can just drag and drop. Otherwise, it is a lot more manual to get clean data.

      Great question!

  10. Pingback: Linklove Boston 2012 Recap | distilled

  11. Pingback: Linklove Boston 2012 Recap | Reputation Expert

  13. Pingback: Identifying Link Patterns with SEO Tools | John Doherty

  14. all that sounds awesome however , is there a program that can run all the backlinks for you..my competitors have 10,000 backlinks omg 🙁

Comments are closed.