Conduct a Backlink Profile Analysis with Excel
By: Osanda Cooray
It is always a good idea to conduct a backlink audit if you suspect that you have been hit by an algorithmic penalty from Google, or you seem to be losing rankings and don’t know why. The data you obtain from a backlink analysis gives you a chance to take a closer look at the links you have and the strategies that have been employed. If you can identify by looking at your profile that a particular strategy is being used to manipulate search engines, there is no question Google is aware of that and will want to penalize you.
I will walk you through on how to conduct a backlink profile analysis using backlink exports from Open Site Explorer, Link Detective, and the amazing power of Excel.
In this case study, clifton.com's backlink profile was analyzed as Cliftons lost a considerable amount of its organic ranking according to Moz ranking reports. Also, in looking at SEO visibility report of Search Metrics, we noticed a significant drop of organic ranking.
The drop had affected several sub-sections of the website, not only the homepage, so one can assume this is much likely to be affected by Penguin 2 update.
What you will need?
List of backlinks extracted from Moz Open Site Explore, Google Webmaster tools & Bing Webmaster tools
Pull External links from OSE
Step 2
Pull Links from Google Webmaster tools
Step 3
Since links extracted from Google Webmaster tTols don't contain any additional data other than the date links were found, we need to find out each URL's respective anchor text, DA & root domain.
In order to check for anchor text each link carries I am using SEO backlinks monitor by MoneyRobot. This is excellent free desktop software that you can use to investigate your backlinks.
Step 4
Once you are done you can highlight all the cells and copy into an excel sheet and remove columns except URL, Anchor Text & URL found. It will look like as follows.
Step 5
We now need to add root domain & domain authority metrics in the given sheet.
You can enter the following formula to extract a domain name from a list of URLs
=MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3)
In order to check bulk DA you can use http://mozcheck.com/
When checking DA please use root domains and remove duplicates which prevents unnecessary API calls. You can use VLookup function to enter relevant DA value to each domain in the sheet later.
Once everything is in place, your sheet should look like as follows.
Step 6
We need to add both Google Webmaster tools & Open Site Explorer data into one excel sheet and remove duplicate URLs. Also we need to add two new columns named Anchor text category & Link type to the new sheet. It will look like as follows.
Step 7
In order to get the link type I am using Link Detective, which will crawl your backlink profile and attempt to classify your links based on the type of link they are. So a few common classifications are directories, profile link, blogroll and article site etc.
Although it won’t possibly be able to classify everything, this can be great for getting a quick view on the breakdown of your link profile.
Once Linkdective has finished classifying your link types, you can download the URL list and include the respective link type in the main sheet. Sometimes it will group certain links as Unknown where you will have to go through and categorized them according. This can be little tedious but once you categorize link types properly it will be much easier for your analysis.
In this analysis, I categorized link types as
Step 8
Next most important part is to categorize anchor text type. I categorized into groups which are
Analysis & Takeaways
Now we have finished gathering required data and are ready to analyze further.
In this analysis I only look at link type, anchor text category, top linked pages, anchor text count and DA distribution.
Now you need to break down your profile with pivot tables.
Pivot table to analyze link type
If you look at this chart you can notice majority of backlinks were coming from directories, link lists created by some SEO agency, event listing pages as well as articles. If you can take a look at link type sheet you can review where most of links coming from are.
You can dig further to analyze which directories are linking to which internal pages based on anchor texts.
Pivot table to analyze anchor text category
When it comes to anchor text type I categorized into four categories exact match, domain match, brand name & not targeted where exact match anchor texts were carrying more than 1.9k links which is definitely a bad sign in terms of a natural back link profile.
Pivot table to analyze page level anchor text and link breakdown
In Top Linked Pages sheet you can review which pages or sections of the website that are heavily targeted by exact match texts.
I've excluded dead links ( 404 ) and contains only exact match anchor text type.
Anchor text distribution across the site
I also look at anchor text data identify any patterns in anchor text distribution. You can notice that too many exact match terms in the anchor text profile.
Identify suspicious links
Here you can see that sites are almost exactly the same, with almost identical content and templates. These links are likely to be suspicious as these were coming from domains with less than 20 DA ( Domain authority ) identical link lists sites followed by exact match anchor texts.
Most of these sites are completely irrelevant and can be considered as link schemes sites that were heavily built on purpose of SEO.
I choose exact match as anchor text category and excluded dead links ( 404 ) & set DA to equal of less than 20
You should do this for your competitors and compare metrics with your site to identify your weaknesses & strengths in terms of link building. What you can investigate further and include in the analysis sheet are
It is always a good idea to conduct a backlink audit if you suspect that you have been hit by an algorithmic penalty from Google, or you seem to be losing rankings and don’t know why. The data you obtain from a backlink analysis gives you a chance to take a closer look at the links you have and the strategies that have been employed. If you can identify by looking at your profile that a particular strategy is being used to manipulate search engines, there is no question Google is aware of that and will want to penalize you.
I will walk you through on how to conduct a backlink profile analysis using backlink exports from Open Site Explorer, Link Detective, and the amazing power of Excel.
In this case study, clifton.com's backlink profile was analyzed as Cliftons lost a considerable amount of its organic ranking according to Moz ranking reports. Also, in looking at SEO visibility report of Search Metrics, we noticed a significant drop of organic ranking.
The drop had affected several sub-sections of the website, not only the homepage, so one can assume this is much likely to be affected by Penguin 2 update.
What you will need?
List of backlinks extracted from Moz Open Site Explore, Google Webmaster tools & Bing Webmaster tools
- Free access to Linkdetective
- SEO Back Links Monitor by MoneyRobot Or Web based backlinks analysis tool
- Microsoft Excel
Pull External links from OSE
Step 2
Pull Links from Google Webmaster tools
Step 3
Since links extracted from Google Webmaster tTols don't contain any additional data other than the date links were found, we need to find out each URL's respective anchor text, DA & root domain.
In order to check for anchor text each link carries I am using SEO backlinks monitor by MoneyRobot. This is excellent free desktop software that you can use to investigate your backlinks.
Step 4
Once you are done you can highlight all the cells and copy into an excel sheet and remove columns except URL, Anchor Text & URL found. It will look like as follows.
Step 5
We now need to add root domain & domain authority metrics in the given sheet.
You can enter the following formula to extract a domain name from a list of URLs
=MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3)
In order to check bulk DA you can use http://mozcheck.com/
When checking DA please use root domains and remove duplicates which prevents unnecessary API calls. You can use VLookup function to enter relevant DA value to each domain in the sheet later.
Once everything is in place, your sheet should look like as follows.
Step 6
We need to add both Google Webmaster tools & Open Site Explorer data into one excel sheet and remove duplicate URLs. Also we need to add two new columns named Anchor text category & Link type to the new sheet. It will look like as follows.
Step 7
In order to get the link type I am using Link Detective, which will crawl your backlink profile and attempt to classify your links based on the type of link they are. So a few common classifications are directories, profile link, blogroll and article site etc.
Although it won’t possibly be able to classify everything, this can be great for getting a quick view on the breakdown of your link profile.
Once Linkdective has finished classifying your link types, you can download the URL list and include the respective link type in the main sheet. Sometimes it will group certain links as Unknown where you will have to go through and categorized them according. This can be little tedious but once you categorize link types properly it will be much easier for your analysis.
In this analysis, I categorized link types as
- 301 Redirects
- Advertisements
- Articles
- Dead Links ( 404 )
- Directories
- Event listing
- Forums posts
- Link lists
- Press releases
- Profiles links
- Sidebar links
Step 8
Next most important part is to categorize anchor text type. I categorized into groups which are
- Non-targeted – non-targeted key terms, images etc.
- Exact match - possible money keywords, link building keywords
- Domain match - keywords containing URLs
- Brand match – brand keywords
Analysis & Takeaways
Now we have finished gathering required data and are ready to analyze further.
In this analysis I only look at link type, anchor text category, top linked pages, anchor text count and DA distribution.
Now you need to break down your profile with pivot tables.
Pivot table to analyze link type
If you look at this chart you can notice majority of backlinks were coming from directories, link lists created by some SEO agency, event listing pages as well as articles. If you can take a look at link type sheet you can review where most of links coming from are.
You can dig further to analyze which directories are linking to which internal pages based on anchor texts.
Pivot table to analyze anchor text category
When it comes to anchor text type I categorized into four categories exact match, domain match, brand name & not targeted where exact match anchor texts were carrying more than 1.9k links which is definitely a bad sign in terms of a natural back link profile.
Pivot table to analyze page level anchor text and link breakdown
In Top Linked Pages sheet you can review which pages or sections of the website that are heavily targeted by exact match texts.
I've excluded dead links ( 404 ) and contains only exact match anchor text type.
Anchor text distribution across the site
I also look at anchor text data identify any patterns in anchor text distribution. You can notice that too many exact match terms in the anchor text profile.
Identify suspicious links
Here you can see that sites are almost exactly the same, with almost identical content and templates. These links are likely to be suspicious as these were coming from domains with less than 20 DA ( Domain authority ) identical link lists sites followed by exact match anchor texts.
Most of these sites are completely irrelevant and can be considered as link schemes sites that were heavily built on purpose of SEO.
I choose exact match as anchor text category and excluded dead links ( 404 ) & set DA to equal of less than 20
You should do this for your competitors and compare metrics with your site to identify your weaknesses & strengths in terms of link building. What you can investigate further and include in the analysis sheet are
- Are most links followed, nofollowed or redirected?
- How are links coded?
- How many of the links pointing to the home page vs internal pages?
- What is the site wide link ratio?
- What is the distribution of Domain authority & page authority of links?
- How many of links have the Authorship?
- Have links been shared on Social networks?
Comments
Post a Comment