Home | Uncategorized | Competitor Link Prospecting in Microsoft Excel

Competitor Link Prospecting in Microsoft Excel

John Doherty —  March 29, 2012
  • Buffer
  • Vote on Hacker News
  • Sharebar
  • Buffer
  • Vote on Hacker News

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

John Doherty

Posts

I'm the new (as of October 2013) Online Marketing Manager of Hotpads.com, soon to be based in San Francisco. Previous to Hotpads I worked at Distilled for 2 years as an online marketing consultant. In my spare time I shoot lifestyle photography, explore new and interesting food in New York, ski, rock climb, and update my Twitter and Google+ accounts.

17 responses to Competitor Link Prospecting in Microsoft Excel

  1. Awesome! For ages I was trying to find a simple excel sheets for link building and now I have them!

    More more! I demand more!! :) )

    Tks!

  2. Hey great tool, now if only my competitors would get some decent links!

    Cheers

  3. Sweet!

    Thanks for making it free! I will definitely put it to the test this week!

  4. …what the other Pedro said :)

  5. great stuff John :)

    testing it, loving it, clean and simple

  6. Is it possible to filter the New Prospects by # of links, DA, etc?

  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. Christoph C. Cemper April 2, 2012 at 6:56 pm

    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?

    • 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. This looks like an awesome tool, but isn’t this already found in opensiteexplorer? the other tool seomoz has? It looks a lot cooler!

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

  12. Awesome work ….. Thanks ..

Trackbacks and Pingbacks:

  1. Linklove Boston 2012 Recap | distilled - April 10, 2012

    [...] use the spreadsheet in this post to do it in [...]

  2. Linklove Boston 2012 Recap | Reputation Expert - April 10, 2012

    [...] use the spreadsheet in this post to do it in [...]

  3. Identifying Link Patterns with SEO Tools | John Doherty - April 27, 2012

    [...] Of course, you can always use other tools with your known competitors to find who is linking to more than one of them, but not to you. I recommend SEOmoz’s Competitive Link Finder or Majestic’s Clique Hunter, or if you want an idea about how to do this in Excel, go on over here. [...]