How To Automate A Competitor Backlink Analysis [Free Template]

If you want to increase organic traffic to your website, it’s no secret you need backlinks.

At Assisted Reach, we begin link building campaigns by analyzing our competitors. Why?

It’s simple. “Why reinvent the wheel?”

Link building is hard, so initially, it’s MUCH more efficient to copy what your competitors are doing.

The problem I see most people have when doing a competitor backlink analysis is comprehensiveness. 

Their process looks something like this:

  1. Use a backlink profile analysis tool like Ahrefs, Majestic, or Moz
  2. Browse through some of their competitor’s backlinks
  3. Get a sense of the type of links that are being built
  4. Begin link building

This is by no means wrong.

However, we have a Google Sheets tool that is a MUCH more comprehensive and automated solution. It will save you time in the long run and help you derive better insights from your competitor’s links.

Here’s why this tool is so useful:

  • It helps you determine where to focus your link building resources
  • It provides exact link type (e.g. guest posts, directories, podcast links, etc.) distributions so you can compare your link profile to your competitors
  • It shows you how strong your links are compared to your competitors by link type
  • It shows you what types of links to build and what anchor text to use
  • It speeds up the link prospecting process, allowing you to filter through your competitors links by link type
  • It automatically filters out links you don’t want to spend your time analyzing
  • It has a built in link intersect tool
  • It shows you the nofollow distribution your competitors have
  • And more!

There are a ton of different insights you can derive from this template. I’ll go through a few use cases we use at my agency at the end of this post.

Make a copy of the Google Sheets tool (no email required or strings attached) and then let’s jump into it!

What Is A Competitor Backlink Analysis?

A competitor backlink analysis is an analysis where you reverse engineer your competitors backlinks. You do this so you can build the same (or similar) links, rank higher in search engines like Google, and drive more traffic to your website.

Now that sounds easy in theory, but actually providing comprehensive, useful recommendations at scale across multiple competitors is the hard part.

That’s why we built this tool.

You’ll be able to derive clear and actionable link building insights to go execute on.

Tools You’ll Need To Conduct The Analysis

Don’t have Ahrefs and Majestic? 

I recommend getting them. 

However, I understand if it’s not financially feasible. If you still want to run a report using this template, click here and we can help you out.

How To Use The Competitor Backlink Analysis Template

I’ll be walking you through how to set up the template for the keyword “competitor backlink analysis.” 

If you’re a visual learner, here’s a video walking through of the template set up. Otherwise, keep reading!

I do recommend actually reading Step 10 below. I go over how to use the analysis to derive insights and strategy.

Step 1) Identify Your Competitors

There are two different use cases for this template:

  1. Analyzing the links of a SERP for a specific keyword(s)
  2. Analyzing the links of an entire domain(s)

We use both at my agency depending on the situation.

1) Identifying Competitors When Running For A Keyword(s)

You can use this template to reverse engineer competitor’s backlinks that are ranking for a keyword or keywords.

You’ll want to do this if you are competing in a competitive SERP from a backlink perspective. Here’s how you figure that out:

  1. Head over to Ahrefs Keyword Explorer
  2. Paste in your keyword or keywords
  3. Look what the keyword difficulty (KD) is

We consider keywords with a KD of 20-30 or higher to be competitor backlink analysis worthy.

Now that you’ve got a keyword, simply type it into Google.

Boom!

Your competitors are looking right at you.

Copy their URLs and paste them into the How To Use tab in column B.

This tab plays no active role in the template, aside from keeping your competitors organized. It also provides quick access instructions on how to use the template.

2) Identifying competitors when running for an entire domain(s)

You can use this template to reverse engineer the entire link profile of one or multiple competitors. 

You’ll want the following to be true about each competitor:

  1. The competitor is a direct competitor from a keyword perspective. Their business model will often be the same (or similar) to yours, but doesn’t have to be. You really just want to be sure this competitor is creating content about topics that are relevant to your business.
  2. The competitor is ranking for a large majority of the keywords you want to be ranking for. You don’t want to reverse engineer the links of someone who isn’t ranking. I know this seems obvious, but it’s important to recognize.

To find these competitors, your first option is to simply Google your core product or service. 

If you are a personal injury lawyer based out of Chicago, search for chicago personal injury lawyer.

Make sure to only use direct competitors

For example, here are the sites I’d identify as competitors versus the ones that I wouldn’t.

Your second option is to use the Ahrefs Competing Domains report. 

If you’re a startup with a pretty unique product or service, but you’re investing in content marketing, this option is great because it’ll show you sites blogging about similar topics.

Open up Ahrefs and navigate to the Site Explorer. Enter your domain and then click search.

Next, click Competing Domains.

This will show you domains ranking for keywords you also rank for. 

The one issue with this is it only shows competitors based on the keywords you have in common.

What if you aren’t ranking for any keywords because your site is new or you haven’t done SEO or content marketing?

You have two options:

  1. Enter in a competitor into the Ahrefs Competing Domains tool that you know ranks for some of the keywords you want to be ranking for
  2. Go with the first option and Google your product or service

Once you’ve got a few competing domains (I recommend 3-5), paste their URLs into the How To Use tab in column B

Step 2) Export Your Competitor’s Backlink Profile Using Ahrefs

Now that you have your competitor’s URLs, it’s time to export all their backlinks.

Note: Do NOT paste over any of the headers in the tool. The Javascript on the back-end looks for the column name as a reference. If Ahrefs ever changes the name of their export columns, the sheet won’t work properly. 

Head over to the Ahrefs Site Explorer and enter a competitors URL.

Click backlinks on the left side menu. Click one link per domain (this will pull one link per referring domain).

Hit Export.

Open the export and copy the entire dataset (minus the headers).

 Paste it into the comp_data tab.

Repeat this process for each competing domain(s)/page(s).

Step 3) Export Your Site’s Backlink Profile Using Ahrefs

This tool will show you if you already have a link that a competitor has.

To get that data, head over to the Ahrefs Site Explorer. Enter the URL of your domain or page.

Click backlinks on the left side menu and click one link per domain.

Hit Export. 

Open the Excel file you just exported. Now copy the data (excluding headers).

Paste it into the client_data tab.

Step 4) Export Your Competitor’s Traffic Data

Total traffic to a domain is a useful metric when determining if a link is worth building or not. 

To get this data, navigate to the comp_domain_traffic_data tab and copy from cell A2 to cell A201.

Open up Ahrefs Batch Analysis tool (it can handle 200 URLs at a time). Paste the URLs you just copied into the tool, and then click Analyse.


Export the data, open the exported file, and delete every column except Target and Total Traffic.

Finally, paste the Target and Total Traffic data into the comp_domain_traffic_data tab starting in cell B2.

Repeat this process until you get Total Traffic data for every URL in column A.

Step 5) Export Your Competitor’s Majestic Data

Majestic’s trust flow and citation flow metrics are great for filtering and removing low quality links you don’t want to build. 

To get this data, navigate to the comp_domain_traffic_data tab and copy from cell A2 to cell A401.

Open up Majestic’s Bulk Backlink Checker tool (it can handle 400 URLs at a time). Paste in the URLs you just copied. 

Click Check backlink counts

Export the results, open the export, and then delete every column except Item, CitationFlow, and TrustFlow.

Paste this into the maj_comp tab (excluding headers). Make sure citation flow and trust flow match up to the column header.

Repeat the process until you have trust flow and citation flow for every URL in column A on the comp_domain_traffic_data tab.

Step 6) Export Your Site’s Traffic Data

Navigate to the client_domain_traffic_data tab and copy from cell A2 to cell A201.

Open up Ahrefs Batch Analysis tool.

Paste the URLs you just copied into the tool. 

Click Analyse.

Export the data and open the exported file. Delete every column except Target and Total Traffic

Now, copy the dataset (excluding headers)

Paste it into the client_domain_traffic_data tab starting in cell B2.

Repeat this process until you get Total Traffic data for every URL in column A.

Step 7) Export Your Site’s Majestic Data

Navigate to the client_domain_traffic_data tab and copy from cell A2 to cell A401.

Open up Majestics Bulk Backlink Checker tool.

Paste in the URLs. 

Click Check backlink counts.

Export the results then open the export.

Delete every column except Item, CitationFlow, and TrustFlow.

Copy the dataset (excluding headers).

Paste this into the maj_client tab starting in cell A2.

Repeat this process until you have trust flow and citation flow for every URL in column A on the client_domain_traffic_data.

We did it!

Now that you have all your data in the competitor backlink analysis template, click Assisted Reach then Process Ahrefs data.

Step 8) Set Your Filters

Not all backlinks are worth keeping around in our analysis. 

Use the Set Filters tab to remove links that you don’t want to include. 

Determining what to filter out is more of an art than a science and it really depends on the competition and how thorough you want to be.

Navigate to the Set Filter tab and set your filters.

Here are the filter definitions:

  • Domain Rating: This is Ahrefs Domain Rating. I’ll typically set this to between 10 and 99 for local clients and 20 to 40 and 99 for national clients, but it really depends on what information I’m trying to derive from the analysis.
  • Trust Flow: This is Majestic’s Trust Flow. I’ll typically set this to between 10 and 99, but again, it depends on what I’m using the analysis for.
  • Citation Flow: This is Majestic’s Citation Flow. I’ll typically set this to between 10 and 99, but again, it depends on what I’m using the analysis for.
  • TF / CF: This is the ratio between Trust Flow and Citation Flow. If I’m being really picky with the links I want to include in my analysis, I’ll set this to between 60-80%. Otherwise, I’ll leave it at 0%.
  • External Links: This is the number of external links on the page. I’ll usually leave this blank. Then, when analyzing if I want to build the link, I’ll reference this number. If I’m being strict with links I want to include in my analysis, I’ll set this somewhere between 80 and 300. Note: This will only filter the competitor data and not the client data, so if you want a fair comparison, leave this blank.
  • Type: If you don’t want to include NoFollow links in your analysis, set this filter to Dofollow. I usually leave this blank because I’m interested in what the Nofollow distribution is.
  • Image?: This will include/exclude links from images. I typically leave this blank because I’m interested in the image link distribution.
  • Domain Traffic: This is total traffic to the linking domain. This is an excellent filter to use when you are trying to get a sense of your competitors “best links.” For local client’s I usually will set this to 100 and for national, it depends on the niche (usually between 1k and 10k).

Once you’ve set your filters, they should automatically apply. 

Just to be sure, go to the Google Sheets menu and click Apply filters.
 

Step 9) Copy Your Data And Categorize Your Backlinks

Now that we have our list of linking URLs filtered, go to the filter_client tab. 

Copy the entire dataset (cell A2 to column O) and then paste the data into the Categorize Client tab.

Go to the filter_comp tab.

Copy the entire dataset (cell A2 to column R) and then paste the data into the Categorize Comp tab.

What if you want to add more URLs to the analysis (i.e. you were too strict or loose with your filters)?

Simply reset the filters and then copy the new filtered datasets to both Categorize Comp and Categorize Client tabs. Note: Make sure to set the filters in a way that excludes all the links that are already on the Categorize tabs.

To finish building the analysis, we have to categorize the links and anchor text by type.

We have data validation set up that you can edit on the data_validation tab if you want to use different categorizations. Simply delete or add your own link or anchor type classifications in columns A and B.

We have a trained team member who does this part of the process at about a rate of 60 to 100 URLs per hour. You can use this as a rough guide to determine how many hours you have to dedicate to categorization.

If you want to swipe the standard operating procedure we use to train our team member who does our link categorizations, enter your email below and I’ll send it to you.

How To Categorize Link Type

Before you start categorizing I recommend doing the following:

On Categorize Comp, hide all columns except column H, I, L, and M (i.e. Example Link, Link URL, Link Anchor, and Image Link?). This hides unimportant information and will make categorization go faster.

On Categorize Client, hide all columns except column E, J, K, and (i.e. Example Link, Link URL, Link Anchor, and Image Link?).

First, open the example link (for competitors) or referring page (for your site).

Can you immediately recognize what type of link is going to be on the page (e.g. is it a directory?)?

If not, hit ctrl + F or cmd + F on your keyboard and search for the Link Anchor on the page.

If you can’t find the link using the anchor text, right-click on the page, and click View Page Source. 

Next, hit ctrl + F or cmd + F, copy the root domain from the Link URL column, and then search for it.

If it’s not there, I’ll typically just delete the row, leaving it out of the analysis.

Make sure to keep an eye on the Image Link column. You’ll need to use the View Page Source method to find image links.

Once you are able to determine the link type, click the dropdown and select the correct Link type.

As you start doing this, you’ll notice there are a lot of fringe case things that you’ll see.

When these come up, my best advice is to just make a decision, add that decision to your rules/documentation for this process, and then move on. It doesn’t make sense to spend time worrying about how you are going to categorize one link because you are likely categorizing hundreds or even thousands.

You’ll realize that categorization is a huge bottleneck in this process. Wouldn’t it be nice if you could automate this? Well I’ve got a decent size database of link type by URL (it’s not public… yet). I’m thinking this could be driven by the SEO community. For every link you categorize and submit to our database, you get a credit. For each credit you have, you can request the link categorization of a URL automatically. If you like the sound of this idea, let me know in the comments and I’ll build that functionality and release it!

How To Categorize Anchor Type

I’m not going to go too deep into this because I learned this from Matt Diggity and he’s already written about anchor text categorization.

Remember, with anchor text, take the distributions with a grain of salt. It’s directional, but with any 3rd party SEO tool, you are never getting the complete picture.

If you want to learn more about anchor text, Michal Ugar at Authority Hackers recently did a study I recommend reading here.

Joshua Hardwick at Ahrefs (who is always putting out top notch content) also discusses anchor text in depth in this post.

This sheet uses the same anchor text labels that Matt talks about in his post. Once you understand what each label means, look at the Link Anchor column and then select the right anchor type from the Anchor Type column.

We can automate Anchor Type categorization for you with BigQuery. Hit us up if you want help with that.

Step 10) Derive Insights And Strategy From Your Analysis

Now it’s time to derive insights and strategy from your competitor backlink analysis.

In the Google Sheets menu, click Assisted Reach then Populate summary. When that script is finished, click Assisted Reach then Create charts.

Nice!

You’re done building the analysis.

All that’s left to do is use the analysis to help you build a link building campaign for the site you’re working on.

It’s important you understand HOW to use the analysis so you don’t use a piece of information incorrectly.

It’s not the report that makes a good analysis.

It’s the report plus a deep understanding of the problem you are trying to solve, the constraints that are present, and the dataset.

That’s why I also highly recommend reading this article by Cyrus Shepard. Understanding how Google values links is key and Cyrus does an excellent job laying it all out.

Determining Where To Focus Your Link Building Efforts

The first thing you’ll want to know is the types of links your competitors are building. The Link Type Distribution chart will show you exactly that.


If you’re doing local SEO, the majority of the links are going to be directory links, but maybe your competitors are doing a ton of scholarship, sponsorship, or some other type of local link building.

If you are doing SEO for a startup, you’ll notice a ton of content based links like guest posts and press links.

Regardless of industry, knowing this information will help you determine which links you need to start pursuing.

One To One Competitor Comparisons

Google rolled out an update on June 3, 2019 and in my opinion, the best analysis of it was by Eric Lancheres.

In his analysis, he says, “The big change that occurred during the June Core 2019 update was related to links, and more specifically, domain authority.”

If that’s the case, wouldn’t you want to know how your link profile stacks up to your competitors?

On the Summary tab, choose a competitor from the drop down you want to directly compare your link profile to. We’ll use Robbie Richard since he ranks first for competitor backlink analysis.

You’ll be able to see the number of backlinks by link type and the average Domain Rating for both your competitor and your domain/page.

In the image above, the majority of links are coming from content based articles that aren’t guest posts with an average domain rating of 58.

This tells me I should run an outreach campaign where I’m reaching out to the same and similar sites with a domain rating of ~58 or more.

Let’s be a bit more exact by using the second table and setting the filter to Content Based – Non Guest Post.

This view shows you how many content based non-guest post links fall into each Domain Rating bucket.

It also shows you the anchor type that was used.

In the example above, three of the five competitor links have Target anchor text. There’s one link between DR 31 – 40, one link between DR 71 – 80, and one link between DR 81 – 90.

I like this view because it shows why a set of links may be stronger than another.

Here’s an example of how you could use this.

Let’s say you trying to rank your homepage for the keyword “chicago personal injury lawyer.

You have the same number of guest posts as a competitor who’s outranking you. You notice that most of your guest posts have branded anchors, while your competitors have target anchors.

This hints at the fact that your links are likely coming from your author bio, while your competitors links are contextual.

You could then reach out to the sites your competitor is guest posting on and try to guest post there too.

Anchor Type Distribution

You can use the anchor type distribution chart to compare your anchor profile to the average of your competitors.

Remember, only include anchor text in your analysis if you’re comparing page to page.

I don’t recommend doing this if you are being really strict with your filters. It can be misleading.

When looking at anchor text, don’t base your distribution off one of your competitors. Use an average of ~5 (like Matt says).

If you want to use the anchor type distribution chart below, only run the competitor backlink analysis template for one keyword at a time. That way you can get ~5 competing URLs imported for that keyword.

Speed Up Link Prospecting

Let’s say you are about to begin a guest posting campaign.

Finding prospects is time consuming and tedious. Good thing you have a competitor backlink analysis!

Head over to the Categorize Comp tab.

Filter Link Type for Content Based – Guest Post.

BOOM!

You have a list of sites your competitors already have guest posted on.

Add these sites to your outreach campaign.

Filter Out Links You Already Have

On the Categorize Comp tab, click the Client Has Link filter.

Deselect Yes.

This will remove sites that have already linked to you.
 

Use The Link Intersect Functionality

If more than one of your competitors have links from the same site, it’s a good indicator that you can obtain that link too.

Go to the Categorize Comp tab, filter the Competitors With Link column to be greater than one.

Now you’ll see all the sites that link out to more than one competitor!

DR by UR Buckets

Since link equity is passed at the page level, Ahrefs URL Rating (UR) is a really useful metric to use when analyzing link strength.

My favorite view to look at here is Domain Rating by UR bucket.

You would create one matrix for your competitor, one matrix for your site, and then a third showing the variance between your site and your competitors.

I’m going to be building this as its own tab in the template. If you want me to let you know when it’s finished, drop your email in at the very end of this post.

Utilize The Dataset To Answer Your Own Questions

The true value of this competitor backlink analysis tool is in the datasets that it creates (i.e. Categorize Comp and Categorize Client).

I built some useful views on the Summary tab using these datasets, but that’s really only scratching the surface of what you can do with this data.

If you’ve got some analytics chops, throw the dataset into a pivot table or SQL database like BigQuery. From there, you’ll be able to answer a lot of link building related questions.

Here are a few examples:

  1. What’s the nofollow distribution by domain rating bucket?
  2.  What percentage of pages get X amount of traffic or more?
  3.  What percentage of dofollow links are low DR, but high traffic?

Final Thoughts

The one downside to building this template in Google Sheets, is there’s a point where there’s too much data. I had originally built this with BigQuery so the size of the data wouldn’t be a problem. If you are trying to run a really large analysis and you reach the limit of Google Sheets, hit us up!

If you want us to run one of these for you, we can do that.

I’m also on Clarity if you want to book a call with me to discuss your analysis or anything else data or marketing related!

I’ve got a bunch more of these Google Sheets automations that I’m going to start releasing to the public.

Enter your email below if you want to be the first to know about them!

Scroll to Top