Why UA Data Backup is Crucial for Your Business and Methods to Preserve Your Data Before it Expires

If you already installed GA4 for your website then you must be wondering how to backup your Universal Analytics data as Google will cease Universal Analytics data collection on July 1st, 2023. The migration of your historical universal analytics data to GA4 is not currently possible. 

Google understands and empathizes with users regarding Universal Analytics going away, and is encouraging them to export their historical data to ensure its availability after the sunset of the Reporting API.

“We know your data is important to you, and we strongly encourage you to export your historical reports during this time,” Google suggests that additional instructions on how to export might be provided in the future. Fortunately, even though migrating our data is not possible, there is still an option to preserve it.

In this post, we will explore the various options for downloading your data, highlight the challenges you may face, and provide guidance on backing up your Universal Analytics data to a database, BigQuery, or other destinations using the most effective tools available.

Note: Obtaining a complete copy of all your GA data with a single click is not possible. To back up your data, you will need to execute a series of queries and extract the relevant information. This process necessitates a basic understanding of the Universal Analytics data model to ensure the accuracy of the data you retrieve.

Table of contents

• Challenges of Backing up Google Analytics Data

• Migration of UA Data to your GA4 Account

• Options for Exporting Google Analytics Data

• Backup Google Analytics data with Analytics Safe

• 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

• Google Analytics Sheets Add-On

• Visualizing Historical Data With Data Studio

Challenges of Backing up Google Analytics Data

Backing up Google Analytics data requires careful consideration and planning due to the absence of a one-click backup or database dump option. Here are some factors to consider:

1. Sampling: Large historic data loads are prone to sampling, which occurs when the response contains over 500,000 sessions. Sampling can impact the accuracy of the backup.

2. Report Query Limiting: When loading a detailed table over a long history, report query limiting may occur. This occurs when high cardinality columns aggregate values into a row labeled "(other)".

3. Custom Dimensions: Combining custom dimensions may result in dropped rows if any of the custom dimensions are not set.

4. Events: Events act as limiting dimensions. When combined, rows will be dropped if any of the event dimensions are not set.

5. Dimension and Metric Limitations: Each API call has a limit of 9 dimensions and 10 metrics per query. Expanding the number of dimensions and metrics per table is limited.

6. User Metrics and Aggregation: User metrics cannot be aggregated, meaning you cannot pull users by day and summarize them by week, month, or year. Queries with Users in them cannot be partitioned, so query design should consider this limitation.

These challenges can be addressed with a well-planned query strategy and the use of appropriate tools to ensure a successful backup of Google Analytics data.

Migration of UA Data to your GA4 Account 

“How Can You Transfer Your Historical Data to GA4?”

The primary concern is whether GA users can transfer or migrate their data from Universal Analytics into Google Analytics 4. Unfortunately, it is not possible to migrate data from UA to GA4 and it seems like it is not a feature that is likely to be added in the near future. It may be so because both UA and GA4 use different data models. Charles Farina, head of innovation at Adswerve, said-

“It is the difference in schema and dimension definitions/calculations that make merging the data not possible.”

The schema is the organization of your data and the language used to describe that data in a way compatible with other systems. He outlines the clearly visible differences between BigQuery integrations for both UA and GA4.

“The UA export is sess-ionized, meaning each row in the export is a session, and every interaction is nested in that row. The GA4 export is very different, where each row is the event (interaction) itself,” said Farina.

Another important reason is that the dimensions, metrics, and audiences in GA4 are defined differently from those of UA. This Google support page outlines many of these. For example, let's look at a common KPI—“Users.”

Universal Analytics reports on Total Users or all users, while GA4 focuses on Active Users—which it defines as anyone who has visited the website at least once in the past 28 days. Even if migrating UA data to GA4 was possible, comparing UA data to GA4 data would be akin to comparing apples to oranges,

Options for Exporting Google Analytics Data

There are four main ways using which you can backup universal analytics data, which are-

1. Using Analytics Safe

2. Export to CSV, Sheets, Excel, or PDF files from the Reports interface of the Google Analytics web application.

3. Data transfer to BigQuery (available for Analytics360 customers only).

4. Export to databases, BigQuery, and files using the Google Analytics Reporting API V4.

5. Google Analytics Sheets Add-On

6. Using Other Third-Party Tools

   
   

Let's delve into each of these options in detail.

1. Using Analytics Safe

Using Analytics Safe to migrate your user activity (UA) data to BigQuery data lakes and gain access to Looker Studio for data visualization and insights offers several advantages. The Analytics Safe team of experts has extensive experience in data migration, storage, and analysis, and they will work closely with you to guarantee that your UA data is accurately migrated without any data integrity loss. Looker Studio can be customized to meet your specific business requirements, providing a tailored view of your data that allows you to gain valuable insights and make data-driven decisions.

Additionally, Analytics Safe provides ongoing support to ensure that your data is always accessible and that you have the necessary resources and tools to effectively analyze your data. The pricing model is flexible and scalable, which means that you only pay for the services that you need. Furthermore, Analytics Safe has a proven track record of providing reliable and secure data storage and analysis services to businesses of all sizes.

Here are some of the benefits of using Analytics Safe to migrate your UA data to BigQuery data lakes and gain access to Looker Studio for data visualization and insights:

1. Expertise: The team at Analytics Safe has extensive experience in data migration, storage, and analysis. They will work closely with you to ensure that your UA data is accurately migrated without any loss of data integrity.

2. Customization: Looker Studio can be customized to meet your specific business requirements. This means that you can create a tailored view of your data that allows you to derive valuable insights and make data-driven decisions.

3. Support: Analytics Safe provides ongoing support to ensure that your data is always accessible and that you have the necessary resources and tools to analyze your data effectively.

4. Flexible pricing: The pricing model is flexible and scalable, which means that you only pay for the services that you need.

5. Reliability: Analytics Safe has a proven track record of providing reliable and secure data storage and analysis services to businesses of all sizes.

In addition to these benefits, using Analytics Safe can also help you to:

• Improve your decision-making process by providing you with access to real-time data and insights.

• Increase your efficiency by automating tasks such as data collection and analysis.

• Save money by reducing the need to hire and train in-house data analysts.

If you are looking for a reliable and secure solution for migrating your UA data to BigQuery data lakes and gaining access to Looker Studio for data visualization and insights, then Analytics Safe is the perfect choice for you.

If you are interested in learning more about how Analytics Safe can help you to migrate your UA data to BigQuery data lakes and gain access to Looker Studio for data visualization and insights, then contact us today.

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

This method is the most straightforward, although it requires some manual effort. Here's how you can export your data from the Google Analytics web interface:

1. In the GA web interface, select the desired view.

2. Choose the report you want to export, whether it's a chart, table, or custom report.

3. Click on the "Export" button.

4. You will be presented with options to save the exported data in one of four file types: PDF, Sheets, Excel, or CSV.

5. Select the desired file type and save the file to your preferred location.

   
   

By following these steps, you can easily export your data from the GA web interface in the format of your choice. The challenge with this method is that it can become tedious and error-prone, especially when dealing with large amounts of data.

Pros and Cons of Exporting Data from Google Analytics Web Interface:

Pros:

• Anyone can do it – no technical skills required.

• Standard queries are already set up, just need to change the date and export.

• Custom query builder assists in creating valid queries.

Cons:

• Very manual and slow, especially with numerous queries or a large amount of data.

• Sampling issues may arise when querying over an extended time frame.

• Limited dimensions and metrics per report.

• There is a limit of 5000 rows per export.

• Data files include multiple header rows and summary rows, making data combination more challenging.

3. Data transfer to BigQuery (Analytics360 customers only)

During the Google IO summit in 2013, Google introduced the feature of writing Google Analytics data to BigQuery specifically for Premium customers. Since then, trillions of rows of data have been transferred from GA to BigQuery for those customers. While GA4 provides this functionality to a wider audience, the BigQuery data transfer service for Universal Analytics is exclusively available to Analytics360 customers.

Pros:

• Data is automatically loaded into BigQuery by Google, requiring no additional action for data backup.

• Provides unsampled, hit-level data indexed to the millisecond, ensuring accuracy and granularity.

• Easy to transfer data from BigQuery to your own data warehouse for further analysis.

Cons:

• This feature is exclusively available to Analytics360 customers.

• It may not be feasible to enable this feature now and use it for backing up Google Analytics data.

Since the data is already written to BigQuery, it is permanently stored in your account. Therefore, there is no need for an additional backup, unless you wish to have the data in your own data warehouse. If that is the case, Analytics Canvas is an excellent solution for transferring your data from BigQuery to your database.

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

That brings us to our fourth and final option, which is connecting to the Google Analytics Core Reporting API v4. Many applications already utilize this API, and it's important to note that your code will no longer be useful after 2023. Instead of coding it yourself, save yourself time, effort, and frustration by finding a reliable tool that can handle this process for you.

It's worth mentioning that while applications using the API retrieve data from the same source, they vary in terms of their construction. Only a few possess a deep understanding of both the API and the Universal Analytics data model.

For instance, even if you don't encounter data sampling on a daily, weekly, or monthly basis, when considering historical data, sampling can occur if the number of sessions exceeds 500,000.

Pros

• There are numerous pre-built solutions available for backing up your Google Analytics data.

• There are specialized tools that are designed to save time and provide additional value in backing up your Google Analytics data.

• Provides the quickest turn-around time.

Cons

• Developing code for this specific API can be a complex and time-consuming task. While the API calls themselves may be straightforward, ensuring the validity of calls for extensive historical data sets and storing the response can be challenging.

• Not all connectors are capable of accurately retrieving your historical data. Many of them do not consider sampling or report query limitations, are unable to handle large volumes of data, cannot retrieve all types of data such as custom dimensions and segments, and may become excessively costly when dealing with significant amounts of data or multiple accounts.

The Google Analytics Reporting API provides you with the capability to retrieve all the available data from your properties and views. It grants you full control over specifying the content of your query and allows you to include the necessary meta-data to ensure that your Google Analytics backup is comprehensive and usable.

Structuring queries to backup Google Analytics data

When performing a backup for migration purposes, it is crucial to delve into more detail than you would for regular report backups. Since the API will eventually be unavailable, it won't be possible to retrieve additional tables or fetch custom dimensions linked to your CRM. Therefore, it is essential to anticipate and consider your potential future needs and design your queries accordingly to ensure comprehensive data retrieval.

When creating your backup plan, take into account the following guidelines:

1. Back up the standard report tables available in the Universal Analytics web UI.

2. Assess the existing reports used in your organization and ensure that you capture all the necessary details such as segments and filters utilized by those reports.

3. Verify that you are backing up the correct Views. If you are unsure, it's recommended to back up all Views.

4. Ensure that you capture important meta-data about the rows in each table. This includes the account ID, ViewID, 'sampled', and '(other)' flags, as well as any segments or filters that were part of the query generating the results. Without this information, the backup data may have limited usefulness.

5. Partition your queries in a way that the results return fewer than 500,000 sessions to avoid sampling.

6. Design your queries with Custom Dimensions and Events carefully to ensure that no data is overlooked. It's likely that multiple queries will be needed.

By considering these factors and following these steps, you can design an effective backup plan for your Google Analytics data.

When it comes to tools with a GA connector, it's important to note that most of them are not specifically designed for Google Analytics data backups or bulk extraction. As a result, using these tools may leave you with incomplete or unusable data.

Google Analytics Sheets Add-On

This option offers a slightly more advanced approach by directly connecting Google Analytics to Sheets, eliminating the need for additional steps like downloading and uploading. To begin, create a folder in your Google Drive that will serve as the repository for your historical data. Next, create a new Google Sheet and give it a meaningful name for future team members, such as "UA Historical Data_Traffic Acquisition_2021."

In the top menu navigation, click on "Extensions," then select "Add-Ons," and choose "Get Add-Ons."

Search for the Google Analytics app in the Google Workspace Marketplace. Once you find it, click on install and then follow the onscreen prompts as instructed. Returning to your Google Sheet, click on the "Extensions" menu once more. This time, you should be able to locate the app for Google Analytics among the available options.

Hover your cursor over Google Analytics and click on "Create new report."

Now, you are ready to export your historical data.

• Step 1. Name your report that will be easily understandable for your team members. For instance, if you are extracting data based on financial quarters, you can name the first report as "Q1 2021" or any other relevant designation that suits your needs. 

• Step 2. Select the Analytics view from which you want to extract data. Choose the appropriate account, Property, and View to proceed with the data extraction process.

• Step 3. Configure the report by selecting the desired metrics, dimensions, and segments. Customize these settings according to your specific requirements for the data extraction.

For this example, we will keep it simple by selecting "Users," "Bounce Rate," and "Goal Conversions" as the metrics, and "source" and "medium" as the dimensions. Please note that ga:sourceMedium is not compatible with Data Studio. If you intend to visualize this data in a sheet, it is recommended to pull the traffic source dimensions separately, such as ga:Medium and ga:Source.

If you want to include all users in the report, leave the Segments field empty. By clicking the blue "Create Report" button, you will be directed to the configuration options for your report.

On the following screen, you will find additional customization options for your report that were not available on the previous screen. You can adjust the date range according to the format YYYY-MM-DD.

Furthermore, you have the option to apply filters, such as filtering by country using the syntax ga:country==United States. Double-check that all the settings are accurate, and then click on "Extensions," followed by "Google Analytics," and select "Run reports" to export your historical data.

Note: you can expedite this process by copying and pasting the configuration to the next column, updating the date range as needed, and running multiple reports simultaneously. This can help save time when exporting multiple sets of historical data.

A report status popup will notify you of any errors or inform you once the report has been successfully completed. Row Number 6 will indicate whether the data is sampled or not, providing valuable information. Additionally, Row Number 7 will display the extent to which the sheet contains sampled data, if applicable.

As discussed earlier, in Universal Analytics, data sampling occurs after reaching 500,000 sessions within the specified timeframe. So, to reduce the number of sessions in your timeframe, you can adjust your report data range

Or, utilize a third-party tool to avoid data sampling if you require the complete dataset and wish to avoid the back-and-forth.

Visualizing Historical Data With Data Studio

After you have extracted your historical data, you may want to create something that is easily comparable to GA4.

Note: Attempting to compare UA and GA4 data will be challenging due to the significant differences in their data models.

Rest assured that your hard-earned knowledge and skills will prove valuable in GA4 as well. To familiarize yourself with locating site traffic, user engagement, events, and conversion reports in GA4, read "Getting started with GA4."

Now, let's shift our focus back to visualizing historical data. To create a Data Studio report that combines a historical data table with a GA4 data table, consolidating your year-over-year (YoY) results in one place, follow these steps:

1. Open Data Studio and start a new Blank Report.

2. On the overlay screen, choose Google Sheets as the data source to connect.

3. Locate the spreadsheet you previously created when exporting your data, which should have the name "UA Historical Data_Traffic Acquisition_2021" if you followed the instructions.

4. Select the desired worksheet, such as "Q1 2021."

5. Keep the options "Use first row as headers" checked to automatically assign names to your metrics and dimensions based on the header row.

6. Specify the optional range that corresponds to your sheet. For example, if your headers start at A15 and the last entry in your sheet is at E62, your range would be "A15:E62."

7. Once Data Studio creates the table automatically, verify that the configuration matches your sheet in the right-hand menu.

8. Ensure that "Medium" is set as the primary dimension. To add a secondary dimension of "Source," toggle the corresponding option.

9. The metrics to be included in the table are "Users," "Bounce Rate," and "Goal Completions."

Your historical data table should resemble the screenshot provided below for reference.

Next, To create the same table for your GA4 data in the Q1 2022 time frame, follow these steps:

1. Right-click on the table you created earlier and select "Copy" to duplicate it.

2. Change the data source from "UA Historical Data" to your Google Analytics 4 account.

3. Since the metrics have different names, you may encounter an error stating "invalid metric." Click on each metric and update it to something similar, such as "Total Users," "Engagement Rate," and "Conversions."

4. The dimensions will also need to be updated to "session/source" and "session/medium."

5. In the same menu, scroll down and set the date range to match your historical data: January 01 - January 31, 2022.

Once you have made these adjustments, your final report will resemble the provided screenshot.

Comparing historical data with GA4 allows for easy analysis of primary metrics year over year. While the comparison of historical data with GA4 provides valuable insights, it should be noted that the analysis may be limited in terms of depth and flexibility. The data cannot be blended as the dimensions and metrics have distinct definitions and calculations in each platform. To achieve more comprehensive historical reporting, especially for advanced metrics like graphic users or goal completions over a specific timeframe, exploring options like BigQuery could be beneficial.

Final Thoughts

While it may not be possible to migrate Universal Analytics data to Google Analytics 4 (GA4) directly, it is important to take steps to ensure the longevity of your historical data. Utilizing services like Analytics Safe can help preserve your data while also offering more comprehensive reporting capabilities.

Here are some of the benefits of using Analytics Safe to migrate your Universal Analytics data:

1. Data preservation: Analytics Safe will help you to preserve your Universal Analytics data in a secure and compliant manner.

2. Comprehensive reporting: Analytics Safe offers a wide range of reporting capabilities that can help you to gain insights from your data.

3. Scalability: Analytics Safe is scalable to meet the needs of businesses of all sizes.

4. Ease of use: Analytics Safe is easy to use and can be integrated with your existing reporting tools.

If you are looking for a solution to migrate your Universal Analytics data and preserve your historical data, then Analytics Safe is the perfect choice for you.

Contact Analytics Safe today to learn more about our pricing options and services.