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:
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.
Great article. I will admit though that being in New Zealand, a lot of these tools don’t have search data for Google NZ, which is super frustrating – especially when trying to localised search volumes for local businesses. In the end, AdWords seems to be the only definitive way to get true traffic volumes for keywords….
Hey Jamie. Volume API should still work for you.