How to Visualize Competitor Link Profiles in Excel

Today’s guest post comes from one of our favorite customers, Nick Bernard, off-site SEO Strategist at Portent.  You can follow Nick on Twitter or Google Plus.

With rumors of another, scarier Penguin update looming on the horizon, how can link builders make sure we’re building a site’s link profile holistically and effectively? Where are competitors gaining the upper hand or, perhaps more importantly, cutting corners?

Identifying the kinds of links a site needs—and the ones of should be wary of—should be the cornerstone of any link building strategy. An easy way to do this is by visualizing the link profiles with PivotCharts in Excel.

Gather and Format the Link Data

First, you’ll need to pull the link data from your favorite source. I’m using Open Site Explorer but Majestic SEO or, if you have access, Google Webmaster Tools will work just fine. In OSE, set up the filter to only show the followed and 301-redirected external links to the root domain.

filtering links in open site explorer

Once you’ve got the link data in Excel, copy the URL and Domain Authority columns and paste them in a new worksheet. If you’re using a different data source, choose a similar metric. At this point I always like to put the data in a table to make it easier to work with. Select all the cells with data, click “Insert,” and then “Table.”

Since this is a high level analysis, I’m just interested in the linking root domains. I’ll clean up the URL column with the Find and Replace feature.



Enter “http://” (without the quotation marks) into the find field, leave the replace field blank, and click “Replace All.” Do this again with “www.” and “/*” to strip off everything but the root domain. Take care to do these three steps in order.

Next, remove the duplicates from the URL column.

remove duplicates

Finally, insert a column and fill it in with the site’s name.


Bam! Now you have a list of all the site’s linking root domains and their DA.

Repeat these steps on one or more of your competitors’ sites and add the data to this table with a different entry in the Name column.

Analyze the Data with a PivotChart

With all the data compiled, you can use a PivotChart to compare the sites. Click any cell in the table, and then select “PivotChart” from the “PivotTable” dropdown menu. This should automatically select the entire table as the data source.


Now comes the fun part. Drag each data field to the PivotTable areas shown below. The Name field will make up the separate series or colors in the PivotChart, the DA field will be the spectrum that each site is visualized across, and the URL field will count the number of linking root domains with each DA value.


You’ll end up with a chart like this:


To make this a little more legible, you can group the DA values together. Right click on any cell in the left column of the PivotTable and select “Group.”

Start at 0, end at 100, and enter the interval at which you want the DA separated. An interval set at 25 produces four levels of Domain Authority. Depending on the data, a smaller or larger interval might be easier to see the contrast between each site.


Now, you’ll have a nice, clean visualization of link profiles across different levels of linking root domain authority.


Put the Visualization to Use

So what? How can this help our link building efforts?

Most importantly, it can show the types of link opportunities to target. In the example above, Site B is way ahead in the middle distribution of DA. Without even looking at the specific backlinks, this range suggests they have a strong presence on smaller industry or personal blogs, local organizations, or niche directories. To see the exact URLs for that range, just go back to the first data table, filter the Name column to only show Site B, and then filter the DA column to display rows between 30 and 60. If I’m a competitor, I now have a very specific group of sites to target.

If I’m working on a site that competes with Site C and they’re inexplicably edging me out on some specific queries, I could filter the raw data to show the low range of DA and take a look at the anchor text for those incoming links. This would give me a starting point on opportunities to diversify my link profile.

On the other hand, of course, this could be a huge red flag for Site C. The low DA range would be the first place to look for dangerous links to get removed. If Site C did get penalized, I could examine the links in that range to ensure my site’s links don’t overlap.

The chart can also indicate a site’s health in other areas of SEO outside of link building. If your site is level with your competitors across the spectrum, yet you’re getting pummeled in the rankings, there are obviously some other issues to remedy—either in overall link quality or onsite problems.

Illuminate Opportunities and Time Bombs in a Link Profile

Staring at lists of 10,000 URLs is an inefficient and boring way of evaluating link profiles. By visualizing them, you’ll be able to easily spot opportunities from competitors and, potentially, identify problems. Knowing exactly which links to target and why is a much more effective strategy than acquiring links blindly—and it will probably save you time and grief down the road.

How do you indentify the types of links your site needs? What’s your favorite way of visualizing link profiles?


  • Spook SEO

    Hey Nick great post!

    Thanks for detailing your process. I’ve been using a different one but your seems like a better way of visualizing the data.

    Thanks for the post.

  • Thanks for the guide. But is there a way/formula to carry out the first three steps of striping out “http://www.” in one simple step?

  • Hi Liang,

    Matt is exactly right. The different ways of handling a domain name and the use of subdomains prevent an easy fix.

    One other way is to use the “Text to Columns” feature in the Data tab of Excel. If you select the column with the URLs and select a period (.) as the “delimiter” of the data, it will separate the cell into different columns at each period. Of course, you’ll run into the same issue because of the subdomains, but it might be helpful for you in another context.

    Thanks for your comment!



  • Hi Liang,
    I’ve asked Nick to respond to this, but he is on vacation, so let me give it a try.

    Because some domains will have the http://, and some will have http://www., some will have trailing slashes and some won’t, it won’t be possible unless you use a macro. (Which gets us much further away from being simple.)

  • Very useful, ill go try this out myself. Thanks for sharing 🙂

  • Cool stuff, I always enjoy a good excel trick in link profile analysis like this one. PivotCharts are extremely cool for visualization, and its essential for getting actionable information.

    I will probably create a pivot chart of the top 5 competitors for each of my keywords and compile them in a master excel sheet. This will show me trends across a range of keywords and I can adjust my link building strategy accordingly.

    It’s also worth getting anchor text distribution charts from opensiteexplorer to see what works for your competitors.


  • Hey Nick

    I’ve just followed your step by step guide and wow – the data visualisation really helps. For my keyword phrase in question, I’ve compared the link profiles of the site’s ranking number 1, 2 and 5. The difference in amount of links and DA between the top 2 and 5 is pretty big. From this I can see that it will be much easier to achieve a position of around 5 than it would be to achieve a top 2 position.

    I’m looking forward to delving further into the data and using this method again

    Thanks a lot for sharing this 🙂


2 pingbacks