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.
The sheet has four worksheets:
- New Prospects
- Your Backlinks
- Competitor 1
- Competitor 2
- 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:
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.
17 thoughts on “Competitor Link Prospecting in Microsoft Excel”
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!! :))
Hey great tool, now if only my competitors would get some decent links!
Thanks for making it free! I will definitely put it to the test this week!
…what the other Pedro said 🙂
great stuff John 🙂
testing it, loving it, clean and simple
Is it possible to filter the New Prospects by # of links, DA, etc?
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
Awesome Mark, please do let me know how you tweak it! Would be cool to see for other verticals.
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.
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.
Pingback: Linklove Boston 2012 Recap | distilled
Pingback: Linklove Boston 2012 Recap | Reputation Expert
This looks like an awesome tool, but isn’t this already found in opensiteexplorer? the other tool seomoz has? It looks a lot cooler!
Pingback: Identifying Link Patterns with SEO Tools | John Doherty
all that sounds awesome however , is there a program that can run all the backlinks for you..my competitors have 10,000 backlinks omg 🙁
Awesome work ….. Thanks ..
Comments are closed.