GA4 is here and Universal Analytics will stop collecting data on 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. 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 Snowflake, and other destinations using the best available tool.
📢 Due to popular demand, we are building an out-of-the-box solution to backup UA data for just $99/view* (some limits apply) See here and sign-up to be on the list!
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.
Table of contents
- Challenges of Backing up Google Analytics Data
- Options for Exporting Google Analytics Data
- Export to Sheets, CSV, or Excel files from the Reports interface of the GA web application
- Data transfer to BigQuery (Analytics360 customers only)
- Backup Google Analytics data to databases, BigQuery and files using the Reporting API V4
- Backup Google Analytics data with Analytics Canvas
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
Google Analytics offers the following ways for standard accounts to download their data:
- Export to CSV, Sheets, Excel, or PDF files from the Reports interface of the GA web application
- Data transfer to BigQuery (Analytics360 customers only)
- Export to databases, BigQuery and files using the Google Analytics Reporting API V4
We explore each of these options below.
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 easiest. 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.
The trouble is, if you are trying to export a lot of data, this method is the most manual and error prone.
| Pro | Con |
| 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.
| Pro | Con |
| 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.
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.
| Pro | Con |
| 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.
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
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
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.
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.
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.