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.

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.

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?