How to create your own Backlinks Analysis Tool

This is my first post on seotakeaways.com (formerly known as seohimanshu.com). If you do link building then my new 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.

How this tool can help you

When you run this tool for a competitor’s website, you can automatically segment its linking partners 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 website like directories, .edu sites, blogs, wikis etc along with all the metrics (PA, DA, no of root domains etc) you usually see in open site explorer.  You can then sort the list in decreasing order of PA/DA (page authority/domain authority) to select your linking partners.

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 SEOs 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. She works as a senior system support analyst in a university and does some hi-fi stuff which i barely understand even after being a computer science graduate from a top notch university Anyways, the only database that i have is 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 SEOs 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 the 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.

Here is how you can use this tool

Step-1: Download back link profile of 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 acquire 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 target. Since MS Access is specially designed to handle huge volume of data, you can analyze even 100K+ back links in one go.

Tool Update

This tool can now 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).

I have got more than 120 requests for free copy of this tool so far even before its official release. Therefore i am assuming a large scale download of my file. So i have to limit the number of downloads to one per email address.  Just shoot me a nice email and i will be happy to send you a copy of this tool

If you like this post then you should subscribe to my blog and follow me on twitter

 Related Posts:

• Link Building through Blog Themes Sponsorship (A-z Guide)
• 6 Cool Link Building Tips for E-Commerce Site
• 10000 Search Engine Queries for your Link Building Campaign
• How to develop relationship with potential linking partners
• Link Building Pyramid – Hierarchy of Link Building Needs
• How to determine the value of a back link
• Link Building through Guest Blogging