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.
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:
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!
36 thoughts on “Three Phenomenal Excel Spreadsheets for Link Analysis”
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.
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.
Thanks for sharing your update to pete’s spreadsheet! useful for quick and visual benchmarking.
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
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.
Brilliant. Thanks for that John!
No problem! If you find another great way to do similar things, please share!
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 😉
Pingback: 7 Achievable Steps For Great SEO After The Penguin Update | Sphinx Web Design Experts
Pingback: On The Last Turn Of The Universe – Top Ducks Secret » 7 Achievable Steps For Great SEO After The Penguin Update
Pingback: 7 Achievable Steps For Great SEO After The Penguin Update | | The SEO BadgerThe SEO Badger
Pingback: 7 Achievable Steps For Great SEO After The Penguin Update Chris Warren « Amalik
Pingback: 7 Achievable Steps For Great SEO After The Penguin Update | Traffixer.com - Get more SEO traffic today. The seo Hub.
Pingback: 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ẻ
Pingback: 7 Achievable Steps For Great SEO After The Penguin Update | Sky Backlink
Pingback: 7 Achievable Steps For Great SEO After The Penguin Update | Best Traffic Tips
Pingback: 7 Achievable Steps For Great SEO After The Penguin Update | Clixto7
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.
Pingback: 7 Achievable Steps For Great SEO After The Penguin Update | Ranking Maine
Pingback: Search Engine Optimization » Blog Archive » 7 Achievable Steps For Great SEO After The Penguin Update
Pingback: 7 Achievable Steps For Great SEO After The Penguin Update | Montachusett Internet Marketing
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
Pingback: 7 steps to Great SEO after Penguin :: Google Penguin Update
Pingback: 7 Achievable Steps For Great SEO After The Penguin Update
Pingback: 7 Achievable Steps For Great SEO After The Penguin Update
Pingback: 7 Achievable Steps For Great SEO After The Penguin Update - Welcome to Dhrubok : Welcome to Dhrubok
Pingback: 7 Achievable Steps For Great SEO After The Penguin Update : Current News
Pingback: 7 Achievable Steps For Great SEO After The Penguin Update | CS5 Design
Pingback: Strategies for Diagnosing Penguin and Recovering | distilled
Pingback: Strategies for Diagnosing Penguin and Recovering | Reputation Expert
Pingback: 135 Articles to Make SEO Your Bitch | Cucumber Nebula
Pingback: Google Disavow Tool: Fighting the Penguin Just Got Easier -- Or Did It? | Article Boss
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!
Comments are closed.