logo-small
Features Prices
News 0
Latest News See All

Temporarily unavailable. Please come back later.

See All
Webinars 0
Upcoming Webinars See All
Upcoming Webinars

Sorry, we could not find any upcoming webinars.

See recorded webinars
Blog 0
Recent Posts See All

Temporarily unavailable. Please come back later.

See All
Brian Jensen

How To Use the SEMrush API in Google Sheets

Brian Jensen
How To Use the SEMrush API in Google Sheets

SEMrush is my favorite competitive research tool that I use for everything from organic and paid keyword research to understanding market share in a specific region. A few months ago, I was working on a large project where it made sense to look beyond the SEMrush interface and pull data directly from their servers via the SEMrush API. The problem was that I’d never used an API before and wasn’t able to locate any resources for a noob like myself to get started.

As luck would have it, I work with some pretty smart people who were able to point me in the right direction. Now that I understand the basics, I want to pay it forward and create a resource for others who use SEMrush, but not familiar with using the API.

What is an API?

Let’s start at the top. An application programming interface as defined by Wikipedia is “a set of routines, protocols, and tools for building software applications.” APIs allow different applications to communicate with databases and many of the social tools digital marketers use on a daily basis are built using APIs. The SEMrush API allows users to pull data directly from their servers. It can make large scale analysis a breeze.

Locating Your SEMrush API Key

Before we get started with examples, you’ll need to locate your API key and number of remaining credits which can be found by navigation to the API documentation page.

Find your SEMrush API key

Using the SEMrush API in Google Sheets

I use Google Sheets frequently to create and collaboratively edit spreadsheets with other users. To provide you with an example of how to use the application and SEMrush’s database via the API, let’s pretend I’m working on a keyword research project with a co-worker in which we’ll need search volume CPC, competition and total number of Google results for a list of keywords.

SEMrush offers a wide variety of reports that include paid, organic, overview, domain, URL reports and more. We’ll be using the Keyword Overview report that will allow us to pull data for all the metrics I’ll need for my keyword research project. Let’s dissect the request example for this report to provide us with a better understanding of the data we’ll be pulling into our Google spreadsheet.

Parts of the report request example

  1. The specific type of report you’d like to request
  2. Your API key number
  3. The report’s columns
  4. The phrase we are researching
  5. The database that we’ll be pulling in data from

Now that we have our API key, the report we’d like to run and a new spreadsheet we’ve created in Google Sheets, it’s time to review a few spreadsheet functions that will make it possible to retrieve and organize the data.

Meet IMPORTDATA

Just like in Excel, Google Spreadsheets supports a wide array of functions. For this tutorial, we’ll be using IMPORTDATA to retrieve data from SEMrush.

Meet IMPORTDATA

The syntax for the function is IMPORTDATA(url), and the value for url must be enclosed in quotes or reference a cell. The Keyword Overview report request example using IMPORTDATA would look similar to the following:

=IMPORTDATA("http://api.semrush.com/?type=phrase_this&key=00000000000000000000& &export_columns=Ph,Nq,Cp,Co,Nr&phrase=keyword&database=us")

Because we’re efficient and we’d like to be able to drag through multiple rows in our spreadsheet, we can replace “keyword” with the cell we’d like to reference in the request i.e. “&A1&”.

Replace the keyword with the cell you'd like to reference

Using the TRANSPOSE Function

By default, SEMrush will populate your data vertically down rows. Using a nested function with the transpose feature will populate your data horizontally across columns. Creating a nested function using TRANSPOSE and IMPORTDATA would look similar to the following request:

=transpose(importdata("http://api.semrush.com/?type=phrase_this&key=00000000000000000000& &export_columns=Ph,Nq,Cp,Co,Nr&phrase=keyword&database=us"))

Using the SPLIT Function

Another Google Sheets function that you may find useful for working with your data is Split. An easy way to think about this function is that Split to Google Sheets is basically what Text to Columns is to Excel. The definition provided in Google Sheets is that this function “will help you divide text around a specified character or string and places each fragment into a separate cell in the row.”

The split function

Similar to Excel, there are many ways to accomplish the same task in Google Sheets and a plethora of different ways to organize and format your data in a way that works best for you.

Try a few different reports on your own and experiment with adding or removing export columns to get the data you need for your project.

That’s it, easy right? With the help of the IMPORTDATA function, using the API in Google Sheets can be a major time saver. Please leave any insight or tips you’d like to contribute in the comments below!

Brian Jensen is the CEO of Congruent Digital, a full-service marketing agency specalizing in cross-channel digital marketing solutions. When he’s not working, you can find him reading a book or riding his bike. Connect with Brian on Twitter, Google+ or LinkedIn

Comments

2000 symbols remain
Mikael Thuneberg
We've added SEMrush as a data source in our Supermetrics add-on for Google Drive (https://goo.gl/rt5G9S). Using the add-on makes it very simple to fetch SEMrush data into Google Sheets.
Gaurav Agarwal
Gaurav Agarwal
Mikael Thuneberg
Have been trying to use Supermetrics and struggling while using it for SemRush. Have asked in the forums, how can i avail help ?
Gaurav Agarwal
Gaurav Agarwal
Gaurav Agarwal
I have received a reply on the forum. Thanks. If you have a better way to reach out for support, pls apprise.
Will try the tool and review here !
David Black
If you like this, you should also check out a webinar we (SEMrush) did back in December of 2014 with Sean Malseed. Founder of CircleRank and creator of http://www.ranktank.org. An entire repository dedicated to this stuff!
http://www.semrush.com/webinar...
Joe R.
Joe R.
Do you have a Google Sheet template with all of this configured that you can share so we can copy?
Brian Jensen
Joe R.
Joe that's a great idea, but unfortunately I don't. The cool thing about the SEMrush API documentation is that it provides request examples below every report with your API key, so most of the legwork is already done for you.

If you want to reference cells, just remember to replace keyword or domain with "&A1&" (assuming A1 is the cell you're referencing). If you get stuck, feel free to DM on Twitter and I'll be happy to help however I can.
Matt LaClear
Matt LaClear
No doubt, webmasters and site owners work hard to build impressive analyses with their data. Sometimes, it’s still difficult to analyze. Running reports with a new spreadsheet in Google Sheets can make the job easier, that is if we can learn how to organize and retrieve the data better. It’ll take a little while to digest this info, but thanks for making a heavy task a little lighter!
Brian Jensen
Matt LaClear
My pleasure Matt and I couldn't agree more that using SEMrush's API can make a lot of analysis light work!
Have a Suggestion?