How to upgrade your reports for Google Analytics 4 without a headache

Users of Google Analytics Universal (GAU) will have to switch to Google Analytics 4 (GA4) in summer 2023. The main problem with this is that the data model and metrics calculation logic are radically different in GA4 compared to GAU. After making the switch to GA4, part of the historical data will be kept in the GAU structure, while new data will be stored in the GA4 structure. For this reason, businesses must put in serious effort to evaluate year-over-year (YoY) metrics after switching (at least 100+ hours of work for mid-level data analysts).

For analysts, this means they’ll spend day after day writing and arranging pages of SQL queries to achieve alignment between Google Analytics Universal and Google Analytics 4 metrics. However, there’s a way to get around this! Analysts can create ad hoc reports in minutes without constantly rewriting SQL queries, and in this article, we tell you how.

Universal Analytics will be going away on July 1, 2023, and is being replaced by Google Analytics 4. For customers of Google Analytics 360 (the paid version of GA), the transition period has been prolonged until July 1, 2024. But no matter which version of GA you use, it’s only a matter of time before your reports will be updated to the new data schema.

In light of this switch, one of the most challenging tasks for analysts is to provide data from Google Analytics Universal and Google Analytics 4 in a unified format.

Circumstances that complicate this task:

• Metrics calculation logic differs significantly between GAU and GA4. Notably, these product versions calculate even traditional metrics differently. You can’t drag all metrics from GAU and GA4 into one chart and expect everything to go smoothly.
• Google BigQuery data schemas are significantly different as well. Universal Analytics applies session-based data schemas, while Google Analytics 4 has event-based data schemas.
• Dashboards include YoY metrics and seasonality trends. So the question is how to keep using dashboards with all these metrics.

Let’s look at a specific example. Say we have this dashboard:

The most crucial part of this dashboard is the seasonality metrics, which represent a comparison of specific metrics for the current and past periods. Historical and operational data must be in the same format to support YoY metrics on marketing dashboards.

In this case, the analyst’s challenge is upgrading N+ legacy SQL queries to provide reliable reports for the marketing team. It’s hard to test and debug everything that can be impacted by even minor fixes to SQL queries.

Transition risks also should be considered. After transferring a report to the new source (from GAU to GA4), analysts may fail to account for fundamental differences in the data schema and metrics calculation logic. For example, sessions are formed differently: only the first user source is considered in GA4, and all other sources leading to a session are neglected. Therefore, some traffic sources won’t be visible in the report.

At first, this can go unnoticed. But in two to three quarters, an incorrect dashboard will lead to inefficient advertising spending, with 30% or higher budget leakage.

Preparing and creating dashboards reminds us of Jenga. Every time they need to replace a block in a report, analysts keep their fingers crossed and hope the whole structure won’t fall.

At OWOX, we are already successfully solving similar problems while transferring our customers’ reports from the Universal Analytics (or GA 360) data schema to the Google Analytics 4 data schema. That’s why we want to share our solution and some SQL queries with our blog readers. Hopefully, this information will help you transfer your reporting without headaches.

Our approach to preparing reports (regardless of the business sphere for which they are created) is based on raw rather than modeled data.

A data model describes data entities, their attributes, and the relationships between entities. For example, users’ actions on the website are merged for a given session, and one user can make several online conversions in a session.

There are four objects — Actions, Users, Session, and Online conversion — and three connections: one-to-many between sessions and actions, one-to-many between the user and online conversions, and one-to-many between sessions and online conversions.

The data model reflects our perception of the real world and answers questions like What is our data about? How is it related? What conditions and restrictions do we apply when working with data? A data model is necessary for people to clearly understand each other.

Data modeling is the process of transforming data into a format that meets your data model’s requirements.

• Increase the value and efficiency of analytics work by:
• Speeding up changes to report structures and metrics calculation logic
• Reducing the cost of supporting reports and dashboards
• Simplifying report discussions and approvals
• Increase data quality in reports by:
• Avoiding duplication when implementing parameters and metrics calculation logic
• Having one data layer that is the source of accurate data

The data model replaces numerous tools and describes a report’s logic, since it describes objects and their calculation logic (which is valid for all reports).

OWOX BI transforms raw data into an analytics-ready format, saving you hours of data preparation. It seamlessly integrates both Universal Analytics and Google Analytics 4 data into your reports.

Here’s an example of a data model built based on Google Analytics data:

As you can see, there are several objects, including sessions, transactions, pages, and devices.

The most interesting and important thing here is that the structure of these objects isn’t interconnected and has nothing to do with the data sources. It doesn’t matter what sources are used to fill these objects with live data (Matomo, Adobe, Google Analytics, etc.). Regardless of the data source, the model returns the same objects as the data model represents your business. It illustrates the real-world objects and metrics you work with.

Let’s see what it looks like in the real world.

Instead of writing SQL queries on top of raw data, such as sessions exported from Universal Analytics or events exported from Google Analytics 4, you can create modeled data. It's universal and easy-to-understand tables on top that represent objects and entities from the real world, such as sessions, users, and page views.

Let’s consider specific examples and data schemas.

Here’s an example of raw data. These are screenshots of a data schema from Google BigQuery for Google Analytics Universal (left) and Google Analytics 4 (right).

As we wrote above, the data schemas differ. We need to find a way to link this data to the Google Data Studio dashboard.

Based on our experience, building modeled tables is the best way to simplify data preparation. Here’s what they can look like:

These are the object lists from the data model above. You can see that the object list is the same for Google Analytics 360 and Google Analytics 4.

This means you can connect your dashboard query with this data using the same queries. The structure of this query is the same.

Let’s see the important reasons to consider building your reports on modeled data instead of raw data.

1. Data is like fruit: you must clean it before blending it. Analysts collect data from disparate services and systems. Naturally, the structure and format of that data vary across sources. To build reports, data from different sources must be correctly merged. By itself, data uploaded through connectors or various ETL services is inaccurate (containing errors, duplicates, and discrepancies) and lacks a unified logic and structure. Inaccurate and fragmented data must be cleaned up and normalized into an analytics-ready format.

With OWOX BI, you don’t need to manually clean, structure, and process data. The service will automatically normalize raw data into an analytics-ready format.

2. You don’t need to copy and rewrite the business logic for each report; for example, you don’t need to:

• Deduplicate transactions
• Filter bot and internal traffic
• Define channel grouping rules
• Define criteria for new and returning users
• Fix UTM parameter tracking

No doubt every data analyst has heard requests from marketers such as these: We’d like to adjust or improve our channel grouping rules based on historical data, or We’d like to identify criteria for new and returning users not in the way it works in Google Analytics, but in our own way or We’d like to consider as returning only those users who have made a purchase within the last six months.

If you build reports based on raw data, all data transformations and preparation activities must be executed at the report level. That means you must spend a lot of time copying business logic into every report.

If you build reports on modeled data, you don’t have to copy business logic. You create it once at the modeling stage.

3. Building reports based on modeled data speeds up ad hoc analysis. You can save time writing new SQL and orchestration transformations to enable annual analysis.

4. Thanks to data modeling, you can create a single source of truth for all reports. When you change the data source in a report, you no longer have to settle every problematic query. You can rely on this source for sessions, transactions, and event data.

5. Simplify the reporting layer. Building reports on top of modeled data is easier than dealing with nested fields and record fields, along with writing some window complicated functions or even JSON extract functions.

1. Modeled data is one more middle layer. It takes time to adjust, and you must also monitor and debug modeled data.

2. Complex JOINs for normalizations.

3. Extra data processing. In order to convert data in a model format, you have to query data. We advise you to use a partition table on a date. With a partition table built around date dimensions, you're able to update only the necessary data. You don’t have to rewrite all the huge tables and pay for all the processed data and gigabytes or terabytes of data in Google BigQuery. You can just upgrade and update the partition you need. This approach is more economical than handling all data tables.

In order to help you handle these cons, we have prepared SQL templates for modeling Google Analytics 360 and Google Analytics 4 data. You can check them out by downloading the additional materials provided in this article.

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.

How easy is it to update your reports to the new Google Analytics 4 data schema? Use modeled data. Instead of building reports on raw data, you can create universal and easy-to-understand flat tables on top of the GA Universal and GA 4 schemas.

Doing this will eliminate the need to copy data and insert normalization logic into dozens of SQL queries. You’ll do it once at the data modeling stage. Although not a silver bullet for every project, it’s the optimal method for those requiring special reports regularly.

Sign up for a demo of OWOX BI if you want to set up and orchestrate modeled tables. We’ll gladly share details on how to prepare similar tables in your projects based on your data.