Home | Search Engine Optimization | Three Phenomenal Excel Spreadsheets for Link Analysis

Three Phenomenal Excel Spreadsheets for Link Analysis

John Doherty —  August 29, 2011
  • Buffer
  • Vote on Hacker News
  • Sharebar
  • Buffer
  • Vote on Hacker News

Do you ever say to yourself: “I really wish I had a spreadsheet that would give me awesome graphs about my link profile distribution”? No? Just me? I doubt it.

Some days we need to have a quick way of visualizing our link profile to know where we stand. All linkbuilders and SEOs are looking for a “natural” link profile. We want a profile that has some lower domain authority links, some good solid DA links, and some awesome links (Moz, Distilled, NY Times) (maybe not the Wall Street Journal because you’ve probably been caught buying links. Oops!). But how can you find such a visualization, and fast?

Well, you could build out a spreadsheet to do it for you, but that’s going to take up some of your valuable billable time (and bosses like billable time).

To help you out (by keeping your boss happy and you with a job), I have found a couple of Excel spreadsheet templates that you can/should use to give you a visualization of the PA of links to your site broken down by increments of 10, one to help you find the DA broken down in the same way, and one to give you the top 10 keyword phrases included in links to your site.

Enjoy!

Link Profiling with OpenSiteExplorer

In this post, Dr Pete provides a downloadable Excel spreadsheet wherein you can copy and paste your OSE export. It will clean the data and provide you with a graph of the distribution of PA across your backlinks.

I do highly recommend reading the post as it explains the reasons for these graphs well, but if you want the spreadsheet, Link Profile from Dr Pete.

Excel Spreadsheet to find the distribution of DA across your backlinks

This downloadable spreadsheet is the same as the one available above, but I have hacked it so that you receive the Domain Authority distribution across your backlinks. Since our industry now believes that the DA greatly affects the strength of a link, I thought this might be helpful.

Here’s the spreadsheet: Link Profile DA TEMPLATE.

Here’s a pretty graph that you will get from it:

This is Everywhereist's, by the way. Baller.

Free Anchor Text Distribution Spreadsheet

Finally we’ve got Ian Bowden’s Anchor Text Distribution Spreadsheet. Once again using OpenSiteExplorer, this spreadsheet will visualize your anchor text distribution. This is helpful in order to see if any of your anchor text is unduly out of proportion and therefore looking unnatural.

I recommend reading the whole post which you can find here because it contains all of the simple instructions. If you want to download the sheet, you can Top 10 Keywords TEMPLATE.

I hope these sheets are helpful to you!

Happy optimizing.

John Doherty

Posts

I'm the Senior Marketing Manager of HotPads.com, based in San Francisco. Previous to Hotpads I worked at Distilled for 2 years as an online marketing consultant. In my spare time I shoot lifestyle photography, ski, rock climb, and update my Twitter and Google+ accounts.

36 responses to Three Phenomenal Excel Spreadsheets for Link Analysis

  1. This is a great post mate! I find resources like these spreadsheets really valuable as they save time and make things more organised!

    Thanks for sharing :)

    • Hey thanks Paul! You are absolutely right, these sheets can make life so much easier. I love building out templated tools that process data for me quickly.

      Now to automate the data gathering too…hmmm.

  2. This is great! My work dont mind paying me for following you every where! I love your passion and energy and the love for excel.

    - Ferdous

  3. Thanks for sharing your update to pete’s spreadsheet! useful for quick and visual benchmarking.

  4. Hi.

    This is exactly what I have been looking for. Previously, for time saving purposes I would simply collect a percentage of the top domains in relation to overall unique linking domains, which would then be compared against the competition.

    However, this technique certainly helps to be more thorough and understand the entire link profile to then make better actionable link building judgements from that.

    By the way, I must admit, I’m a complete novice on excel and I’m probably the only one who is struggling to create the graph properly; but can you tell me, when you have exported the linking data from OSE, I notice the sheet entitled ‘Domains’ collates the OSE into a cleaner format. But the important, ‘MAX DA’ & ‘Graph’ sheets do not change to represent my data. My first thought was to create a pivot table from the domain sheet information, however it didn’t get me anywhere.

    I would appreciate your help But a great excel creation! Thanks

    • Denver –
      Sorry it’s taken me so long to reply. When the new data doesn’t populate, all you have to do is right click on the column and click “Refresh”. That will bring the new data in and populate your graph correctly.

      Good luck!

  5. Brilliant. Thanks for that John!

  6. John,

    Great post. What about this one from SEOmoz: http://www.seomoz.org/blog/link-profile-tool-to-discover-linking-activity

    Great way to compare your link profile to the competition. If you do not have the paid API it requires a little manual work, but immensely valuable.

    • Well, I agree that it’s a great tool, but it’s not an Excel spreadsheet so that’s why I didn’t list it. Also, that tool hadn’t been created when I wrote my post ;-)

  7. Am I correct that OSE exports are only available if you’re a PRO member of SEOmoz at $99/month?

    • Yes, however if you don’t have an a paid account, you can run a few reports a day (4-5 I think), but with these, a lot of data is missing.

      Imo, it is worth paying the $99/month even if it’s just for OSE.

  8. Hi,

    I have run the first of Dr. Pete for PA and second spreadsheet from you with DA.

    Is there a way to get a subdomain Authority Report adapting your Excel spreadsheet?

    I found it the DA not precise enough, because many profiles have back links from subdomains of high DA free website/blog services, like wordpress, blogspot, edublogs.org, webstarts.com etc. So their profile looks with DA analysis better than it is in reality.

    Thanks and greetings from Hamburg, Germany

  9. Older post, but still very useful info. I’m just getting into using Excel for backlink profiling. I’ve only used Twitter’s API and php so this is a bit different for me.

    I’ll be implementing this spread sheet to expedite domain procuring. Thanks!

Trackbacks and Pingbacks:

  1. 7 Achievable Steps For Great SEO After The Penguin Update | Sphinx Web Design Experts - May 21, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  2. On The Last Turn Of The Universe – Top Ducks Secret » 7 Achievable Steps For Great SEO After The Penguin Update - May 21, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  3. 7 Achievable Steps For Great SEO After The Penguin Update | | The SEO BadgerThe SEO Badger - May 21, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  4. 7 Achievable Steps For Great SEO After The Penguin Update Chris Warren « Amalik - May 21, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  5. 7 Achievable Steps For Great SEO After The Penguin Update | Traffixer.com - Get more SEO traffic today. The seo Hub. - May 21, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  6. 7 Achievable Steps For Great SEO After The Penguin Update | Web Siêu Rẻ | Thiết kế Website Giá Rẻ | Dịch Vụ Thiết Kế Website Giá rẻ - May 21, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  7. 7 Achievable Steps For Great SEO After The Penguin Update | Sky Backlink - May 21, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  8. 7 Achievable Steps For Great SEO After The Penguin Update | Best Traffic Tips - May 21, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  9. 7 Achievable Steps For Great SEO After The Penguin Update | Clixto7 - May 21, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  10. 7 Achievable Steps For Great SEO After The Penguin Update | Ranking Maine - May 21, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  11. Search Engine Optimization » Blog Archive » 7 Achievable Steps For Great SEO After The Penguin Update - May 21, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  12. 7 Achievable Steps For Great SEO After The Penguin Update | Montachusett Internet Marketing - May 21, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  13. 7 steps to Great SEO after Penguin :: Google Penguin Update - May 23, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  14. 7 Achievable Steps For Great SEO After The Penguin Update - May 24, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  15. 7 Achievable Steps For Great SEO After The Penguin Update - May 24, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  16. 7 Achievable Steps For Great SEO After The Penguin Update - Welcome to Dhrubok : Welcome to Dhrubok - May 25, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  17. 7 Achievable Steps For Great SEO After The Penguin Update : Current News - May 25, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  18. 7 Achievable Steps For Great SEO After The Penguin Update | CS5 Design - May 25, 2012

    [...] Great Excel templates for DA/PA analysis [...]

  19. Strategies for Diagnosing Penguin and Recovering | distilled - June 14, 2012

    [...] created a Domain Authority checker spreadsheet based off of Dr Pete’s that is the second in this post, or you can just download it directly HERE and go to the post to figure out how to use it. [...]

  20. Strategies for Diagnosing Penguin and Recovering | Reputation Expert - June 14, 2012

    [...] created a Domain Authority checker spreadsheet based off of Dr Pete’s that is the second in this post, or you can just download it directly HERE and go to the post to figure out how to use it. [...]

  21. 135 Articles to Make SEO Your Bitch | Cucumber Nebula - August 14, 2012

    [...] Three Phenomenal Excel Spreadsheets for Link Analysis [...]

  22. Google Disavow Tool: Fighting the Penguin Just Got Easier -- Or Did It? | Article Boss - November 10, 2012

    [...] means that keeping track of exactly who is posting them, and to where, is critical; you can use a spreadsheet for this. The good news is that bad ones you might have generated can usually be taken [...]