Compiling the ULTIMATE link list: A guide for agencies – Part 1
Unfortunately, this title might be a little misleading for some of you. But this is not an article on how and where to get some new ground breaking links that will catapult your site rankings. Rather this is about analysing the links you already have pointing to your site and diagnosing any potential issues and problem areas.
I gather most of you that have wound up here reading this article are most likely the culprits of a vicious penguin attack or have received a link warning message in webmaster tools. You might even be engaging in proactive management of your link profile. In either case, hopefully this can help you out a great deal.
The method i am going to describe below can turn out to be very time consuming and very taxing, especially for large sites. But if you bear with me, you’ll have the most comprehensive and accurate data on back links to your site than you ever thought you could have.
For this exercise you will need the following tools at your disposal:
- Webmaster tool access for your site
- Majestic SEO paid account – www.majesticseo.com
- AHREFS paid account – www.ahrefs.com
- Microsoft excel
- Scrape box or similar program
- The ability to mentally cope with lots and lots of data
What happens if i only have the free account?
Well unfortunately, you are quite limited. However you can still follow these same steps to get as much as you can muster with enough free programs you can find. The above is by far the ideal method though. Paid accounts allow you to access a tonne more information about your domain which is really a given. Some good free programs you might want to try are:
- Link detective – www.linkdetective.com
- Link Diagnosis – www.linkdiagnosis.com
- Bing webmaster tools – www.bing.com/webmasters
Let the games begin: First cab off the rank – Google webmaster tools
- Find your domain in your webmaster tools dashboard
- Head to your preferred domain set in webmaster tools. If you have only added the www or the non-www version of the website, then the link data may or may not be what you’re expecting
- Follow this method for your preferred domain:
- Hit the “Links to Your site” option in the side nav menu after clicking the “Traffic” drop down
- At the bottom of the “Who links the most” section, click more.
- Lastly, click the “Download latest links” option and save the CSV file into a folder on your computer.
- Log in to your account
- Head to the “My Reports” tab – https://www.majesticseo.com/reports
- Enter your domain without the www into the “Analyse a new website” box
- In the Create Report section we are going to create 2 reports. Both from the Historic and Fresh Indexes.
- Choose the “Historic Index” first and then right click on “Create Report” to open a new tab.
- Similarly, choose the “Fresh Index” and right click on “Create Report” to open a new tab.
- When this has been completed, Head back to the “My Reports” tab – https://www.majesticseo.com/reports
- You will see both the Historic and Fresh index reports in the “Standard Reports” section, open them both up
- On each tab, head to the “Backlinks” section as seen below. Make sure you set the “Backlinks limit” to “No Limit”, as seen below
- Export results into CSV at the bottom of the page and Save into a folder where you put the WMT tool reports.
- Log in to your Ahrefs account
- Once logged in, enter your domain into the search bar. Again without the www.
- Once the next screen has loaded, click the “external” tab
- On the next screen, in the top right you will see an export button, click that and then choose the UTF-16 option.
- Save into the appropriate folder
Putting it all together in excel
At this stage, you have numerous reports and lots and lots of data to sift through. Luckily, excel comes to the rescue to help us perform the following functions:
- To remove all duplicate values – Since we have compiled links from all the major link search engines, there’s no doubt that there’s going to be some serious overlaps
- Reveal what our link to unique domain ratio looks like
- Reveal what our Anchor text ratio looks like
At the end of the day, we can go into depth with a lot more metrics, such as Page/domain strength, Page title of the linked page, no follow vs do follow etc. But to keep it simple, we are going to focus on core metrics – Linked URL, Link URL and Anchor text.
Let’s start to import the data
- Right, with our reports ready to clean up the first thing we want to do is create a new workbook in excel and call it the “domain | Links Master List”. Save the document onto your computer.
- Create a tab for each report you have. So that should be 5 in total.
- Import all your link data from each report into their own respective tab. Give those tabs an appropriate name; i.e Majestic fresh index report. See below for what your document should look like:
- Copy and paste all the data into the relevant tabs.
You now have access to link data from the 3 major link crawlers available in one spread sheet. Before we start to remove duplicates we need to fill the data gaps for the WMT data that Google kindly does not include in the same report.
Enter Scrape box
No, we aren’t going to now go and create 1,000,000 forum signature links. Instead we are going to use it for 2 main functions.
- To get more data on our WMT tool links – Anchor text and Linked URL
- To find out which links are still active vs dead
- To find out no follow vs do follow
Because the Webmaster tool links don’t include Anchor text and Linked URL we need to get this first.
- Copy the WMT tool links into a txt file. Save in an appropriate spot
- Create another txt file and enter the domain you are analysing into the first line – no www. Save in an appropriate spot
- Once this is completed, head to the upload section on the bottom right of the scrape box main screen. Go ahead and upload these text files. The 3rd upload box from the top is where you upload the txt file containing the domain you are analysing and the bottom upload box is where you upload the WMT link data. See below:
- Once this is done click “Check Links”
- You will be presented with the following screen – make sure the URL’s are in the left hand column.
- When that is all good, click “Start” at the bottom left hand corner of the screen.
- When the program has finished analysing, click the “Export Links” function and choose all links into csv.
Before we use scrape box again we need to clean up our data first. With the new information you have obtained about the WMT links, make sure this is entered into the WMT report tab in the master list csv. At this stage, we now have Link URL, Linked URL and anchor text data across all platforms.
Let the clean-up begin
Starting with the hardest first, there’s a lot of columns we don’t really need in the Ahrefs report.
- First of all delete the selected columns (indicated by red) below. Leave the one’s in white:
- You may have a lot of blank rows in the data. To rectify this Highlight the data excluding the headers in A1, B1 and C1.
- In the top right hand corner of excel, use the custom sort function in the Sort and Filter section.
- Sort by column A. Make sure your screen looks like this one:
- Once sorted, change the appropriate column headers to Link URL, Linked URL, Anchor text and add on the Dofollow/Nofollow column.
Now on to Majestic SEO. Luckily, the data here is pretty well presented. All you need to do is delete some columns you don’t need. As this is many, I can’t paste a big enough picture into this article. However starting with either the fresh or historic index, all you need is columns which give Target URL, Source URL and Anchor text. Delete everything else. Then add on your Dofollow/Nofollow column.
Because we have downloaded both the fresh and historic indexes we need to combine this into one data set. Grab the remaining index and follow the same steps above. Once you have done this copy into one tab in your spread sheet under the respective columns.
Next step is to remove the duplicates
- Starting with the Majestic SEO data, highlight rows A to D and then head to the data tab in excel.
- Next, select remove duplicates. Once you are at the bottom screen, click ok.
Your Majestic data now has no overlaps. Do the exact same procedure, for WMT and Ahref’s. There shouldn’t be many duplicates here, but this is more of a precautionary step than anything else.
Compiling it all into one:
Once all spread sheets are in the correct format i.e Link URL, Linked URL, Anchor Text and Dofollow/nofollow, it is now time to mesh them all into one tab in your master list.
- Create a new tab in your Master sheet called “Final List” or something similar.
- Copy and paste all the data across into this tab, make sure the data goes under their respective columns.
- Once this is done we need to an overall duplicate value removal. Like before, Highlight columns A to D, and then head to the data tab in excel. Next, select remove duplicates. Same process as before.
Once this is done, we now have created a master list of links for your domain. Congratulations! Not even Google has as much information on links to your site as this does.
This is it for part 1, as i need a week to recover, I will be posting part 2 next week which will show you how to use excel to get some useful metrics which will help in your diagnosis, such as unique anchor text ratio, unique domain to link ratio etc.