I do quite a lot of link analysis, and I might be a bit late to the party with this one.
My own approach to link analysis is stripping various link data sources down to the lowest common denominator and building up from there. In most cases, since I’m using Webmaster Tools data, so this means I’m starting from the links alone. This is a mistake.
If you’re agency side, or replacing the previous in-house manager you probably won’t have a crystal clear picture of tactics used in the past. The problem (or future problem) in spotting potentially harmful implementations of tactics is that linkbuilders have become more conservative in their approach to anchor text, especially in large scale (widgets, embeds and templates) applications. I have to remind myself to look closely at those innocent looking {brand|www.brand.com} links, as well as the really obviously SEO’d stuff. It’s worth taking 10 minutes to look at this. The good thing about Template, Embed and Widget links is that they use the same surrounding text (as do articles and comments that have forgotten to spin). Ahrefs data gives us the surrounding the pre-anchor and post-anchor text. We can use this to quickly get a grasp on a few of the less immediately obvious parts of a link profile.
Working With The Data
Ideally you’ll want to count occurrences of each unique string of text within your data. Some methods of looking at this won’t be feasible if the data set is too large (e.g. Pivot Table). If so just order A-Z and work by eye. I like to use =COUNTIF and then order largest to smallest, but it’s very inefficient and you probably shouldn’t do it:
Ordering the data (and provided the site has some template spam) you should spot something that looks a little like this:
If not, congratulations(?). If so, pop that string into Google along with the anchor text used to see how far it’s propagated. Use this information wisely. Should all the other tools include this data?
I have discovered a truly remarkable method which such a blog post is too small to contain.