How to get keyword search volume data using APIs

Will Nye

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.

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.

SEO Search Volume

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:

http://api.semrush.com/?type=phrase_this&key=[your-API-key]&export_columns=Nq&database=uk&phrase=search+engine+optimisation

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.

=WEBSERVICE("http://api.semrush.com/?type=phrase_this&key=[your-API-key]&export_columns=Nq&database=uk&phrase="&A1)

Double click in the bottom right hand corner of the cell to auto-fill your entire list.

Keyword research with SEMrush

Be prepared for Excel to hang for a while whilst this processes. When it finishes you’ll be left with something like this:

Search Volume6600
Search Volume3600
Search Volume8100
Search Volume5400

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.

Keyword research with SEMrushKeyword research with SEMrush

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.

Grepwords

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:

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.

Comments are closed.


Join the Inner Circle

Industry leading insights direct to your inbox every month.