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("https://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("https://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!
Comments
Either just recently joined or is too shy to say something.
For some reason, the only data Google Sheets displays in the cell are the names of the columns entered instead of the data itself.
Any insight would be appreciated!
Either just recently joined or is too shy to say something.
Either just recently joined or is too shy to say something.
However, for me it is not working when I try it on a lot of domains in the same sheet.
I have hundreds of formula with the following query:
http://api.semrush.com/?type=domain_rank&key=XXXXXXXX&domain=XXXXX&database=it&export_escape=0&export_decode=1&display_date=20120115&export_columns=Ot
When I enter it on the browser, no problem. It gives me results. But when I use it with the IMPORTDATA function, I have got a "Loading" forever.
It seems that when I have a few queries on the Google sheet, it works fine, but with more than 10, it is not working.
Does anybody have an idea of how I could solve this?
Thanks very much.
Guillaume
SEMrush employee.
This might to be an issue with the big databases, in cases like this it is recommended to lower the amount of requested data. If you have any other issue please feel free to contact SEMrush support by email to mail@semrush.com or use the contact form.
Either just recently joined or is too shy to say something.
Even with 10 requests at the same time, it does not work
Thanks
Occasionally takes part in conversations.
Occasionally takes part in conversations.
Either just recently joined or is too shy to say something.
Either just recently joined or is too shy to say something.
I =transpose(IMPORTDATA("http://api.semrush.com/?key=0000000000000000000000 &type=domain_ranks&domain=domain.com&export_columns=Ot&database=us"))
Either just recently joined or is too shy to say something.
Either just recently joined or is too shy to say something.
Either just recently joined or is too shy to say something.
Will try the tool and review here !
An experienced member who is always happy to help.
http://www.semrush.com/webinar...
Either just recently joined or is too shy to say something.
An experienced member who is always happy to help.
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.
Occasionally takes part in conversations.
An experienced member who is always happy to help.