How to Back Up Universal Analytics Data

The end is nigh. Universal Analytics is getting deprecated. That means your historical Google Analytics data will soon disappear. 

If you’re planning on backing up your historical Universal Analytics data, now is the time and this article is for you.

I spent a few days working through this problem for websites I manage. During that time I read a lot of blog posts. I tried a number of third-party tools. And talked to a few friends & experts. Through this process I learned that it is not so straightforward to back up your UA data! There is not one way to do it.

In this article I’ll explain what worked for me and I will give you guidelines to help you understand what’s right for you. If you have questions or want help with your backup, leave a comment or contact me at samthebrand.com.

First, ask yourself a few questions…

1. How detailed do you need your backup to be?
2. How presentable does your backup need to be? Who needs to see it?
3. Do you need to combine your historical UA data with your current GA4 data?
4. Do you need to also back up your GA4 data?

Continue reading to help you understand your needs and decide what’s right for you.

What was right for me

Before we get into the details to help you understand what’s right for you, here’s a quick explainer on what worked for me. This is a very brief summary that leaves out a couple tricky details.

1. Download summary UA tables: Export CSVs for a few primary reporting views in UA
2. Download detailed UA tables: Use a third-party tool to download CSVs for a couple more complex UA views ($)
3. Initiate GA4 backup: Sync GA4 to BigQuery
4. Download GA4 backfill: Export CSVs for GA4 data prior to sync date
5. Load UA & GA4 backups: Upload all CSVs to BigQuery
6. Transform summary tables: Write SQL to combine all datasets
7. Visualization layer: Create dashboard in Looker

Is this the right process for you? Maybe.

1. How detailed do you need your backup to be?

You probably need less data than you think you need.

Basic: If you don’t know or don’t care

The past has passed and what matters is how your site performs in the future. So you would not be crazy to just stop reading right here. 

If you want to do the bare minimum, open up your UA property in Google Analytics and screengrab primary views or export them as PDFs. Save the files in your drive and spend time on more productive activities like learning new skills or writing content to grow your audience.

Intermediate: If you need basic reporting views

The easiest way to back up basic reports is to go into your favorite Google Analytics views, click Export, and save to Google Sheets or save CSVs.

Make sure you double check what’s saved because the sheets might not be as detailed as you like. You may need to drill down into deeper reports to get the level of detail you require.

This method is good for downloading daily or monthly reports, and downloading overall views on historical performance like top viewed pages or regional reports.

Advanced: If you need row or user level data, or if you want to combine multiple dimensions

Then you’ll need to use the Google Analytics Reporting API. You can do this in a few ways:

1. Use the Google Sheets add-on to access the reporting UI
2. Write your own code
3. Pay a third-party

Each of these methods has benefits and shortcomings. If you’ve got a budget, and you know you need your data, then it is probably worthwhile to pay someone to do the job right so you can spend time on more productive activities.

There are a few paid services that can help you with this task. Analytics Canvas is a great option. I also provide this service, so reach out to me at samthebrand.com if you need help backing up all the Google Analytics data that is available via the API.

2. Do you need more than backups?

So now you know your options for downloading your data. That’s step one. Again, I’m a big believer in doing the bare minimum to save your historical data. So it might make sense to stop reading here.

But now it’s time to ask yourself…

• Do you also need to back up your GA4 data?
• Do you need a 100% complete GA4 backup?
• Do you need to combine your historical UA data with your GA4 data?
• Do you need to create or share professional-looking reports using historical data?

If you answered “yes” to any of the above questions, then continue reading.

3. Do you also need to back up your GA4 data?

Your GA4 data isn’t going anywhere. This is Google’s state of the art analytics framework and you will be able to use the Google Analytics UI for the foreseeable future to view the reports you need. At any time in the future, if you choose you do need to back up your GA4 data, you will be able to do so.

But there is no time like the present. And Google makes it really easy to sync GA4 to BigQuery to back up your data for free. So you might as well start now.

Here’s an article detailing how you can sync GA4 to BigQuery. Check it out. 

Syncing GA4 to Bigquery starts by clicking into your Admin view in a GA4 property >> Product links >> BigQuery links. Follow instructions from there.

4. Do you need a 100% complete GA4 backup?

If you synced GA4 to BigQuery, then you’re probably missing data in your backup. That’s because your backup starts on the day that you started the sync. Your backups will be missing the GA4 data that was collected between the end of Universal Analytics (June 2023) and the start of your GA4 sync.

So if you want a full backup you’ll need to download data from the gap. Just like for Universal Analytics, there are a few ways to do this. Again, less is more, and it is not urgent that you fill this gap now because the full GA4 history is explorable through the Google Analytics UI. But we’re here now, so we might as well get it out of the way.

For a comprehensive backfill, use the Reporting API or pay a third party like Analytics Canvas. I also provide this service, so reach out to me at samthebrand.com.

But you can get a good enough backfill by using either of these two free paths:

1. Create summary reports in Google Analytics 4 and export CSVs
2. Use the Google Analytics Sheets Add On

In each case you’ll hit limits on how many rows you can download at once. So you may need to batch your downloads by date range.

An example

To access the GA4 summary report below I clicked into the Pages and screens report, added a second dimension: date, and set the date range to the full duration of the backfill. The resulting CSV got cut off at about 5,000 rows, so if you want the full dataset you may need to batch your downloads by date range.

5. Where to save your historical data?

Google sheets or CSVs in the cloud is probably good enough for most cases. But if you’re syncing your GA4 data, then you’re already using BigQuery and so you might as well put all your historical data in BigQuery.

Putting your data in BigQuery is free up to 1 terabyte, which is 1,000 GB. It’s a lot of data. You probably won’t hit that limit. And you can set alerts so you are notified if you are about to hit your limit.

2 additional benefits of using BigQuery…

1. BigQuery integrates easily with Looker (another Google product), which you can use as a visualization layer for your backup
2. You can use the BigQuery SQL console to query and combine your historical backups

6. How do you want to visualize your historical data?

If you just need to save all your historical data and be confident that it will be backed up moving forward, then stop here.

But if you need to visualize your historical data or share it with a stakeholder or client, then you’ll need to think about presentation.

If you’re just downloading PDFs from the Google Analytics UI…

You’re done. First design your charts in the Google Analytics UI to make sure they’re the right reports you think your audience wants to see.

If you’re downloading CSVs and keeping them in the cloud…

Use Google Sheets or Excel to build charts on top of your raw data. At minimum, show your audience the date range covered by your backups.

If you’re using BigQuery…

Then you may want to build reports with Looker.

Looker lets you connect to your current GA4 reports directly through a Google Analytics integration, and you can connect to your BigQuery backups through a BigQuery integration.

Once you’ve added your data, you can build charts and style your reports so they look professional. Your clients will appreciate this.

7. Do you need to combine all your backups?

In a perfect world, it would be easy to combine your historical Universal Analytics data with your current Google Analytics 4 data so you can get a full historical picture of your website analytics.

But UA data and GA4 data each have a unique shape. In fact, GA4 sync data has a different shape than GA4 data extracted via Google Analytics or the Reporting API. So if you are like me and you want to see ALL the backups in a single report, then you’ll need to do some data transformation to create a common format for each of your datasets.

Depending upon the detail of your backups, this can be straightforward or hard. No-code tools like Analytics Canvas can help you create compatible summary formats through its drag-and-drop interface. I can also help you do this, so reach out to me at samthebrand.com and we’ll see how I can help.

If you know a little SQL, you can do it yourself.

Here’s a SQL snippet I used to combine summary data from all 3 datasets. Please let me know if I can help you run this on your own data.

WITH newga4 as (
SELECT EXTRACT(DATE from TIMESTAMP_MICROS(event_timestamp)) as date, COUNT(DISTINCT user_pseudo_id) users, COUNT(DISTINCT (concat(user_pseudo_id, ' - ', (select value.int_value from unnest(event_params) where key = 'ga_session_id')))) sessions, COUNT(*) as pvs
FROM `GA4SYNC`
WHERE event_name = 'page_view' AND event_date > '20240101'
GROUP BY 1
)


SELECT
 COALESCE(ua.date, hga4.date, newga4.date) date,
 COALESCE(ua.users, hga4.totalUsers, newga4.users) users,
 COALESCE(ua.sessions, hga4.sessions, newga4.sessions) sessions,
 COALESCE(ua.pageviews, hga4.screenPageViews, newga4.pvs) pageviews,
 ua.date ua_date, ua.users ua_users, ua.sessions ua_sessions, ua.pageviews ua_pvs,
 hga4.date hga4_date, hga4.totalUsers hga4_users, hga4.sessions hga4_sessions, hga4.screenPageViews hga4_pvs,
 newga4.date newga4_date, newga4.users newga4_users, newga4.sessions newga4_sessions, newga4.pvs newga4_pvs
FROM `UAHISTORICAL` ua
FULL OUTER JOIN `GA4BACKFILL` hga4 on ua.date = hga4.date
FULL OUTER JOIN newga4 on newga4.date = hga4.date
ORDER BY 1 desc;

Thanks for reading and best wishes.