How To Speed Up DataStudio Reporting With Google Sheets

As a Web Analytics professional, being proficient with your tech stack is fundamental to your day-to-day. As part of that tech stack, Google DataStudio is a powerful data visualization tool that allows you to create interactive and customizable dashboards. Not only is it free to use but DataStudio offers a user-friendly drag and drop interface, hence many marketers use this tool to visualize data in a clean and crisp format.

A range of free data sources can be used, including Analytics, BigQuery, Search Console and Sheets. There are plenty of additional connectors that have been designed so you can visualize data from other sources, such as HubSpot, LinkedIn or Facebook. 

The main issue that arises with DataStudio is its load speed. In this article, we will show you the various ways that you can increase the speed of report loading by sending your marketing data to a Google Sheets document and creating a data source using the Sheets connector.

This article focuses on data from Google Analytics but similar approaches can be considered for reporting on other marketing platforms in DataStudio.

Why is the load speed so slow on Data Studio?

Suppose you’re trying to report on Google Analytics data in DataStudio. If you have added a segment or are trying to view data from a Google Analytics view with a high volume of traffic or hits then you might experience significant delays in load time. This requires the querying of data directly from the Google Analytics servers, often involving many API requests, which can take some time to run. 

The solution is simple: your data source should contain only the data that you will be displaying in the dashboard. Google Analytics contains data for every tracked hit from every user that visits your site – the vast majority of this data will not be necessary for your DataStudio dashboard. If you export all of the required Google Analytics data into a Google Sheets document and use this as the data source instead, load speed will increase significantly. 

Exporting the right data into the Sheets document will still take some time, but once the data is there your DataStudio views will load quickly. We recommend automating queries and running them overnight so you can wake up to fresh data in your Sheets document.

Although this article uses Google Analytics as an example, the same concept of exporting data into Sheets can be used for all data sources.

It is also worth mentioning that these methods can facilitate data processing and merging, reducing the need to blend data sources or create custom fields within DataStudio itself.

How to export data from Google Analytics to Sheets

Method Comparison

Analytics Edge

Analytics Edge is a free Microsoft Excel Add-In that allows you to query data from Google Analytics into an Excel spreadsheet. This method is the most straightforward as it requires no costs and it is easy to select the data you need as there are drop downs to show you all the available fields in Google Analytics. 

Simply go through the tabs to set up your query. If your data is being sampled (or if you’re unsure) then make sure to include date as a dimension and tick the “Minimize sampling” box in Options in order to ensure data is as reliable as possible.

The main caveat with the Free version of Excel is that it can often be a little buggy – the Paid version is likely to be better. If you experience errors, we recommend reducing the date range and splitting out your requests across multiple queries. For instance, instead of pulling data from one month, this might need to be done in two queries with two weeks date ranges. 

Once you have queried the data you want, simply copy and paste it into a Sheets document.

R / Python

This is possible in any programming language but there are packages available in R and in Python with documentation available to run you through the functions. In order to pull data from Google Analytics and send it to Google Sheets, no existing programming experience is needed. However, this method provides experienced programmers with a multitude of options, such as: 

– Merging with data from other marketing platforms (HubSpot, Hotjar, Search Console etc.)

– Automating of query with script scheduling 

For those with no experience, we recommend downloading R and the RStudio environment as these are more easy to set up than Python. A brief guide on how to set this up on your device can be found here. 

Simply run the following lines of code to send sessions split by channel grouping and device category into Google Sheets. The date range, metrics and dimensions can easily be changed accordingly, as well as the Google Analytics ID and Sheets URL.

Have a look at the documentation to see what else you can do with these packages: googleAnalyticsR and googlesheets4.

install.packages("googleAuthR")
install.packages("googleAnalticsR")
install.packages("googlesheets4")
library(googleAuthR)<br>library(googleAnalyticsR)
library(googlesheets4)
ga_auth()
gs4_auth()

ga_id &lt;- #ID of your GA View#
dateRange &lt;- c("2020-01-01", "2020-11-30")

sessionsData &lt;- google_analytics (ga_id,
    date_range = dataRange,
    metrics = "sessions",
    dimensions = c("date","channelGrouping","deviceCategory"),
    anti_sample= TRUE

sheet_write(sessionsData, ss = "#Section of URL of your Sheets doc#", sheet = "Sheet1")

Note that the section of the URL that you will need to insert into the code can be found in the search bar at the top – see the highlighted section in the screenshot below

Supermetrics / Paid Tools

Supermetrics for Sheets is a paid tool that offers many data connectors, including a GA to Sheets connector. Although Supermetrics offers a user-friendly interface similar to that of Analytics Edge, it is worth looking into other paid tools that offer such a connection.

Both Analytics Edge and Supermetrics offer dynamic date ranges, such as “last month”, preventing you from having to update dates whenever you are querying data. However, Supermetrics offers a useful “Add to Old Data” option which reduces total query time. 

Supermetrics acts as an Add On to a Sheets document, removing the need to copy and paste from Excel.

Supermetrics has 4 main advantages over Analytics Edge:

– You can send data to Google Sheets from other data sources (Facebook, LinkedIn, BigQuery etc.)

– Better customer support

– No need to copy and paste from Excel to Sheets

– An option to “Add to old data”, reducing query time

N.B. Don’t get this confused with the Supermetrics for DataStudio connector!

Summary

DataStudio is an extremely powerful tool for displaying data which tends to get good traction with all audiences. This is likely because it is user-friendly but also offers an element of interactivity for those wishing to drill down and increase the granularity of data. 

The primary hindrance to DataStudio when reporting on Google Analytics data is the report load speed, which is experienced when querying high volumes of data. 

Google Sheets offers a solution to this by acting as an intermediary data source, holding in it only the required Google Analytics data for your DataStudio reports. Various methods can be used to query data into your Sheets doc, all with their own pros and cons.