Richard Baxter’s Google Adwords API Extension for Excel plug-in has completely turned my keyword research world upside down, requiring a new set of tools to cope with massive data overload. I’m not complaining, it’s an awesome plug-in and free to boot. But new tools require changes in the processes and other tools used to wrangle into a form that’s usable. With a combination of Baxter’s AdWords API extension, DigDB plug-in for Excel, and good old fashioned waiting for processing to complete in Excel I’ve cut my high-volume keyword research time by 75%.
By high-volume keyword research I mean identifying related keywords for thousands of known phrases, which while fantstically tedious is also quite useful for finding new keyword markets to optimize for. For example, within five minutes of reading Baxter’s post on SEOgadget and downloading the plug-in, I was happily fetching keyword ideas for 46,000 phrases. Why 46,000? Well, I had a list of 200 cities, 150 topics and 4 different phrasings for each combination of city and topic. Adds up quickly does’t it? Thank god for MergeWords. But back to the AdWords API plug-in.
I quickly discovered that I had to run the queries in batches af about 5,000 phrases each or my poor PC would bog down horribly. Still, I was able to accomplish in 3 days what would have taken me 3 weeks. Three tedious, mind-numbing weeks of copy/paste/search/download/repeat. I was so excited that I immediately blogged, tweeted, +’d and shared Baxter’s post everywhere I could.
Then I discovered I had a new problem. My API queries had resulted in a combined 15 million keyword ideas. Many of these are duplicates — as many as 75-90%. And many are not relevant to the specific need I’m researching, despite their relevance in Google’s algorithmic eyes. SEOs are no strangers to scrubbing data, but how do you scrub 15 million keywords? How do you use Excel’s handy “remove duplicates” feature on 15 million keywords when Excel’s maximum rows per file is a little over one million? And that’s assuming you can get the individual keywords to march tidily into a single column for deduping in the first place. Which I couldn’t.
The AdWords API plug-in spits the keyword ideas out in a single cell with each related keyword separated by a comma. Naturally, since I’m a data hog, I wanted all 800 related keyword ideas the Google AdWords Keyword Tool was willing to give me. That’s 800 comma-separated phrases crammed into a single cell. It’s easy enough to separate those into unique cells so that you have one keyword per cell, but you still can’t dedupe 15 million cells in Excel without some external help.
Enter the DigDB plug-in for Excel. It slices, dices and juliennes data quickly and efficiently in lots of different ways. DigDB took my 15 million keyword phrases in their individual 15 million cells, extracted every unique phrase, and lined them up in a tidy column on a new worksheet in Excel. It did it quickly, without complaining and without freezing every speck of my PC’s memory. Just to be safe, I ran the extraction process 15 times on 1 million keywords each. Each extraction took about 7-10 minutes.
The download site offers tens of practical examples for using the plug-in, each with a sample spreadsheet so you can try it out to be sure that the description matches what you actually need to do. It costs $78 a year, but there’s a free 15-day trial so you can kick the tires. $78 seems like a lot to me, but on this one project alone using one feature of the tool I saved maybe 40 man hours. I’d say that’s worth the price if you plan on doing large volumes of keyword research.