How to Backup & Visualize GA3 Data for Free
With Universal Analytics ending on July 1st, it’s vital to back up your data. In this guide, we’ll show how you can back up GA3 data in a few simple steps using free tools.
To follow these instructions, you must access Andre’s GA Nitro guide.
This is a webinar write-up of André Mafei’s talk at GA4ward V5. Watch the recording on YouTube below:
About the Google Sheet template
The link above provides a template for Google Sheets and Looker Studio. This splits up the process of using the Google Analytics extension for Google Sheets. It also comes with a ton of predefined settings.
Downloading your data
Let’s follow the step-by-step process for downloading your GA3 data.
Step 1 – Make a copy of the GA Nitro Sheet
To start, you need to make a copy of André’s UA backup template for Google Sheets. Once you’ve made a copy, the instructions are displayed within the sheet, but refer back to this article in case you need more information.
Step 2 – Add a start and end date (maximum of a year)
We can put only a limited amount of data into each Google Sheets file. That’s why we need to download data twelve months at a time.
Step 3 – Add the URL of your GA property
Once you’ve inputted your chosen data into the template, you should add a link to your GA property. Simply head over to your GA account and copy the URL. Finally, paste the URL into the allocated spot on the guide.
Step 4 – Run your GA report in Google Sheets
Now, head to extensions> Google Analytics > Run reports. Depending on the size of your site, the creation of this report could take anywhere from 10-30 minutes.
Step 5 – Copy the URL of the spreadsheet and organise
Once the GA3 data has been populated in the sheet, copy the link from the Google Sheet and place it in the allocated spot. You may also wish to change the name of the sheet to keep things more organised.
Building your own Looker Studio dashboard
Follow the link to visualise your GA3 data with Looker Studio. You can also open Universal Analytics to compare the data between digital analytics tools.
You’ll notice an’ Edit and Share’ button from the top of the Looker Studio page. This is how we create new dashboards in Looker Studio, using a template from your data.
After clicking ‘Edit and share’ and selecting ‘Acknowledge and save’, you will have a copy of the dashboard. You can now play around with this in any way you wish.
Note: None of your data will leave Google. This template is simply a Google Sheets file and Looker Studio template.
Comparing your data
In Looker Studio, your data should look something like this.
In GA3, data should resemble the below image.
Look at any number in Looker Studio and see how it compares to the data reported in GA. For example, let’s compare the bounce rate.
We can see that the bounce rate in Looker Studio matches with GA3.
Now, let’s head over to ‘Source/Medium’. In GA3, the ‘Users’ column cannot be exported due to the limitations of the API. Make sure you stick to comparing data from the chart.
From Looker Studio, you can click any of the table entries to change the chart.
You can also change the metric displayed in the chart by choosing ‘optional metrics’.
In addition to this, you can filter by entering a value.
Now, let’s look at ‘All Pages’. You can see here that Looker Studio is trying to be smart and presenting data as a URL.
But what if we don’t want data presented in this way? To make this data clickable, we can head to the editor and choose ‘Properties’ > ‘Page’ > change the data type to ‘Text’. Lastly, let’s publish our changes.
The data entries in the report should now be clickable.
Downloading goals
What if you want to download your goals? The API only provides us with IDs ‘Goal ID 1’, ‘Goal ID 2’, Goal ID 3’, etc.
Luckily, there is a simple fix for this problem. Head to ‘Goals’ within your Google Analytics dashboard. Sort by ‘id’ and then copy the list.
Now, open a new sheet in Google Sheets and paste in these values. Then, copy the first three rows from this table.
Go back to the template spreadsheet, choose Goal Names>Edit>Paste Special> Values Only.
And there you have it. A list of the definitions of your goals. These will now show up in your Goals report.
In GA3 we were allowed up to 20 goals. This report splits our goals into three parts for both sourced medium and for campaigns.
Our goals can be further customised to our liking. We just need to head back into the editor, select the goal table, and choose the metric we’d like to edit. In this instance, let’s change the name of our goal to ‘Search Result View’.
Now, instead of being named ‘Sample Goal Name 1’ our goal is more easily identifiable. 12
Using the Google Sheet
From the Google Sheet, you can quickly access all the data you need. From the toolbar at the bottom, we can view all result tables.
By choosing ‘All Sheets’, you can also navigate to ‘Source Medium’. This page contains all the data that is being used by Looker Studio.
Utilizing these features in GA4
Last year André launched ‘GA4 Classic Mode’. Thanks to this, we can now not only visualise GA3 data but also GA4 data. Andre plans to launch a new extension to the Google Sheet that will also combine both GA3 and GA4 data.
Back up now
There’s still time to back up GA3 data. Why not make the most of Andre’s free solution and try GA nitro for yourself?
- How to Backup & Visualize GA3 Data for Free - 27/06/2024
- How Consent Mode V2 Affects Google Ads: What to do About it - 08/05/2024
- What is a Customer Data Platform (CDP) & How to Use One - 25/04/2024