How to Backup Google Analytics data for Migration to GA4

Ameet WadhwaniData warehousing, Universal Analytics

GA4 is here and Universal Analytics is being removed from Standard sites as of July 1st, 2023. With that comes a renewed focus on archiving data so that it will still be available after the Reporting API is sunset on July 1st, 2024. In this post, we’ll show you the options for downloading your data, outline the challenges you’ll encounter, and show you how to backup Google Analytics data to a database, to BigQuery, to Sheets, and other destinations using the best available tools.

📢 Due to popular demand, we have built an out-of-the-box solution to backup UA data from just $99/view* (some limits apply). We’ve also added a 20-page Looker Studio Universal Analytics backup report. Get started today!

The Universal Analytics Backup Utility was designed to export Google Analytics data using the Reporting API. It has a series of pre-defined tables, a custom query editor, and an optional Looker Studio report. Pricing starts at just $99. Learn more about our utility, or continuing reading about the process of creating a backup below.

Exporting Universal Analytics Data: What data is available?

One thing to note: It is not possible to “get a copy” of all your GA data with a single click. You will have to make a series of queries and extract the data that you need to backup. This requires at least some knowledge of the Universal Analytics data model to ensure you get accurate data.

Also note that while data will be available for both Standard and 360 accounts until June 30th, 2024, your data retention settings may prevent you from getting sufficient history. Check your data retention settings to determine how urgent this is for your website.

backup google analytics data using canvas
Typical workflow for backing up Universal Analytics data to BigQuery using the Google Analytics Reporting API

Determining what data to backup

Perhaps the biggest part of the exercise is determining what data to backup. This will involve having someone familiar with the website review the existing reporting and setup of the GA Property and Views.

  1. Audit for Properties and Views that need to be backed up. Exclude tests, old views, etc.
  2. Verify the data retention settings. Under Admin > Tracking Info > Data Retention, you can find settings that allow you to specify how long Google Analytics retains user and event data before automatically deleting it. All other data is retained for the full history of your site.
  3. Collect and audit all reports currently being used by stakeholders to determine what is being analyzed:
    • navigate to the existing reports in the UA web interface (under Customization > Custom Reports).
    • request all reports that stakeholders are currently viewing outside of the web UI.
  4. Audit for Custom Definitions: Under Admin > Property > Custom Definitions, check for Custom Dimensions and Metrics. If they are used, determine what they are and which tables / reports should contain them.
  5. Audit for Goals: Under Admin > View > Goals, check for Goals to determine which Goals to include in the backup.
  6. Audit for Events: Under Reports > Events > Overview, check for events. Discuss the use of events with stakeholders to understand the tagging strategy and how to create valid event reports.
  7. Audit for Demographics and Interest Reports: Under Reports > Audience > Demographics > Overview – is the view setup for Audience and Interest Reports?
  8. Audit for Segments: Find them under Admin > View > Segments and ask stakeholders if they applied segments to their reports.
  9. Discuss how much history stakeholders want retained. To the last site redesign or all history?
  10. Discuss how detailed of a backup stakeholders expect. Is it enough to backup the data in existing dashboards and reports, or do they want more?

Before embarking on the backup process, get a sense for expectations from stakeholders. Is it enough to backup the data in existing dashboards and reports, or do they want more? Most importantly, find out what their budget is for this project. Most sites can get a reasonable backup for just $99, however the larger sites that want more details will require time and effort.

Challenges of Backing up Google Analytics Data

Since there is no ‘one-click’ backup or database dump for Google Analytics, you’ll have to decide what data to backup. Unlike exporting data for routine reporting, backing up data requires a bit more planning. You’ll want to consider:

  • A large historic load is much more likely to encounter sampling, which triggers when there are more than 500,000 sessions in the response.
  • Loading a detailed table over a long history is more likely to encounter Report Query Limiting, where results with high cardinality columns will aggregate your values into a row labelled “(other)”.
  • Custom dimensions, when combined, will drop any rows where any one of the custom dimensions is not set.
  • Events are limiting dimensions. Similar to custom dimensions, when combined, they will drop any row where each event dimension is not set.
  • There is a limit of 9 dimensions and 10 metrics per query for each API call. There are limited ways to expand the number of dimensions and metrics per table.
  • User metrics cannot be aggregated (you cannot pull users by day, then summarize to get users by week, month, or year). Similarly, queries with User in them cannot be partitioned, so design your queries accordingly.

These challenges can all be addressed with the right query plan and the right tools.

Options for Exporting Google Analytics Data

📢 Due to popular demand, we have built an out-of-the-box solution to backup UA data from just $99/view* (some limits apply). We’ve also added a 20-page Looker Studio Universal Analytics backup report. Get started today!

Google Analytics offers the following ways for standard accounts to download their data:

  1. Export to CSV, Sheets, Excel, or PDF files from the Reports interface of the GA web application
  2. Data transfer to BigQuery (Analytics360 customers only)
  3. Archive your UA data with Google Sheets (Free add-on from Google)
  4. Export to databases, BigQuery and files using the Google Analytics Reporting API V4

We explore each of these options below.

NEW: Backup your UA Data with the Analytics Canvas Backup Utility!

NEW Backup Utility makes it easy to extract data from UA and load it to your data destination

The new Analytics Canvas Backup utility is arguably the easiest and most cost efficient way to extract your Google Analytics Data. At just $99 per view for each 10M rows extracted, it includes a pre-built table library, an editor to easily customize your backup, and the ability to define custom queries to ensure your dashboards and reports can live on well past the UA sunset date. Check out the new UA backup utility from Analytics Canvas and create a backup that fits your needs and your budget!

Export to Sheets, CSV, or Excel files from the Reports interface of the GA web application

This method is the most manual but is for sure the most straight forward. In the GA web interface, select the view, select the report from a chart, table, or Custom Report, and click Export. You will be prompted to save to one of 4 file types – PDF, Sheets, Excel, or CSV.

export google analytics data

The trouble is, if you are trying to export a lot of data, this method is the most manual and error prone and will take an incredibly long time – hours, even days longer than using a backup utility.

Anyone can do it – no technical skills required.Very manual and slow, especially if you have a lot of queries or a lot of data.
Standard queries already dialed in – just change the date and export.Easy to encounter sampling when querying over a long time frame.
Custom query builder that helps to make valid queries.Limited dimensions and metrics per report.
Limit of 5000 rows per export!
Data files include multiple header rows and summary rows, making them more difficult to combine.

Data transfer to BigQuery (Analytics360 customers only)

At the Google IO summit in 2013, Google announced that Google Analytics would write data to BigQuery for Premium customers. Since then, trillions of rows have flowed from GA to BigQuery for paying customers. While GA4 brings this functionality “free” to the masses, the BigQuery data transfer service for Universal Analytics is only available to Analytics360 customers.

Data is automatically loaded into BigQuery by Google. No further action required for backing up this data.Only available to Analytics360 customers
Unsampled, hit level data indexed to the millisecond.Too late to turn this on and use it to backup Google Analytics data
Easy to move data from BigQuery to your own data warehouse.

Since the data is already written to BigQuery, it is permanently in your account. There is no need to take a further backup, unless you want that data in your own data warehouse. In that case, Analytics Canvas is an excellent solution to move your data from BigQuery to your database.

Archive your UA data with Google Sheets Add-on

Google offers a Google Analytics Spreadsheet add-on together with a template that they have created for archiving Universal Analytics data. This option is for those who have minimal requirements or small data sets.

FREE tool available from GoogleEach query must be modified individually.
10 pre-defined queries to help you get startedThe maximum data per Sheet is 10M cells.
The add-on does not eliminate sampling or “other” and is not designed for mid-size to large data.

Backup Google Analytics data to databases, BigQuery and files using the Reporting API V4

That leaves us with our 3rd and final option, connecting to the Google Analytics Core Reporting API v4. There are thousands of applications that do this already, and there will be no further use of your code beyond 2023. Spare yourself the time, effort, and frustration of coding this yourself and find a reliable tool.

However, while all applications using the API read data from the same source, they are not all built the same and very few have an intricate knowledge of both the API and the UA data model.

For example, while you may not have sampling on a daily, weekly, or monthly basis, on a historic basis if there are more than 500,000 sessions, your data will be sampled.

Lots of out of the box solutions that can backup your Google Analytics data.Coding to this particular API is complex, time consuming work. While the calls are easy, making valid calls for large, historic data sets and storing the response is not.
Specialist tools available that can save time and add value.Not all connectors can get your historic data accurately. Many will not account for sampling or report query limiting, can’t handle large volumes, can’t get all data (custom dimensions, segments, etc.), or can be overly costly if you have lots of data and lots of accounts.
Shortest turn-around time.

The Google Analytics Reporting API will allow you to get all available data from each of your properties and views. You have complete control over what to include in your query and the ability to include all the meta-data you need to make your Google Analytics backup usable.

Third party solutions, like Analytics Canvas, are able to use the API together with other Google tools, like Looker Studio, Sheets, and BigQuery, to connect data from the API to other BI tools as part of the backup process.

Interactive dashboard made using the Looker Studio Linking API and a backup of a website's Universal Analytics data.

Structuring queries to backup Google Analytics data

When backing up for migration purposes, it is important to go into more detail than you might for backing up routine reports. When the API is off, there is no way to add additional tables or fetch those custom dimensions that link to your CRM. Now is the time to consider what you might need at some point in the future and design your queries accordingly.

Consider the following when designing your backup plan:

  • Backup the standard report tables found in the Universal Analytics web UI
  • Survey existing reports used in your organization, ensure you capture details like segments and filters that the reports use.
  • Make sure you are backing up the right Views – get them all if you are unsure.
  • Ensure you are capturing meta-data about the rows in each table. Important considerations are the account ID, ViewID, ‘sampled’ and ‘(other)’ flags, as well as segments or filters included in the query that generated the results. Without this information, backup data has limited usefulness.
  • Partition queries to return less than 500,000 sessions to avoid sampling.
  • Carefully design queries with Custom Dimensions and Events to ensure you are not missing any data. Multiple queries are likely required.

Most tools that have a GA connector do not specialize in Google Analytics data backups or bulk extraction. As a result, they will leave you with incomplete or unusable data. This is where Analytics Canvas is different than the rest.

Backup Google Analytics data with Analytics Canvas

📢 Due to popular demand, we have built an out-of-the-box solution to backup UA data from just $99/view* (some limits apply). We’ve also added a 20-page Looker Studio Universal Analytics backup report. Get started today!

Analytics Canvas has been Google Analytics Trusted Testers since 2010, working together with the GA API team to ensure accurate data flows through to GA users. In that time, we’ve delivered data to Analytics Standard and Analytics 360 customers all around the world, dealing with the most complex cases involving billions of rows and thousands of accounts.

Now you too can use the tool the experts rely on for GA data extraction to backup your Universal Analytics data!

Advantages of using Analytics Canvas to export your Google Analytics data

  • User friendly tool that lets you start downloading data in minutes.
  • Quality tested to deliver accurate GA data
  • Capable of handling all of your data, no matter how many accounts, properties, views, or rows.
  • Delivers the full history without sampling or report query limiting, a.k.a. “(other)” where possible
  • Custom query builder with all available dimensions and metrics, including those custom to your account.
  • Create your own UA queries with more than 9 dimensions and 10 metrics*
  • Include standard and pre-defined segments for your account, or develop your own segments in the query builder
  • Include any number of filters within the query builder
  • Download data from multiple segments into the same table
  • Download data from multiple accounts, properties, and views in the same table and with the same query
  • Get the complete history of all available data for each Property and View.
  • Export Google Analytics data to CSV, JSON, Excel, MySQL, SQL Server, RedShift, Postgres, Oracle, BigQuery, Snowflake, and more data sources
  • Budget friendly

* The API limits you to 9 dimensions and 10 metrics per query. However, there are ways to get a 10th, 11th, and sometimes a 12th dimension in your table. This involves including dimensions as segment names, using meta-data, and splitting combined dimensions like Source/Medium. While you can expand the number of metrics, it is tricky. Follow this article for details.

Tools to backup Google Analytics data

NEW! Analytics Canvas Universal Analytics Backup Utility

The Analytics Canvas UA backup utility was developed due to popular demand. Our customers and GA enthusiasts told us very clearly that they wanted a backup product that did as much of the heavy lifting as possible.

Our NEW UA backup utility provides nearly 50 tables out of the box, allowing you to customize them based on time granularity, level of detail, and time period. You can enhance the tables with your own custom dims and metrics, and you can create custom queries that capture the data in your existing dashboards and reports. Check it out here!

Due to popular demand, we’ve added a 20 page Looker Studio report to the Universal Analytics Backup Utility. It includes the most popular reports, is highly interactive, can be customized and white-labelled, and delivers the data tables to Sheets or BigQuery.

Export Universal Analytics data to a dashboard in Looker Studio

Analytics Canvas Online

Analytics Canvas Online is a powerful new application that can download all available Universal Analytics data. By default, it will store UA data in BigQuery tables connected to your Analytics Canvas account. You can then move the data to your own on-premise or cloud data warehouse in minutes.

If you have lots of data from lots of accounts and views, such as an agency, app developer, or franchise based business, or if you have a successful website with lots of volume, Canvas Online is for you.

Simply create custom queries for each of the tables you’d like to backup, then connect those to your preferred data source, such as BigQuery or Google Sheets.

Canvas will make the required API calls and deliver the data to your destination. Note that a subscription to Analytics Canvas is required.

backup Google Analytics data

Analytics Canvas Desktop

Analytics Canvas Desktop and Server install and run behind your firewall, allowing you to seamlessly connect to your database. The Windows application exports data to CSV, JSON, Sheets, Excel, Data Studio, BigQuery, SQL Server, Oracle, MySQL, Postgres, Redshift, and various others like Snowflake via ODBC or by cloud file services like S3 and Google Cloud Storage.

For advanced use cases, Analytics Canvas Server lets you run data in batches and pass variables into the job at run-time.

exporting Google Analytics data to a SQL Server database
Archiving Google Analytics Data into a SQL Server Data Warehouse with Analytics Canvas Windows On-premise tools

A full featured free trial is available and, like all subscriptions, includes access to both the Online and On-premise applications. Both applications allow you to connect to an unlimited number of accounts and make custom queries so that you can archive all your Google Analytics data, no matter how much data you have!

Sign-up for a free trial or contact us to discuss your backup requirements.