If your site has a penalty at the moment then this post will help you work out what’s going on and how to recover.

A lot of people have no idea what they need to do when their site receives a penalty. I find lots of people on forums offering what they consider solid advice without actually knowing what the problem is.

To properly diagnose a penalty you need to know exactly what the problem is. Once you know that, getting out of the penalty is very straight forward.

I created a personal spreadsheet to help diagnose backlink penalties quickly which can be used to create a plan of action moving forward.

I have made a video showing you around the spreadsheet and how to use it to diagnose your own sites. You can dowload the template underneath the video.

What You Will Learn

  1. How to analyse backlink based penalties quickly & easily
  2. How to collect all of the required data
  3. How to setup my backlink analysis spreadsheet template
  4. How to use the data to create a positive & focused plan of action

Click Here to Read the Transcript

WARNING: Steal Your Competitors Search Rankings, Traffic & Income

Resources In The Video

Download Your Backlinks

SEO Tools

Don’t Like Videos? Follow This Instead!

Did your website get hit with penalties from the last Penguin update? This tutorial will show you how to evaluate your backlink profile to check for previous and future penalties.

We’re going to collect all the data required for analysis and use that to make a plan of action to recover from the penalties.

Backlink Profile Spreadsheet

I created my own spreadsheet that has the the backlink profile of two different websites. The columns make it very clear so I can keep track of all the data easily.

I can see clearly what processes I’ve used on either websites, the social media data and keywords which I removed for the video. The backlink data is broken down into two sections – all and indexed links.


The metrics for the backlink data are this:

  1. Total links to the website
  2. % of those links coming from unique domains
  3. % links that match the anchor text
  4. % links that match broad anchor text
  5. % links with no anchor text
  6. % links from deindexed domains
  7. % unindexed links but the domain the link is from is indexed

The metrics for indexed data goes are the same except they’re only for the links that are indexed in Google. I’m assuming Google only cares about the links that are indexed which could give you a heads up on the next Penguin update.

Website 1 Analysis

Website 1 has over 30,000 links but not even half are indexed. 91% of the indexed links have an exact anchor text which is unusually high. The rest of the unindexed links can help lower that percentage.

If you go to the all backlink data, 90% of them have an exact match anchor text which is high as well. Too bad it wasn’t around 20% or else we could have just indexed the links with no exact anchor.

To save website 1, I would create this plan of action:

  1. Index all links that have don’t have an anchor match
  2. Remove or update exact anchor links
  3. Build a balance for generic anchor links for a natural looking profile

Website 2 Analysis

This website is much smaller, with only 191 links with 112 indexed. If you look at the numbers, there is a fairly good mix of broad match, exact match and generic anchors.

So what made this site’s ranking tank?

Take a look at the % links from deindexed domains. 25% is fairly high with Google not trusting 1 out of 4 backlink domains. Care to venture a guess as to what links were used to rank this website?

That’s right – high PR websites. This website was hit with the negative effects of the mass high PR blog deindexation.

Luckily Penguin was not behind this hit so we can build a straight forward recovery plan. I would simply build more links with less of an emphasis to high PR websites and begin tiered link building instead.

How to Analyse Your Own Website

First download all of the backlinks to your website for analysis. There are several tools that can help you do that:

I like to use as many tools as I can so I don’t miss anything. As soon as you got all the URLs, download the excel spreadsheet that I use to sort out your links.

Click on tab 1 and paste in your links there.

site 1

Remove the duplicates and expand the selection.


Next we need to gather some additional information like root domain, anchor text used, status, URL indexed, domain indexed and the page rank of the URL and domain.

To do this, I like to use Scrpebox. Open up Scrapebox and paste the URL list from clipboard.


Click on Trim to Root and export this list. Copy it and paste it in the root domain section of the spreadsheet.

To check the Page Rank, click Check Page Rank and Check Domain Page Rank.

page rank

Click on Export and save as an XLS file. Now to see if the links are indexed, click on Check Indexed and export that again as an XLS document.


Now we need the URL and domain page rank. Clear your list, paste our backlink list again and click Check Page Rank and URL rank.

url rank

Again export both lists as an XLS file and we just have a few more pieces of information to put together.

Remember to name all the XLS documents appropriately so we can differentiate them easily later on!

Open up a text file and paste our links of backlinks into there. Save it and open up another one with just the our actual URL.  Back in Scrapebox, tick the Check Links and open up your website in “Website” and the list of backlinks in “Blog List.” Click Start to begin checking links.


Export and save it again as an XLS document. Now that we got all the information we need, it’s time to export all of that into our spreadsheet.

These are the steps to fill in the anchor text, links to and the status columns:

  1. Open your links found spreadsheet
  2. Type “=vlookup(” in the links to column in our spreadsheet
  3. Click on the backlink in the first column of the links found sheet for the label to pop up – something like A2 and add another comma
  4. Go to the second sheet with the links found and highlight all the sections
  5. Type comma, 2 for the column with the links to, false and a bracket to close

links to

For the anchor text, copy the code and paste it under the anchor text column. Change the 2 in the code to a 3 and repeat the process and change the 3 to a 4 for the status. Highlight the three columns and double click to fill it out.

anchor status links to

The last four columns are filled in the same way. Type in the same code as earlier with =vlookup( except now open the indexed URLs spreadsheet and highlight those columns. Finish it off with false and a bracket like before.

Repeat this process for the rest of the columns for domain indexed, URL page rank and domain page rank. When you’re all done, it should look something like this.

complete list

Now that we’re all done filling out the necessary information, hold select A to highlight everything and then select C to copy it all. Right click, select Paste Special and choose Values. That will turn all our formulas with actual values.

Next we need to delete the backlinks that no longer link to our site. Click Status and then Filter. We want to uncheck “Found” and click OK.


After it filters, highlight everything and delete it since they are the links that don’t link to the website anymore. Go back up to Filter, click Found again and this will bring back all of our live links.

We can finally start filling out our spreadsheet!

Enter in the keywords, click on “# Links Exact Match Keyword”  and replace the red text with your keyword. Do the same for broad match and anywhere else that is red and says “Insert Keyword.”

insert keyword

To find out how many root domains we have, go to the spreadsheet with our backlink profile, highlight it, copy and paste it into another sheet and remove duplicates.

After removing any duplicates, highlight the Root Domain column all the way down to find out how many we have and enter that number back into our analysis spreadsheet.

To find the indexed root domains, go back to the spreadsheet and filter the domain index by those with Yes. Highlight those to see how many we have and enter it into our analysis spreadsheet again.

Now all the correct data is in and our spreadsheet should be completely filled out for website 1.

all data

Now we need to hide all the numbers columns that start with a hash so highlight those and right click to select Hide.


Finally we have all our spreadsheet filled out with the right information. Let’s take a look to see what our backlink profile tells us.

This website is currently ranked on page 1 of Google so it should have some good results. Here’s the analysis so far:

  • 132 links, 46 indexed
  • 11% exact match anchor
  • 6% broad match
  • 83% generic terms
  • 7% links come from deindexed domain

So from these numbers, you can tell this profile is overall pretty decent. My only recommendation is to start indexing the current links since there’s only 46 out of 132 indexed so far.

The Importance of Your Backlink Profile

I’ll make this spreadsheet available to download under the video along with the sample links I used in this tutorial.

You can even use it to analyze other aspects as well like the top keywords and their anchor text percentages.

It’s really important to keep your backlink profile up to date so it doesn’t get hit with penalties and this spreadsheet is really useful for seeing how your site can be improved or figure out why you did get a penalty.

WARNING: Steal Your Competitors Search Rankings, Traffic & Income

Resources In The Tutorial

Download Your Backlinks

SEO Tools

Learn How To Steal  Your Competitors Rankings With My #1 Personal SEO Strategy.
Get instant access to my personal SEO strategy that I use on every single site that I launch time & time again.
 Share On Google+ Now!