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.
Google Keyword Tool is a window into the world’s innermost desires. Often the keywords reported are mundane and predictable: [palm springs hotels], [hotels in palm springs], [cheap palm springs hotels]. The relative search frequencies for variations on search terms are occasionally interesting, but usually the terms themselves are pretty predictable. Some days, however, I’m struck by the oddity of a phrase that I see in the reports. To the keywords that brighten my days I dedicate my new “As Seen on GKT” column.
Today’s As Seen on GKT: [clothing optional hotels palm springs]
My favorite aspects of this phrase:
Clothing optional, not nudist. As if these searchers are either uncertain that they want to commit to full nudity, or recognize that perhaps there are some situations in which nudity is not preferable. Or perhaps one member of the party prefers to be nude and the other prefers to remain clothed. OR perhaps the searcher wishes to remain clothed but likes to be among nude people. So many interesting reasons why someone might prefer the optional option.
Palm Springs is a frequently searched destination for those who prefer optional clothing. It’s sunny and warm year ’round, so that makes sense. But Palm Springs is in the desert, known for its intense rays. I’m hoping their lack of packed clothing leaves lots of room for sunscreen. In case you’re wondering, other popularly searched options include Key West and the Caribbean.
When slogging brute-force through masses of keyword research, I tend to download everything from Google Keyword Tool to .csv files for easier filtering in Microsoft Excel 2010. After much gnashing of teeth and trying ineffective XLS scripts, I stumbled across a beautifully simple way to merge .csv files using the Windows Command Line (AKA the C Prompt).
Let’s say I have downloaded 3 .csv files (file-A.csv, file-B.csv and file-C.csv) and I want to quickly merge them.
Simply open the command line by typing the word “run” into your Windows Start Menu, or execute the cmd.exe file at C:\Windows\System32\cmd.exe. A black window will open like a portal into 1985.
Next, direct the command line to the directory that contains the individual .csv files you want to merge. The command line begins in the C:\Windows\System32\ directory. I keep an empty folder at C:\merge for this purpose so I don’t accidentally merge other files. So all I need to do is type “CD merge” to ask the command line to kindly switch to my C:\merge directory.
Next, the copying. Type “Copy *.csv merged.csv” to copy all .csv files in that directory into a single file called “merged.csv.” You can call the file anything you want: Actually, I usually call it “1.csv” instead of “merged.csv” because it’s fast.
Here’s what the whole process looks like:
And you’re done. Open C:\merge\merged.csv (or whatever you called it), dedupe it and you’re ready to filter out the wheat from the chaff in Microsoft Excel 2010. Enjoy!
Another handy tip to go with this is that if your files are in another folder with a long path name, you can drag the folder name from your Explorer address bar directly into the command prompt window after typing CD , and it will fill it in with the necessary quotes. Yes, drag and drop into a DOS command line.
Google AdWords quietly updated its Keyword Tool on Sept. 7, resulting a dizzying drop of up to 95% in search counts for some phrases. For those who rely heavily on the keyword tool to predict potential SEO performance increases or to identify phrases to optimize for, this update will come as a heavy blow. Details are sketchy from Google, and the SEO community has been silent on the issue except for Dave Naylor and a few folks in the forums. So what happened?
“If you use both the previous and updated versions of the Keyword Tool to search for keywords, you may notice differences between the tools for statistics on Global Monthly Searches and Local Monthly Searches. This is because the previous version of the Keyword Tool provides search statistics based on Google.com search traffic and traffic from search partners, while the updated version of the Keyword Tool provides search statistics based on Google.com traffic only. We’ve updated these statistics based on user feedback, and hope you find them helpful for keyword selection.” >> from AdWordsPro on the AdWords Help Forum
The Search Network: Your ads may appear alongside or above search results, as part of a results page as a user navigates through a site’s directory, or on other relevant search pages. Our global Search Network includes Google Maps, Google Product Search and Google Groups along with entities such as Virgin Media and Amazon.com. >> from AdWords Help
OK, so previously the data was based on an aggregation of Google.com searches and traffic from search partners like Amazon and Virgin Media. But since the Sept. 7, the data is based solely on Google.com searches. Am I the only one who would have found that incredibly helpful to know before now that a data source as large as Amazon’s was skewing the data in the Google keyword tool?
Yes, we all knew/theorized/suspected that the AdWords keyword tool data was skewed. And given the source (Google) and the purpose of the tool (get advertisers to pay for ads on keywords) we took it with a grain of salt. But seriously, 95% inflation is a very, very big grain of salt.
If you don’t already, this is a wake up call to diversify your SEO data sources and understand that most public SEO data is relative rather than exact. First, diversification. Try using more than one data source for keyword data. There are a lot of tools out there, but these are the old stand-bys: WordTracker, Keyword Discovery, SEO Book’s tool. Keep in mind the source of each tool’s data — they either use the APIs from the engines themselves or toolbar & ISP data, which renders each pretty much as reliable as the Google keyword tool anyway.
Which brings us to relativity. Get used to the reality that you will never know the exact number of searches that occur for any given keyword or phrase. Public keyword data and search frequency data are imprecise. The only data you can be certain of in SEO is the data that comes from your own log files on your own site. All other data is approximate. If you base projections on approximations, be certain that you take into account the squidginess of the data sources.
In this update, the biggest differences between pre and post appear to be on the larger, more competitive head terms, both in this sample and in the analysis I’ve been doing over my own keyword sets. Here’s an example. Let’s pretend we’re researching keywords for a site that offers hotel reservations. I might target sampling of keywords like these.
Click to view larger. Data source: Google AdWords Keyword Tool, Exact Match, US English.
Had I taken the Google AdWords Keyword Tool at face value, I would expect there to be a US English keyword market of 450,000 searches for the phrase [san francisco hotels]. I might choose project to win 10% of those searches and figure a conversion rate of 5% based on my analytics. Each conversion might bring me say $10, so I might project a value of (((450,000 * 10%) *5%) *$10) = $22,500 in a month.
Unfortunately, the updated number based on true Google.com searches, according to the tool in September, is 95% lower at 33,100. So now we’re looking at (((33,100 * 10%) *5%) *$10) = $1,655 in a month. Not bad for a single keyword in a single month, but much lower than the previous projection and it comes with hefty competition.
I googled [madison holiday charity] and was dismayed by the lack of relevant results. My favorite holiday tradition is “adopting” a family in need, giving them Christmas gifts that they otherwise would go without. Now I can see that I need to add SEO for charities to my gift list.
Think what an optimization campaign around [location] + [charity gifts] or [christmas donations] could do for a local charity. Say a seasonal program attracts 100 volunteers — what if SEO could attract 20% more? I’ve reached out to help my favorite charity optimize for next year since this year is essentially a wash for holiday optimization. Next year, look for http://www.mompop.org at the top of the SERPs for [madison charity gifts]!
I finally stumbled on MOM (Middleton Outreach Ministry), a local charity serving the western part of Madison, WI. Like many charities, MOM is a local operation staffed by hard-working volunteers and a few employees that wear too many hats to focus on online marketing. Even the national or global charities tend to lack strong SEO savvy. What would they optimize for? Keyword research would light the way, of course.
I did some quickie keyword research to find the optimal annualized keyword phrase for holiday and Christmas donation gifts. The keyword market isn’t huge, and the data for Madison-based phrases is even tinier since we’re not a major metro area. In the end I had to settle for non-location-specific keyword phrases, logically prepended with location. Here’s what I came up with:
The tricky thing with keyword research is intent. When folks search for [charitable gifts] they may be looking for general charities to donate to as opposed to the intent I had in my search, giving holiday gifts through a charity. The non-holiday keywords are incredibly valuable, of course, but will probably be utilized at the top of the site’s hierarchy. [Christmas donations], [holiday giving], [holiday charities] and [charity christmas gifts] are more on target for the seasonal holiday charity campaign’s landing page.
So let’s look at an example. MOM’s Madison Christmas donations page appears to target the program’s name: Sharing Christmas Program. That’s handy for folks who saw another form of marketing and already know the program exists, but it’s not going to get them a lot of natural search-referred traffic or donations.
While the page mentions some of the optimal individual words, none of the valuable phrases are incorporated together. Here’s what I would do for the Title Tag: Christmas Donations: Madison Charity Christmas Gifts & Holiday Charitable Gift Giving
That one string targets my primary phrase of [christmas donations] and touches six secondary keyword phrases either by exact match or with another word interrupting the exact phrase:
[charity gifts]
[donation gifts]
[charitable gift giving]
[holiday giving]
[charity christmas gifts]
[charity christmas gift]
In addition, the heading, body copy and meta description should be optimized for the primary and secondary keywords. The best I can do for them for this year’s holiday giving season is give a link, but next year we’ll be ready with optimization a-blazing!
