EMPOWERING ORDINARY MARKETERS
TO BUILD EXTRAORDINARY LINKS.

Prioritize Link Opportunities in Excel

by Jon Cooper
154 Flares 154 Flares ×

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.

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. 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.

2. Download Backlink Reports

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?

  • Domain level – when you want to look at all of the links to a competitor’s domain. For most general competitors, this will be the correct one to use.
  • Prefix level – if you only want to see the links to a certain section of a competitor’s site. For example, if you only want to see links to their blog (/blog/) or to a product category (/category/), assuming their URL structures for sub-pages are consistent (i.e. if all blog posts are under /blog/).

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).

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

cd /Users/tscooper/Desktop/AhrefsExports/Campaign1

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.

4. 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, 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:

  • 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!

Example Spreadsheet

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’:

http://www.brushymountainbeefarm.com/

http://www.kelleybees.com/

http://www.dadant.com/

http://www.mannlakeltd.com/

https://www.millerbeesupply.com/

http://westernbee.com/

http://www.sacramentobeekeeping.com/

http://www.ruhlbeesupply.com/

http://www.betterbee.com/

http://www.gabees.com/

http://www.bee-commerce.com/

http://www.sflbeesupplies.com/

http://www.hungrybearfarms.com/

http://busybeebeekeepingsupplies.com/

http://www.hillsidebees.com/

http://www.busybeesupplies.com/

http://www.tobeeornottobee.us/

http://www.blueskybeesupply.com/

http://www.virginiabeesupply.com/

http://www.nebees.com/

Note: The columns generated in step 5 are the final two (M & N).

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 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).

The fifth is that a new post was published after this one by @matshepseo outlining alternative steps to #2 & #3 (although Ahrefs only allows a limited number of standard reports).

The sixth is how to do the CSV merge on Windows. Here’s the play-by-play thanks to Gavin Ward & Douglas Millar:

  1. From Windows Explorer, navigate to containing folder where csv files are saved
  2. Right click on folder name in menu bar & copy address
  3. Use key combination: windows key + x & select Command Prompt
  4. Enter command “cd [then right click paste (adds in address)]” hit return
  5. Type command “copy *.csv all.txt”
  6. Navigate back to 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!

This post was written by...

Jon Cooper – who has written 128 posts on Point Blank SEO.

Jon Cooper is a link builder based out of Gainesville, FL. For more information on him and Point Blank SEO, visit the about page. Follow him on Twitter @PointBlankSEO.

NEED LINKS?
Relax - I send out free emails full of
cutting edge link building tips.
64 Comments
  1. John Summers says:

    Jon, great practical walk through. Thanks for sharing this and some of the tools I have not yet used. Cheers

  2. Glen Allsopp says:

    I love nerdy stuff like this because I know I could never figure it out on my own.

    Epic write-up John (and cool idea with the gifs). Will try this out later today 🙂

    • Jon Cooper says:

      Given what you’ve done, I highly doubt that! 🙂 Just think we’re each attacking the problem from a different side of the hat, which in turns digs up a lot of useful stuff for the other, which is why I’m always keeping tabs on ViperChill.

      Also, for anyone else, just emailed Glen about this (http://recordit.co) which is a super awesome tool for quick GIF recording. Wish I knew who initially told me about it, because they deserve the credit for finding, but it might just be the best thing since sliced bread.

      • Joseph Lee says:

        I was extremely curious about the .gif videos. It’s the first blog I have seen that but it makes for an excellent step by step tutorial tool. Great work. The .gif’s integrated in to the blog had me glued to the article only partially interested in the article but extremely interested in your content presentation. I can’t wait to try this. Great article and an awesome share of a great resource for tutorial writers!

  3. Vince Lin says:

    How do you deal with link crawler blockers mate like Spyder Spanker?

    Thanks

    • Jon Cooper says:

      Not familiar with that particular one, but if I understand what you’re getting at Vince, it does prevent some sites from being a part of certain link indices (i.e. Ahrefs) which sucks, but there’s no way around it 🙁

      • Vince Lin says:

        Exactly – a world where a smart blog network or webmaster hides crawlers from indexing his site. I guess you’d’ have to continually update your IP block list for new crawlers. That’s what I would do.. if I owned a blog network. Wink Wink.

      • Jeff says:

        I’m guessing you either won’t want or won’t be able to get links on most pages that are blocking crawlers. They’ll be part of someone’s network. For link prospecting it doesn’t make much difference, unless you plan on replicating their blog network (which is totally valid 🙂 )

  4. BonnieB says:

    Terrific info. Every little bit helps……….no way I am unsubscribing. Bring on more !

  5. Hey Jon,

    You should check out Link Research Tools. They’ve got a tool called the Common Backlinks Tool that does exactly what you’ve set out – but automatically. You can even hook Ahrefs in to it, combining it with their own link data, to get rock solid link data. I’m a big fan.

    • Jon Cooper says:

      Thanks James! Me checking out LRT is long overdue; didn’t realize they had this exact feature, so will look into it this week. Really appreciate you letting me know!

  6. Weng Jauod says:

    Hi Jon,

    I’m a fan of PBSEO and this is a really helpful post. Can’t wait to work using this method. Thanks a lot.

  7. Yasir Salman says:

    After long time got awesome stuff again 🙂
    Thanks

  8. Thoufeeq says:

    Thats a lot of details, but the process-driven approach makes it easier for anyone to try it out. Thank you very much, Jon!

    You mentioned that you pull metrics like HomePage PR as well as URL PR, and sort the list accordingly. So PR is still a reliable metric, in your opinion?

    Thanks

  9. Great walkthrough Jon, and certainly not a method I have tried yet – guess what I will be doing later on 😉

    Can I ask, what sort of success rate do you have when using this method to find competitors backlinks? And would you recommend Ahrefs over some of the alternatives like Majestic / LRT / OSE?

    Andy

    • Jon Cooper says:

      Thanks Andy – success rates are completely dependent on the relevance of the opportunity. If I reached out to just the 50 best of 5000, I’d have much better success rate than if I reached out to all 5000 without looking at each individually.

  10. Very useful post, Jon.

    I just wanted to add something for those who don’t use UrlProfiler to get the domain quickly; with SEO tools for Excel (free) you can use:

    =CONCATENATE(“http://”;RegexpReplace(RegexpReplace(CELL_WITH_THE_URL;”(.*)//”;””);”/(.*)”;””))

    it first strips http:// so then you can use / as a control char and delete anything after it; finally re-add http:// if needed.

    😉

    • Jeff says:

      You can also use: “=IF(ISNUMBER(FIND(“www.”,A2)),MID(A2,FIND(“www.”,A2)+4,FIND(“/”,A2,9)-FIND(“www.”,A2)-4),MID(A2,FIND(“//”,A2)+2,FIND(“/”,A2,9)-FIND(“//”,A2)-2))” if you don’t use SEO Tools for Excel (Which is a must-use in my book).

      The formula cuts down to the domain 🙂 Might have some glitches but I’ve used it a long time.

    • Jon Cooper says:

      Hat tip to both of you for those formulas!! Really appreciate it guys.

  11. Brian Dean says:

    Really helpful and actionable as always, Jon.

    One of the top questions I get from new link builders is: “What do I do with all of these opportunities I just found from ScrapeBox or ahrefs?”. So this will be a reference that I’ll send people to from now on 🙂

  12. Hi Jon,

    Great post! 🙂

    You can also use Monitor Backlinks tool as your alternative. It automatically generates link data of your competitors’ backlinks once you export them from the tool. Though the highest # of competitors that you can get link data from is 10, if they are your top/main competitors, then doing this competitor link research regularly is worth your time, it would give you tons of link opportunities every week (yeah, Monitor Backlinks has the ability to regularly monitor the links acquired by your competitors).

  13. Tariehk says:

    Thanks Jon, I am going to have to try this out. I am starting to use ahrefs and now I will try this process.

  14. Sepid says:

    Enjoyed!
    Thank you for sharing the file.

  15. Ian Howells says:

    This will do Step 4 in Excel:

    =SUBSTITUTE(IFERROR(LEFT(SUBSTITUTE(SUBSTITUTE(A2,”http://”,””),”https://”,””),FIND(“/”,SUBSTITUTE
    (SUBSTITUTE(A2,”http://”,””),”https://”,””))-1),SUBSTITUTE(SUBSTITUTE(A2,”http://”,””),”https://”,””)),
    “www.”,””)

    Assumes this is in B2 and your URL is in A2.

  16. Jeff Siebach says:

    Thanks for the breakdown Jon. I’ve been using Majestic’s ‘Clique Hunter’ tool to find opportunities but that is limited to 10 competitors, being able to compare 50+ competitors will certainly give a more valuable set of options.

  17. I’ve been meaning to get more involved in technical data-driven SEO and this is a great way to make that happen. I saw your tweets re: COUNTIF function and it got me curious. So glad you decided to expand into a post. Great work!

  18. Jeff says:

    I do appreciate the step by step. Thanks. I did buy your guide, and would like to know how important is this data, actually? Where in the list of priority of linking methods does this method actually stand? Getting the frequency of my competition’s links from the same site is good but in reality wouldn’t I be better off finding unique links from authority sites where my competition ISN’T link from, especially if there are a lot of them? I mean some specifics would be interesting to understand your thinking here.

    Also, how is this method different from, say, a hub finder?

    • Jon Cooper says:

      This is actually some of the lowest hanging fruit out there, so in terms of how much of a priority it is, it’s at the top of the list!

  19. Hello Jon,

    Great steps, I always love actionable steps to take back with me to work.

    For windows, you can merge CSV files really easily.

    – From within the folder of .csv files you wish to compile
    – Hold Shift + Right click within the folder and select “Open Command Window Here”
    – This will open a command prompt directed at the target folder with all your CSVs.

    From there, type
    copy *.csv desired_filename.csv

    The asterisk being a wildcard to pull in all CSV file names from within the folder.

    • Jon Cooper says:

      I was waiting for someone to chime in on how to do that in Windows! Really appreciate the tip Michael, hope a few lucky souls that run Windows scroll down & see this.

  20. Jamie Knop says:

    Nice little walk through Jon for some easy wins.

    Competitor analysis is one area I think most SEO’s neglect. You can often get some of your best links doing it. It’s also something good to do BEFORE putting together an SEO strategy, as it shows how your competitors have gained quality links, then you can incorporate these tactics as part of the strategy.

    Do you gather the list of competitors manually? Or use data from something like SEMRush or SearchMetrics?

    For step 4 feel free to use me URL trimmer here http://www.seoweather.com/trim-urls-to-root-domain-standardise-urls-prefixes/ or for people who own ScrapeBox can trim URL’s through that.

    Cheers,
    Jamie

    • Jon Cooper says:

      Thanks Jamie!

      I do it manually, as I need to do a quick investigation of each to see the type of value add their website has (i.e. informational vs. transactional) and to see if the website has any other purpose (i.e. a site ranking for insurance keywords, but also happens to be a bank), because that’ll dictate WHY a site is getting links, and could be bad for the competitive analysis I’m doing.

      Thanks for the trimmer!!

  21. A great process Jon. Thanks for sharing this!

    I have a process for bulk exporting backlink reports from Ahrefs that you might find helpful. It doesn’t create the one backlink per domain report, but it will save you a whole lot of clicking!

    I just posted the process over on my blog. I won’t link drop here without your permission, but head over to my site and visit the latest blog post.

    I also included an Excel Macro to help combine all of the .CSV files.

  22. Jennifer says:

    This step-by-step walk through is awesome. I’ve bookmarked it for future reference. I love the data side of SEO!

    • Jon Cooper says:

      Thanks Jennifer! I would consider this the shallow part of the data side :). There are far cooler things more technical people are doing with data like this.

  23. Very good one!! step by step. there many good tool to track the back links, i tried ahrefs tool and it very good.

  24. Marketing says:

    What a great walk through! I find the use of GIFs to demonstrate steps so clever. Never seen anything like it and the process itself is very neat. If only SEO could always be this clean and straight forward.

  25. John says:

    Hi Jon,
    i really thank you for discussing how we can discover the competitors’s link building opportunities.I will apply your walk through on my site and I will come a gain to check your link building strategies to enhance my site rankings on Google.

  26. This is a GREAT article. Thanks for sharing the process. I’ve bookmarked and will be coming back to review this next time I am doing competitor analysis.

  27. Arun says:

    Thanks Jon. These kinds of techniques will help me to improve my white hat seo tactics.

  28. Mattijs says:

    Great article John. I’m always struggling how to keep track off all those competitor links. Taken the comment of John (August 4, 2014 at 4:54 am) into account i’m going to try Link Research Tools first.

  29. Mark says:

    Since you discuss the merging of CSV files – our program does this automatically for you – Bulk File Merger. We have both Mac and Windows versions available.

    Great article and thanks for sharing.

  30. Friggin sweet guide. Alone the URL Profiler as a recommendation was worth the read. Detailed guides like this are simply boss 🙂

    In my opinion competitor analysis is what SEO is mostly about. You “simply” get all those sweet links they have + some that are hard to or impossible to get for them. Then just serve a little better content with a little better structure. And they are done for…

    You might even get me to become a Excel ninja… One day!

    Greetings from Innsbruck,

    Alex

  31. Jay Patel says:

    Your practical SEO tips never seized to amaze me but this time around I lost it completely. It seems that it is too technical for a person like me. Nevertheless, I shall give it a try and see if I find any issue

  32. Matt says:

    Hey Jon… another great post, you must be doing something right, I can tell by who is commenting on your posts…

  33. Nice work Jon,

    i just want to help you at the 3rd step, if you’re not using a Mac:

    If you run Microsoft Excel you can use some lines of code on a Macro. In order to merge some CSV files just:

    1- Put all theCSV files on a directory
    2- Copy also that “excel-with-macros” file on that dir
    3- Open the excel file, and go to Macros menu option (the last, or press ALT + F8)
    4- Execute the macro and let it merge all the csv files on a row
    5- Save the file

    The original code is from Luis Mondelo (i founded it at http://www.todoexpertos.com/categorias/tecnologia-e-internet/software-y-aplicaciones/microsoft-excel/respuestas/htgjznu4a5wso/unir-un-conjunto-de-excels-csv-a-uno-solo)
    But i setted for you on a file ready to use. It can be downloaded from my blog http://www.tecnicaseo.com/wp-content/uploads/tools/combinarcsv.xlsm

    Hope to help!!

  34. It’s truly very complicated in this active life to listen news on TV, therefore
    I simply use the web for that purpose, and get the moxt up-to-date information.

    my blog poost spanish jokes funny

Prioritize Link Opportunities in Excel - Point Blank SEO
154 Flares Twitter 0 Facebook 0 Google+ 86 LinkedIn 68 154 Flares ×