Categorising Your Links with Excel and Open Site Explorer

Having an understanding of the link profile data associated with a website has always been key to understanding the likely competitiveness of the site in organic search. With some luck, this post will help you discover a way to determine the types of links in yours, or your clients back link profiles.
Link data deep diving

For me, there are a few key metrics at play – linking C blocks, quality metrics such as page authority, domain authority and of course, anchor text. There are so many tools available that I probably barely need to mention the fact that Open Site Explorer, Majestic SEO are as critical to link analysis as oxygen is to breathing.

Because of such tools, it’s far easier to understand how many links you have and their overall quality, it’s still pretty tough to pin down what type of links you have. I’m talking about directories, “quality” article websites, forums, “academic” links and so on.

Today I’m going to share a snippet of my link assessment methodology – a method to help you determine what type of links might be lurking in the back link profile of your latest client acquisition.
Background reading

Before you get started, you’ll need some background reading. I’ve already covered the Excel skills you’ll need to assimilate over on SEOmoz, so if you missed out, skip over to “Keyword Research – Using Categories to Make Your Process More Actionable“, have a play around with arrays and the categorisation query and head back here for the rest of this post.


How to categorise your back links by link type

What type of links do you have? What category of websites might be out there, linking to you? You can’t categorise them all, but, you can have fun trying to get a rough idea. Check out this beauty – a simple dashboard counting the types of links to an example website I created shortly after the first US Panda update.



If your site has a massive directory submission footprint, or has a very large number of links from article sites such as ezinearticles.com, you’ll be able to see what’s what in this handy dashboard view.
How to get started

To get started, you’ll need to download the file at the bottom of this blog post, and of course, fetch the “linking pages” data from your favourite website via Open Site Explorer:



Then, simply open the file and paste your data in the top right hand cell of the “OSE Data” tab:



Finally, head to the “DashBoard” tab, and select: “Data > Refresh”



As soon as you click refresh, the pivot tables will all update and you’ll get a gorgeous, infographic style report on your back link profile.
How do I drill down on these links?

Being able to “see”, visually, the make up of your back link profile is great to a point – but it’s nice to be able to drill down and get to the data. In the “DashBoard” pivot table view, simply double click a value (see right: “Directory”). The double click action will open a new tab with only the directory links identified.
How does this work (roughly) and how can I improve it?

This particular version of the spreadsheet is ready to be extended to meet whatever purpose you see fit. Essentially, the formula in the spreadsheet checks each row in the “URL” column in the “OSE Data” to see if it matches with any of the domains in the “Domains” tab. To see the domains that have been included, right mouse click the tabs and unhide the “Domains” sheet.




The “Panda Winners” data is calculated from the original Sistrix data set on the topic, while the free directory list is available on the Directory Maximiser website. It’s easy to update the lists or rework them as you see fit. For example, you may wish to extend the directories based on your own data, or include blog links you’ve built to look at crossover between your own, and other’s link building strategies.
Anchor Text Distribution

Scroll down to the bottom of the dashboard to see your top 10 anchor text term distribution. As a side note, we have a Linkscape powered anchor text tool here – give it a whirl!


Ok, this is awesome – where can I download this spreadsheet?

Download the spreadsheet here – if you find it useful, or have suggestions, modifications and improvements, feel free to add your comments and downloads right here. The best files will get listed on this blog post with a link back to your site. Happy link data deep diving!
Here’s one I made earlier

I couldn’t resist one last demo – click the image for the full sized version