Using SEMrush API with Google Sheets
Streamline your SEO Workflow Research
If you are involved in SEO then you know how much time it takes to carry out keyword research for clients. If you've spent hours exporting organic research data for a domain in SEMrush and then importing it into Google Sheets - this article is for you. Using the SEMrush API with Google Sheets can save a TON of time with SEO workflow. It seriously reduces the time it takes to carry out keyword research and competitor research, or at least the data collection part of the process. Whether you are an SEO Consultant, SEO Freelancer or work in an agency, time is always precious. So let's dive in.
[lwptoc]
Introducing the SEMRush API
SEMrush has an api for their platform that can be used to literally suck their extremely valuable data into a 3rd party solution. If you are a developer then this opens up a whole raft of opportunities to integrate this data into other platforms. But, if you just want to streamline your SEO workflow, then using Google Sheets to collect and organise your SEO research data, then this approach is perfect.
SEMRush and Google Sheets Intro
Put simply, combining the SEMrush API and Google Sheets is an awesome combo for your SEO workflow, but, it's worth taking some time to plan your approach rather than diving right in because, if you're not a developer, it can take a little bit of time to get your head around the steps in the process to get things working for you, rather than against you. If you are part of an agency and have access to web developer resources then great. If not, or you are an SEO Consultant or SEO Freelancer then this is a great option.
SEMRush API Data Interface Options
First off, to get data out of SEMrush using their API and into Google Sheets (or Excel for that matter) you basically have two options;
Using the IMPORTDATA function in Google Sheets (or Excel)
Using the Supermetrics Google Sheets add-on
A Quick Word on Supermetrics
I love Supermetrics and if you haven't checked it out I thoroughly recommend it. There is a free trial available and if you can fit it into your workflow, it's an awesome and flexible tool. We use it extensively to pull data from Google Search Console to analyse our organic SEO performance for clients.
This guide will only look at option one above as for now, Supermetrics is a whole other subject. That being said, if you want the most flexible, customisable research partner for SEO and the SEMrush API, then this should be your tool of choice.
Researching your API Query Options
To get your data out of SEMrush using their API and into Google Sheets, we need to use the IMPORTDATA function. We also need to reference the SEMrush API documentation in order to structure our IMPORTDATA query.
In this case we are looking at the Organic Research data from SEMrush, and then specifically, the data found on the Positions tab in that part of their software. See below;
SEMrush web Report: Domain Analytics > Organic Research > Positions Tab > Organic Search Positions (screenshot)
SEMrush Organic Research Example
You can see that the data in this report is all the ranking keywords for the domain ebay.co.uk in the UK database. We can export this data into a CSV file and then import it into a spreadsheet using the export option. That doesn't take too long but if you need to do this for multiple domains then this process becomes very time-consuming.
To use the API to get this data into our spreadsheet we need the following information;
Our SEMrush API key
The target domain
The data fields we want data for (you will need to reference the SEMrush API documentation for this)
Building your SEMrush API Query
The final API query will look something like this (spoiler alert!);
https://api.semrush.com/?type=domain_organic&key=yourapikey&display_limit=100&export_columns=Ph,Po,Nq,Kd,Cp,Ur,Tg,Tr,Co,Nr,&domain=ebay.co.uk&display_sort=tr_desc&database=uk
API Query Elements
type= This is the report data you want to access
key= This is your API key
display_limit= This is the number of lines of data you want to export (remember you pay per line of data in API credits)
export_columns= These are the columns of data you want to export
domain= This is the target domain
display_sort= This is how you want the data displayed
database= This is the database you want to pull the data from
Building an SEO Research Template
To build the API query using the IMPORTDATA function you can write it individually but the real opportunity is to create an entry field in a spreadsheet to just enter the domain names you want pull data for. For example a client URL along with a list of competitor URLs. This is where we need to get a little creative.
As an example, let's build a spreadsheet with a tab for us to enter a list of target domain names. Then we will have the data collected via the API placed into separate tabs of our Google worksheet, one for each domain name. The tabs for each individual domain will contain all of the data that we see in the Organic Research>Keywords section of SEMrush (as shown in the screenshot above).
If you want your own copy of the Google Sheet I've used in this articles I've created a Google Sheets publically shared file 'Using SEMrush API with Google Sheets Download' that you can access and copy via that link.
The first tab (to enter the target domains) will have 5 columns - A to E
Column A - Target URL
Column B - https://api.semrush.com/?type=domain_organic&key=yourapikey&display_limit=100&export_columns=Ph,Po,Nq,Kd,Cp,Ur,Tg,Tr,Co,Nr,&domain=
Column C - =A1 (URL)
Column D - &display_sort=tr_desc&database=uk
Column E - =C2&""&D2&""&E2
Customising your API Call Parameters
You will notice there are a few parts of that set up that need to be configured before you make your API call to the SEMrush databases. The first being the target URL. Then you will need to add your API key - this can be obtained from your SEMrush account (with a suitable subscription only) and finally, how many lines of data you want from the database.
In our spreadsheet (shown above);
Column A will contain just your target URL (e.g. ebay.co.uk)
Column B - you need to enter your SEMrush API key where shown
Column B - you need to adjust your display limit accordingly. I suggest fixing it to maybe just 5 whilst you get this set up and tested.
Note that in this specific example the display limit is going to be the top 100 ranked keywords, sorted by traffic volume.
Final Example URL for our IMPORTDATA Function
The final contents of the URL we will use with the IMPORTDATA function, in this example will be;
https://api.semrush.com/?type=domain_organic&key=yourapikey&display_limit=100&export_columns=Ph,Po,Nq,Kd,Cp,Ur,Tg,Tr,Co,Nr,&domain=ebay.co.uk&display_sort=tr_desc&database=uk
Setting Up our Google Worksheet
Based on the above, our final URL for the IMPORTDATA function (above) should be in column E of our first worksheet. This is the final URL we will use with our data function. If we have a header row and this data is in the second row, our target URL will be in Worksheet1 (which we are going to name 'Competitors'), cell E2 (see screenshot below, just to recap).
Just for reference, we will name the subsequent worksheets in our document, Competitor 1, Competitor 2 etc. I'd advise naming them with the URL of each of the target URLs as that makes referencing the data easier for you and your client if you are sharing the data.
Using the IMPORTDATA Function
In the subsequent tabs, or just tab in this example (labelled ebay.co.uk above) as we are only using one domain name, we use the IMPORTDATA function. On the second tab in cell A1, we enter the following
=IMPORTDATA(Competitors!E2)
This means we are effectively putting the following in the top left cell of the new worksheet;
=IMPORTDATA(https://api.semrush.com/?type=domain_organic&key=yourapikey&display_limit=100&export_columns=Ph,Po,Nq,Kd,Cp,Ur,Tg,Tr,Co,Nr,&domain=ebay.co.uk&display_sort=tr_desc&database=uk)
This is your api call to the SEMrush database completed. The following data will be pulled from api and put in your worksheet tab, looking like this;
Formating the Raw Data Import
Now we format the imported data. But before we do that, we want to record what we do as a macro so we can use it to format subsequent worksheets, for other domains, way more quickly and efficiently. So, things like, making the header row bold text, adding text wrapping to the header row, freezing the top row - whatever you want to do to format your data.
Irrespective of how you want your data formatted, we need to split the cell data as everything goes into one cell from the data pull.
You need to select the whole of column A
Select Data > Split data to columns
Separator > Semicolon
After you've split the data and done some formatting - you will have the following;
Streamlining your Competitor Analysis Research
Now you have everything set up to use the SEMrush API to get your keyword and competitor research data into a Google Sheet. You can create tabs for each competitor and then just add extra lines for each of them in the first tab of the spreadsheet.
Then, all you need to do is put the function IMPORTDATA(Competitors!E2), from Cell A1 the second worksheet, into the third (second competitor) and change it to IMPORTDATA(Competitors!E3) etc. Just to make it clear, cell E3 will contain the next URL for which you want to get data, E4 the next and so forth. Only the target URL changes, the rest of the information is as you set it up above.
We advise putting you put your time into building your Competitor Analysis spreadsheet upfront. Then. all you will need to do is just enter the competitor URLs into the correct cell in the first worksheet and let the API do all the work. Then just run your formatting macro to sort and format the imported data and you're done!
If I've missed anything or something isn't clear or you want more info - please get in touch. I'm always happy to answer questions!
Looking for a Squarespace SEO Expert?
As a Squarespace SEO expert based in the UK, I provide bespoke SEO services that can help your Squarespace website achieve better search engine rankings and increase traffic to your site. With my in-depth knowledge of Squarespace and expertise in SEO, I can help optimise your website for relevant search terms, improve website performance, and enhance your online presence. If you're looking for a Squarespace SEO expert in the UK, get in touch to learn more about how I can help your business succeed online.