Feb. 2015 update – you can now run through the process using Majestic in the same way now that they’ve (finally) introduced the ‘1 link per domain’ feature. Announcement here.
This is a simple walkthrough of a very actionable, helpful tip that will allow you to figure out which competitor link opportunities are most important.
If you’ve ever used either the Clique Hunter from Majestic, or the Competitor Link Finder from Moz, then you know how useful those features are but how limiting their tools are. Essentially, we want to take that idea & apply it at a much larger scale.
For those who haven’t used either, what we’re doing is trying to find the URLs & Domains that link to the highest number of unique competitors. So for example, if a certain page links to 7 of 10 competitors, then I sure as heck want to get that link too! By finding the number of unique competitors being linked to, we can prioritize important opportunities.
Here’s a walkthrough of how to do this in Excel.
The first step is aggregating a large list of all of your competitors. We’re not looking at those just on page 1; we want as far back in the SERPs as you can go & as many as you can find. Don’t forget about directories, links/resource pages, and other lists of relevant competitors you can get your hands on. Depending on the vertical, my lists have totaled over 100 at times.
For the sake of this example, we’ll assume your list is in Excel.
First, copy & paste the list of competitors you have in Excel into URLOpener.com. Click ‘Submit’, which will lead to a page full of external links of your competitors (your list will probably be bigger than the below).
Now use LinkClump to open all links into new tabs (if your list is very large, i.e. 100+, then you might want to break up the list).
Now, For the sake of this example, and because when I use just one provider, I use them, we’re going to use Ahrefs for finding all of their backlinks. But before we open Ahrefs, let’s assume that you have the below bookmarklets installed in your browser:
In each of those new tabs you have for each competitor, hit one of the above bookmarks on them, which will take you to a list of all of their backlinks. But which one should you use for each?
At this point, all of those tabs should now be pages on Ahrefs.com. Now we want to download each of those backlink reports by clicking the Export button, as shown below.
Note that you have to wait 10 seconds between each export, which is annoying, but unavoidable (however, currently trying to use a few automation programs so it doesn’t have to be done by hand).
We want to take all of those CSVs and combine them into one spreadsheet. There are a couple ways to go about this.
A. Depending on how large of a number there is, and if you’re using a Mac, you can automate this with a couple lines of code in the Terminal app.
It’s a very simple process, so don’t shy away from it! First, you need to make sure all CSVs are in a single folder by themselves.
Next, open up the Terminal app, which is located in your Applications > Utilities folder. Now it’s time for the 2 lines of code you need to enter. Here’s the first, which navigates you to that folder of CSVs:
As you can see, my folder is located on my Desktop in the ‘Ahrefs_Exports’ folder. In order to find where your folder is, simply right click on it and click ‘Get Info’ which will tell you its location, as shown below. Then, add on the name of your folder at the end (in my case, ‘Campaign1’).
Once you type it in, and hit Enter, nothing really happens, so don’t expect much. Here’s a GIF below of me typing in that command correctly.
Now that the Terminal knows which folder you’re talking about, the following line of code will combine all CSVs in that folder into a single, merged CSV:
cat *.csv >merged.csv
Now that they’re all merged, open up the new CSV (it’ll be located in the folder of that you were merging in), and delete the extra header rows you have. I do this by sorting the initial column in Descending order, then deleting all but 1:
Note: hat tip to the Monchito blog for showing me how to do the above.
B. If you’re not on a Mac, but still are working with a large list, there are a number of tools on the Web that combine CSVs. I haven’t tried any of them out, so unfortunately I can’t make any recommendations.
C. If it’s a rather small list of competitors, what I do is instead of waiting 10 seconds before each export, I open up each & start combining them manually as I go.
This is optional if you’re only looking to find the exact URLs of individual link opportunities, but if you’re also looking for which domains link out to the most number of competitors, include this step in your process.
There are a number of ways & a number of tools to help with this, but I’ll show you how I personally do it.
I use URL Profiler for finding metrics of individual URLs, but it also takes a list of URLs and pulls out the domains.
Once downloaded, instead of selecting any individual metric to pull, leave all boxes unchecked, and simply import your URL list by copying & pasting it. Your screen should look like the below.
From there, hit ‘Run Profiler’, and it’ll take about 10 seconds to run. Once finished, you’ll get an export with the following rows for each URL:
Delete all but ‘URL’ and ‘Domain’. The reason we don’t want root domain is because it’s difficult for programs like this to strip them down to the correct root domain each & every time, so instead, let’s just work with the subdomains, which is also more helpful for relevance (i.e. in cases like blogspot.com).
Copy & paste those two columns into a new sheet of your main, merged spreadsheet of links, then use VLOOKUP to pull the domain for each URL into your main sheet (walkthrough on VLOOKUP here).
At this point, you should now have all the links of all your competitors into one spreadsheet, and you’ve now added ‘Domain’ as a column.
The last step is where we turn this data into something truly useful. We’re going to be counting the number of occurrences of both URLs & domains (assuming you did step #4) to see often they showed up in our spreadsheet.
We’ll be using COUNTIF, which is a fairly simple Excel function that looks like this:
The first step is choosing the range. For this, you’ll be selecting an entire column, which can be written as ‘A:A’ if it was column A you’re selecting.
The second step is choosing the value to search for in that range. For this, select the cell value in that row in that column to search for (i.e. A2). So in the end, the function should look like this if the first column is the URLs:
Do that for both the URL column, and the domain column, and you now know how many times each URL/domain has appeared.
You can now sort the list by either of those and find the link opportunities that your competitors have capitalized on the most!
To give you an example of what a finished spreadsheet will look like, I created one based off the following 20 websites ranking for ‘beekeeping supplies’:
Note: The columns generated in step 5 are the final two (M & N).
There are a few things to note about the process overall and the example outlined above.
The first is that this process should be split up when not all of the sites you’re including share something in common. So for example, you could split up sites & run different reports based on the following groups:
Otherwise, the occurrence data wouldn’t be significant.
The second is that in the example above, the link data I pulled from Ahrefs is set to “one backlink per domain”. This means that if a competitor received multiple links from the same website, only the most authoritative (according to Ahrefs) would be listed in this data.
I find it the most useful for my own applications, but you should be aware of this if it’s an issue for what you’re wanting to use it for. The other option is ‘all backlinks’.
The third is that you can (and should for serious applications) use link data from multiple providers, instead of only Ahrefs. As of February 2015, Majestic has debuted the ‘one link per domain’ feature to make the process identical.
The fourth is that in the example above, I used URL Profiler only to pull out the domains of the URLs, but when I do run through this process, I usually pull other metrics as well (i.e. homepage PR & URL PR).
With that said, do you have any questions about the above process? Do you know a better way of doing things? If so, I’d love to hear about them in the comments!! I respond to nearly all comments on this blog, so don’t be shy!