SEO: Automating Keyword Selection with AdWords API

Optimizing a website around highly searched keyword terms can materially increase traffic from search engines. But identifying those keywords takes time. Few SEO managers relish the tedium of slogging through thousands of keyword variations — as critical as that process is to a search-engine-optimization program.

Thankfully, Richard Baxter at SEOgadget recently released a revolutionary free “Google AdWords API Extension for Excel” that automates the most tedious steps of the keyword research process: pasting keywords into the Google Keyword Tool, clicking search, downloading the results, and repeating many times.

Baxter’s Excel plugin shifts the amount of time spent manually copying and pasting to more rewarding tasks, such as keyword analysis and optimization. The best part is that you don’t have to be an Excel wizard to use it. You’ll need Excel 2003, 2007 or 2010 in 32 or 64 bit to use this plugin, as well as a Google AdWords API. Once you have those pieces, simply download the ZIP file from SEOgadget that includes the plugin and the sample documents to help you understand how to use it. SEOgadget’s blog post goes through all the details, so we’ll focus here on examples of usage.

What Can It Do?

The extension pulls data from the API based on any match type (broad, phrase, exact), country code (U.S., U.K., etc.), and device type (web, mobile, etc.), enabling precise control, such as the following examples.

“Report Keyword Ideas” for a list of products or phrases. Note that the result is a comma separated list in a single Excel cell.

"Report Keyword Ideas" generates keywords to optimize for specific products, terms, and phrases. This example uses "black shoes" and "blue shoes."

—

“Report the Local Monthly Searches” data for a list of keywords.

"Report the Local Monthly Searches" shows the number of geographic-specific searches for a given term. "Red soles for shoes" recorded 450,000 monthly searches, in this example.

—

“Report the Local Monthly Searches” data for a list of keywords with the Local Search Trends.

The expanded "Report the Local Monthly Searches" shows the number of searches for a given term across many consecutive months, indicating trends.

—

Using arrays, the extension allows all sorts of fancy combinations of the above, enabling keyword ideas and local searches data collection with a single copy, paste, and click. For example, with a single array I pulled in 50 keyword ideas each for the keywords “black shoes,” “blue shoes,” and “red shoes,” on exact match “U.S.” for “web devices,” and also collected the Local Monthly Search data and Local Search Trends.

This partial spreadsheet shows the number of searches for keyword ideas across multiple months, for the terms "black shoes," blue shoes," and "red shoes," focusing on the exact matches "U.S." and "web devices."

Now that would have been easy to do manually, but scale that out to 3,000 keywords or 30,000 keywords instead of just three and you start to see the potential for this tool.

What Are the Limitations?

This Excel extension comes with samples and notes, but it’s not a full-fledged application. It does take some fiddling with to learn how it works. Consider the time spent as an investment in future productivity. The return on investment is definitely positive.

While the Google AdWords API Extension for Excel itself is free, API calls will cost $.25 per unit — a “unit” is 1,000 requests. So running a query on 4,000 keywords will cost a whopping $1.00 in AdWords. Comparatively, copying and pasting 4,000 keywords into Google AdWords Keyword Tool — even in the maximum allowed increments of 100 keywords per search — would take 40 copy, paste, click, wait, and download sequences. Depending on how fast and focused you are, that’s an hour’s worth of mind-numbing time lost on data collection. And it is not to mention that the more keywords you paste into the tool at once the fewer results you get for each. Since the manual method takes more time and returns less complete data, the small expense is worth it to me.

The data returned is only Local Monthly Searches today, though it sounds like Baxter is open to adding support for Global in the future. This isn’t much of a limitation, however, because content optimization should be based on the data for the audience at which it’s targeted.

Depending on the size of the query, unless you have a supercomputer at your disposal you may want to leave it running overnight. The queries can consume a lot of memory and processing power when done in bulk. Consider switching Excel calculations to “manual” to avoid recalculating the entire sheet every time a single cell changes. In Excel, go to Formulas > Calculation Options > Manual.

Conclusion

I’ve experimented with a lot of ways to speed keyword data collection, and this one tops them all. It’s free — except for the API calls themselves — it’s customizable, and there’s no harm in just fiddling with it to figure out how it works. Plus you’ll probably learn some interesting new Excel techniques along the way to assist with other areas of the business.