Let’s say you’ve just received a new hot client lead. As part of your initial research, you should be interested in future proofing the site’s backlink profile against unnatural link warnings from Google. You need to quickly figure out if there are links pointing to this domain that could create future havoc in terms of Google’s Penguin algorithm and massive decreases in organic traffic. It’s time to investigate the site’s backlinks, also known as backlink analysis.
You may now feel a little overwhelmed about how exactly to go about this. After all, you are a busy person and the site may have tens of thousands to millions of links pointing to it. In this guide, I will walk you through analyzing a site’s backlink profile to look for problems. Working for Ayima, I would normally use our internal backlink information to read a site’s backlink tea leaves:
Since this data isn’t publicly available, I am going to use Ahrefs and Excel. Feel free to use another source of backlink data such as OSE to conduct your backlink analysis. Before getting started, I would recommend asking your site contact if they know about any previously purchased or unnaturally acquired links pointing at their site and if they can provide you a list of such links for investigation. Reviewing these links with a critical eye will greatly supplement your analysis below.
In this guide, my goals are to share tips around the following topics:
- Building Excel skills for SEO
- Using pivot tables to answer data questions
- Identifying problematic/unnatural links
- Gaining confidence around backlink analysis
“You have a grand gift for silence, Watson. It makes you quite invaluable as a companion.”
- Sherlock Holmes
Our site:
Nice organic growth! – source: semrush.com
Step 1: Collecting raw link data for backlink analysis
Visit https://ahrefs.com/, login, and type your domain in the homepage main search box. Use ‘yoursite.com’ rather than a specific URL such as http://www.brand.com/ Turns out this site has some 110,000 backlink pointing to it. Of course, no one has the time to review a 100,000 links or even 1,000 links so how are you going to look through all of these links to spot troublesome backlinks that you can highlight with the potential new client? The short answer is you aren’t. You are going to rely on sampling, judgment, and organizing data. Side note: You may be wondering why we are downloading data into Excel rather than looking at the top anchors tab in ahref.com. I have done this because drilling down to specific linking URLs for domains with big backlink profiles tends to load slowly in an ajax, web-based environment. Excel gives you added flexibility and speed to make the data work for you specific requirements. We will be going to the ‘Raw export’ tab and downloading the maximum amount of backlinks to Excel. In this case, we are talking about 110,000 backlinks. With really large site, you may not want to download the maximum amount, as 1 million rows of data in Excel is a royal pain in the arse. Also, since Ahrefs orders these links by their value, 100,000 or so backlinks is plenty for this type of analysis. Once you have this data downloaded and open in Excel, we are going to do a couple things. First we are going to trim our data down to one link per domain to help us maintain sanity. Then we will create a pivot table to look at data in different ways.Step 2: Trimming data down to one link per domain
In your raw export spreadsheet, copy the column of URLs pointing at your site (i.e. UrlFrom – column C) into the first available blank column (i.e. column V). Change the heading of this new column to DomainFrom. Now highlight the entire column DomainFrom: Our goal now is to trim these URLs down to their root domain (e.g. from "https://www.example" to "example.com"). I do this with ‘Find and Replace’ and then use the tool ‘Text to Columns’. First open Find and Replace (command+F on Mac): We are going to use Find and Replace 4 times to get rid of the first part of the URL (HTTP). Leave ‘Replace with’ blank and click ‘Replace All’ after using each of the ‘Find what’ patterns below. Work your way through the 4 http patterns to effectively delete this part of the URL. Make sure to do this in the same order as shown below to make sure this works correctly. In the ‘Find what’ field:- http://www.
- https://www.
- http://
- https://
Step 3: Using a pivot table to show the most common anchors pointing at a site
To create your first pivot table with this data, in the Excel ribbon go to Data then click on PivotTable. This will open a new spreadsheet with a most unhelpful default pivot table. Using the PivotTable Builder, we can massage our data quickly into an actionable format.- Add the Field names: NoFollow and Site-wide to the Report Filter area
- Add the Field names: Anchor and URLFrom to the Row Labels area
- Add Field name: Index to the Values area
Step 4: Identify your most common commercial anchors
What’s a commercial anchor? A commercial anchor is a phrase that other sites use when linking to your site that has commercial value. In other words, this is a keyword phrase that sites want to rank for because it will result in search engine traffic and revenue. Other anchor types include: brand anchors which include your brand name and noise anchors which are phrases that no one is really trying to rank for such as 'click here'. Above we find the following commercial anchors:- Bad Credit
- Free Credit Score
- Best Credit Cards
- Free Credit Score
- Free credit report and score
- Credit Score
- Your free credit score
- Cards for bad credit
- Free Credit Score & Credit Report Data. No Credit Card Required.
- Jasper cheap flights
- Richmond cheap flights
- Burlington cheap flights
- Barrie cheap flights
Step 5: Expand your commercial anchors and start looking at backlinks
Here are some types of links you should keep an eye out for:- Links or images on pages marked as sponsored or advertorial
- Links in articles that look editorial but the article talks about compensation
- Links in articles that use commercial anchors unless you believe it truly happened naturally
- Links on pages that have lots of commercial anchor links to other sites
- Links in forum profile pages (forum spam)
- Commercial anchors in blog comment sections (comment spam)
- Links on pages that just don’t feel good… go with your gut
- Links from link networks
- Commercial anchors on article directory sites
- Links from a link network or series of mirror sites on different domains
- Links from a page full of outbound commercial anchor links
- Links from a directory full of commercial anchors
- Followed, directly linking banners on sites under Sponsored-type headings (did they ‘sponsor’ the site to buy PageRank? = bad. Or did they simply get a link as a by-product of a large involved sponsorship? = typically okay.)
- Followed, directly linking images on poor quality, sketchy websites
- Lots of banner links using commercial alt text
- Use a free credit score to check your credit score.
- Use Brand.com to check your score.