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:

Join over 5000 subscribers!
Receive an update straight to your inbox every time I publish a new article.

 

About the Author:



My business thrives on referrals, so I really appreciate recommendations to people who would benefit from my help. Please feel free to endorse/forward my LinkedIn Profile to your clients, colleagues, friends and others you feel would benefit from SEO, PPC or Web Analytics.

 

 

  • http://seolinkmonsterreport.com SEO Link Monster

    Himanshu, this is one heck of a tool that you have developed!!

    I shall be emailing soon for a request to give your software a spin!!

  • http://alanng.blogspot.com Alan Ng

    This is excellent! I was wondering when you were going to come out with your own set of tools. I do believe a decent customized tool set within your arsenal is essential to placing yourself in front of the game. As the amount of data increases so does the amount of time needed to dissect it, breaking down the data into what you are actually looking for quickly and effectively is what makes the difference.

    Excel is one of the best companions for SEOs and using it efficiently and effectively can really reduce ‘drag’ time! I’ll be writing more about the toolkit we’ve been devloping in Excel at Branded3 later in our blog post also. It’s great to have such a wide range of tools to test, nice one!

    • http://www.seotakeaways.com/ Himanshu

      My own set of tools….humm,,,, Frankly speaking it is just a matter of time. I want to build tools which are affordable if not free so that more people can benefit from them. At preset all the great SEO tools out there are very expensive.

      I agree with you on Excel. It is a great tool. But then it is not really meant for data management. Sometimes we use this tool for all the wrong reason. I have no problem with Excel as such and i will continue to use it. I think both excel and access complement each other and are wonderful SEO tools.

      • Alan Ng

        Yes regarding the costs of the the tools out there! With regards to Excel, I agree it isn’t great for data management, but for data extraction and manipulation it is pretty much the staple for any decent SEO.

        While Google docs is great in terms of quick data extraction with with use of Xpath queries. For any large amount of data manipulation, most of the time, I exported data back into Excel.

        Pairing Excel up with the winHttp library in VBA and using APIs you can essentially cut out the middle step of exporting data and make everything readily accessible from within one environment. Either I understand how long it would have taken to develop such a tool and would be great to test some time.

  • http://www.techendeavour.com Suresh

    I am going to try this now thank you so much Himanshu.

  • Shumail

    Well this is really a handy tool, I am sure now people will start copying this:D

  • Yash

    The tool works great. Thanks for sharing it.

  • 2JustinwithLove

    Great post. Politely waiting for the tool.

  • Vikas Sharma

    Great tool Himanshu. Can i get the access.

  • http://www.dartt-online.org/ seo-learner

    Please send me this tool. I have sent you the email.

  • Manoj Kumar

    plz send me the tool access. Thanks

  • JohnZ

    Awesome tool. Sent the mail. please check it.

  • Jitender

    another great post Himanshu. I think i can develop this tool. But since you are giving the tool for free so won’t mind. I think i don’t need to send you any request email :)

  • seoNerd4Life

    can’t wait to get the access. I have been looking for such tool for a long time.

  • Tom

    Access please. My email is: (removed) Thanks in advance.

  • twistedSEO

    Please send me a copy of the tool. I am following you on twitter and i have shared your post to all my followers.

  • http://www.africanchildforum.org/site/ Aiden

    Thank you for sharing this awesome tool with the SEO Community. I have sent you the email. Please send me a copy of the backlink anlaysis tool. Thanks again.

  • Basma

    The tool looks really good. Can i get a copy please. My email is (removed) .Thanks

  • Aeesha

    Tools like this help link builders a lot. Thank you for the access. It works great and is helping us in competitive analysis.

  • Rachel

    Thanks for giving me the access. I’m gonna use it right away and send you the feedback.

  • http://www.mardel.com/ Charles

    I am pro link builder and this tool is really a boon for me. I wish you also work on the open site explorer alternative. Thanks for sharing this awesome tool. Cheers

  • Sandy

    Great tool Himanshu. When we are going to get the access :)

  • SEO Expert India

    Thank you so much for giving us the access. Great tool -D

  • http://timesofindia.indiatimes.com/defaultinterstitial.cms Rajiv

    Thanks Himanshu for sharing this wonderful tool. I am using it now. It is working well for me and my team.

  • http://www.thenewparentsguide.com/ Nakisha

    Thanks for giving us the copy of this tool. I love the way it quickly produces reports. It is also easy to use. Thanks again.

  • http://www.moving.com Moving

    Hey Himanshu, what’s up with your RSS? I didn’t see anything from you in a while and today there’s 15 articles.

    • http://www.seotakeaways.com/ Himanshu

      Sorry about that. May be because of the recent site migration and my first post on seotakeaways, you got so many posts all at once.

  • http://www.houseofcopy.com Brian Clark

    Looks like an awesome tool, Himanshu. Could I please get access to it?

    Thanks!

  • http://www.moving.com Moving

    No problem. I love your posts, just curious as to what happened. Yeah it does look like something to do with the site migration.

    Cheers

  • John Paul

    very useful tool Himanshu… I want this tool how can i access this tool ?

    • http://www.seotakeaways.com/ Himanshu

      Just sent me a nice email :)

  • Acadia online marketing

    Your backlinks analysis tool looks like an ideal way to optimise link building campaigns. Can’t wait to get the access.

    • http://www.seotakeaways.com/ Himanshu

      Acadia i tried to sent you the copy of the tool but your email is bouncing back.

  • Torbjorn SEO

    This tool looks unbelievably great. It is going to be a huge time saver for link builders like us. Request sent :)

  • http://www.flowersdubai.co.uk/ Dawson Miller

    Great work Himanshu. I have been playing with it for a while and have been amazed by its functionality. Can’t wait to hear everyone’s thoughts on this new tool. Cheers mate.

  • Ethen Foster

    Ok i am impressed by what this tool can do and looking forward to get a copy :)

  • http://www.starwoodhotels.com/luxury/property/overview/index.html?propertyID=422 Rajiv

    Awesome work Himanshu. The tool looks incredible. I sent you request and still wating to get the access. I hope you find some time to read my email. Thanks

    • http://www.seotakeaways.com/ Himanshu

      Hi Rajiv,

      I have got your email and the tool is on the way. Enjoy and don’t forget to send me feedback.

  • FirstDigital

    Thank you for sending us the copy of this SEO tool. Your tool makes link building far more efficient.

    • http://www.facebook.com/ Jaydee

      Ah yes, nicley put, everyone.

  • http://www.ercole.net/hayaser/start.asp Ercole

    Very excited to start using it. Thank you.

  • Paloma

    Greetings from Spain. I have been an avid but silent reader of your blog for a long time. I really appreciate your generosity to share such a powerful tool with other SEOs for free. I sent you the request long time ago and haven’t got any response so far. May be my email got lost in the hundreds of emails you are getting each day. Can’t wait to use this superb tool.Gracias

    • http://www.seotakeaways.com/ Himanshu

      Hi Paloma,

      Thanks for the kind words. I have already sent you the SEO tool. Please check your inbox, spam folder or Junk email.

  • Nishant

    I used this tool and it is really good. Thanks for sharing it and keep doing wonderful work like this.

  • http://www.mridulgupta.com mridul gupta

    How can i download this tool?
    I don’t have your mail id.

    • http://www.seotakeaways.com/ Himanshu

      You can find it through the ‘contact us’ page.

  • http://www.ontarget-media.com richa

    Hi Himanshu I am following you from last 5 months and really big thanks for your generous explanations and learning’s. I am interested to try out your tools too.
    Your backlinks analysis tool looks like an ideal way to optimise link building campaigns. Can’t wait to get the access.

    Thanks in advance

  • http://www.kidum-asakim.org.il/ Kidum Asakim

    Hi
    can you send me a copy please?
    thanks

  • Nayan

    can you send me a copy please?

  • http://www.eltalearning.com Jignesh Gohel

    Hi Himanshu,

    First of all congratulations for creating such a wonderful tool. I must agree with your comment regarding to investing time for developing such tools as there will be always conflict in our mind whether we should invest our time in developing the tool or should we buy one and invest that time to our work.

    I would love to test this tool if you can send me one.

  • http://www.webworldtips.com Kedar@webworldtips.com

    Nice to know about your developed tool. I just want to use this one and how it acts. Please send me this one to my mail.

  • http://www.sampleinsurance.org/ Sample Insurance

    Excellent tool..you have done great job here! Thanks!

    • NoelPCj

      It seems I read all the feedback/comments here, this software looks great to me and I really want to use it..please send me a copy to my email thx..noelpcj@gmail.com..thank you so much..

  • Arbi

    Hi,

    I would like to try this tool. Please send me a copy at my email.

  • Rg

    Hi Himanshu,

    Great Tool & I love your posts! Very informative. Please send me the access to this tool.

    Thanks!

  • http://xtendedview.com How To Tech

    grate tool. Is there any standalone .exe file available for this ? I am ready to pay and buy it. please let me know.

  • http://spidermarket.wordpress.com Jon

    Great article, I never used ms access. i will be tinkering with it. can you put me on your list to send me a copy of your tool?

    cheers,
    I will be writing something on this awesome tool, let you know.

  • http://spidermarket.wordpress.com Jon

    I don’t know if you got my last request of your copy. i don’t see it, so here it is again. can u send me a copy of your tool?

    cheers!

  • http://www.convertlofts.com Gaurav Mehta

    Hey Himashu,

    Would really appreciate if you could send me this tool… looks like the right tool I need to track backlinks as I look to build my blog.

    Thanks very much in advance

    Gaurav
    SEO Amateur in the UK

  • http://www.searchoptmedia.com Gary Viray @ Search Opt Media

    Himashu,

    I would really appreciate if you can send me this tool.

    Thanks in advance

  • Galyna

    Himanshu where I can get this ddb file?
    Thank you

    • http://www.seotakeaways.com/ Himanshu

      Hi Galyna! You are late for the party. This file is no longer available.

  • http://www.windowsphonesupport.com/ vijay

    Can anybody tell how to make an backlink api to put on a website

  • http://www.inseoservices.com SEO Montreal

    Thank you very much for share this tool, i`ve started reading your post http://www.seotakeaways.com/ultimate-data-visualization-guide-seos/ and was really incredible. Could you send me please a copy of this tool? my email is fleon79@gmail.com,

    Thanks in advance,

    Francisco

  • Karthik

    Hi,

    Its really interesting, could you please send me the copy to my mail id karthik@brightbridge.co

  • http://ngocchinh.com/ Trần Ngọc Chính

    Hi, greetings from Vietnam !

    Thanks for developed excellent tool. I can’t wait to use this super tool, could you please send me the copy to hi@ngocchinh.com ?

    Thanks in advance

  • Tuan Le Duc

    Hi, greetings from Vietnam !
    a great way to create a background tool. You can send for me a copy of it. I thank you if you take the time to send it to me

  • http://www.incion.com/ web design company los angeles

    Wow nice information you have shared here. Actually Google made
    searching of information easy on any topic. Well keep it up and post
    more interesting blogs.

  • http://www.online-phd-uk.co.uk/ OnlinePhDUK

    seems brilliant tool, will use the same techniques and see the results…

  • http://digitalspher.blogspot.com Suprabhat Mondal

    why this is no more available to download i really want to make this tool for my http://worldtechbuzz.com please suggest some another way

  • http://iblesoft.com vikas kumar

    Hi there, This is nice tool. I think i mess the change to download. Could you send a copy to me on “vikasi4u@gmail.com”.

  • Natsha Tkachenko

    Really want to create backlinks or earn positioning!? Well … do not be fooled by any kind of malicious software or who knows what rare strategies. Forget about trying to get this challenge through any means not worth … the only official website and totally legal to do this is this; http://linxgo.com/6fh20 Try it and you will understand the difference and the trouble which you could have gotten by without more and search positioning. Besides creating legal backlinks, you can view and manage each backlink, page by page.

  • http://www.sinelogix.com Shashikant Chauhan

    i think this tipe of tool was i never see on internet which is made by you this is great .hats off man for your work.

    PHP Web Development
    Company Bangalore

  • Prospectar Sin Sufrir

    Hi Himanshu:

    I form to Spain. Thank you very much for Excellent tool. Could you send a copy to me on prospectarsinsufrir@gmail.com

    Best Regards!

  • http://www.recoverpenalty.com/ Swaranjit Singh

    Hi Himanshu,

    Great tool, Really impressive! I want to use this super tool, could you please send me the
    copy to s.swaranjit@gmail.com ?

    Thanks in advance.

    Regards,
    Swaranjit Singh