How to Get Search Volume Data Straight into Excel [Mozcon Debrief]

A few weeks ago I attended (and had the honour of speaking at) Mozcon. Mozcon is without a doubt the best SEO conference I’ve ever been to, and I’ve been to a lot. It rocks to be amongst so many stars! Anyhoo, just to say thanks to the Mozteam for flying me over to sunny Seattle, I brought them a new toy to play with. And the best part, it’s free for anyone to use.

Introducing the Adwords API Extension for Excel.

Oh Gosh, Richard Made a Video

My team were insistent – no light piano jazz this time. It sucks. I have no idea what’s wrong with them – elevator music is awesome. So here’s a quick tour in my best British accent. Think: “shine yer shoes, Guvnor?” (If you’ve ever heard Rand’s Brit accent attempts, you’ll know where I’m coming from with that.)

Get Yourself an Adwords API Key

Obviously that’s a pretty quick tour in the video, so it didn’t include the fiendishly simple set up process. So you know, you’ll need an Adwords API key. Adwords API keys are available to My Client Center account holders. Get one of those from here. For whatever reason, it’s tough to migrate to an MCC account from a plain old Adwords account, so take my advice and create a fresh Google account login for your Adwords API.

Learn the Queries

Learning the queries is pretty easy. Here are the most important:

getAdWordAvg()

getAdWordAvg(keyword,"[MATCHTYPE]","[COUNTRYCODE]","DEVICE")

Example: =getAdWordAvg(A1,"EXACT","GB","WEB")

Description: returns average search volume from the adwords API. Matchtype accepts broad, exact and phrase match. Country codes can be found in the Adwords documentation and devices can be mobile or web.

arrayGetAdWordStats()

arrayGetAdWordStats([TABLE],"[MATCHTYPE]","[COUNTRYCODE]","DEVICE")

Example: =arrayGetAdWordStats(myKWlist,"EXACT","US","MOBILE")

Description: array formula (auto adds {} brackets) will return data from a list of keywords in a table (average search volume and seasonal data).

arrayGetAdWordIdeas()

arrayGetAdWordIdeas([TABLE],"[MATCHTYPE]","[COUNTRYCODE]","DEVICE",[NoOfResults])

Example: =arrayGetAdWordIdeas(Table1,"BROAD","US","WEB",20)

Description: array formula (auto adds {} brackets) will return suggestions from a list of keywords in a table (average search volume and seasonal data).

I Want, I Want! Gimme the Download! Gimme!

Just so you know, the full installation guide is over on SEOgadget. All you have to do is download this zip file and run setup.xls. That’s all!

After years of working with Excel, I’m still really excited by it. It’s solved a lot of problems for me in the past and I'd just like to personally thank my chief data wrangler and master datasmith of Choice, Tom Gleeson. He rocks.

I’d really love to hear your thoughts, feedback and of course how you’re using the extension to work smarter in your keyword research.