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
- How to analyse backlink based penalties quickly & easily
- How to collect all of the required data
- How to setup my backlink analysis spreadsheet template
- How to use the data to create a positive & focused plan of action
Hi guys Matthew Woodward here with a new video that will teach you how to analyse your backlink profile for current and potential penalties in the future.
You’re going to learn how to analyse backlink based penalties quickly and easily. How to collect all of the data that we need to complete the analysis. How to import this data into my backlink analysis spreadsheet template and how to use this data to create a positive plan of action to move forward.
So let’s get straight into things with a quick tour of the spreadsheet I created. I have the backlinks of 2 sites plugged into this spreadsheet which provides me with all sorts of actionable data.
I’m always testing and developing my backlink process so the link process column helps me keep track of exactly which process I have used on which site. I also have the keyword in the general section which I have removed for the purpose of this video.
I also like to keep track of social data such as likes, tweets and votes on Google+
I break the backlink data down into 2 parts, all backlink data and then the same metrics just for just indexed links.
These metrics are-
The total amount of backlinks pointing to the site or page
What percentage of those links come from unique domains
What percentage of those links are the exact match anchor text such as pay day loans
The percentage of those links that use a broad match anchor such as cheap pay day loans online
The percentage of links that have no anchor match such as your URL or generic anchors like click here
The percentage of links coming from deindexed domains
The percentage of links that are not indexed but the actual domain the link is coming from is indexed
In the red section you can see the similar metrics only for the backlinks that are currently indexed in Google.
The reason it is broken down like this is because I’m making the assumption that Google ranks site based on the indexed backlinks it knows about and doesn’t count any efforts from links that aren’t indexed.
This could be used to give you the heads up on an upcoming penguin based penalty.
Both of the sites on this spreadsheet have different problems based on their backlink profiles.
So let’s take a closer look at site 1 first, it has over 3,000 links but only 966 of them are indexed at the moment. Looking at the percentage of indexed links with exact match anchor we can see that is unnaturally high at 91%.
Perhaps some of those 2,000 unindexed links could be used to save our skin and lower that percentage. If we come across to the % links exact match anchor text in the all backlink data columns we can see its sat at 90%. It would of been nice to see this figure sat at closer to 20% because then all we would have to do is index all the backlinks that don’t include our exact match anchor, no dice this time though.
If you haven’t figured it out already we can clearly see the site is going to be hit by penguin if it hasn’t been hit already.
As a plan of action to save this site I would-
First of all I would make sure all of the links that have no anchor match are indexed and that would be these 10 percent here
I’d then see if there are any opportunities to bulk remove or update the links with exact match anchors, for example most of the sites linking to this site are wiki links that can easily be edited.
I would then look to put together a plan of action to build x amount of links with generic anchor texts until these percentages look more natural
So lets move onto the 2nd site.
This has a much smaller link sample of just 191 links of which 112 are indexed. The exact match anchor percentage isn’t fantastic but it’s not high enough to trip a penalty either at the time of publishing this video. In fact there is a pretty good mix of exact match anchors, broad match anchors and generic anchors.
So what is it caused this site to drop like stone in the serps?
The clue lies in the percentage of links from deindexed domains. At 25% that is pretty high, that means google explicitly states they do not trust the domains of 1 in 4 backlinks.
Can anyone hazard a guess at what types of links were used to rank the 2nd site?
You guessed it, it was high PR blog networks.
This site tanked around the time of the mass high PR blog network deindexation which included everything from ALN to private networks. This created a huge negative velocity in links and trust that was flowing to the site overnight, and just like that the rankings disappeared.
Knowing that penguin is not to blame I can come up with a pretty straight forward plan of action to recover the site. It’s just a case of building more links to it with less of a reliance on high PR blog networks and move it over to the tiered link building process instead.
I hope you can see the benefit in taking the time to put a spreadsheet like this together, it really does make things very easy to work out and move forward positively.
To analyse your own website the first thing you need to do is download all of the backlinks pointing to the page that you want to analyse.
All we want to end up with is a raw list of the URLs.
My first point of call is the SEOMoz Open Site Explorer, if you do not have a subscription you can get a 30 day free trial which you can use to grab your links, the link for the trial is underneath the video on my blog.
I also have a subscription to Ahrefs.com which I prefer to use over MajesticSEO. If you don’t have a subscription, the free account will give you some of your backlinks.
SEO Spy Glass is part of the SEO Power Suite and that will go out and hunt down your sites backlinks from a range of sources.
You can also log into Google Webmaster Tools and Bing Site explorer to download link samples.
I prefer to download my backlinks from as many places as possible to make sure I haven’t missed any, the more accurate your source data is, the more accurate your final analysis will be.
Once you have a raw list of the URL’s you can move onto the next step.
Download the template spreadsheet from the post on my blog and open it up. Come over to the site 1 tab down here, paste in your list of backlinks.
Once you’ve done that come up to data and remove duplicates, expand the selection and remove duplicates and just hit on ok.
Once you’ve done that we then need to get the additional points of data that you can see up here. They include the root domain, where the backlink actually links to, the anchor text that’s used, whether the backlink is still there or has been deleted, if the actual URL is indexed, if the actual domain is indexed, what the URL page rank is and what the domain page rank is.
To get all of this data I’m going to use scrapebox. The first thing that you want to do is import your url list and paste it from the clipboard. Then trim to root. Now export this URL list and copy it to your clipboard and paste it into the root domain section.
We are also going to check the page rank for these domains here. To do that click check page rank, check domain page rank. I’ll resume this video once this process is finished. When that’s finished you will have all of the page rank values for the domains here. Just click on export, export as xls and save it as domainpr.
Next we want to check if these domains are indexed or not so we just hit checked indexed and whether they are Google indexed or not. I’ll resume the video once this is done.
Ok so the index check for the domains is now completed we just need to export that as xls again domainindexed.
So we’ve now got the data for the domain indexed column and the domain pr column, we need to get the url indexed and url page column in the same way. So if clear this list, import our list of backlinks again and then go with check page rank first and make sure you click on get URL page rank and I’ll be back once that’s finished.
Ok so the page rank check is complete, then export urls & PR as XLS and we’ll call it urlpr click save, ok and then run the index check and I’ll be back once that’s completed.
Ok the index check for the URL’s is completed hit ok, export that list again as xls and that is urlindexed and hit save.
And the last bit of data we need to collect is the links to, anchor text and status columns. To do that we need to create two text files. The first text file is just going to be a list of our backlinks. So if we just save that as listofbacklinks.
And the second file needs to be our actual url which is www.opendoorloans.co.uk and we’ll just save that as our website.
I would just like to point out that this isn’t my site I just picked a result of the first page for payday loans which is a pretty competitive niche. I have no idea how this analysis is going to turn out so let’s hope it provides us with some actionable data at the end of it.
Anyway once you’ve got a text file of your list of backlinks and your website url you can come down to here hit check links, open your website and then open your list of backlinks here. I always do this check without any proxies in use and then just come here and click check links. So you can see it’s going to check 232 websites for our link to this 1 website. So just click start and sit back and wait to finish and I’ll be back in a moment.
Ok now that’s completed export links, export all as excel. And I’m just going to call that linksfound and hit save.
So now we have all of the data that we need and we just need to get it into excel which is actually pretty straightforward to do.
Ok the first thing that we are going to do is populate the links to, anchor text and status columns. To do that we need to open up our spreadsheet called links found and then in this links to type =vlookup( click on our backlink here a comma, come onto our second sheet, click up here and scroll all the way across to column D. Click up here, enter a comma and then we want to return this column of data and then we write comma false close brackets hit enter. And you can see there that has pulled in where that pages links to.
And then copy this, paste it into here, change this 2 to a 3 which brings our anchor text in and then paste it in again but this time change that to a 4 which tells us if the link is found or not. Then highlight these just double click here and that will fill it down for all the backlinks.
So now all we need to do is populate these last 4 columns in a similar kind of way. So we open up the URL indexed spreadsheet first. Come across to here, =vlookup( click on the URL, comma, come across to this sheet, highlight these 2, comma 2 comma false close brackets and then we can fill that down.
Close the URLindexed sheet, open the domain indexed sheet. =vlookup( this time we click on the root domain, hit comma, go over to this sheet, highlight these comma 2 comma false close bracket. And then fill that down.
I’m just quickly going to do the last 2 columns so I’ll be back in a moment.
So now we have a complete set of data to work from but there’s a few things that we need to do. First of all you need to press ctrl + a to select everything, press ctrl + c to copy it, right click, click paste special, values and ok. And that will replace all of the formulas here with the actual values that the formulas returned.
The next thing that we need to do is remove any backlinks that are no longer linking to the site. So to do that we just click on status here and then filter, and we want to untick found and click on ok.
And then down here we can just highlight all of these, right click delete row and then in the status column up here select found and this will return a list of your current valid backlinks that are linking to your site right now.
Now there’s just a final few things that we need to do to get all of the stats to load in here correctly. First of all lets update the keyword here which is payday loans and if you come across here to where it’s red insert keyword, payday loans.
We also need to insert the keyword here, payday loans. And keeping coming across until we see something else in red. And this is the last keyword that we need to change here.
And then these insert number, to get the number of the root domains because I couldn’t for the life of me work out the formula to do it. If we come to our backlink profile, select all copy it and come to a new sheet, paste it and then click on the data remove duplicates. We want to unselect all and just remove the duplicate root domains and click on ok. In this case there were no duplicate values hit on OK.
And then if we select all of the root domains here we can see there is a total of 131. So if we come back to our template sheet which I’m having trouble finding, there we go. We can just type 131 into there. Then we need to know how many of the root domains are actually indexed.
So if we go back here and filter it, domain indexed, just yes and then have a look at this number here and that is 122. So we can just type that in there.
Ok we now have all of the data in place everything’s looking up correctly. We need to hide the number columns here anything that starts with the hash here, so highlight those right click and hide.
And the same for these 2, hide. Then come across here and hide these columns here. And that it we’ve now got all of the data so let’s have a look at what it all means.
Bear in mind that this site currently ranks on page 1 of Google for payday loans so we are going to expect it to have a pretty good backlink profile. You can see there are a total of 132 links of which 46 are currently indexed.
The anchor text break down is actually pretty good, 11 percent exact match, 6 percent broad match and 83 percent generic terms. And that is also reflected in the indexed backlinks as well.
What’s also interested is it only has 7% of its links coming from a deindexed domain so that’s pretty good especially when you compare it with 25% of the example I showed you earlier.
In general this site has an overall very good profile, the only plan of action that I would recommend is that theres only a low percentage of the current links indexed so I would look at getting those indexed to move the site further up the rankings.
I’m going to save this spreadsheet now and upload it to the site so you can download it. The download link will be available underneath the video. I’ll leave all of the payday loans links things in there so you’ve got a sample of stuff to get you started.
And then you just need to repeat the process you’ve seen throughout this video but for site 2 using the site 2 tab down here.
I hope all of that made sense to you and you now have the ability to analyse your backlink profile for potential penalty problems.
You can use this spreadsheet for other things as well such as analysing the top 10 results of a keyword to replicate what they are doing and see their how anchor text percentages compare and so on.
You can build on this spreadsheet further by adding other metrics such as average URL and domain PR.
Or you could use something link seo tools for excel to beef up the spreadsheet even more, check out the link to it under this video you will be very impressed with what it can do.
If you want to see more videos and tutorials like this then head over to my blog at www.matthewwoodward.co.uk and sign up to my email list.
I never send out any emails promoting products or guru spam, I only ever send an email to let you know when a new post or tutorial is available.
If you want to say thanks, You can do that by simply sharing the video with others!
Resources In The Video
Download Your Backlinks
- SEOMoz (free 30 day trial) – Great suite of tools you can use to download your backlinks
- Ahrefs – Get a free account to download some more of your links
- SEO Spy Glass (free version available) – Software that downloads your backlinks from multiple sources
- Google Webmaster Tools – Login to GWT to download a sample of your backlinks
- Bing Site Explorer – Login to the tools to get a sample of backlinks