I love SEO tools. I’m sort of a pack-rat when it comes to Google Docs tools and Excel add-ins. I went absolutely nuts when Tom Critchlow posted his Google Docs SEO tutorial on distilled. Since then, I’ve rolled a few tools of my own and collected pretty much anything to do with SEO and spreadsheets since then.
I’m a firm believer in bootstrapping. As an SEO consultant, the less I have to spend on tools, the more I can reinvest in my company and grow the business. With an eye toward the virtues of bootstrapping, I’ve assembled a list of the best SEO tools for Excel and Google docs I’ve ever seen. I hope you find it as endlessly intriguing and useful as I do.
Before we get started, be sure you have an SEOmoz/Linkscape API key. That will be important.
Google Docs Tools
First off, I want to show you a tool I concocted after reading Tom’s tutorial. It borrows heavily from the example in Lesson two, but I want to give you a quick idea of the potential here; I’m by no means a programmer, but if I can make my own tools with these things, so can you.
Long Tail Keyword Prospecting
Long Tail Keyword Prospecting - Make a Copy
Long Tail Keyword Prospecting - Instructions (right here!)
The basic idea behind this tool is to quickly explode your keyword list with long-tail goodness. It’s not an exceptionally robust tool, but it’s great for brainstorming article topics and the like.
You’ve got four columns here. The formulas behind the sheet will slap the various permutations together and run each of them through Google Suggest, spitting out a list of 2-10 keyword suggestions.
The really fun thing about this tool is how easy it is to drill down to the uber long-tail phrases; I got a suggestion for “car speakers reviews” from the root “car speakers”, so let’s drill a bit deeper and run the suggest for “car speakers reviews” by adding “reviews” to our 3rd word column.
Ta-da! Instant prospecting. Follow the link above and make a copy of the spreadsheet. Then, have your way with it.
Content Strategy Generator
Content Idea Generator - Make a Copy
Content Idea Generator - Instructions
The folks at SEOgadget deserve a huge thanks for this; almost half of the tools I’ve collected have come from them. Their Content Strategy Generator Tool is an amazing piece of work that gives you more trending headlines and topics on a keyword search than you could possibly handle in one sitting. Couple this with the link prospecting tool, and you’ll never be able to use the “writer’s block” excuse again.
This tool doesn’t take much explanation: the results speak for themselves. Drop a topical keyword in the query box (currently holds “hotel”) and watch the magic happen. The sheet will automagically pull top news headlines, tweets, YouTube videos, Digg and Reddit results, Topsy results, Yahoo! Answers, Blog Catalog results, Fark articles and more. I know, I cried too.
This sheet will keep your finger firmly on the pulse of the news in your niche. Just thinking about the possibilities makes me salivate.
SERP Competitive Analysis
SERP Competitive Analysis - Make a Copy
SERP Competitive Analysis - Instructions
Distilled practically sweats innovation and tools, and this one from Tom Anthony is a salty, gorgeous example of that.
When you want to analyze keyword competition quickly, you can crack open this spreadsheet and see the MozRank, domain authority and number of linking root domains.
It’s also a great way to give a client an at-a-glance look at what they’re up against. Maybe it will help them manage their expectations when they’re telling you to get them to rank #1 for “credit cards.” ;)
Oh, and remember when I said you’d need a Linkscape API token? That time is now.
SEOmoz API for Google Docs
SEOmoz API for Google Docs - Make a Copy
SEOmoz API for Google Docs - Instructions
This one is pretty self-explanatory; it pulls Linkscape/OpenSiteExplorer data into a Google spreadsheet. This is a great baseline template for rolling your own competitive analysis tools and the like. I don’t really feel like this needs more explanation, so go in and mess around with it. If you break it, just make a new copy from the link above.
OSE Link Profile Tool
OSE Link Profile Tool - Make a Copy
OSE Link Profile Tool - Instructions
Oh, holy crap. It’s Tom Anthony again! I’d forgotten where I’d found this tool until I started writing this post. Let us all bow our heads and offer our oblations to the great Tom Anthony. Amen.
This is similar to the Link Detective tool shared in this post not too long ago. It builds out a visual representation of your link profile, helping you identify anomalies that you can smooth out or correct.
In the instructions Tom provides in the link above, you can see his analysis of a client’s link profile, highlighting the fact that they have an abnormally large percentage of links from sites with a domain authority of 25-34. The high concentration of similar domain authorities can be a signal of a link wheel, blog network or paid link. Instant actionable data! Delicious.
Excel Tools
Oh, you thought we were done? No way, Jose. Now we’re to the Excel section. Excel has a few advantages over Google Docs spreadsheets, and Gdocs does better with some things than Excel. The takeaway is that it’s better to use both than to rely on just one. So here are the three best Excel SEO tools I’ve seen.
AdWords API Extension for Excel
AdWords API Extension for Excel - Download
AdWords API Extension for Excel - Instructions
Oh look, SEOgadget again, who could’ve figured? ;)
This tools makes analyzing potential traffic crazy easy; toss in a list of the keywords you’re targeting, mess with the numbers and roll a formula to determine CTR and you’ve got some solid projections. While it does require an AdWords API key (which can be difficult to obtain if you’re not a PPC guy), it’s well worth the effort.
The possibilities with this add-in are immense; you can roll this tool together with OpenSiteExplorer reports and domain-centric keyword suggestions to quickly estimate competitor’s search traffic, or, you can judge the fidelity of Google’s predictions against your own traffic data by pulling Google Analytics reports into Excel.
Inbound Link Categorization
Inbound Link Categorization - Download
Inbound Link Categorization - Instructions
Let’s hear it one more time for SEOgadget! Those guys are awesome.
This tool is a lot like the OSE Link Profile Google Docs tool shared above, but in Excel. It also builds in a categorization element that makes segmenting link profiles easy. Oh, and the reports look super sexy too.
You pull in your link profile CSV from OpenSiteExplorer and this spreadsheet will analyze and categorize your link profile, showing you anchor text distribution, and buckets of links based on known directories, article distributors, blog networks and so on. Sexy, sexy data.
Niels Bosma's SeoTools for Excel
Niels Bosma’s SeoTools for Excel - Download (32-bit OS)
Niels Bosma’s SeoTools for Excel - Download (64-bit OS)
Niels Bosma’s SeoTools for Excel - Instructions
This is the holy grail of Excel SEO extensions. It has one of the most robust feature sets I’ve seen in an extension, and Niels is updating it constantly. It has mind-boggling scraping capabilities (get a proxy if you plan on using them a lot) and a ton of on-page analysis tools.
I don’t think I can say enough about this extension; it’s one of the best free SEO tools out there, and you can use its functions to roll your own tools. I don’t need to waste your time talking about it. Go download it and have fun!
Excel and Google Docs can be some of the best agile tools around, and these are some of the best I’ve found.
What are your favorite Excel/Google Docs SEO tools? Did I miss one?
Mitch,
Awesome resource! I just sent a few of my co-workers these links. From now on, I can just send one link, lol. Also, I love Tom Anthony's tool, but for some reason it's not pulling the data from the Array quite right. Francois Goube made a slightly updated version of Tom's tool that I thought I should point out.
https://bit.ly/seoops
Also, if for some reason anyone's script isn't working, download the "Site Relvenacy" script in the Google Docs Script Gallery. When you do, you can just use getPA and getDA functions to get the data and don't have to "hide" data you don't want. It's completely plug and play. Though it's quite finacky sometimes, so it's best to learn how to change the script to your liking.
Nice post, an addition to my resources!
Thanks Asad! One can never have too many tools. ;)
Yeah perfectly said, btw let me share my personal idea here, I am looking for some way to integrate tools together, something which can take the traffic stuff from Google Analytics, keyword and backlink data from GWT, other ranking matrices from the opensiteexplorer and so on...
Is there any such solution for this? I mean is there tool that can give us an integrated solution for this? Or can we make one such in excel to extract lots of our desired stuff from various sources?
Really good post. I think this goes to the core of what many SEOs are facing right now: total data overload. Between social media, analytics, link data, rankings....it's all just too much to handle at one time. I love tools like this that distill stuff down. We don't have time to check all these numbers manually, but with a little spreadsheet magic...bam!
I totally agree! Scaling to a large number of clients means data overload and a lack of focus on metrics that really matter. These are some great looking tools.
Great stuff, Mitch!
I really LOVE SEOgadget, but hate that it isn't able to do this keyword magic stuff inside Google Docs as well..
High five! :-)
Trond
High five! ;)
It is a shame. There are just some things Gdocs can do that Excel can't, and vice versa. It's unfortunate, especially since it's free and the requests are sent from Google's IP address.
One day it may all be united under a single application... one day...
Great, this just make me realise that we all have our own set of tools.. so many different ways to achieve our goals in SEO.. yet nothing is crystal clear.
Great tools and awesome list. Now if we could only get Niels' SEO tools on Mac....
Great post, hopefully I'll get these working successfully following your advice as in the past they've been a little temperamental.
It would be if SEOMoz would somehow incorporate the Google Docs stuff so it could be set up with a few clicks, but not sure if that's possible.
I think you meant "would be awesome if" or something. ;)
The sheet here might be the closest you'll be able to get to it. Just make a copy and put in your API key and it should work... Let me know if it doesn't, though. I'd be happy to help!
Yes, you're right. I was typing that with sleepy fingers. Our office is very empty with nearly everyone away at the moment but I hope to try it soon. Thanks.
Way to go Mitch, thanks for sharing. I'm coding one for social media sites like
Topsy, and Social Mentions.
cheers!
Good deal! Once you have that finished, drop a comment here with a link to it. I'd love to check it out.
Nice! I'm also interested in learning more about what you're building. Keep us informed.
Thanks for sharing, I can't wait to try out some of these tools. I seem to be having a problem with downloading the excel files. Everytime I try to download it, I have to request premission to get it. Am I doing something wrong?
No, I just failed to set the permissions to the correct level. =/ Apologies, it is now fixed and you can view/make copies.
A big +1 for this. Having a programming background, I never thought I'd be using Google docs as much as I do for organizing my SEO. I use it daily, and you managed to actually provide some killer new insights here (especially love the OSE API use).
As a side note, I noticed that the first few spreadsheet copies that I clicked above aren't full on "public", but leave me needing to request access. Not sure if that's how you intended it or not.
Yes, sorry. I've switched the permissions and the sheets should all be accessible now.
I have very basic programming knowledge, and advanced Google Docs/Excel tools have gone a long way towards bridging the gap between my pure copywriting background and a working knowledge of scripts and real programming languages.
very clear and usefull stuff above there thx for sharing :)
No problem, I hope you find it as useful as I have. The guys at SEO Gadget/Distilled and others that made these tools are fantastic; I look forward to seeing more tools from them in the future (and, of course, I'll share those too!)
Thanks for the read, Franck!
Good Tools and Better Post. Thanks Mitch for sharing with us.
Hi Mitch,
Thanks for sharing this article. I have not much knowledge about Excel but i will review and apply this step by step.
Thanks,
I would like to get in on the fun but am getting error messages for SEOmoz API for Google Doc.
Here's what i'm getting:
"invalid argument: https://lsapi.seomoz.com/linkscape/url-metrics??AccessID=member-(MY NUMBER)&Expires=1334065005&Signature=Sv%2BUh6EYTNtAp216gZzudQCiV3k%3D&Cols=103616137253"
This shows up in B1 when put the "=" in front of the cell's getLinkscape(A2:A4)
Probably doing something stupid but have no clue how to fix/adjust/change.
Any help would be appreciated. Thanks, Dan
Great Information! Spreadsheet is one of the most important tools that enterpreneurs and businesses can leverage.
Great!!!
Superb post, good sir! I actually have tested most of the mentioned tools and have even bookmarked all of the links to the original posts. Being a self-confessed inbound marketing student/newbie myself with very limited knowledge of programming languages (and/or web scripts if that's how it should called) and budget, having these tools ready-made and freely available is a steal. Having all these in one place, and adding in the pointers on how each should be used and its data be interpreted makes this bookmark-and-share-worthy. By the way, I just raped the thumbs-up and share buttons.
Whoa buddy, little personal there at the end. ;)
Glad you liked the post though, sir! Thanks for the shares, as violent as they may have been.
Great list - I use a few of these already but there are a couple I hadn't come across before (the content idea generator's one of them). Thanks! :)
Really very helpful and useful post.
Excel proves that it's still up there with the best tools for helping you both maintain and track your SEO campaigns.
From tracking your keywords, directory submissions, even your expenses, it's so handy.
https://www.microsofttraining.net/b/exceltraining/2013/02/how-excel-can-help-you-build-a-successful-seo-campaign/
These are some really great tools, I really like using Google Spreadsheets for making some quick SEO tools. I have also seen some really advanced tools that pull all sorts of data at once like twitter handles, Bing news, Google news, related searches Dig information, and so much more all in one spreadsheet. I wish I had the time to make more of those as there is some really powerful stuff that you can do with a few simple formulas put together the right way.
Right now I am working on a (non-SEO) tool to help job seekers find contact information for many businesses all at once instead of manually visiting hundreds of sites to get their contact info.
Great concepts here, just making the move from an external keyword tracker to my own spreadsheet now
hello,
thanku so much giving me information about such a useful tools in SEO. I m always interest in SEO so I am very happy to visit this Article.
thanks again and have a nice day...
great post... thanks for share
Now a days excel sheets are suing in almost each and every companys and in some google doc's so its really so much needful for kepting the details of their data and it really helps a lot and its really so esay for using also, and this is a really nice blog please keep posting such of these kinds of posts.
Cheers for this Mitch.
Great to see all the most relevant tools collected together in one easily bookmarkable post.
Am only just starting to look at these kind of DIY tools (I know - lots of catching up), so it's a really useful for a beginner like myself.
Thanks.
Nothing else to say, but thanks, Mitch. Too much amazigness for words.
It is kind of beautiful, isn't it?
Sometimes... when I turn my computer on... I see these sheets and my eyes tear up. I take a few minutes to compose myself, but that feeling... that fuzzy warmth... it never goes away.
*sniff*
wow, what a great selection of tools! thanks for sharing this!
Ok, sorry about that. I thought the Google Docs had public access permissions but it seems they did not. It's fixed now, feel free to go back and make copies now.
I love when an article has too much information to digest in 10 minutes. I'm am going to bookmark this one for later. Thanks for gathering all of those resources into one place.
I haven't though about Google Docs as an SEO tool. Regarding SEO purposes, I only use it to keep track of the links I create. So thanks a lot for this lesson, I'm sure it will prove really useful!
Tools for an SEO are what weapons are for a soldier, there is no limit in tools for SEO, the more the better! Sometimes even a small free tool can make your whole large task easier, thats why I keep note of every small and big tool available for SEO on net.
Nice post with good explanatory images, appreciate that!
Thats right, tools really are like weapons for an SEO...
Great post and even better tools. Thanks
Great collection of tools. For the SERP Competitve Analysis v2, has anyone else had problems getting this to work?
After entering my credentials, the Report tab of the document does not seem to bring any data through and shows "#ERROR!" under MozRank.
I've regenrated my secret key but no luck. I remember getting this to work when it was first released but now no joy.
This lovely message appears when looking in the cells:
Request failed for https://lsapi.seomoz.com/linkscape/url-metrics/?AccessID=Your%20ID%20goes%20here&Expires=1333622198&Signature=AVQbYBu%2F3VQgQPXLamOr4%2F91Gvw%3D&Cols=103079249920 returned code 401. Server response: { "status" : "401", "error_message" : "Authentication failed" } (line 36)
Any thoughts?
It looks like the formula you're using isn't incorporating your member ID into the API call. Either you've pasted your member-id in the wrong cell, or the formula is referencing the wrong cell.
In the formula you pasted, you can see where it says "Your%20ID%20goes%20here".
That's where your member-id should be. :) Hope that helped!
Also, if you just made your API key, you may have to wait an hour while SEOmoz's servers commission it.
Thanks for taking the time to respond. I am obviously doing something completley wrong; my member ID has been entered into the sheet marked "Config", Cell B3 and the Secret Key in Cell B4; not much to get wrong there!
I've made no other changes to the Google Document so don't understand why it would reference the wrong cell.
I'll keep ploughing away at it; maybe I'll get a eureka moment.
Thanks for the post. Some great links to some great tools.
Mitch! What a great resource for the whole community! And thank you so much for the mention.
Rocking post Mitch!!!
I'm using "Niels Bosma's SeoTools for Excel" and it's BEST. Saves a lot time for me. I'm going to try all other tools you mentioned. My God, I've a lot to learn in SEO!!!
Thanks
Wow, these are amazing, thank you. These tools will make prospecting a snap... And the GDoc use will make it easy to share results with others. Very impressive, I hope this gets promoted to the main blog.
great post I really used to think the spreadsheet seo thing might be very complicated always avoided it...it was really very very very helpful so thanks a lot
and I think there r some issues still with the google docs sheets here which you provided ..few of them didn't work then I went to instructions blogs and copied the document from there and it worked.
And thanks to SEOmoz for API they really have done awesome job.
Normally I hate anything to do with Excel spreadsheets but I love your keyword prospecting idea. The best tool would be one that all my profile backlinks for me lol.
Awesome post Mitch! I've found Google Docs & Excel are tremendously useful once you get to know them (which can be a major hurdle) and I must say your "pack-rat" mentality is certainly paying off the for rest of us with this collection. Have played with a few of these before but will definitely be bookmarking this one for future reference to take keyword research and content strategy specifically to that next level.
Thanks again!
Brett,
No problem! Thanks for the read. :)
With all the free tools/apis/integrations/etc available now, it's easy to do almost everything you need in Google docs/Excel. Of course, it's a lot less stable/reliable, but it's a whole lot cheaper. I love tools like this because they make life easier and they can be customized much MUCH easier than a full application.
Thank You.
Thank you for this great tool. I also need to get more familiar with the ins and outs of Google docs, but I can see the value down the road.
Thanks for putting so many great resources in a single post Mitch. Very timely also, I've been getting low on fresh ideas for my weekly blog posts. I'm going to use the Content Idea Generator to start on some more drafts tonight.Got it bookmarked in my SEO Gems>Tools section already :)
Very nice and amazing tools, thank you
Amazing information, thanks!
thanks
really we are using excel sheets daily and with the Google, docs very helpful,
your article is very good and helpful thanks.
g
Thanks for “Inbound Link Categorization”Really helpful tool
A big thanks...just what I was looking for!