Organic search is almost always one of the top sources of traffic to a website, and if you want to understand traffic acquisition you need to understand the performance of this marketing channel.

The volume of organic search traffic your website gets still, to a large extent, depend upon the volume and quality of website backlinks.

Though link building is a by-product of content marketing, analyzing the backlink profile of a website is still important, in order to understand the overall SEO performance.

For example, if the majority of backlinks are coming from directories and articles websites, you know the old school link building took place or the link builder is still engaged in outdated and dangerous link building practices.

But scanning each and every backlink of a website is not practical. You need a tool through which you can visualize the overall backlink profile of a website, something like the one below:

From the chart above, we can conclude that majority of backlinks are from blogs which is a sign of a healthy backlink profile.

Now if we look at the backlink profile of another website, we can see that the majority of backlinks are from directories which means the link builder/content marketer is doing a crappy job:

There are a lot of backlink analysis tools available in the market, but they don’t do a very good job of visualizing the backlinks data. Most of them, just give you a very looooong list of backlinks to analyze.

Now when I am trying to understand the SEO performance, I am not going to analyze each and every backlink. I want an x-ray report of a website backlinks profile which I can show to the client and say :

“Here is why your organic traffic is so low, or

Here is why your organic traffic is less than that of your competitor, or

Here is why your link builder/content marketer need to be fired“

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

Imagine how quickly you can assess the performance of any link building / content marketing campaign, if you can visualize the back links data.

This is the power of data visualization.

Since I couldn’t find any such tool, I built my own. My tool can automatically segment the backlink profile of any website into the following types of links:

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

When you run this tool for a website, you can automatically segment its backlinks in a meaningful way. Instead of getting a long list of say 5k or 10k backlinks, you get several reports and each report contains a list of a particular category of links (like blog links).

If you are using open site explorer to get the backlinks of a website then you can also see the metrics like PA, DA, number of root domains, etc in the reports.

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 analysts generally don’t tend to think beyond Excel spreadsheets.

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

So I brushed up my MS Access skills and started working on it. It took me a 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:

First of all,

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

And we analysts, deal with a lot of data. I analyze anywhere from 40k to 50k backlinks 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 that satisfies multiple conditions and Excel falls flat on the ground there.

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 backlink analysis to keeping records of our clients.

If you are primarily dealing with numeric calculations then use Excel. For database management especially relationship-based, use MS 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.

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 becomes 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 do not contain words like ‘blog’, ‘directory’, ‘wiki’ etc.

I used the same logic to develop SQL queries to filter out different categories of backlinks. For example, the following is the SQL query to filter out all the backlinks from articles websites:

SELECT [Site Back links].URL, [Site Back links].Title, [Site Back links].[Anchor Text], [Site Back links].[Page Authority], [Site Back links].[Domain Authority], [Site Back links].[Number of Links], [Site Back links].[Number of Domains Linking to Domain], [Site Back links].Followable, [Site Back links].[301], [Site Back links].Origin, [Site Back links].[Target URL]

FROM [Site Back links]

WHERE ((([Site Back links].URL) Like “*article*” And ([Site Back links].URL) Not Like “*blog*” And ([Site Back links].URL) Not Like “*directory*” And ([Site Back links].URL) Not Like “*.edu*” And ([Site Back links].URL) Not Like “*.ac.*” And ([Site Back links].URL) Not Like “*.cc./*” And ([Site Back links].URL) Not Like “*.tec./*” And ([Site Back links].URL) Not Like “*forum*” And ([Site Back links].URL) Not Like “*.gov*” And ([Site Back links].URL) Not Like “*.nic*” And ([Site Back links].URL) Not Like “*News*” And ([Site Back links].URL) Not Like “*telegraph*” And ([Site Back links].URL) Not Like “*dailymail*” And ([Site Back links].URL) Not Like “*bbc*” And ([Site Back links].URL) Not Like “*guardian*” And ([Site Back links].URL) Not Like “*ft.com*” And ([Site Back links].URL) Not Like “*times*” And ([Site Back links].URL) Not Like “*pressrelease*” And ([Site Back links].URL) Not Like “*wire*”)) OR ((([Site Back links].URL) Not Like “*blog*” And ([Site Back links].URL) Not Like “*directory*” And ([Site Back links].URL) Not Like “*.edu*” And ([Site Back links].URL) Not Like “*.ac.*” And ([Site Back links].URL) Not Like “*.cc./*” And ([Site Back links].URL) Not Like “*.tec./*” And ([Site Back links].URL) Not Like “*forum*” And ([Site Back links].URL) Not Like “*.gov*” And ([Site Back links].URL) Not Like “*.nic*” And ([Site Back links].URL) Not Like “*News*” And ([Site Back links].URL) Not Like “*telegraph*” And ([Site Back links].URL) Not Like “*dailymail*” And ([Site Back links].URL) Not Like “*bbc*” And ([Site Back links].URL) Not Like “*guardian*” And ([Site Back links].URL) Not Like “*ft.com*” And ([Site Back links].URL) Not Like “*times*” And ([Site Back links].URL) Not Like “*pressrelease*” And ([Site Back links].URL) Not Like “*wire*”) AND (([Site Back links].Title) Like “*article*”));

Note: Don’t try to write SQL queries manually. Instead, use the design view to write SQL queries.

MS Access has got 3 views: Datasheet View, Design View, and SQL View. Right-click on a tab in MS Access, to see these views:

Here is how these views look like:

Use the design view to write SQL queries.

Requirements for using this tool

#1 You need access to a tool like ‘open site explorer’ or another similar tool that can fetch the backlinks of a website.

#2 You need MS Access installed on your hard disk. I developed this tool using MS Access 2013. I have not tested its compatibility with other versions of Access.

Requirements for customizing/fixing this tool

You may need to customize this tool to meet your specific requirement or you may find this tool not working for you. I designed the database tables, keeping open site explorer data in mind.

If you are using another backlinks analysis tool (Majestic, Ahrefs, etc), then you may need to change the table design, in order to retrieve the data correctly.

However, no matter which backlink analysis tool you use, the underlying logic behind retrieving the data and SQL queries is going to remain the same as long as you have a basic understanding of SQL and basic knowledge of how MS Access works and how the MS Access database is structured.

Instructions for using this tool

Step-1: Download all the backlinks of your website from Open Site Explorer, or another similar tool, into Excel.

Step-2: 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.

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

Step-4: Right-click on the table named ‘Site Back Links:Table‘ and then select ‘Import’ > ‘Excel’ from the drop-down menu:

Step-5: Browse the excel spreadsheet you want to import, keep all other settings intact, and then click on the ‘ok’ button as shown below:

Step-6: In the next step, make sure that the ‘First Row Contains Column Headings‘ checkbox is checked. Then click on the ‘next’ button as shown below:

Step-7: In the next step, keep the default settings intact and again click on the ‘next’ button as shown below:

Step-8: In the next step, make sure that ‘Let Access add primary key‘ checkbox is checked. Then again click on the Next button as shown below:

Step-9: In the next and final step, change the name of the table to ‘Site Back Links’ in the ‘Import to Table‘ text box as shown below:

Make sure that you change the table name, otherwise this tool won’t work.

Step-10: 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 click on the ‘close’ button to close the ‘Import Spreadsheet Wizard’ dialog box.

Now you will see a screen like the one below:

Step-11: Double click on the table ‘Site Back Links‘ (on the left-hand side) to open it. Similarly, double click on each report (like ‘Articles Links’, ‘Blog Links’, ‘Directory Links’ etc) to open them one by one:

Note: Depending upon the format in which you imported the excel data into MS Access, you may see ‘Enter Parameter Value‘ box as shown below:

Keep clicking on the ‘ok’ button until it is removed. If you don’t see any data in the datasheet view then you may need to go to the design view to fix this problem. There is also a possibility that no data really exist for a particular report.

For example, if your website has got no backlinks from directory websites, then your ‘Directory Links’ report will not show any data.

Once you have opened all the query reports, the screen will look like the one below:

Step-12: Double click on the ‘chart’ object to see the backlink profile of your website:

Once you can see the chart, you can also click on a tab and see a particular category of backlinks (like social media links).

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

Now you can get a much clear picture of which category of links is driving the organic search traffic.

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 a compressed format and 25 MB in uncompressed format).