How To Prioritize Link Opportunities in Excel

If you’ve ever used either the Ahers or SEMrush to find your competitors’ backlinks, then you know how useful those features 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.

1. Find All Competitors

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.

For the sake of this example, we’ll assume your list is in Excel.

2. Download Backlink Reports

For the sake of this example, and because when I use just one provider, we’re going to use SEMrush for finding all of their backlinks. So, navigate to the SEMrush backlink analytics tool and paste the URL your first competitor there. Then

1. Click on the “Backlinks” tab.
2. Select to display 1 “Links per ref. Domain”.
3. Create a folder in your desktop and name it “Exports1”.
4. Extract all competitor’s links as a CSV file and save it there“.
   

3. Combine CSVs Into A Single Spreadsheet

We want to take all of those CSVs and combine them into one spreadsheet. There are a couple of ways to go about this.

1. Depending on how large of a number there is, and if you’re using a Mac, you can automate this with a couple of 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:

cd /Users/Eliot/Desktop/Export1

*Change “Eliot” to your user name and also rename the folder according to your needs.

As you can see, my folder is located on my Desktop in the ‘Exports1’ 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, ‘Export1’).

Once you type it in and hit Enter, nothing really happens, so don’t expect much. Here’s a screenshot 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).

1. 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.
2. Find Domain of Each URL (optional but recommended)
   
   

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, including 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:

• UID
• URL
• Path
• Domain
• Root Domain
• TLD

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.

5. Count Occurrences Using COUNTIF Function

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:

=COUNTIF(range,”text”)

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:

=COUNTIF(A:A,A2)

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!

Things to Note

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:

• General competitors
• Category-specific competitors
• Industry blogs
• Content asset-specific competitors
• Otherwise, the occurrence data wouldn’t be significant.

The second is that in the example above, the link data I pulled from SEMrush is set to “one backlink per domain”. This means that if a competitor received multiple links from the same website, only the most authoritative 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 SEMrush such as Ahrefs and Majestic.

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. DA and Trust Rank at a URL and domain level).

The fifth is how to do the CSV merge on Windows.

How to do the CSV merge on Windows

• From Windows Explorer, navigate to containing folder where CSV files are saved.
• Right-click on the folder name in the menu bar & copy the address.
• Use key combination: Windows key + x & select Command Prompt.
• Enter command “cd [then right-click paste (adds in address)]” hit return.
• Type command “copy *.csv all.txt”.
• Navigate back to the original folder and open all.txt using Excel.

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! Also, do not forget to visit my SEO and link building focused blog here.