Wednesday, April 11, 2012

Excel and Google Docs: Tools for the Ultimate SEO Dashboard

Posted by Mitch Monsen

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?


Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don't have time to hunt down but want to read!

wireless developers home builder etfs new homes warranty

No comments:

Post a Comment