3 Time-Saving Tips When Cleaning Up Your Link Profile
Prepping for a link cleanup project is no easy task. Ever since Google's Penguin updates started cracking down on unnatural linking practices in 2012, many site owners who enlisted the aid of unscrupulous SEO services to build hundreds or even thousands of links for them have eventually found themselves on the receiving end of a penalty. Seeing your organic traffic tank after a Google algorithm update is a scary realization, but do not lose hope. If you think your site may have been penalized by a recent algorithm update, our AlgoSleuth Tool can help you see the organic traffic trends and know for sure. You will also want to check Google Webmaster Tools for your site and see if you've been dealt a Manual Penalty. If there are no messages in Webmaster Tools then the penalty is most likely algorithmic. If AlgoSleuth identifies a particular update where you were hit hard or you see a manual penalty identified in Webmaster Tools, you need to audit and most likely clean up your backlink profile. A thorough write-up of the steps involved can be found here. Below we outline 3 time-saving recommendations to keep in mind when undertaking a link cleanup project.
1. Pull from as many data sources as possible.
Google has said that you don't generally need to utilize third party tools to find all the "unnatural" links pointing to your website, but from personal experience and the experience of others this just hasn't proven to be true. Multiple backlink data sources such as Ahrefs, Moz, and Majestic SEO (in addition to WMT) will help uncover as many of these unnatural backlinks as possible, ensuring that you do the cleanup right the first time around .
If you followed the aforementioned step, you'll have at least 3-4 data pulls in CSV format to deal with. To save yourself time, you'll want to import all of this data into one spreadsheet to work with. The column headers you really need are: Backlink URL and Anchor text. Feel free to include the URL on your site the backlink is pointing to, if desired. The rest can be deleted when importing your pulls into one spreadsheet. Now that they are all in one master file, you'll want to de-duplicate your list so you are not wasting any time going over something twice. This can be accomplished in both Excel as well as Google Docs. For Google Docs, create a new "Master Data" tab. In Cell A1, paste the formula shown below, replacing the portions underlined in red with the tab names of your data pulls.
=unique(query(vmerge('Open Site Explorer'!A1:B;'Majestic SEO'!A1:B;'Ahrefs'!A1:B);"select * where Col1<>'' ";0))
If you are working in Excel, you'll first need to copy all of your data from each of the 3-4 tabs into one single "Master" tab. After you've done that, you can follow the directions here under "Filter for unique values". The end result will be the same as the Google Docs method, outputting a list of unique backlinks to work with.
3. Filter out 404's.
If your backlink profile is sizable, this step can save you quite a few hours. The SEO tools mentioned earlier, such as Ahrefs or Moz, do a great job at crawling the web for these backlinks but your backlink lists will undoubtedly contain 404's from sites that have since closed up shop. Before you start going through your master list to ask for link removals, you'll want to filter out these 404's to double back on or add to your final disavow list. This is easily accomplished in Google Docs with a simple custom script. After installing the script, add a new column to your master tab called "Link Status". Now you can call on any cell containing a URL with =HTTPResponse() and see an output of the current HTTP Status. After doing this for one cell you can drag the formula down and it will automatically run on your URL's as shown below.
If you are working in Excel you'll want to install the free SeoTools plugin which lets you call HttpStatus() on a cell in the same way as the Google Docs method. After you've done this in Google Docs or Excel, you can quickly filter your "Link Status" column to exclude backlinks with a 404 status. This will help focus on all the live links first and then double back later on the 404's.
Now that you've combined your lists, de-duplicated, and prioritized by live links, you're ready to start combing through the list manually to find potential bad eggs among the bunch. It's not too hard to discern natural links from "unnatural" ones but if you want to save even more time a link cleanup tool such as LinkDetox is recommended. It helps you easily ID the bad links and even serves as an organized area to contact webmasters to request a link removal and keep good records.
Cleaning up a backlink profile that has been hit by Penguin is never an easy task. Even for smaller sites, a full-scale analysis and cleanup can take weeks. Just as you encounter frustrations when trying to build new links, you will with cleaning up old ones. Many webmasters will be unresponsive when asking for link removals. Some will even ask for money to remove the link(s) or flat out refuse to remove them. There will be times when you just want to bang your head against your desk, but in the end, cleaning things up can give you a much better idea of how to move forward and build better quality links in the future. As always, if you have any questions about this post or need some extra help feel free to drop us a line.