Categorising Your Links with Excel and Open Site Explorer

deep diving - gettit? - did I go too far with the duck thing?

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.

An image from the keyword categorisation 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.

small excel links dashboard

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:

open site explorer

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

Excel

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

Excel-data-tab

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.

unhide
domain list

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!

distribution of anchor text

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

the finished item

Image credits:
spacepleb

Categorising Your Links with Excel and Open Site Explorer, 4.5 out of 5 based on 2 ratings

Comments

  1. Mike van der Heijden

    Excellent article, I have been doing similar analysis using the Open Site Explorer data, but the downloadable spreadsheets should be very useful for people not as advanced with excel!

  2. Danny Penrose

    Great post Richard, very useful indeed. I’m a big fan of keeping things organized and tidy so this spreadsheet is going to go down a treat.

  3. Jerry Okorie

    I love the caption “Link data deep diving”, this is exactly what I’ve done with the spreadsheet. It’s great and my team loves it too..Thanks for sharing

  4. David Ewing

    Richard, thank you for the download!! As more a newbie with excel where would you look for either templates like the one you gave or learning resources.

  5. Donna Fontenot

    Tried this but quickly realized my little free version of Excel Starter doesn’t have a Data tab, so I can’t refresh the data (or if I can, I couldn’t figure out how).

    Now I’m sad. :(

    Maybe I’ll pony up the cash to upgrade. maybe…

  6. Steve Lock

    Very generous of you to give this away as a download. Will definitely be playing with this for a while!

    Unfortunately I am stuck with Open Office right now. Doh!
    :-(

  7. TenderMay

    Thank you for sharing this great spreadsheet! One note (probably, not very important ;)), there are some duplicates within your list of directories. But of course it doesn’t cause any faults. Cheers.

  8. LFMAG

    Great spreadsheet…we work with another one but you gave me some good ideas to implement in our own excel. Thanks for sharing Richard :)

  9. Matt

    Great post & thanks for sharing.

    Not sure why, but my graphs are not being created by seomoz data added? Not even anchor text pie chart. hmm…

  10. richardbaxterseo Post author

    Well, now you don’t have to! And of course feel welcome to develop the idea, add new domains and categories and send it back so we can share with the rest of the community. Thanks Brian!

  11. TenderMay

    SEOmoz have added a new column to their reports called ‘Number of Domains Linking to Domain’. So, importing data from OSE don’t forget to add the same column to the link-categoriser. Also, if you want to extend, for example, the list of directories, edit formulas before that. ;) Hope it’s useful. Thanks one more time for the link-categoriser.

  12. Cheryl

    Does anyone know how to get opensite explorer to update the number of links have been staying the same number now for over a month

  13. Amanda

    I have to agree with Matt – in Excel for Mac (at least that’s what I have) the data in the “Dashboard” pie chart isn’t being populated – and I don’t know enough about Excel to monkey around and fix it. Any thoughts?

  14. Brian Crouch

    Hi Richard,

    Thanks so much for sharing this valuable resource for free. I’ve noticed that the new OSE inbound links report contains an extra column: “Number of Links” (F) which is not within this spreadsheet. Easy to deal with, I simply deleted it before pasting the CSV into columns A-J.

    Cheers, and see you at Mozcon!

    Amanda, is it possible that when you pasted the new data, you overwrote columns K-Q? And did you click Data -> Refresh?

  15. JDIZM

    I’m a first timer on SEO Gadget, and what a resourceful page to land on. Nice link analysis & spreadsheet!

  16. justin

    Great article. Unfortunately, I’m getting the same problem as Amanda with the chart not refreshing, even after deleting the number of links column and making sure columns A-J are correct

  17. Sander

    Hi,

    Im’just a beginner at organizing data with Excel. The sheet works really great except i can’t seem to expand the list of blog networks. It does not get taken into the formula like the directory column. When i change the reach from F7 to wider range by hand, the whole formula breaks and doesn’t return the right values anymore.

    how can i fix this, or expand these lists without the formula breaking. I’d love to expand this file with social links 2, which will work kind of the samen but then the formula has to work.

  18. @RFASEOPM

    Hello Richard,

    First off, great tool. Extremely practical and visually powerful.

    I do have a few questions:

    With OSE changes since the first iteration of your article, should we filter data as follow: http://screencast.com/t/molhCaVXo

    and when stripping the csv, should we remove the following column: http://screencast.com/t/P6yrSab3vsi

    Finally, how do you categorize paid directories? When looking at the hidden tab, it seems only free dir are included.

    Thank you for your response and again, thanks for the great tool.

    Sincerely,

    Richard

    ps. there is a potential typo: http://screencast.com/t/ufKDdm2T