Open Site Explorer and Excel, a link made in heaven

As we all know, SEOMoz has recently launched its latest toy, an Open Site Explorer. I am going to assume that you have been playing around with it for a bit now. I will also recommend you to read SEOGadget’s post with some tips and ideas.

But I am here to tell you how Open Site Explorer and Microsoft Excel combined can help you when researching links against your competitors.

Let’s use the term “shoes”. First of all, let’s see how the top 10 compare in terms of external links. To do this, I will run all the URLs in Open Site Explorer.

And then filter all links to show only Followed links from external pages to this page.

And then export it to CSV. You will get a nice excel file with all the links (up to 10000, but not many pages will exceed that number) that linkscape think are relevant. Cool. Let’s do that 10 times, with all the URLs in the top 10.

So now that you have done this for the 10 URLs, your computer may look a bit crazy with all those 10 windows open. In one of the spreadsheets add a column named “Destination URL”. In that column you will have to include the URL that those links pertain to. Like this:

Do that for all the sites, and move all the data under one same spreadsheet. Add a filter in the header, order it by Page Authority. You can also add colour for each of the sites, this will make it easier to see things at first sight. At the end you will have something like this:

That looks interesting but let’s filter it so it only shows links with “shoes” in the anchor text.

That looks even more interesting. Here is where you should think how you can use this data. Here are a couple of ideas.

Do the links correlate to rankings?
Pivot tables are great. I don’t know much about them, but I really like them, I wish I knew more. You can create a pivot table to count how many links each site. I only took those links with page authority of 15 of greater, and I made a nice graph out of it:

Look at that! Magic. The first few positions seem to correlate to the links. Office is first, Faith second. It does seem to be not as accurate when the number of links are similar, Clarks is third in links, but sixth in rankings…

What have we learnt? Looking at the number of links can be good if you only want a general overview of the situation. It can give you guidelines, but not certainties. If I was any of the competitors I would imagine that having around 100 links with “shoes” in the anchor text would probably give me a spot on second or third position.

Where can I find new site that may want to link me?
I am glad you ask. Let’s say that you manage office.co.uk. To filter the links so you only see the ones that are linking to competitors do the following. Order by Destination URL (A to Z) and cut/paste all the links pointing to your site into a new sheet (Sheet2). You will have to go back to the sheet with all competitor links and create a column named “linking me”; in that column you will have to paste the following formula: “=COUNTIF(Sheet2!B:B, B2)>0”. That will work as long as the URLs are in column B.

That formula will return whether that URL is linking to you too. If the result is FALSE, then they are not linking to you, and you can approach them.
In this example, if you were managing office.co.uk, there are 554 URLs that are linking to your competitors and could potentially link to you too!

Conclusion
These are just two examples of hundreds where a bit of analysis with Open Site Explorer and a bit of Excel can help your campaign. It will give you a good snapshot of what your competitors are doing, where are they successes and their failures. Do some research before planning your campaign!

How useful did you find this post? What data did you get out of OSE and Excel?