Table of contents
How to upgrade your reports for Google Analytics 4 without a headache
Vlad Flaks, CEO @ OWOX
Olga Mirgorodskaya, Creative writer @ OWOX
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.
Sign up for a demo of OWOX BI if you need help updating reports to the Google Analytics 4 data schema. We’ll tell you the best way to do it in a manner that is relevant for your business.
Problems when updating reports to Google Analytics 4
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.
How to speed up and simplify the updating of reports for Google Analytics 4 with OWOX BI
Our approach to preparing reports (regardless of the business sphere for which they are created) is based on raw rather than modeled data.
What is a data model
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.
What is data modeling
Data modeling is the process of transforming data into a format that meets your data model’s requirements.
Why you need data modeling
- 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).
How OWOX BI works with data modeling
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.
Pros and cons of data modeling
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.
SQL templates for Google Analytics 360 and Google Analytics 4 schemasDownload now
Automatically import Non-Google ad cost data into Google Analytics 4 with 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.
Find out the real value of ad campaigns
Automatically import cost data to Google Analytics 4 from all your advertising services. Compare campaign costs, CPC, and ROAS in a single report and make fully-informed decisions.
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.
Can I export my GA4 data to other platforms?- Yes, you can export your GA4 data to BigQuery or Google Sheets using the Data Export feature. Additionally, GA4 integrations with Google Ads and Google Marketing Platform allow data sharing between various platforms."
Is it possible to use both Universal Analytics and Google Analytics 4 simultaneously?- Yes, it is possible to use both Universal Analytics and Google Analytics 4 simultaneously. This enables the user to gradually transition to GA4 without losing data from Universal Analytics.
What are the benefits of upgrading to Google Analytics 4?- Upgrading to Google Analytics 4 offers benefits such as a better understanding of user behavior, advanced machine learning capabilities for deeper insights, and improved cross-device measurement.