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:
Filter out nofollowed and internal links then down load your links into excel where we can begin to play around with them.
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.
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.
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;
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).
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.
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.
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.
Or you could spice it up a little using an area chart
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!Line Break
Author: Tim (256 Articles)
Tim Grice is the owner and editor of SEO wizz and has been involved in the search engine marketing industry for over 9 years. He has worked with multiple businesses across many verticals, creating and implementing search marketing strategies for companies in the UK, US and across Europe. Tim is also the Director of Search at Branded3, a Digital Marketing & SEO Agency based in the UK.