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.
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.
- The specific type of report you’d like to request
- Your API key number
- The report’s columns
- The phrase we are researching
- 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.
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&”.
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.”
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!
