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.

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



Stay Updated: Sign Up for Webinar & New Blog Alerts

45 thoughts on “Categorising Your Links with Excel and Open Site Explorer

  1. 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 says:

    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 says:

    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 says:

    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. Greg Shuey says:

    Thanks for sharing your spreadsheet… definitely makes my life much easier!

  6. 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…

  7. Jenny Lynn says:

    Great Article. Thanks for sharing.

  8. Wow David, great work – I can see I’m going to spend a few days before the end-of-month reporting playing with this

  9. @Donna – you definitely need Excel in your life! I couldn’t live without it. Google docs is ok too though – but Excel is killer features wise.

  10. Danny Penrose says:

    Actually Richard, I think you look like a David to be fair. LOL

  11. Steve Lock says:

    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!

    :-(

  12. TenderMay says:

    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.

  13. Oh – thanks for that. My full list is closer to 7,000 – I imagine there are a few more duplicates there!

    Richard

  14. LFMAG says:

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

  15. Matt says:

    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…

  16. olleck says:

    THX for the spreadsheet. I will test ist…

  17. Yatin Mulay says:

    Just tested this. Works like a charm! Thanks a lot Richard. Stumbled & Tweeted.

  18. This is a very slick application. I would buy something like this.

  19. 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!

  20. Wool Overs says:

    Woah, great Spreadsheet, will definately be using this in the future!

    Your a Sreadsheet Ninja!

  21. Great post. Have been using OSE a lot lately, especially to compare competitor links.

  22. Arbtech UK says:

    Absolutely awesome article. For laypersons this is an incredible resource.

  23. Damon Ebanks says:

    This is a really nice article. I thank you for the time and effort put into it.

  24. Aman Talwar says:

    Great article, thanking for sharing the info. Looking forward to your webinar with seomoz.

  25. TenderMay says:

    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.

  26. GrumpyOldSEO says:

    Looks like it could be a great tool. Sadly I’m one of the dinosaurs with an out-dated version of Excel :-(

    What’s the minimum version required for this tool?

  27. David says:

    Thanks, should come in useful

  28. Cheryl says:

    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

  29. Amanda says:

    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?

  30. Brian Crouch says:

    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?

  31. JDIZM says:

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

  32. justin says:

    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

  33. justin says:

    ^ on Mac Excel 2011. Anyone else getting the same problem?

  34. Derek Jansen says:

    Great use of Excel Richard, especially in these “link hypersensitive” times we live in :) Keep the Excel tools coming.

  35. Thanks a lot Derek!

  36. Totally awesome! This is a fantastic tool. What a great way to do a website analysis.

  37. Is it possible to get a more updated version? I see the spreadsheet was 2011 and a lot has changed since then?

  38. Richard says:

    Nothing has changed….

  39. Sander says:

    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.

  40. @RFASEOPM says:

    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

  41. Hi Sander,

    had exactly the same issue this afternoon and drove me mad, however the solution is simple the formulas use arrays. This means if you update the ranges you need to remember to press Ctrl + Shift + Enter

    see link for an over view:

    http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx

  42. Thanks for a great article. I am up on your theory.

Comments are closed.