Using Pivot Charts to visually analyse competitor link profiles

  • 0
  • March 29, 2010
Patrick Altoft

Patrick Altoft

Director of Strategy

Analysing the link profile of your website and comparing it to your competitors is a fundamental part of any link-building strategy. The normal method is to download all of your competitors links and try to replicate them but sometimes you need to take things further and do some data analysis before you start work.

A link profile is the single biggest SEO asset that a site can have and yet not many people stop to think about what their current profile looks like before trying to improve it.

Link Profile

Building links is time consuming and trying to replicate your competitors 10,000 links will probably take years. The key to success is to figure out where they are beating you in terms of authority and fix that first. If a competitor has a few trusted links then you won’t beat them by going after a load of links from low quality sites.

I’m a big fan of Open Site Explorer and we use this (and the other SEOmoz tools) a lot in our link analysis but only as a source of data, the actual analysis part is done in Excel using the power of the pivot table.

One of my favourite ways to visualise a link profile is to create a pivot chart showing the quality distribution of links across whatever metric you choose. The chart above uses Domain Authority from SEOmoz (a scale of 0 to 100 with the most authoritative sites having a domain authority of 100) to compare the link profiles of Blogstorm, SEO Book and SEOmoz.

The same analysis can be performed with metrics such as PageRank, mozRank, mozTrust. One thing to remember is that Domain Authority has a slight flaw (which hopefully will be fixed soon) because it treats sites as sub-domains of and also treats blogs on & sub-domains the same authority as the root domain.

Comparing your link profile against the top 10 competitors in your industry on metrics such as mozTrust and mozRank shows you where your site is performing badly – perhaps you have loads of trusted links but very few anchor text links from low trust type sites. Perhaps you loads of low trust links but no links from really top domains.

Once you have interpreted the data you can start to put things right.


The first step is to download all the links from the domains you want to analyse. Open Site Explorer is good for this but you can use another tool if you prefer as long as you can populate a column with the metric you want to analyse.

Import all the links into the same sheet and add a final column called “Domain” which is the name of your site and your competitors sites so that you can identify the links to each.

Next you need to highlight the entire sheet and create a Pivot Table with the following fields:

Pivot Table

Finally highlight the data and click on the Pivot Chart button in the options menu and you’re done.

Update: The chart above is a stacked area chart which is perhaps slightly misleading unless you are used to them, below is a non-stacked area chart which shows that the link distributions are quite similar.

Free of charge. Unsubscribe anytime.