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.
Really great post Richard. I just started using it and it makes my research process so much more efficient. I started wondering, and this is to anyone in the community, is there away to extract the SEOMoz keyword difficulty in large quantities through the API or some other tool? I have been working on building a large keyword research database/report, and that would be a great addition to this in terms of speeding up the process.
Great post! Once I get around to doing this it will save me sooo much time.
Paul
Absolutely great!
By the way I want to thank SEOmoz and all their brilliant speakers from Mozcon to share with us non-attendees some tips and secrets after the event.
That's so true.. for the people like me, as attending conferance was almost impossible for me due to geographical difference...
Thank you SEOmoz for publishing all the great stuff they talked about in SEOmoz conferance...:)
The conference was fantastic! Hopefully you'll be able to make it next year and thanks for the kind words.
WOW! Thanks Richard! You made this Excel junkie's week!
That got a thumbs up :-)
I really like being able to do as much of my work using only excel and a browser. But I still don't want to spend more time than necesarry doing it.
The bookmarklets from Tom Critchlow's post and now this Excel extension are two great tools that allow anyone who is working with SEO to get the work done faster and easier.
Nice work!
Brilliant presentation at Mozcon and brilliant follow up post! Thanks Richard
Aw, thanks Greg - you know how to make a guy feel good about himself :-)
some time the person we are writing about make the comment special... there defiantly some thing in you :)
Thanks for the tip on this, I am always looking for new ways to speed up working with Excel!
Check it out, they rejected it... https://prntscr.com/6wbgxb
subscribing to replies.
I am so excited about this tool! But I cannot seem to use it yet. What is the level of API access required - read-only, standard, admin? Must it be admin? Are there any information privacy concerns involved? Thank you again.
Thank you Richard for sharing. I really enjoy each of your post (those on your website and the seomoz' one)...Your advices are always simple et quick to understand and to replicate.
thank you
Thanks for the awesome contribution Richard - and it was great having you at Mozcon.
To be honest, I've never been that good with Excel. That's why I love these types of post. In the meantime, I think it would be a good idea if we just hired you.
Thanks buddy - so I've had a lot of requests for more of these. They're easy enough to put together - Excel skills FTW! What does the community think? :-)
really nice post by richard about adword tools . hope this kinds of post will be published again and this is great information for seo .thanks to richard and his whole team.
This is a useful post. I love crunching stuff through spreadsheets although favour Google Docs for that, handing the burden of repeated queries and processing to Google's servers.
Here's a simple spreadsheet that uses the SEOmoz API to give you a quick glimpse of a domain's MozRank and Domain Authority. Useful for a quick analysis of a domain portfolio, as I was disposing of some names. You need to plug your SEOmoz API details into the first sheet.
https://docs.google.com/spreadsheet/pub?key=0AlLz8RVIQwOYdElZZlFkZm9OT2ZLOUVNRDdpaVc5eGc&output=html
I'll work on a Google Doc-ified version of the Adwords spreadsheet.
Thanks to Tom Anthony for the re-purposed script.
Jeremy - it seems that your api and secret key are visible on that link. You will probably want to remove that asap.
Thanks for the reminder Ontario SEO!
Does anyone know how long it takes to get your API after registration?
Is there a certain way to fill out the application?
Thanks!
Hi, this is working great once i'd got the API token. Just a question using AdwordsStats function. Why is the first column returning zero for all keywords? Is this the month directly preceding the current month? should it be counted in?
Jason Hotham
Is this still possible? Does Google make it extremely difficult to get an Adwords API these days?
Thanks for the great post Richard,
Do you know how muich it roughly costs to use this tool heavily for an hour?
I need to present some numbers to my boss to get this approved so I can begin enjoying the benefits of using Excel for SEO.
Thanks Richard for this wonderful work!Can anybody help me with this tool, how can I get data for "Only show ideas closely related to my search terms" please help me & let me know how to achieve same in the richard's excel file.It will really save me a lot of my timeThanks in advance.
On the new update there doesn't appear to be anywhere to add the client's email address in the ''Add API Credentials'' in the setup document. Why has this been removed?
So it doesn't work for me when I try to pull data.
Also I'm having to use an old machine with Excel 2003, will this work for pulling the last 12 months data?
Great Video and Post! I definitley look forward to testing some of these ideas out.
Excel = King. That is the end of it really :-) Excel makes our lives much easier and allows us to create some many rules that it's beyond belief. This post also just made people's lives easier. Thanks.
Thanks for share such important thing though this article....
Video and the content makes an easy step to know more on this topic.
Thanks,
SEOCatalysts
Sweet sharing Richard! Seeing the video helps visualise what you've been writing about over at SEOGadget. Some may be wondering about the actual cost of API calls using your Excel plugin:
"The arraygetadwords functions page the requests (800 rows per api call) so you can get data for 800 keywords for .025 cents
The standard functions – that’s one api call per query, so 1000 keywords will cost you 25 cents."
https://seogadget.co.uk/google-adwords-plugin-excel/#comment-15674
Thanks again
Darroch
Hey Darroch - thanks for reminding me! Yes, api calls are incredibly inexpensive for any reasonable sized project. Using the array function is the best way forward (800 KW's - one call).
My maths might be incorrect but from my calculations:
Each API unit costs $0.00025 ($0.25 / 1000)
800 Separate Calls = (API Unit Cost * (800/0.1)) + (API Unit Cost * 5 * 800) = $0.02 + $1 = $1.02800 Array Calls = (API Unit Cost * (800/0.1)) + (API Unit Cost * 5) = $0.02 + $0.00125 = $0.02125
So the cost savings are even larger! $1.02 vs $0.02125 (98% cheaper doing an array formula!)
Might be worth updating the post with the costings?
Awesome post Richard. Can’t wait to have some free time to play around with this. Thank you for sharing.
Cool! Do find the time - the best bit is when the search volumes appear (*I'm easily impressed*) :-)
Thanks, this looks like an amazing tool! Looking forward to taking it for a spin to see what it can do.
I have been waiting on some design to pull in the monthly searches since Google removed them from their interface.
The suggestion feature seems to be one of the more interesting.
Can you emulate the traffic estimator to allow you to see traffic by region or city on a keyword?
Thanks Richard.
When i was at MozCon i applied for the Api key from adwords.Still no respons :-(
It takes ~2 weeks - to the best of my knowledge, they don't email you to let you know you've been approved. Keep checking by logging into your adwords account. Hope that helps!
My colleague got one approved in around 10 days. There was no email notification though exactly like Richard said.
Amazing Stuff! I ve seen this live.
Pretty interesting extension. I'm looking forward to playing around with it to see how much time it would take to do an extensive keyword list. As great as this seems, I would rather use an online database that is quicker and that uses less amounts of data entry.
Excellent post.
I don't have a huge amount of experience using Excel for SEO (it's something I'm working on developing), but its got me thinking about how to use this. Think I may try pulling in ranking, visits (to estimate CTR) and CR, to improve keyword targeting.
Is there already an add-in somewhere, which allows you to pull in specific data from GA for visits and CR, or is it best to just export it? Thanks.
Hi Jon, GA to Excel is possible via Excellent Analytics - at SEOgadget we have custom dashboards set up for exactly this. We automated the reporting process some time ago. Next Analytics is worth a try, too.
Perfect. Thanks a lot.
I'm impressed! Very nice tool which I'm definitely going to use...!!! Now let's go to the download page :)
Brilliant post!
thanks alot for sharing, Ive always had an intrest in pulling adwords data straight to excel.
Awesome sir!
Back in a meeting in our office, I proposed that a senior in SEO person should have a clear understanding with MS excel and data extraction as this can make lot of work easier and at one place, and seniors where not taking interest in hiring the person with good Excel skills but as soon as we are watching advancements in excel I believe the time is not far when excel will be considered as one of the basic SEO tool
Great work!
What exactly you are talking about Richard? And your last headline 'I Want, I Want! Gimme the Download! Gimme!' is really annoying to be frank.
You get thumbs down for telling the truth here. How many people understand this post? Raise your hands please.
** Edited SEOmoz Community Etiquette Guide
@DevakiPhatak
What exactly do you not understand about the post.. Its pretty straight forward.
This post goes over how to extract keyword phrases that are popular to add into the structure of your website and what keyword phrases to try for. Doing a competition analysis on these keyword phrases created from this plugin will tell you whether to try for that particular keyword phrase or not. I did all of this in the post manually and I can tell you that it will save me a lot of time. Saving time means getting the job done quicker, lower costs to clients, which in turns brings in more clients which makes me more money.
Kudoos Richard!
A basic understanding of keyword research is needed to understand this post I guess. You will have to start with some other posts on how to do keyword research before you read this one. There are many posts here to help you with that. Perhaps add a 'related posts' section here? It would help with users and promote seo.
That seems an irrational reaction to a small part of an overall great post.
To me, Richard was the 2nd most influential speaker at Mozcon behind Avinash. I see you are a pro member, but I have never seen anyone with a negative 12 Mozpoints. Congrats!