Performing comprehensive keyword research for a large site can be a challenge. Often you’ll end up with tens of thousands of keywords from different sources, many of which have their own methods of estimating demand.
Historically, this meant reprocessing an entire list of keywords through Google Keyword Planner. This data was far from perfect, but it was the best we had available.
Google. Making Collecting Useful Marketing Data Really Hard Since (Not Provided)
Unfortunately, recent changes have made Keyword Planner far less reliable. Google is now bucketing search volumes for close variants, and applying ranges to the search volumes for “low spending accounts”. Some users have been affected, some haven’t. We’re aware of a top 1000 Adwords spender who is affected, so it’s possible that all ad planner data will eventually be obfuscated – although there may still be workarounds.
In light of these changes, alternatives like Moz’s Keyword Explorer are starting to look pretty good. It uses clickstream data to validate search volumes, which should help on the accuracy front. For those wanting to keep using Keyword Planner data, however, an external API could now be the best option.
Work Smarter, Not Harder with an API
Search volume APIs are nothing new — they’ve always been a great way to circumnavigate Google’s painful keyword limits — but if you haven’t used one before, then now is a great time to get stuck in.
The two main options, SEMrush and Grepwords, both use data from adwords, but store their keyword corpus independently. This means that you can effectively access the legacy, un-bucketed keyword data — for now at least.
As an example, let’s take a look at two independent queries: ‘SEO’ and ‘Search Engine Optimisation’. In Keyword planner, these have been bucketed and both now display as 33,100.
Via SEMrush, however, we get completely different results:
- SEO – 33,100
- Search Engine Optimisation – 1,600
Getting Started With SEMrush
With fairly comprehensive documentation, getting started with SEMrush is easy. If you’re logged in, the examples will automatically feature your API key which means you can just paste them into the browser if you want to test it out.
Assuming we just want pull search volume, the call is really simple:
All we need to do is insert an ID (
key=), specify a regional database (
database=), the columns we want to export (
export_columns=), and the query (phrase=).
Bulk Lookups with SEMrush and Excel
To perform a bulk lookup using Excel, move your keyword list into one column and then use the WEBSERVICE function. List your API call surrounded by quotation marks and include &A1 to automatically append the keyword listed in the first cell of column A.
Double click in the bottom right hand corner of the cell to auto-fill your entire list.
Be prepared for Excel to hang for a while whilst this processes. When it finishes you’ll be left with something like this:
To tidy up, copy and paste the column as values to remove the formulas. Select the column again and press CTRL + H, which will bring up the replace function. Hold ALT and type 010, which is the line break character code. It will look like nothing has happened, but if you look closely your cursor will have changed to a dot.
Hit replace all to replace the line breaks with nothing. Repeat the process, replacing ‘Search Volume’ with nothing.
Note: If you’re on an older version of Excel (pre 2013), then you might not have the WEBSERVICE function. In this instance, you can install a free add-on called Power Query.
Using Google Sheets
If you prefer using Google Sheets, the above can be accomplished with IMPORTDATA. Sean Malseed has put together a great template for this that automatically sorts out the formatting. Google Sheets will often stop working after a few thousand requests, though, making Excel preferable on larger projects.
With Grepwords, API calls are also really easy to construct, meaning you could use WEBSERVICE in Excel or IMPORTDATA in Google Sheets.
Alternatively, our old friend SEOgadget for Excel supports Grepwords out of the box. Here’s the how-to guide:
You can also check out this instructional post on using Grepwords, including a list of all of the location parameters you can use.
Lastly, For a really, really broad stroke try Keywordtool.io’s API. Keywordtool.io has search volume data available from Google Ad Planner (all countries), Amazon, Bing and Youtube. Nice.