As we all know, Universal Analytics has discontinued operations and has been replaced by Google Analytics 4 which offers much more sophisticated data analysis capabilities.
However, it is essential to effectively back up your UA data to ensure data integrity, continuity, and informed decision-making.
To help you save your historical data from UA, we'll delve into a step-by-step guide on how to export data to CSV or Google Sheets. We'll then discuss the integration of third-party connectors like Analytics Canvas or Supermetrics that efficiently export all data from Google Analytics to BigQuery for larger datasets.
We will also walk you through the challenges you might encounter in exporting Google Analytics data in the BigQuery backup as well as what analytics properties can export data to BigQuery.
So without further ado, let’s dive right into it!
How to Backup Universal Data
1. Exporting Data to CSV
One of the methods to extract Google Analytics data is to export it to CSV format or Google Sheets since it will allow you to save the data locally and retain a copy for future reference.
To export the data, follow these steps:
- You need to download the sheet and then make a copy of it where you need to configure sharing settings to ensure data protection.
- Next, you need to change the following values in the Main Control sheet: start date, end date, view ID, and row limit to signify data characteristics.
- You can add the Google Analytics extension to your CSV or Google Sheets file to leverage its benefits. For this, you need to integrate the GA add-on through “Extensions”.
- To run reports, you can click on the Google Analytics add-on in the Extensions and select “Run Reports” to get all of your data in these sheets.
|
Note: Exporting data to Google Sheets or CSV files is suitable for smaller amounts of data or reports. If you have larger data requirements, then a warehouse such as BigQuery will be more useful. |
2. BigQuery Integration Using Connectors
To extract data from UA to be exported to BigQuery, third-party integrations such as Analytics Canvas or Supermetrics, work as connectors to efficiently export large amounts of data.
|
Tip: Analytics Canvas is a more industry-affordable option as you can view 10 million rows from unlimited tables for just $99/view. |
- Exporting Universal Data to BigQuery Using Analytics Canvas
- Launch Analytics Canvas and set up credentials for both Google Analytics and BigQuery accounts. Set up the UA backup service within Analytics Canvas and configure it to capture and store your Google Analytics data for export. Make sure that you familiarize yourself with the UA backup process to prepare data for export beforehand.
- Next, establish a connection to your BigQuery account directly within Analytics Canvas by providing the necessary authentication and permissions for Analytics Canvas to export data to BigQuery.
- Choose the specific UA data you want to export and utilize Analytics Canvas’ interface to select relevant data sets, dimensions, and metrics. Map the selected data fields from Google Analytics to the corresponding fields in your BigQuery dataset. BigQuery table's schema can ensure accurate mapping to align with the target.
|
Note: To set export parameters, you need to configure the parameters such as frequency and date range within Analytics Canvas. You can do this by defining how often the export should occur and specifying the time range for data retrieval. |
4. After this you need to run the export process directly in Analytics Canvas. This will trigger the transfer of UA data to BigQuery based on the configured settings. Make sure that you keep an eye on the export process to ensure its smooth execution and validate the data in BigQuery to confirm that it accurately represents the selected GA data.
|
Tip: In Analytics Canvas, you have the option to set up an automation process that will schedule recurring exports. We recommend you set this up as it will ensure a continuous flow of UA data into your BigQuery dataset. |
- Exporting Universal Data to BigQuery Using Supermetrics
Supermetrics is a data pipeline provider that can help export your data to BigQuery by using Google Analytics API.
|
Tip: Insights from your website performance may be missed since Google Analytics only samples a part of your data to create reports when large amounts of data are present. To overcome this problem, you can use Supermetrics to employ its partitioning capabilities that break down data into smaller components to create reports and avoid data sampling. |
The following steps will help walk you through the process to export Universal Analytics data to BigQuery:
- Ensure that your Google Cloud account is set up before starting data export.
- Create a project in BigQuery to store data by filling out “New Project” details of “Project Name”, “Organization”, and “Location”.
- Next, you need to connect to Google Analytics by navigating to “Marketplace” in the left-hand-side drop-down menu under “More Products”. In the Marketplace, you will find a “Source” called “Google Analytics by Supermetrics” where you will “Enroll” and then “Configure Transfer”. When you select this source, you will need to fill out all the fields that are shown. Click “Connect Source” after you are done filling out information to connect the Google Analytics API to BigQuery.
- A window will pop up for your Google Account confirmation where you need to select the “Schema”, “Account”, and “Segments” that you need to pull from reports. The “Standard Schema” is a good place to start to save your data. Then you need to click “Save”, choose “Schedule Backfill”, and select “Run for a Data Range”. Once you click “Ok” and refresh the page you will be able to see your transfer status.
- You can view your content data by selecting your project in the “SQL workplace”.
|
Tip: You can divide your data into smaller chunks to avoid API limitations by backfilling your data one quarter or one month at a time. |
The integration of a third-party connector can help import Universal Analytics data to GA4 and preserves your historical data.
3. Utilizing Google Analytics Support
Google Analytics support resources such as its Reporting API that helps you connect to Looker Studio and to export data to your Google Cloud. It has programmatic methods that can export UA report data.
The Google Analytics Reporting API can also help you in:
- Automating data reporting for complex tasks
- Building personalized dashboards to represent UA data
- Integrating business applications to your UA data
Understanding the UA Dimensions and Metrics
The query parameters involve the date ranges, filters, dimensions, and metrics of your UA data that need to be set to apply characteristics to your data.
To set metrics, you need to select columns to extract specific data that you would require to make decisions and achieve goals such as average session duration, bounce rate, engagement rate, etc.
Dimensions are the rows from where your data is exported that can be selected through traffic sources such as “Source” or “Medium” that contain the above-mentioned metrics.
|
Tip: You can customize the metrics and dimensions data pull in the Report Configuration in Google Sheets. |
UA Dimensions and Metrics Limitations
You will have to restrict which data you want to export since UA does not have a one-click backup, therefore, it will require more planning as compared to exporting data for reporting.
You might need to consider the following challenges and limitations for data backup:
- Limited API Call: Each API call has a limit of 10 metrics and 9 dimensions per query. There is also a limit on the number of ways dimensions and metrics per table can be expanded.
- Limited Data for Custom Dimensions: Specific data points cannot be captured or analyzed due to restrictions by a fixed set of dimensions and metrics.
- Limited Data Dimensions for Events: If any event dimension is not set when combined with metrics then any row can be dropped.
- Limited Customization: Some custom dimensions and metrics cannot be queried together thus it takes time to figure out the ones that can be combined in the same query.
- Lack of Real-Time Data: Delays in processing slow down monitoring of key metrics and timely decision-making.
- Non-customizable User Metrics: User data cannot be pulled based on days and then summarized into weeks, months, or years and user queries cannot be partitioned.
Managing API Limitations
API limitations are a vital aspect to consider when backing up data since it affects the total amount of queries you can process at a time. Let's take a look at these limitations that you might face in UA data backup.
Universal Analytics API Limitations
API Limitations in Universal Analytics
There is a default quota for read requests of 10 requests per second for each user allowed by Google Analytics API and 50 requests per second for each user for write requests. These limitations ensure API stability and reliability.
Developers may encounter challenges when they extract data ranges from UA such as issues related to the API's limitation of 10,000 rows per request.
Overcoming Limitations
The optimization of the extraction process can help overcome some API limitations in UA backup and ensure a successful backup process in UA backup. Dividing data into smaller ranges or utilizing pagination techniques can help achieve this task.
Developers can ensure that the data is retrieved efficiently within API limits by making multiple requests with fewer data and using pagination parameters. Features such as sampling controls can also be used to ensure precise data is retrieved.
Challenges in Handling Data Partitions
You may face the following challenges during data exporting that may lead to delayed reporting and decision-making:
- Device Tracking Challenges: Data might be fragmented or incomplete since tracking user behavior across devices can be challenging.
- Cross-Domain Tracking Complexity: Similarly, it is difficult to accurately track users across multiple domains or subdomains.
- Data Sampling: Large amounts of data are sampled while exporting which may cause inaccuracies during reporting.
- Incomplete User Journey Tracking: Reliance on cookies through devices, browser switches, or cookie-related issues may lead to incomplete data capture.
- Data Privacy and Compliance: Universal Analytics has stricter data privacy regulations with which compliance can be challenging for businesses.
- Data Storage Limitations: There is a storage limit and historical data that exceeds this limit can be deleted from Google Analytics history.
Final Thoughts
We conclude our post on How to back up Universal Analytics (UA) data and once again highlight how important it is to preserve your data integrity and continuity for informed decision-making.
We've explored various methods to backup UA data in this post by exporting data to CSV or Google Sheets which allows you to save and retain data locally. Moreover, the integration of third-party connectors can help in efficient data export to BigQuery.
Furthermore, Google Analytics support resources, such as the Reporting API can be used for automating data reporting, building personalized dashboards, and integrating business applications with UA data.
Remember that effective data backup ensures that your business remains equipped with accurate insights but also ensures that you only backup data that is essential since exporting large amounts of data will lead to sampling which might cause inaccuracies.
If you liked this blog, you might also be interested in how to integrate Google Analytics 4 with BigQuery if you want to learn more about how to import Universal Analytics data to GA4.
Check out more of our blogs.