Visualising & Understanding Where You Need To Build Links

If you’re anything like me you will have been in a position hundreds of times, where you a trying to rank for a given keyword and no matter what you do, you cannot break into that top 5 or hit that first position.

Part of you says ‘just keep going & build more links’, however the truth more often than not is that you simply aren’t building the right type of links.

I thought it would be a good idea to run through how I Β like to analyse link data and establish where I need to build links.

So, I want to incorporate a little open site explorer, excel, pivot tables and vlookup. I’ll try and make it as straight forward as possible for anyone that isn’t that familiar with excel.

Collecting The Links

For the purposes of this example I have chosen 2 sites that rank in the top 5 for mobile phones, I am not going to reveal the sites as I can’t be bothered dealing with complaints, businesses spend hundreds of thousands on SEO but they don’t want anyone to know πŸ™‚

Anyway head over to Open Site Explorer:

opensite settings

Filter out nofollowed and internal links then down load your links into excel where we can begin to play around with them.

links in excel

Organising The Links

OK now we have all the data together there are only a couple of columns I want to use for the purposes of this example, Unique Domains and Domain Authority.

I prefer to use domain authority rather than page authority because more and more often I find this to be the most influential factor.

So…. to get the unique domains you will have to perform a ‘text to columns’ on the URL column, copy and paste it into another sheet otherwise things will get messy.

text to columns

Once you have a long list of domains, input them back into the main sheet next to the url column and use the data & remove duplicates option to have a 100% unique list of links.

Add the Pivot

So you have the columns you need, select all and insert a pivot table.

pivot info

Add domains into the values area (ensure it is set to count) and domain authority into the row labels area, this will display the data like so;

pivot chart

Simply add a chart if you want to visualise what you have put together which is literally a domain authority scale and the number of domains for each authority figure.

Once you have done all this for your site, you now need to perform exactly the same for the competitor ranking where you want to be.

Putting it together

So you should have 2 pivot tables, yours and your competitor’s, open a new sheet and add numbers 1 – 100 in column A (this is going to be where you combine the two tables).

comparison structure

In a separate sheet you need to add the two pivot tables side by side (same work sheet as the above structure), however simply copy and paste the figures not the 2 tables as we are going to perform a vlookup to put it all together.

comparison pivots

Now in the new 1-100 table you have created you need to use vlookup to transfer the data for each site across, see the below vlookup query I performed, I don’t want to go through vlookup here, Richard Baxter is does a far better job than I could ever do so read his here.

vlookup table

Right, you have performed the vlookup and have your two sites side by side, you can now play around with the data and begin to understand what types of domains you are missing from your profile.

comparison chart

Or you could spice it up a little using an area chart πŸ™‚

comparison area

Well not really spiced up but you get my point…

Surely Just Building Links from Top Quality Domains Will Eventually Put Me At The Top????

Don’t fall into this trap, if you just go after the big fish all the time you will lose for 2 reasons…

A – They are harder to get so you will never get the quantity required

B – Since when did a link profile with PR5 links only look natural??

Be smart and build your links from the right places.

I just wanted to thank everyone for their support on the Best UK Online Marketing Blog Poll, I have no idea how I got to the top but I am flattered at the amount of support. If you haven’t had chance to vote yet I would really appreciate it πŸ™‚

Thanks to all!


is the CEO of Branded3 a Search Marketing Agency in the UK. Tim has over a decade of experience in Search and regularly speaks at key events and conferences.

10 thoughts on “Visualising & Understanding Where You Need To Build Links

  1. Congratulations on coming out top on the poll. I think people recognise authenticity when they see it.

    And well Tim, I put in a vote for you – so that explains it πŸ™‚

    Because I am only looking after my own site, Opensiteexplorer/SEOMoz is a bit pricey for me. Of course it would be wonderful if it would just go out and create the backlinks as well….

    I was looking at MajesticSEO and wonder what your thoughts are on Majestic and OpenSite compare for the kind of things a person in my position needs?

    I looked at SheerSEO as well, but unless I didn’t understand it properly, it seems to produce information on one site only.

  2. Thanks David, appreciate the vote and comments πŸ™‚

    I agree for one or two sites SEOmoz isn’t worth it, however I am sure open site explorer lets you export links into excel,, you just cannot see them within the tool.

    To be honest most of the tools including Majestic is not really a personal tool, I used to use SEO elite back in the day but I’m not sure how that performs now.

    Suggestions anyone?

  3. I’m a bit confused, I’ve used OSE but have never delved into excel (this deep)… how does this help me determine what domains I need links from? Simply copy my competitor based on the chart? ie. if they have a higher domain authority on site X… then I should be trying to obtain a link there as well?

  4. Hi Sandy,

    Sorry, the excel stuff is never easy to explain in a blog post.

    This comparison helps you establish where your link profile is lacking, so if you may have more links than your competitor with domain authority between 15 – 40, however building more links in this range will not help you rank higher, you need to see where your competitors are out linking you in order to jump them. This gives you a very easy way of visualising this and implementing a link building strategy going forward.

  5. Hi Tim, great post! I will be sure to to vote for you πŸ˜‰

    I tried this out on some of my sites to see where I needed to focus my link building efforts and I got back back some interesting results!

    Although time will only tell if I will rank better because “as I’m sure you’ll agree” if Google sees a mass of a certain range of links being built, they will think “hang on! This isn’t natural! Bye Bye!”

    Does this mean you think Google will judge the “range” of links you should build based on the highest ranking sites for a particular term or is it just a race to build more links than a competing website?

    I’m sure Googles algorithm is more complex than this taking other factors into consideration.


  6. Hi Steve,

    Thanks for the vote, appreciate it.

    I personally think Google likes to see a nicely balanced link profile, so you may have a heavy link profile between 20 – 50 but your competitors have more links at the higher end, like wise you may have a lot of high profile links but your competitors are making far more noise at the lower end. When we build links we try and keep the above link chart as flat as possible building links from diverse domain authority not just diverse domains.

    No site only earns high end links and good sites don’t only earn article links, you have to get the mix right and performing this type research helps you understand where you’re lacking in comparison to those you want to beat.

  7. This approach seems to have begun to work for us. We have been using tools such as as a means to get a rough idea of where our main rivals sites are advertised from. From here, we can then get an idea of how well ranked (page rank) these websites are and how many links they have going to their website.

    Over the past few months, we have seen an increase in our traffic levels and also our rankings.

  8. Hey Tim,
    Great post, just had a couple of questions.

    1) wouldn’t it be better to look at page authority of incoming links as oppose to domain authority, since I could have a link on a DA 60 but if it has low PA than it’s not really helping me too much

    2) If I’m building links on a site wide basis to more pages than say just my homepage would there be any merit to looking at the backlink profile of my whole site vs my competitors whole site or does this kind of analysis work better on a page by page basis?


  9. Hi Mike,

    I think it’s important to analyse the whole site as well as on a page by page basis. However, if you’re going head to head with a page you need to understand firstly what that page has going for it. Content, links, internal links. Then look at how the overall domain profile could be helping.

    In terms of page authority over domain authority, I don’t necessarily agree. If you have a link on a really strong domain, but the page is fresh and has no PR, I think that is more powerful than placing a link on an aged page with PR. I think Google places more weight on links that come from new pages, if you’re link is there at the point of inception then it seems to be a signal of trust. Links added after a page has been crawled seem to carry less value.

    So looking at the domain authority of incoming links gives you a better idea of the type of websites your competitors are able to engage with. I am not saying you shouldn’t do this based on page authority, I guess personally I am more interested in the quality of sites my competitors have links on rather than just pages.

Comments are closed.