How to Export and Backup UA Data in BigQuery and Google Sheets

Backup your Universal Analytics data before it’s gone forever →

On April 27, 2024, I hosted a live workshop with Ameet Wadhwani, Product Manager at Analytics Canvas. The workshop focused on how to backup Universal Analytics (UA) data in BigQuery and Google Sheets before the impending sunset of UA on July 1, 2024.

The event provided valuable insights and practical solutions for businesses and agencies looking to preserve their historical UA data.

Slide Deck:

Why Back Up UA Data?

With the upcoming shutdown of the UA interface and API, it is crucial for businesses to backup their historical data for various reasons:

1. Analyzing growth and traffic over time: By preserving historical UA data, businesses can maintain a comprehensive view of their website's performance and growth over an extended period. This information is invaluable for understanding long-term trends, identifying seasonality, and making data-driven decisions.
2. Complying with data retention policies: Many organizations, particularly in industries such as healthcare and education, have strict data retention policies that require them to store data for a specified number of years. Backing up UA data ensures compliance with these policies and avoids potential legal issues.
3. Ensuring continuous reporting and comparing key metrics: Transitioning from UA to GA4 can be challenging, especially when it comes to maintaining consistent reporting and comparing key metrics over time. By backing up UA data, businesses can create a seamless reporting experience and accurately compare performance before and after the switch to GA4.

Considerations for Backing Up UA Data

What Data to Back Up

When deciding what UA data to back up, consider the following:

1. Existing reports and dashboards: Identify the most critical reports and dashboards currently used by your organization and ensure that the necessary data is included in your backup. This may include data from custom reports created by team members or stakeholders.
2. Dimensions and metrics compatible with GA4 for year-over-year analysis: To enable year-over-year analysis and blending of UA and GA4 data, focus on backing up dimensions and metrics that are compatible between the two platforms. This will allow for a smoother transition and more accurate comparisons.
3. Routinely viewed data in the web interface: Consider the data that is regularly accessed and viewed through the UA web interface, as this likely represents the most important information for your organization. Make sure to include this data in your backup plan.
4. Data that might be needed for future analysis: Think about potential future analysis needs and include relevant data in your backup. This may include data related to specific campaigns, product launches, or other key events that could be valuable for retrospective analysis.
5. Specific requirements from your organization or team: Consult with your organization or team to identify any specific data requirements or retention policies that need to be addressed in your backup plan. This may include data related to compliance, legal, or financial matters.

How Far Back to Go

The amount of historical data to back up depends on several factors:

1. Availability of data (limited by data retention settings): The availability of historical data in UA is subject to the data retention settings applied to your account. If data retention limits have been set, you may not be able to access data beyond a certain point in time. Check your UA settings to determine the extent of available data.
2. The last major website redesign: Consider backing up data at least as far back as your website's last major redesign. This will provide a consistent view of performance and allow for meaningful comparisons over time.
3. Organizational requirements for data retention: As mentioned earlier, some organizations have specific data retention requirements. Ensure that your backup plan aligns with these requirements and covers the necessary time period.
4. Budget constraints: Backing up large amounts of historical data can incur costs, particularly when using platforms like BigQuery. Consider your budget constraints when determining how far back to go with your backup. Prioritize the most critical data if resources are limited.

Other Considerations

When creating a backup, audit the following:

1. Custom dimensions and metrics: Review your UA implementation to identify any custom dimensions and metrics that need to be included in your backup. These custom data points often provide valuable insights specific to your business and should not be overlooked.
2. Goals: Ensure that your backup includes data related to your UA goals. This information is crucial for understanding the performance of your website or application in terms of conversions and user engagement.
3. Events: Include data related to events tracked in UA, as these provide insights into specific user interactions and behaviors on your website or application. Events can help you understand how users engage with your content and features.
4. Demographics and interests: If you have enabled demographics and interests reporting in UA, make sure to include this data in your backup. This information can provide valuable insights into your audience's characteristics and preferences.
5. Segments: Consider backing up data for key segments that are important to your analysis and reporting. Segments allow you to focus on specific subsets of your data, such as particular user groups or traffic sources.

Options for Backing Up UA Data

There are several options available for backing up UA data, each with its own advantages and limitations:

Manual Web Downloads

• Limited to 7 dimensions per query: When manually downloading data from the UA web interface, you are limited to including a maximum of 7 dimensions per query. This can be restrictive when trying to obtain a comprehensive backup of your data.
• Maximum of 5,000 rows per download: The UA web interface allows you to download a maximum of 5,000 rows per file. This means that backing up large datasets will require multiple downloads and manual stitching of the data.
• Requires data preparation for downstream use: Manually downloaded data often requires additional preparation before it can be used in other tools or systems. This may involve cleaning up the data, reformatting it, or combining multiple files into a single dataset.
• Slowest and most error-prone process: Manually downloading data is the slowest and most error-prone method for backing up UA data. It is time-consuming and requires significant effort to ensure data accuracy and completeness.

Google Analytics 360 BigQuery Export

• Only available for GA 360 customers who enabled the feature before March 2024: The BigQuery export feature is only available to Google Analytics 360 customers who enabled the feature before March 2024. If you did not enable this feature prior to the specified date, you will not be able to use this method for backing up your UA data.
• Data is in a nested model, making it difficult to query: The data exported to BigQuery through this feature is in a nested model, which can be complex and difficult to query for those not familiar with the structure. This can make it challenging to work with the data and extract meaningful insights.
• Not designed to be used as a standalone backup: The GA 360 BigQuery export is not designed to be used as a standalone backup solution. It is primarily intended for advanced analysis and integration with other tools, rather than long-term data storage and preservation.

Using the UA Reporting API

• Publicly available API: The UA Reporting API is publicly available, meaning that anyone with the necessary technical skills can access and use it for backing up their data. This provides flexibility and control over the backup process.
• Requires technical expertise and understanding of the API's complexities: Using the UA Reporting API requires a significant level of technical expertise and understanding of the API's complexities. It involves working with API calls, authentication, and data structures, which can be challenging for those without a technical background.
• Allows for customization and automation of the backup process: One of the key advantages of using the UA Reporting API is the ability to customize and automate the backup process. You can tailor the backup to your specific needs, including selecting the desired dimensions, metrics, and date ranges. Additionally, you can automate the process to run on a regular schedule, ensuring that your data is consistently backed up.

Selecting a Backup Solution

When choosing a backup solution, consider the following factors:

1. A significant table library with predefined queries: Look for a solution that offers a comprehensive table library with predefined queries. This can save significant time and effort in setting up your backup, as you can leverage existing queries that have been optimized for common backup scenarios.
2. A company or organization that understands the complexities of the UA API: Choose a provider that has deep knowledge and experience working with the UA API. They should understand the intricacies and limitations of the API and have a proven track record of successfully backing up UA data for other clients.
3. A data source that you own and control: Ensure that the backup solution allows you to maintain ownership and control over your data. Your backed-up data should be stored in a location that you have direct access to, such as your own BigQuery project or Google Sheets account. Avoid solutions that lock your data into proprietary systems or require ongoing fees to access your own data
4. Flexibility in customizing the backup: A good backup solution should offer flexibility in customizing the backup process to meet your specific needs. This may include the ability to select specific dimensions, metrics, and date ranges, as well as the option to include custom dimensions and metrics in your backup.
5. Scalability to handle large datasets: If you have a large website or app with a significant amount of UA data, it's crucial to choose a backup solution that can handle the scale of your data. The solution should be able to efficiently process and store large datasets without performance issues or data loss.
6. Transparent and predictable pricing: Look for a backup solution with transparent and predictable pricing. Avoid solutions that have hidden costs or variable pricing based on usage. A clear, fixed pricing model will help you budget effectively and avoid surprises down the line.
7. Reliable customer support: Choose a backup solution provider that offers reliable customer support. Having access to knowledgeable support staff can be invaluable when setting up your backup, troubleshooting issues, or seeking guidance on best practices.
8. Positive user reviews and testimonials: Research user reviews and testimonials from other businesses or agencies that have used the backup solution. Positive feedback from real users can provide confidence in the solution's effectiveness and reliability.
9. Compatibility with your existing tools and workflows: Consider how well the backup solution integrates with your existing tools and workflows. Ideally, the solution should allow you to easily export your backed-up data to the platforms and tools you already use for analysis and reporting.
10. Data security and privacy measures: Ensure that the backup solution provider has robust data security and privacy measures in place. This includes secure data transmission, encryption of stored data, and compliance with relevant data protection regulations such as GDPR or CCPA.

Analytics Canvas UA Backup Utility

Analytics Canvas offers a user-friendly solution for backing up UA data, with features such as:

1. Elimination of sampling and report query limiting: Analytics Canvas has developed algorithms to minimize the impact of sampling and report query limiting, ensuring that your backed-up data is as accurate and complete as possible. This is particularly important for large datasets where sampling can significantly skew the results.
2. Management of API calls and limits: The Analytics Canvas UA Backup Utility handles the management of API calls and limits, so you don't have to worry about exceeding quotas or dealing with API errors. This streamlines the backup process and reduces the risk of data loss due to API limitations.
3. Customizable table library: Analytics Canvas provides a customizable table library that allows you to tailor your backup to your specific needs. You can select the dimensions, metrics, and date ranges to include in your backup, as well as choose the desired level of granularity (e.g., daily, weekly, or monthly data).
4. Integration with Google Sheets and BigQuery: The UA Backup Utility seamlessly integrates with both Google Sheets and BigQuery, providing flexibility in where you store your backed-up data. Google Sheets is a simple and accessible option for smaller datasets, while BigQuery offers scalability and advanced querying capabilities for larger datasets.
5. Affordable, fixed one-time pricing based on the volume of data: Analytics Canvas offers straightforward, volume-based pricing for their UA Backup Utility. The pricing is based on the number of rows of data backed up, with affordable tiers starting at $99 for up to 10 million rows. This predictable pricing model allows you to budget effectively and avoid ongoing costs.
6. A 20-page Looker Studio (Google Data Studio) dashboard template: As part of the UA Backup Utility, Analytics Canvas provides a comprehensive 20-page Looker Studio dashboard template. This template allows you to quickly visualize and analyze your backed-up data, with pre-built charts, tables, and filters. The template can be easily customized to match your branding and specific reporting needs.

Pricing and Special Offer

Analytics Canvas provides an affordable solution for backing up UA data:

• $99 for up to 10 million rows of data: The entry-level pricing tier allows you to back up up to 10 million rows of data for a one-time fee of $99. This is a cost-effective option for smaller websites or apps with a limited amount of UA data.
• $198 for up to 20 million rows of data, including the Looker Studio dashboard: For $198, you can back up up to 20 million rows of data and gain access to the 20-page Looker Studio dashboard template. This tier provides additional value by including the dashboard template, which can save significant time and effort in visualizing and analyzing your backed-up data.
• Additional $99 for each additional 10 million rows: If your dataset exceeds 20 million rows, Analytics Canvas charges an additional $99 for each additional 10 million rows. This incremental pricing allows you to scale your backup as your data grows, without incurring excessive costs.

To make the UA Backup Utility even more accessible, Analytics Canvas is offering a special discount for workshop attendees. By using an exclusive link provided during the workshop, attendees can receive a 20% discount on their backup purchase. This limited-time offer makes it even more affordable to secure your UA data before the impending sunset.

Transitioning from UA to GA4

In addition to the UA Backup Utility, Analytics Canvas offers tools to help blend UA and GA4 data for continuous reporting and year-over-year comparisons:

1. Blending UA and GA4 data: Analytics Canvas provides a solution for blending UA and GA4 data, allowing you to create a seamless reporting experience during the transition period. This enables you to compare key metrics and maintain consistent tracking of your website or app's performance.
2. Eliminating GA4 sampling: GA4 introduces new sampling thresholds, which can impact the accuracy of your data. Analytics Canvas has developed techniques to minimize the effect of GA4 sampling, ensuring that your blended data remains as accurate as possible.
3. Simplifying BigQuery exports for non-SQL users: For those who want to leverage the power of BigQuery for storing and analyzing their GA4 data but lack SQL expertise, Analytics Canvas offers a user-friendly solution. Their tools allow non-SQL users to easily select the desired dimensions and metrics, and automatically generate the necessary SQL queries to extract the data from BigQuery.

By leveraging these tools, businesses and agencies can smoothly navigate the transition from UA to GA4, while maintaining data continuity and minimizing disruptions to their reporting and analysis workflows.

The sunset of Universal Analytics presents a significant challenge for businesses and agencies that rely on this data for insights, decision-making, and reporting.

However, by understanding the considerations involved in backing up UA data and exploring the available options and solutions, organizations can proactively secure their historical data and ensure a smooth transition to GA4.

Analytics Canvas provides a comprehensive and user-friendly solution for backing up UA data through their UA Backup Utility. With features like elimination of sampling, customizable table libraries, integration with Google Sheets and BigQuery, and an affordable pricing model, Analytics Canvas makes it accessible for businesses of all sizes to preserve their valuable UA data.