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.