So it’s no secret that I love Excel. Don’t worry though – you don’t need to be an Excel wizard to use it to your advantage – especially when link building. So here is my 3 easy ways to use Excel to improve your link building. Using Excel for Link Building: Monitor your success I […]
So it’s no secret that I love Excel. Don’t worry though – you don’t need to be an Excel wizard to use it to your advantage – especially when link building. So here is my 3 easy ways to use Excel to improve your link building.
Using Excel for Link Building: Monitor your success
I personally like to keep track of all my outreach in an Excel spreadsheet but I also love the feedback you get from BuzzStream about your success rates and reply percentage.
This is something that can easily be replicated in Excel – but does (unfortunately) have to be something you input yourself. This is something that I automatically do anyway so it doesn’t really bother me. So here’s some ways that I keep track of my link building success.
So let’s assume you have a table along the lines of this one. Hopefully you’ve done more outreach than this but I struggled to think up anymore websites – so four it is!
This is the kind of thing I normally use – but with a couple of things added like ‘Type of Link Building’ and ‘Type of contact’. These will come in handy though and it is definitely worth keeping them so you can find great things out about your success.
Say we wanted to find out our general link success rate and our broken link building success rate. We can do both of these just using the COUNTIF and COUNTIFS function. Like so:
Which pretty much means ‘count all the cells in the ‘Link’ column that say ‘yes’ and divide that number by the number of cells in the ‘Status’ column that say ‘contacted’. I have then formatted the cell as a percentage.
This uses the COUNTIFS function too so it means something along the lines of ‘count all the cells in the ‘Type of Link Building’ column that say ‘Broken’ and divide this number by the number of cells that have both ‘Broken’ in the ‘Type of Link Building’ column and ‘yes’ in the ‘Link’ column.
So these will end up looking like this:
If only this were true of my own success rates! You can shuffle the equations around all you like to find out which method is working best for you – why not try adding new columns allowing you to track even more data. Why not find out whether your success rate is higher from contact forms or direct email, or whether aiming for the webmaster of a site ends with better results.
Find Broken Links in Excel
This is something I first read about in Fabio Ricotta’s post. It’s one of those things that before you know about it, you would never have expected that you would be able to find out so easily.
So here we go. The idea is that you use Opensiteexplorer.org to grab a bunch of info about your site
and then use Excel to whittle it down.
Start by running a competitor’s site (or any related site) through Open Site Explorer and select the ‘Top Pages’ tab and then export the data to CSV.
Make this data into a table. Now the key to this one is the HTTP Status column where you can Filter everything out but the 404s. Like so:
This will give you a list of all your competitor’s 404s – which you can run through Open Site Explorer again to give you a list of pages with broken links to your competitor’s site.
Link building tip number three: Pivot Tables
Pivot tables were once a total mystery to me but now (thanks to Annie Cushing’s amazing post) I’ve learnt how to use them to give me a clearer picture of competitor back links. I have watched a lot of Annie’s videos so here’s a quick summary of her wizardry.
First you need to identify your competitors, run them through Open Site Explorer and export your results to CSV and you just need to have these open on your desktop.
So here’s a screen shot of the spreadsheet I’m going to make my pivot table in. I’ve imported the back link data from my first competitor – abraisnofun.com.
As you can see I’ve added two more columns to the beginning of the table and formatted that data as a table. This is necessary to make the pivot table that I want to show you. In the domain column you want to strip just the domain from the URLs and the site column will just be the name of the site the link is from.
So first we need to fill the domain column. So can do this really easily with the LEFT and SEARCH functions (don’t worry if you have no idea what I’m talking about). In the domain column I have written:
Where C10 is the cell that my URL is in – so the cell next door.
This equation pretty much means ‘from the left of cell C10 search for a forward slash in cell C10 but ignore the first 9 characters’. If the first 9 characters were not ignored then it would find the first “/” and stop – which would be bad.
Now all you have to do is enter the site that those particular back links are from in the ‘site’ column and double click the right corner to make it apply to the rest of the data. So you should have something that looks like this:
Now paste your next competitor’s back links directly underneath this table. The table should expand to include the links and your domains should be automatically processed too. All you need to do is add to the ‘Site’ column for each one.
You will now have one big table with all the data from all your competitors in it. Now it’s time to make your pivot table!
A pop-up box will appear. It will usually automatically select your table and suggest you open it in a new worksheet. When you click ‘OK’ you will be presented with a new worksheet with an empty table on the left and your Field List on the right.
In this order select:
– Page Authority
– Domain Authority
Selecting Page Authority and Domain Authority right after Domain is important otherwise the table may not work properly. Before you run away to be amazed by the table you must also change the settings of your values.
Now I don’t like how it looks so under the new PivotTable Tools Design tab select Report Layout and choose Outline Form – it’s a lot easier to use!
Now it’s time to organise your table. So up the top of the Domain Authority Column, right click and select Sort, Sort Largest to Smallest.
This will sort your table so the sites with the highest Domain Authority will appear first. Now you can organise Page Authority too.
Choose a page somewhere in the middle where the page authorities differ and do the same again. This will mean that while each URL is ranked primarily by its Domain Authority, if the site has more than one link from more than one page – these pages will be ranked with the most desirable first.
Now we can have a look at the data.
From this we can see that excelisboring.co.uk has 5 links from www.amazinguniversity.ac.uk and that linklocation4 is the most desirable opportunity from that site.
We can also see that all three of our competitors have links from the same page on www.charity.org.
This table is not only a great observational tool but one you can work with too. I would usually now go through each domain and highlight those that I want to attempt to gain a link from. I will always use this instead of the raw data because you can so easily scroll through and see the domains that a number of your competitors have links from! As well as usefully marking out the best page out of a selection to try to secure a link from. If you want to learn more about knowing what makes a good link then here is a great post about link building considerations.
If you have any other Excel or link building tips then I would really love to hear them!