Automatically pull your ranking data from SEMRUSH to Google Data Studio

Spending less time on reporting so we can spend more time on doing valuable work for our clients is a mantra we’ve always had here at Marketing Signals.

Recently we’ve taken the step of moving all our clients onto Google Data Studio. Not only does this give our clients transparency and more control over the report, it also saves us valuable time. The report is automatically populated with statistics and our focus can be including our insight and getting on with the work needed to take our clients to the next level.

The only problem we’ve recently come across with Google Data Studio is the inability to automatically update client rankings. Obviously as Google Data Studio is a Google product, plugging non-Google easily is a no-go. To combat this we’ve come up with a solution that involves SEMRUSH API and Google sheets, which we will be sharing with you today! There’s a little leg work to get it setup, but once you’ve set it up this will automatically pull ranking data into your client’s data studio dashboard.


Configuring the sheet…

Edit the API call with:

  • Your clients Project ID from SEMRUSH. This can be found in the URL when you go into your client’s project within SEMRUSH

  • Add your API key into the call. You can find your API key here. Replace the X’s in the call.

  • Include the base URL you will be tracking. This will just be the barebones of your client’s URL with wildcards on each side that will catch any landing pages. You can see the section below that you need to replace:

  • Amend the date range. At the moment due to the formulas within the ranking sheet, this date range has to span 24 days. In a coming update we will be changing this to 30 days. If you need the sheet to cover a different date range, just let us know and we can make the amends to your sheet. The date range is found here (format is YYYYMMDD):

Once you’ve done these steps, your API formula is ready to be called. Press enter and you should get all the relevant ranking data from your project in the sheet.

Onto the rankings sheet.. 

At this point you should see the relevant data pulling through to your sheet, the only problem will be that the data won’t look clean. Due to the way the cleaning formula works, you will have to tweak some formulas to get it as you need for your client.

Depending on the amount of keywords, you may also have to drag each cell down to the required number of keywords.

The first area you will need to edit a formula is the Landing Page (E8), which is below:

What this formula basically does is cleans up all the guff you usually get through the API call, in the landing page case you will get something similar to this:

Lu: { “20170501”: { “*\/*”: “http:\/\/\/landingpage\/” }

For a client, this isn’t going to be readable, so the formula deals with this by removing all the guff and the backslashes and leaves you with a clean URL. All you have to tweak here is the -46 you see in the above formula. Play with this number until you get just the landing page URL without any other data. This will be different for each client. Once you have the sweet number, drag the formula down. Also be aware, if your client URL uses HTTPS instead of HTTP change the FIND integer from http: to https:. If it uses neither, pick something else for the formula to find that is common throughout the landing pages.

The final two formulas you need to amend are the Current Position (M8) and Previous Position (Q8) . Again this is purely to clean the data as each position has your clients URL in the data, as you can see below:

Be: { “* \/*”: 6 }

To combat this, just tweak the formula number again, until you are just left with your position number. In this formula make sure that the number you use for the minus is also included within the mid formula.

Whatever number you get for the Current Position formula, just use in the Previous Position formula and that should clean up all the data!

At this point your sheet should now be ready to use! Just hide all the irrelevant columns and rows so you are left with a normal looking keyword ranking table!

Plugging into Data Studio…

So, now your data is updating and the API is working, the final step is to plug this all into your clients Data Studio.

Once you are in Data Studio, all you have to do is create a new data source and pick Google sheets, make sure you uncheck ‘include hidden and filtered cells’ and set the optional range to the range on your API sheet (this range should start at C7 and end on R* with * being how many keywords you have in your table).

And You’re Done!

At this point you should now have the Google sheet plugged into your Data Studio. Each month you can now just change the date range within the API and this will automatically update the sheet within Data Studio! If you have any questions or feedback on the sheet let us know in the comments, we’ll be working on improving this in the coming months so keep an eye out!

👇 Like what you read? Share what we said! 👇