How to create your own Backlink Auditing Tool

 

If you analyse the performance of SEO Campaigns then this tool is going to help you a lot.

It is a tool designed to automatically segment the back link profile of any website into following types of links:

1. Articles links – These are mainly the back links from article directories.
2. Blog links – These are the back links from blogs
3. Directory links – These are the back links from directory websites
4. Edu links – These are the back links from educational institutions like .edu websites.
5. Forum Links – These are the back links from forums.
6. Gov. Links – These are the back links from government websites.
7. News links – These are the back links from news websites like BBC,  Guardian, New York Times etc.
8. Non-English links – These are the back links from non-English websites like Japanese, Chinese or Russian websites.
9. PR Links – These are the back links from press release websites.
10. Social Media Links – These are the back links from social media like Twitter, Facebook, LinkedIn etc.
11. Wiki Links – These are the back links from wiki sites like Wikipedia.

You basically get the Xray report of a website back links profile in a second

No back link analysis tool in the market still provides such type of functionality.

Imagine how quickly you can assess the performance of any link building campaign.

For example, if majority of back links are coming from directories, you know that you need another link builder. No need to scan each and every back link and calculate DA/PA.This is the power of data visualisation.

 

How this tool can help you?

When you run this tool on a website, you can automatically segment its back links in a meaningful way.

Instead of getting a long list of say 5000 or 10000 back links you get several reports and each report contains a list of particular type of link. 

These links are the links from directories, .edu sites, blogs, wikis etc. You can also see all the metrics (PA, DA, no of root domains etc) you usually see in open site explorer tool.

 

How I developed this tool?

For ages I looked for a tool like this.  The functionality of the tool was clear to me but not the logic. I wrestled with Excel for weeks to create this tool as we marketers generally don’t tend to think beyond Excel spreadsheets.

Then one day my brainy wonderful wife saw me struggling with the tool, she suggested me to develop the functionality using a database.

The only database that I have to at that time was MS Access 2007. So I brushed up my Access skills and started working on it. It took me just couple of hours to develop the functionality I wanted so badly and man it was so damn easy.  It also highlighted some serious limitations of MS Excel we so proudly use every day.

First of all,

Excel is not designed to manage large amount of information and because of this it is very easy to introduce errors (esp. duplication problems) into a spreadsheet which then makes analysis pretty challenging.

And we marketers deal with lot of data. I analyze anywhere from 40000 to 50000 back links in one go.

Excel is a good tool if you want to retrieve the data in one way. But this is not generally the case. We often need data which satisfies multiple conditions and Excel fall flat on the ground here.

You can’t run complex queries in Excel without using even more complex formulas. Because of the two dimensional design of a spreadsheet (data stored in rows and columns)  you can’t analyze the multi-dimensional nature of the data.

I don’t claim that you can’t develop my tool using Excel.

You can drive nails with a crescent wrench but that doesn’t make it a hammer“.

Excel is simply not designed for data management, yet we use it for everything from back link analysis to keeping records of our clients. If you are primarly dealing with numeric calculations then use Excel. For database management esp. relationship based use Access.

 

How my tool works?

The tool works on some simple and some complex SQL queries.

The logic is based on boolean algebra. Once you understand how the tool works you can then customized it to suit your requirements.

However explaining SQL and boolean algebra from scratch is beyond the scope of this blog post. Please feel free to skip this section if it doesn’t make much sense to you.

So for example if I want to filter out articles sites from a list of websites, I will use the following boolean expression:

Statement 1= (URL = ‘article’ AND Not Blog AND Not Directory And Not …..)

Statement 2= (Title = ‘article’ AND URL = Not Blog Not Directory….)

Statement 3= Statement 1 Or Statement 2

Statement 1 means retrieve those records where the websites contain the word ‘article’ somewhere in the URL but do not contain the word ‘blog’, the word ‘directory’, the word ‘wiki’…….. When this statement is evaluated the value returned will be either True or False.

Statement 2 means retrieve those records where the websites contain the word ‘article’ somewhere in the Title tag but do not contain the word ‘blog’, the word ‘directory’, the word ‘wiki’…… in the URL. When this statement is evaluated the value returned will be either True or False.

Statement 3 means if Statement 1 becomes false then evaluate Statement 2. If statement 2 become false then don’t retrieve any record.

So basically I am looking for all those websites which contain the word ‘article’ either in the URL or in the title tag but which don’t contain words like ‘blog’, ‘directory’, ‘wiki’ etc.  I have used the same logic to develop SQL queries to filter out different type of websites.

 

What do you need to use this tool?

1.  You should have a basic knowledge of how MS Access works and how the MS Access database is structured.

2.  You need access to a tool like ‘open site explorer’ or other similar tool which can fetch the back links of a website.

3. You should have MS Access installed on your hard disk. I developed this tool using MS Access 2007. I have not tested its compatibility with other versions of Access.

 

Instructions to use the tool

Step-1: Download back link profile of your website or your top 5 or top 10 SEO competitors from ‘Open Site Explorer’ or other similar tool into excel. I download only ‘followed’ external links to speed up my analysis.

Step-2: Consolidate all the reports into one excel spreadsheet and then remove duplicate domains using FIND and SEARCH functions, for example: =LEFT(B2,SEARCH(“/”,B2,1)).

You can choose not to remove duplicate domains. I remove them because I want to see only one link from a particular domain and also because I have to analyze anywhere from 40000 to 50000 back links.

Step-3: Download the database file named SEOTakeaways-backLinkAnalysisTool.accdb, unzip it and then double click on it to open it .

Right click on the table named ‘Site Back Links’. Select ‘import’ > ‘Excel’ from the drop down menu.

 Browse the excel spreadsheet you want to import and then click on the ‘ok’ button as shown below:

  Make sure that the ‘first row contains column headings’ check box is checked. Then click on the ‘next’ button as shown below:

Keep the default settings and again click on the ‘next’ button as shown below:

  Make sure that ‘Let Access add primary key’ checkbox is checked. Then again click on the next button as shown below:

Final step is critical. Change the name of the table to ‘Site Back Links’ in the ‘import to Table’ text box as shown below and then click on the ‘Finish’ button.

You will be asked to “overwrite existing table or query ‘Site Back Links’ “. Click on the ‘yes’ button and then later click on the ‘close’ button to close the ‘import spreadsheet wizard’ dialog box.

  Now you will see a screen like this:

Double click on the table ‘Site Back Links’ (on the left hand side) to open it.

Then double click on each search query report (like ‘Articles Links’, ‘Blog Links’, ‘Directory Links’ etc) to open them one by one. Once you have opened all the query reports, the screen should look like this:

Final Report of SEO Takeaways SEO Tool

Once you have got this report, you can click on a tab and see particular type of back links.

You can then sort in decreasing order of PA/DA and remove all those links which you don’t want to analyse/target.

Since MS Access is specially designed to handle huge volume of data, you can analyze even 1 million+ back links in one go.

This tool can also visualize the back link profile of a website like this:

Now you can get much clear picture of which type of links are driving the rankings of a website.

To see this chart you just need to double click on the chart object in the MS Access interface.

This tool is free to use and I would have happily shared it via a direct link but the database file size is very big even after compression (930 KB in compressed format and 25 MB in un-compressed format).

 

Related Posts:

Subscribe to my blog
Join my free newsletter and learn to avoid the analytics mistakes everyone seems to be making over and over again.

 



Subscribe to my YouTube Channel > > >

 



Click on the banner above to register and then use the discount code BPOS15 during checkout to receive 15% off the price of registration for 2 Day, All Access and Combo passes.

 

About the Author:

Please feel free to endorse/forward my LinkedIn Profile to your clients, colleagues, friends and others you feel would benefit from PPC, Web Analytics or Conversion Optimization.

For any question/comment Contact me