Diogo Abrantes da Silva

In this article, which premiered during Superweek 🥳, I will demonstrate how to backup data from your Universal Google Analytics for free using the Google Analytics API, Google Sheets, and Looker Studio. The result? A beautiful dashboard that allows you to easily view your data like this one here or image below.

👀 Additionally, since this process was specifically designed for UNICEF Portugal and shared with their consent, I kindly request that if you find this method useful, please consider making a donation.

⚠️ Before you begin, please also review the “What you need to know about this method” section. If you require assistance with the process, I am willing to help, provided you make a donation to UNICEF.

 

Why use this method over others?

To easy answer to this question is that it’s a FREE and structured way
In the official Google article, if you do not have a Google Analytics 360 account, the ways that you can back-up your Google Analytics Universal (GAU) data are:

• by exporting each report individually (which would take a long time to do),
• use the GAU API to save the information in a cloud you want (not easy to do without a programmer)
• or use the Google Sheets Add-on, which is what we have done here but added a way to do it without sampling date and make it useful with a UI that you could look at.

You could also use Google Big Query. However, you would need a connector to do that and a way to structure the data (the logic you want to use when downloading the information). These are done with some tools that have a cost associated with (image below).

 

How does this work (video)?

 

What you need to know before using this method

Before you start here’s what you should know about this process:

• The numbers you’ll get should not be sampled, that was the goal and reason why I divided the queries so much.
• The numbers you’ll get with this method, even though they shouldn’t be sampled, won’t be exactly the same as in the Google Analytics UI (just like with Big Query) because of the sum of the cells/users won’t be deduplicated.
• You might have to adapt or edit the Google Sheets if you have too much data and keep an eye on your sheets, so the file keeps saying: “Contains Sampled Data: No” on every page report (excluding Google Ads Report)
• The Google Ads reports are the only reports that should be sampled (but you’ll have that data in Google Ads).
• It’s important that you use a copy of the sheets I created because of the cell limit per sheet.

 

Which tools you’ll need to create a backup of your Google Analytics Universal data

The tools you’ll need are:

• The official Google Add-on for Google Sheets connection to the Google Analytics API (install it here)
• Google Analytics Editor Access

 

How to create a backup of Google Analytics data and view it in Looker Studio process

1. Install the Google Sheets add-on to the Google Analytics API
   
2. Make copies of these 2 Google Sheets (click on each link): 1️⃣GAU DataSet 2️⃣ GAU DataSet 2
3. Remove the text “Copy of” from both sheet names. This will make your life easier later on with Looker Studio.
   
4. Enter your view number in all the yellow cells (image below) in to the 2 Google Sheet files you copied on the pages called Report Configuration (image below). 
   
5. Run the Google Analytics add-on on both sheets (don’t forget to first add the view number). If it’s the first time using it will ask permission to access the data. Here, if you run into some issues check the possible errors list by clicking here or scrolling below the instructions.
   
6. 🥳 Perfect, you have your data backed up. Check that all the sheets say: “Contains Sampled Data: No” (image below) except the sheets called “GAds Kwds and Queries” and “GAds Accounts” which should not be an issue because you have access to that data in Google Ads.
7. Now we can make the data more useful by using Looker Studio to view it in a user-friendly way. This process will take more time but don’t worry it’s a onetime thing.
   Click here to go to the Google Analytics Universal Looker Studio Template and click on Copy (like the image below).
   
   
8. Now you’ll need to match each Google Sheet of your data with your Looker Studio resource (follow the process below).
   
   
   
   
   ⚠️ If you just added a report and it doesn’t show in the list yet, don’t worry keep adding the rest of the reports and they will eventually show up.
9. Now if you find this report useful, please consider making a donation even if it’s 5€s, $5 or whatever.

 

How to add a new report

I’m happy to help. 😉 So even though I’ve created several reports from the main Google Analytics Universal UI there were several more I didn’t. However, it’s very easy to add a new report to your new Looker Studio report and your Google Sheet.

Here’s how to do it:

1. Create a new report in the Report Configuration with the variables you need to create your report by checking the code for each metric and dimension.
2. After having that report, connect it to your Google Analytics Universal Looker Studio Template.
3. Copy one of the Looker Studio page and edit the source you have just created as the source of that table and chart.

 

How long can the backed-up data be stored for free?

As long as you have a Google account you can keep your data with you. It’s also easier to just download yout data into Excel sheets. Enjoy 😉

 

Are there any limitations on the amount of data that can be backed-up for free?

No. Actually you could even create more sheets with more data. As long as you separate the sheets you should not hit any limit here.

 

Possible errors and solutions:

• Report Name: Response Code: 413. Message: response too large.

This means the number of cells in the response would be too large to retrieve.

Solution: In that case you can add a filter like I did in our 2 files of ga:sessions>100 Like that you would only gather information from the API of results that would at least, for that dimension, have 100 sessions.

• ScriptError: Exceeded maximum execution time

This means that the script is taking too long to gather the data.

Solution: run one query at a time. For this make a copy of that Report Configuration page and remove from the original all the columns except column B. Each column after column A is a report. So, what you want to do is run the Google Analytics Add-on on report at a time.

• Wow! This doc has changed a lot. To sync to the latest version, reload 

This means you just need to reload the page.

• ScriptError: This action would increase the number of cells in the workbook above the limit of 10000000 cells.

This means that the API query would increase the number of cells over the maximum limit.

Solution: It’s important to use the Google Sheets I provided because the add-on creates new sheets with many unused cells that still count towards the limit. That should solve the issue. If even using the sheets I created you still run into to this issue, just create a new sheet and add that one in Looker Studio.

 

Other possibilities and improvements for this process:

• Connect to GA4 data all in Google Sheets and keep the data flowing (GA4+GAU)
  Yes, it is possible for you to merge this data with your Google Analytics 4 data to have a continuum report for your sessions.
• It’s also possible to speed up the response time from Looker Studio by using the tables from Google Big Query instead of the Google Sheets.
• You can actually use the same Google Analytics Universal Looker Studio Template to report on the data in Big Query to easily visualize it.

 

If you found this article useful, have any comments or corrections please send me an email at ads[@]diogo.in or a message on LinkedIn: https://pt.linkedin.com/in/dasilvadiogo/
Let me also thank André Mafei for the idea and his GAU dashboard and of course Superweek for the video.