How to save historical Universal Analytics data when migrating to Google Analytics 4

From July 2023, Universal Analytics will stop collecting data. It will be replaced by Google Analytics 4. For this reason, many users are wondering: Is it possible to transfer accumulated (historical) data from Google Analytics Universal to Google Analytics 4?

Spoiler alert: You won’t be able to transfer data to the new interface. But you can save your data outside the Google Analytics interface so you can work with it later. In this article, we show you how.

Google Analytics users cannot transfer Universal Analytics data to their Google Analytics 4 property because these two systems use different data structures. Right now, it is possible to transfer only settings for users, events, goals and conversions, and audiences.

The main difference between Universal Analytics and Google Analytics 4 resources is the data model. Universal Analytics uses a session data model, while Google Analytics 4 uses an event model.

The second reason for the incompatibility of Google Analytics 4 and Google Analytics Universal data is that dimensions and metrics are defined and calculated differently.

You can find a detailed comparison of the Universal Analytics and Google Analytics 4 data models in the Help Center.

You can also see the differences in data export schemas in Google BigQuery for Universal Analytics and Google Analytics 4.

In data exported from Universal Analytics, each line is a session, and each interaction during a given session is on its line. In turn, data exported from Google Analytics 4 is fundamentally different, since each line is the event itself (an interaction).

All reports that take data from the Universal Analytics API will stop updating, as new hits will not be processed.

Report before turning off Universal Analytics:

Report after disabling Universal Analytics:

To avoid this, you can upload your Google Universal Analytics data to Google BigQuery.

In order not to lose your Universal Analytics data, you can use a special connector from OWOX BI. It will upload your historical data from Universal Analytics to Google BigQuery in this structure.

Besides, with OWOX BI Streaming, you can collect data in Google BigQuery from your website in a Google Analytics 4 compatible structure. At the same time, you can avoid the limitations of Google Analytics 4 and get each event from your website in your data warehouse in near real-time:

• Real-time analytics-ready data collection
• Real-time common behavioral data for non-consent users
• Raw data without event limits
• No sampling
• No limits for website properties

Data is cleaned, normalized, and exported to a separate table for each day.

By setting up both data import pipelines (GA Universal → GBQ and GA 4 → GBQ), you can combine Universal Analytics and Google Analytics 4 data into one data model.

If you’re migrating from Google Universal Analytics to Google Analytics 4, you’ll need to upgrade your reports to the new data schema.

Analysts will have to write and debug multi-page SQL queries over and over again to achieve an exact match between Universal Analytics and Google Analytics 4 metrics. It’s a nightmare for any analyst to learn a new data structure and rewrite SQL queries. But you can avoid that pain, free your analytics department from this work, and allow analysts to create custom reports in minutes without rewriting SQL queries.

You can do this by creating universal and easy-to-understand flat tables on top of the Google Universal Analytics and Google Analytics 4 schemas. The main advantages of this approach are that it increases the explainability of reports and simplifies ad hoc analysis.

Instead of copying and pasting normalization logic across dozens of SQL queries, you’ll do it once during the data modeling phase. It’s not a silver bullet for every project, but it’s the go-to solution for those who have regular ad hoc reporting needs.

Here is an example of what a data model for an ecommerce business might look like:

OWOX BI analysts have prepared SQL templates for data modeling in Google Analytics 360 and Google Analytics 4. Fill out the form below to receive them by email:

Besides using the OWOX connector, there are several other ways to save historical data from Universal Analytics. Let’s consider them in detail.

Google Analytics currently offers several ways to export data from your Universal Analytics property.

• Manually export individual reports to the following formats:
• CSV
• ТСВ
• TSV for Excel
• Excel (XLSX)
• Google Sheets
• PDF
• Export data using Google Analytics Dev Tools: Query Explorer (API)
• Export data using the Google Analytics Spreadsheet Add-on (API)
• Use BigQuery Export for Google Analytics 360 customers

There may be other ways to export historical data from Universal Analytics in the future: Google support says they are considering this challenge.

Google Analytics allows you to download the data you need for further work using an EXPORT button above the date range in each report.

Open the Google Analytics report you want to save. For example, Acquisition > All Traffic > Source/Medium. You can then apply additional settings: add a segment, a filter, or another parameter for the report.

Next, in the upper right corner, click the EXPORT button. Select the file format from the drop-down menu: PDF, Google Sheets, Excel (XLSX), or CSV.

This is the easiest way to save historical data, but it has a limitation — you can download a maximum of 5,000 rows. This is the number of rows that Google Analytics displays in the interface. If there is more data, you will have to use other methods of exporting your data.

Another thing to consider is that if you have thousands of visits per day, your data may be sampled.

Sampling is the practice of analyzing a subset of all data in order to uncover meaningful information in the larger data set. The main disadvantage of sampling is that you will not get accurate information, since your reports will be based on partial data.

Standard Google Analytics reports are not subject to sampling: that is, if you do not modify the reports in any way, then they will contain 100% of the data.

One way to avoid sampling is to reduce the date range. You can split the desired period into several date ranges, upload data from those ranges to Google Spreadsheets or Excel (XLSX), then combine the data you need.

The Google Analytics development tools include Query Explorer, which allows you to interact with the Core Reporting API by creating queries to retrieve data from your Google Analytics views. You can use these queries in any of the client libraries to create your own tools.

Open Query Explorer and click the orange LOGIN button.

Sign in to your Google Analytics account that has access to the property you are working with.

Select an account, property, and view to export data.

Then select a date range in YYYY-MM-DD format, metrics, dimensions, and any filters or segments you want to apply (all dimensions and metrics available through the Core Reporting API are listed and described here).

Note: Not all dimensions and metrics can be requested together. Select the dimension or metric check box to view all values that can be combined in a single query.

If a query involves metrics with different scopes, a 400: Bad Request error is returned.

There is also a limitation on the number of requested dimensions and metrics: you can specify no more than 10 dimensions and 7 metrics in one request. This is more than you can request in standard Google Analytics.

In this example, we are interested in the source/channel, city, number of sessions and users for these parameters.

Note: If you plan to visualize this information in Google Data Studio, you will need to set the ga:Medium and ga:Source parameters separately.

The upload period can be set manually, or you can leave it on the default value of the last 30 days. In the Metrics field, we set indicators, and in Dimensions, we set parameters.

Next, click Run Query and get the result in the form of a data download.

Note: The API has a limitation — you can only download 10,000 rows at a time. If after clicking Run Query 10,000 rows have been downloaded and there are 11,080 rows in total, you will need to run the query a second time to get the remaining 1,080 results.

To do this, you need to write 10001 in the “start-index” field and run the request (if the “start-index” field is empty, 1 is set by default). After that, you need to re-run the query and download the remaining results.

After the query is completed, you can download the data in TSV format. Before the downloaded table there is a button that will help you do this.

Note: You should also pay attention to the phrase “Contains sampled data” after the number of downloaded results. In this case, since there is a lot of data, sampling is used. If you see the phrase “Does not contain sampled data,” no sampling has been applied.

Does the Google Analytics API solve the sampling problem? It depends on the amount of traffic on your site. If you have little traffic or you have chosen a short reporting period, sampling can be bypassed. Otherwise, you will have to run many queries to get your data without sampling.

Exporting data to Google Sheets and exporting data using Query Explorer both rely on making requests to the Core Reporting API.

With the Google Analytics Spreadsheet Add-on, you can:

• Request data from multiple views
• Create your own calculations based on report data
• Create visualizations using built-in visualization tools and embed these visualizations on third-party websites
• Schedule reports to run and refresh automatically
• Control who can see your data and visualizations using Google Spreadsheet’s existing sharing and privacy features

To upload data from Google Analytics to Google Sheets, you first need to log in to Google Sheets at docs.google.com/spreadsheets and create a blank spreadsheet.

In the new spreadsheet, you need to change the table settings so that when automatically uploading data from Google Analytics, you can format a cell as a number, date, or amount in a specific currency.

By default, you may have other regional settings, and in connection with this, difficulties may arise in the future. For example, say that cost data from Google Analytics is uploaded with a period/dot. In this case, if we need to add two numbers, we will get an incorrect result.

To avoid this, you need to open the table settings (File → Settings)

Change the regional settings to United States, and save the settings.

Next, you need to install the add-on. To do this, go to Extensions → Add-ons → Get add-ons.

In the dialog box, find and select Google Analytics (you can use the search function):

Install the extension. Next, you need to select the account whose data you want to send to the Google Analytics app, then grant Google Analytics access to your Google account. If everything is done correctly, a message about the add-on being installed will appear and the pop-up window will close.

Now go to Extensions → Google Analytics → Create new report:

After that, a menu will open in which you need to specify the settings for the request. As an example, let’s create a download request similar to the one we used for Query Explorer in the previous block.

At this step, only basic settings are presented and not the entire request, so we set only the parameters and indicators. Next, click Create Report and go to the page with the request. After that, you can change the date range as well as apply various filters and sorting options.

Under the table with parameters, there is a link to the parameters reference as well as to other useful information on working with the extension.

After filling out all necessary request parameters, run the report in order to upload data to Google Sheets. To do this, go to Extensions → Google Analytics → Run reports.

As a result, you will receive uploaded data taking into account the specified parameters and indicators in a new sheet within your document.

Google Analytics 360 customers can export data to Google BigQuery.

When you first link a view to BigQuery, Google Analytics exports data for 13 months or 10 billion hits (whichever is less). This export of accumulated data is done only once per view. If you later unlink a view and relink it to a different BigQuery project, Google Analytics will not export the data for that view again.

When you upgrade a standard property to a version that is supported by Google Analytics 360, the system also exports data collected in the 13 months prior to the upgrade (limited to 10 billion hits).

The official Google Help Center describes the detailed process of setting up the export of Google Analytics 360 data to BigQuery, as well as other useful information on this topic.

Currently, there is only a manual way to import ad cost data into Google Analytics 4. You can automate this process and save valuable time, using the solution from OWOX BI.

Important! If you plan to import advertising costs to Google Analytics 4, then you need to add the required parameter utm_id (campaign identifier) to the links of your advertisement campaigns.

Due to the termination of Universal Analytics, you need to take care of saving your historical Universal Analytics data and reports that work on this data right now. To avoid downloading data manually and for each separate report, we recommend you use the connector from OWOX BI. It will automatically upload all your historical data from Universal Analytics to Google BigQuery. Book a demo for more details.