How To Back Up Your Google Universal Analytics (UA) Data Before July 1st

It’s almost that time – Google is removing access to its Universal Analytics interface starting on July 1st. 

If this is the first time you’ve heard about this, I’m sorry to break the news to you. But don’t panic! There’s still time to save and back up past website analytics data for future reference.

In this blog post, I’ll explain how you can back up all of your data securely, and view it alongside GA4 before all access goes away. Let’s get started.

---

 

Why You Should Back Up Your Account

If you’ve made it this far, you’re likely familiar with Google’s new analytics platform, GA4. GA4 analyzes traffic, data, engagement for your ads, and anything else you may need to monitor. It first launched in 2020, however, this was not commonly adopted by many businesses until roughly 2022-2023. 

Most businesses stood beside Universal Analytics until just last year, and up until July 1st, 2023 when Google decided to stop processing new data.

Although Universal Analytics can no longer collect new data, it’s still a valuable source of historical data that we highly recommend backing up for future reference and use.  As a result, many businesses only have 1-2 years or less of historical data on their website traffic!

Ways to Save GA4 Reports & Data

There are several ways to preserve your historical GA reports, but for most situations, we suggest considering bypassing the report and exporting options directly within GA.

By doing so, you can opt for a method using the Google API that makes the process easier and provides your data in a more usable format.

We recommend using the free Google Sheets Google Analytics Add-on or a third-party tool. Let’s go over some ways you can export your data within analytics.

Export to PDF

You always have the option to export a specific view you’re looking at to a PDF. This is a great way to take a snapshot of any report, and the metrics and trends. However, this data isn’t very flexible.

We generally recommend saving this data in a way that allows you to graph and query it as needed, either in a spreadsheet or data warehouse.

Export to CSV/XLSX/Google Sheets

Google Analytics allows you to export directly to a spreadsheet, however, there are some limitations with these reports. The three biggest considerations are:

1. Dimensions in your reports (specifically date dimensions)
2. How these are exported
3. Row limits

When you export a report, for example, a basic source/medium report, Google provides the data in two sheets:

1. By dimensions of the table, such as the source/medium.
2. By date of the graph, such as sessions/users by date.

The problem is that often we would want these dimensions, or breakdowns/columns combined in one report.  This way we can drill down or view this data by multiple dimensions, for example, both source medium AND date.

Unfortunately, these default reports are only useful if you pre-filter your report to exactly the view you want. In general, this can be a limiting method to export and use data in the future.

The other issue with the default export method is that you’re limited to the number of rows shown in your report, which you can expand to a max of 5000. If your report is more than 5,000 rows, you would have to export multiple pages and combine these.

Here is an example of the output of the CSV sheet.  The first sheet referenced two different data sets for our source/medium report export.

Below are the two datasets, one sheet with source/medium, and the other with sessions by date. As you can see, we don’t have these dimensions together in one view.

There is also a feature in GA to build custom reports which provides more flexibility for adding dimensions to your reports under customization. This allows you to build custom tables in order to export multiple dimensions that may be helpful including date. However, we still run into the row export limits, especially when combining multiple-dimension breakdowns.

Because of this, we recommend avoiding the option to export your date compared to the next method we’ll go over, which is using the sheets add-on.

Google Sheets Google Analytics Add-on (Our Recommendation)

We recommend using the free Google Sheets add-on that Google publishes. This allows you to export your data at no extra cost! By using this option, you can define what dimensions and metrics you want for multiple reports, and run the report! This will export your data directly to Sheets:)

This add-on allows you to “create a report” where you select your GA account and configure your dimensions and metrics. This will then create a report configuration sheet.

You can then rinse & repeat this process for multiple reports in the same spreadsheet! This will create additional columns for each configured report.

Now from the add-on menu, you can run your report which will process all queries configured in your report sheet. 

(Note: You’ll need to select an appropriate start date and how many rows to run which you can reference from each report spreadsheet once you run them.) We also recommend verifying you’re using the correct GA account/property/view and avoiding data sampling which is displayed on the individual sheets.

To get the add-on:

1. Extensions > add-ons > get add-ons
2. Search google analytics
3. Install “Google Analytics”

 

Or access the Google Sheets Analytics Backup Extension directly here.

Now onto the fun part… creating a report!

1. Start by clicking “Extensions” on the top left of your Sheets
2. Head down to “Google Analytics”, then over to “Create new report.”

For those of you who are visual learners, here’s a little screenshot for ya:

From there, you can name your report, select a view, and choose your configuration options.

Now let’s add some dimensions to get the ball rolling:

Initial Report Setup

Now that your report is loaded into the configuration screen, you will need to set the start date, and eventually update the limit cell.  For now, update the start date to a date on or before the UA account begins tracking data, the 1000-row limit will keep our initial report request fast.  

Now you can run your report by clicking “Google Analytics” then “Run Reports”.

Query Updates

After initially running the report, there are a couple of things you will need to check to update, followed by re-running the report.

Updating the Number of Rows to Return

First is the Total Results Found cell.  When this report was first run, it returned only the first 1000 rows from the limit cell on the configuration sheet.  Copy the results found value into this limit cell on the first page.

Avoiding Spreadsheet Cell Limits

The second is considering cell issues for your spreadsheet.  Google Sheets is limited to 10 million cells, and running reports with a high row number will quickly have you approach this limit.  If you attempt to process a report that tries to create too many cells, you will see an error message, and your reports will fail.

You have two options to avoid and solve this:

1. proactively removing extra columns
2. splitting your reports into multiple sheets

Removing extra columns is a quick and easy method that I’d recommend always doing after initially running your reports with the 1000-row limit.  Your reports might only be using the first 8 columns for example, so deleting the default columns up through col Z  to the right of your data will free up a lot of extra space.  If you remove 20 blank columns times 200k rows, that one report will free up 4 million rows towards that 10 million limit.

The next option is to split your report into multiple sheets.  Above we looked at creating multiple reports in a single spreadsheet, however, you may need to split reports into separate documents to avoid row limits.  We would recommend creating different sheets for acquisition, pages, and landing page reports for example.

If you have a single large query, you can also split a single report by date if needed, however, if you are running to this, you are likely running into sampling as well! (see below)

Avoiding Google Analytics Data Sampling

Next, we need to check for Sampled Data. The Contains Sampled Data cell tells us if this report is the actual data collected for the report, or if it’s sampled/modeled.  Typically this happens if there is too much data requested from a single API request or report.  If this cell has a value, we recommend configuring your reports to avoid sampling.  

To avoid sampling with the Google Sheets add-on, the first thing we recommend is trying to split your report into multiple requests by shorter date range.  By simply splitting the request into two, three, or more smaller requests, often you can avoid data sampling.  Generally the closer to 100% you are, the less to might need to split.  To speed up this process to find the date ranges that might align with unsampled data, you can try to find a similar report and date range in the GA UA interface, find the shield icon, and see the sampling rate.

Each time you update these, select run reports to process the report!

Third-Party Tools

Many paid third-party tools allow you to query and export reports through the Google Analytics API’s. This is sometimes referred to as extract-transform-load tools (ETL OR ELT).

Some popular tools that work for Google Analytics are:

• Funnel
• Supermetrics
• Hevo
• Stitch

These tools can streamline your reporting process and can offer more flexibility for reporting. These do come at a cost and have their own complexities, but if you have a lot of data and reports to back up, and are running into sampling often, it is an option to consider, particularly for large businesses and e-commerce brands. 

One common report that will run into sampling is e-commerce-reports such as your product performance reports.  We often like to view this data by multiple dimensions including date, traffic source, and campaign ID which can quickly scale rows.  Having a solution that can batch requests automatically can be very helpful with exporting this data, and these tools usually have features to avoid data sampling.

Reminders & Caveats

By now, you’ve got a good grasp/overview of what to do when exporting your data and backing up your reports. It all seems fine and dandy for now, but there are a few things to note and avoid when exporting.

1. Backup Before July 1st, 2024

Don’t wait until it’s too late!  Make sure your reports are backed up before July 1 when the API and UA interface will no longer be accessible.

2. Avoid Data Sampling

As mentioned, Data sampling can occur in Google Analytics when you request or view a report with a large number of results. Sometimes under these conditions, Google may run “sampled”  or modeled data. We recommend avoiding this, especially if you’re backing up this data as a historical source of truth.

3. Avoid Row Issues & Splitting Reports

You’ll likely run into row issues if you’re returning lots of data or running multiple reports. You can make sure this doesn’t happen by deleting excess cells.

You can also split reports into their own spreadsheets, for example, one sheet for:

• Basic reports
• Acquisition reports
• Pages
• Landing pages

4. Key Takeaways

• Remember, you only have until July 1st!
• Ensure you have the correct account and all of the essential properties before exporting
  • Such as date range, data sampling, specific dimensions, and traffic source
• Avoid sampling
• Make sure to update your row count
• If you run into issues, try removing excess cells

Our Report Recommendations: A Summary

Okay, you may be feeling a little overwhelmed by all the data you may need to export. If you want to focus on just a handful of reports to export, here’s what we would recommend starting with:

• Basic sessions/users by date

Acquisition Reports 

• Metrics: Sessions, Users and New Users, Goal Completions (if relevant)
• Reports:
  • Source/Medium – By date and source/medium
  • Referrals – by date and referral (if relevant)
  • Campaigns – by date, campaign and source/medium

Page Reports

• By Page and Date
• By Page, Date and other breakdowns you find valuable (i.e. source medium)

Landing Page Reports

• By Landing Page and Date
• By Landing Page, Date and other breakdowns you find valuable (i.e. source medium)

Other Reports

• Events breakdown
• Goal Completion breakdown
• E-commerce reports
  • Many to consider here, including:
  • Revenue by date
  • Transactions by date
  • Product performance by date
  • Detailed breakdowns for each (by source/medium, campaign, etc)

—–

We hope you found this blog helpful, and wish you the best as you look to back up your Google UA data by or before July 1st! In the meantime, if you have any questions or concerns – reach out to our team of digital strategists. We’d be happy to help!