Google Analytics Real-Time data in Data Studio
There’s so much that I can write about Data Studio, examine how this product serves so many needs of my customers and all free. But, this article is focused on enabling Real-Time Google Analytics data in your Data Studio sheets.
For some reason, even though Data Studio has so many great data connectors including Google Analytics, They didn’t enable us to connect to the Real-Time Google Analytics API.
In this article, I will walk you through our solution for presenting Google Analytics Real-Time data in your Data Studio reports.
This is a more technical article than the previous ones
Our technique is the following:
Using the superproxy to fetch data from the Google Analytics Real-Time API
Presenting the data in Google Sheets (which has a connection to the Data Studio)
Adding a trigger to our Sheets, so that it will be refreshed automatically
Connecting our Sheets to the Data Studio for Google Analytics Real-Time data (latency of 1 minute)
I can’t explain the superproxy and how to implement it better than Pete Frisella which is a Google Analytics Developer Advocate. If you’re not familiar with the superproxy please watch his webinar and follow his instructions.
Notice: some of his instructions are not up-to-date, there were many changes in the GCP (Google Cloud Platform) UI. They are minor, I hope you’ll get your way around.
Then we need to enter our query and its refresh interval
This query is all events by event Category Action and Label. You can easily modify it with the Google Analytics Real-Time API explorer
After we save our query we will reach this page:
We will enable our endpoint and then Start Scheduling.
Let’s copy the URL, we’ll use it real soon
Now let’s open a new Google Sheets file, under Tools, we choose Script editor
Let’s replace the default function with this one:
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets() // This is the sheetID 0 is the first sheet, 1 is the second sheet etc
sheet.getRange(‘A1’).setValue(‘1’) // We add this so that the cell will be refreshed and not rely on cache
sheet.getRange(‘A1’).setValue(‘=importdata(“[Place here the URL you copied]&format=csv”)’)
Now let’s run this function automatically every 1 minute (or any other time frame)
Let’s click on the Current project’s trigger icon (or Under edit, select the Current project’s trigger)
Now we choose the function we would like to run (refreshSheet), then we choose that the event will be triggered – Time-driven (not from spreadsheet), at last, choose how frequent you would like it to reload. The most frequent is every 1 minute
You’re free to fetch anything you want from your Real-Time Google Analytics Account into the Data Studio (almost real-time)