Hopefully if you are reading this then you’ve already read last month’s part one. If you haven’t already read it then I’d suggest doing so or this won’t make a great deal of sense! In part one I went through some basics such as collecting data from tools such as Open Site Explorer and Majestic; moving on to explain how you can pull in domain page rank using SEO Tools for Excel and checking whether or not your links are live with the custom filters in Screaming Frog. The idea behind part one was to help you to get to the point at which you can quickly filter through your data and identify links that might be worth considering for removal.
But what if you want to go one step further and pull in even more data? Perhaps you want to identify links that could be on a network of sites, or maybe you’d like to sort your link profile by number of links per domain or by overcooked money term anchor text. To do the above you’ll need Excel and an install of SEO Tools for Excel. That’s all you need! The end result should be a really in-depth sheet of data which you can sort by multiple points in order to weed out priorities.
Identifying Site-wide Links
For my example I’ve chosen to take a look at the links pointing to the MotoGP page on Wikipedia. Counting the number of links per domain is really simple and easy to do in Excel. All you need to do is use the =COUNTIF function. This function works exactly as the name states – it simply counts the number of occurrences and returns the result. In this case, we’re counting the number of times a link appears on a domain. You’ll first have to separate out the domain from the linking URL which I explained in part one – but assuming you’ve done that you’re ready to go:
[Domain] = our second column in the table
B3 = the domain we’re looking to count
As you can see from the screenshot below, pasting the formula above into our ‘link count’ column enables us to easily sort our link data by the number of links per domain. That’s particularly useful if you’re concerned about site-wide links. Because at this stage we’re only looking for a link count and no other metrics, this is perfectly achievable with a large combination of data from as many tools as you like including OSE, Majestic and Webmaster Tools.
Another very useful thing is having the ability to filter your anchor text by ‘money terms’ or keywords that may have been over cooked when building exact match anchor text. It’s also extremely beneficial to get a real idea of proportion; for example how much keyword anchor text do I have compared to brand?
At SEOgadget we use exactly the same process for categorising keyword data so it made perfect sense to apply this ‘categorisation’ method to link analysis. Learning how to do this was at least one evening’s worth of frustration, but it was worth it in the long run! To start with you’ll need to open a new TAB in which you can start to add your terms for categorisation. Using the same Wikipedia page as an example means we’re quite limited on the amount of anchor text variety – but that should make it a nice and simple example to learn on.
For the purpose of this example I’m going to use ‘wikipedia’ as my ‘brand’ anchor text and ‘MotoGP’, ‘Grand Prix’ and ‘bike’ as my keyword anchors. In real life you will probably have a lot more variation and keywords to enter in. As you can see below, I’ve now got a nice table with my anchor text categories in place:
I’ve also added a ‘random’ category above which takes into account anchors such as ‘click here’ and image alts etc.
The next step is to create some new columns in your main table for your anchor text categories. In line with my chosen categories above, I’ve created three new columns: ‘brand anchors’, ‘keyword anchors’ and ‘random anchors’.
Now you’re ready to use the formula below:
Anchor Type’!$A$2:$A$3 = our ‘brand’ column in the ‘anchor type’ tab
$D2 = our anchor text column in the main table
F$1 = our ‘brand anchors’ column header in the main table
It may look complicated, but all you need to do is paste the formula above into the first cell into your ‘brand anchors’ column. As long as you’ve adjusted the formula to select the data from the correct cells you should be fine:
If you find that it doesn’t work out the way you want it to then trust me; as long as you spend some time working it out you’ll get there in the end! The majority of people get stuck when they either haven’t adjusted the formula correctly, or their data is inconsistent (usually casing issues).
Once you’re done you can then start to filter through your back links by keyword anchor text, brand anchor text and random anchor text. It’s worth pointing out that if you filter by ‘brand’ this will include all anchor text with your brand included, so that could mean a mixture of both keyword and brand. If you want to see your keyword anchor text with no brand mentions at all, simply filter that data out in your ‘brand anchors’ column.
Alternatively if you leave a comment or ping me an email I’ll do my best to help you out!
Pulling in the IP Address
I’ve found that being able to see your link data by IP address can be really handy when trying to spot networks, or find a contact for a site that may have no contact details. If you’ve ever tried to do link removal you’ll definitely have come across sites with absolutely no contact details at all. If you’ve pulled the IP address for each domain then you might get lucky and spot another domain that does have contact details.
Spotting duplicate IP addresses across multiple domains is a sure fire way of spotting a crap network. The good news is that if you’ve got SEO Tools for Excel installed, collecting IP address data couldn’t be easier.
Make sure you’ve added an ‘IP’ column into your main table and then use the SEO Tools for Excel’s =ResolveIP function:
Adding together the things in this post and part one, the end result will be a very large but hopefully insightful set of link data. The real aim is to be able to quickly sort through your data by any metric you wish, and quickly gain an accurate idea of what your link profile looks like. If you are considering embarking on some link removal work, then having this insight should save you a lot of time and unnecessary work. Enjoy!