Google Adwords API Extension for Excel
Capture search volumes and seasonality data straight into Excel
Yes, you heard that right. Introducing the SEOgadget Adwords API Extension for Microsoft Excel – easily capture large volumes of search query data directly into Excel using your Google Adwords API key. Save huge amounts of time gathering data and spend more time analysing!
Wow! Tell me about the features
 | Exact, Broad and Phrase Match |
| Generate local search volumes by country code |
| Rapidly extract search volumes for 1000′s of keywords |
| Acquire Mobile and Desktop Search Volume |
| Generate 1000′s of related queries using suggest service |
What are the standard functions?
The SEOgadget Adwords Excel plugin creates a series of functions designed to fetch data from the Google Adwords API. Once you’ve installed the plugin, you’ll get a handful of new functions to make fetching keyword data a far more effective and efficient process. Here they are:
getAdWordAvg()
Example:
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.
getAdWordStats()
Example:
Description: returns local search volume and previous 12 months separated by commas
getAdWordIdeas()
Example:
Description: returns keyword suggestions based on API suggest service
There are Array functions too?
Yes, there are! Use these with a little caution as they can request a lot of data (800 rows per API call) – I suggest you set Excel to manual calculation. Then press F9 (or click Calculate Now) to recalculate the workbook when ready. You’ll find the calculation options under the formulas tab (and also under Excel Options which also offers an option to stop auto-calculation on exit).
If you don’t disable auto-calculate, no problem – just use the array formula to capture data, then copy and paste values to avoid accidentally re-requesting very long lists of keywords.
arrayGetAdWordStats()
Example:
Description: array formula (auto adds {} brackets) will return data from a list of keywords in a table (average search volume and seasonal data).
arrayGetAdWordIdeas()
Example:
Description: array formula (auto adds {} brackets) will return suggstions from a list of keywords in a table (average search volume and seasonal data).
Performance & requirements
The SEOgadget Excel plugin works with Excel 2003, 2007 and 2010 in 32 and 64 bit. If you’re going to be doing big bits of research using arrays, you’ll need quite a powerful computer (a good excuse to ask for a faster machine with more memory!). Under load testing, my i7 Windows 7 machine took around 3 to 4 minutes to process 30,000 keywords. You probably don’t want to use that much data every day, though.
Obviously, you’ll need a Google Adwords API key to get search volume data using this tool.
How to install
1) Download the zip file, extract the “SEOgadget” folder and open “distribution/setup.xls” – enable the macros and follow these step by step instructions. When you open the setup file you’ll see this screen:
*Don’t forget to enable macros!*
2) Add your API credentials and click “Update Credentials”
3) Click the “Install API Add-in” button and wait for this dialogue:
4) Restart Excel and start capturing your search data!
Now for the technical bit
SEOgadget hired Master Datasmith Tom Gleeson to develop this plugin – (here he is on Twitter). I highly recommend Tom (he’s our Excel developer of choice). If anyone knows how to automate the hell out of Excel, it’s Tom. Here’s his technical bit.
The add-is was developed using a marvelous open-source tool called ExcelDNA. This allows for the fast (and cheap) development of .NET add-ins for Excel. Microsoft would prefer if everybody used their Visual Studio (not the free express version, the expensive one) based VSTO technology to build such add-ins; don’t, ExcelDNA is way better and easier to use. ExcelDNA is built around a C core (making it fast and robust) and allows for the development of add-ins using either C# or VB.NET.
The biggest downside with ExcelDNA is the lack of documentation, but the user forum is very active and top notch. For a good introduction see Ross Mclean’s series of blog posts on the subject.
So why not use plain old VBA to build the add-in? The answer to that lies with the other piece of technology used i.e. Google’s v201101 Adwords API. Most APIs these days use either JSON or simple XML formats to pass data and requests back and forth between servers and clients. In the early days of Web Services it was envisaged that a highly structured (and extremely complex) variation of XML, namely SOAP, would be used for this task. Thankfully common sense prevailed and SOAP is now very much a minority sport.
Unfortunately, the Adwords API developers went with SOAP, but, made up for it by providing developers with a set of excellent client libraries for all the major development platforms, such as the JVM, CPython and .NET but alas, not for VBA.
This .NET client library also accounts for the choice of the C# , rather than VB.NET, as the add-in’s development language, as all the examples (like most .NET libraries) were coded in the language, using C# makes its easier to get up to speed.
(The ability to package the resulting code in a single DLL (a .xll rather than VBA’s .xla) is also another good reason for using ExcelDNA if VBA is not an option.)
The add-in is available for three platform types:
- Excel 2007/2010 32 bit (requires NET 2.0 run time, which is now to be found on all but the most ancient of machines).
- Excel 2003 (should also support back to Excel 2000, and possibly ‘97, but not tested). This too requires NET 2.0. It lacks however, the very useful helper “array” functions that auto-resize arrays to fit the returned tables and the ability to run in multi-threaded mode.
- Excel 2010 64 bit. This requires not just a 64bit OS, but a 64 bit installation of Excel (the default for Excel is to install the 32 bit version even on 65 bit machines, if installed using the supplied SETUP.xls it will check this for you). The 64bit version requires the NET 4.0 run time, which is very likely to be already installed if using a modern Windows 7 64 bit OS. But if not or you’re not sure, then power up Internet Explorer and visit http://www.hanselman.com/smallestdotnet/ the site will check what versions of .NET are installed and suggest the most suitable download.
I want to play! Where can I download this plugin?
This plugin is completely free to download and use. As it’s a free tool, we may only be able to provide very limited support and by downloading and installing the plugin, you agree that the tool is not provided under any warranty, and that the software author and SEOgadget are excluded from any liability to the users of this software.
DOWNLOAD HERE
Google Adwords API Extension for Excel is one of our latest posts from: SEOgadget.co.uk.
-
Capture search volumes and seasonality data straight into Excel Yes, you heard that right... Read Full Story » -
Let's review a topic about how to Display Dynamic Feed With Google AJAX Feed API. Have you ever... Read Full Story » -
Let's review a topic about how to Display Dynamic Feed With Google AJAX Feed API. Have you ever... Read Full Story » -
New Google Adwords certification program 2010 Today we comentarte three initiatives related to... Read Full Story »
