How to update your Universal Analytics reports in Google Sheets and Looker Studio with Google Analytics 4 data

Universal Analytics (UA) will sunset on July 1, 2023. That means your UA properties will stop gathering new data. If your web analytics reports in Google Sheets or Looker Studio still use UA data, it’s time to update them with Google Analytics 4 data.

This guide will show you how to update your reports effortlessly using Supermetrics. Before we begin, make sure you have a Google Analytics 4 property. Watch this ‘How to set up your GA4 property’ tutorial if you’re unsure how to do it.

Skip ahead:

• The differences between Universal Analytics and Google Analytics 4 reporting
• Transition your reporting from Universal Analytics to Google Analytics 4 using the field equivalence cheat sheet
• Update your Universal Analytics reports with GA4 data in Google Sheets
• Update your Universal Analytics reports with GA4 data in Looker Studio
• Update your UA schemas with GA4 data in a data warehouse

The main differences between Universal Analytics and Google Analytics 4 reporting

The way these platforms track user interactions is very different. UA used a paradigm based on sessions and pageviews, while GA4 tracks user interactions as events. Additionally, GA4 doesn’t use third-party cookies for tracking, while UA heavily relies on it.

GA4 lets you report on web properties and mobile apps, but UA was limited only to the web. That’s why ‘pageview’ in GA4 changed to ‘view’, as mobile apps don’t really have any pages.

Google also made many changes to the fields, such as removing the bounce rate and introducing the engagement rate, replacing the goals and transactions with conversions, and removing segments from their API. 

Read more about the main differences between Universal Analytics and Google Analytics 4 from our previous blog post.

Traffic-source dimensions

Traffic-source dimensions such as Source, Medium, Channel Group, and Campaign are based on sessions in UA. In GA4, there are three different types of traffic-source dimensions available, including:

• User-scope dimensions show you where your new users come from. These dimensions always include the prefix ‘First user’ in the GA4 UI, such as ‘First user campaign’. 
• Session-scoped dimensions show where new and returning users come from when they start new sessions. These dimensions always include the prefix ‘Session’ in the GA4 UI, such as ‘Session source’.
• Event-scoped dimensions help you attribute credit for a conversion event that a user triggered. Event-scoped dimensions don’t include a prefix. The source and medium for non-conversion events are ‘(not set)’.

Fields compatibility

Some fields that used to be compatible when reporting with UA, such as ‘Ad costs’ and ‘Pageviews’, are no longer compatible when reporting with GA4. There are also some differences when using the GA4 API compared to native GA4 UI, but hopefully, the API’ll become more flexible. Here are the main incompatibilities you may encounter when reporting with the GA4 API.

URL dimensions with query strings

URL dimensions with query strings are compatible with a limited set of metrics and dimensions. For example, if you rely on a page path such as /store/contact-us or UTM tags such as utm_source=facebook&utm_medium=social, you’ll most likely run into these limitations.

Event-scoped dimensions

Event-scoped dimensions such as ‘Event traffic Origin’ are listed under the ‘Event source’ category when reporting with Supermetrics. You can query these dimensions with a limited list of fields, and you should split the conversions by an attribution source. Non-conversion events, such as the ‘Event count’ metrics, can’t be used with Event-scoped dimensions. See the full list of incompatible fields here. 

We recommend using either User-scope dimensions, such as ‘User Campaign name’, listed under the ‘User Source’ category, or Session-scoped dimensions, such as ‘Session Campaign name’, listed under the ‘Session Source’ category when reporting with Supermetrics.

Conversions vs. goals and transactions

Since GA4 uses event-based tracking, it’ll change how you track your goals and transactions. For an event that you marked as conversion in GA4, in Supermetrics, you can find the conversion event as a custom field using the dimension’ Event name’ or the metric ‘Conversion count for…’

Engagement rate vs. bounce rate

One change that caused a stir was Google’s decision to replace the bounce rate with the engagement rate in GA4. Bounce rate shows the percentage of sessions containing only one-page views. 

Engagement rate is the percentage of sessions that last longer than 10 seconds, have a conversion event, or have at least 2 pageviews or screen views.

Bounce rate is still available in GA4, but it reflects the opposite of the engagement rate metric (Bounce rate = 100 – Engagement rate). It’s important to note that even though the outcome is the same, the values are incomparable.

API restrictions

Lastly, even though you can report multi-channel conversions and segments in the GA4 UI, this data isn’t available in the GA4 API. That’s why you won’t be able to find multi-channel conversions and segment data if you’re using a third-party connector like Supermetrics to get GA4 data into your reports.

One way to report on your segment data is to create an audience in GA4 that uses the same criteria as your segment. Once you create an audience in GA4, you can use that audience to build a report with Supermetrics using the ‘Audience name’ dimension.

Transition your reporting from Universal Analytics to Google Analytics 4 using the field equivalence cheat sheet

Now that you know the differences between UA and GA4 and the challenges these changes have created, let’s look at how you can easily transition your reports from Universal Analytics to Google Analytics 4 using Supermetrics. 

In this section, we’ll show you how to easily update your Google Sheets, Looker Studio reports and/or data warehouse schemas using Supermetrics. Before we start, here’s our gift: a field equivalence cheat sheet. It’ll help you navigate the changes in field names and type.

Update your Universal Analytics reports with GA4 data in Google Sheets

If you’ve built web analytics reports in Google Sheets using UA data, here’s how to update your reports with GA4 data. 

In case you’re not familiar with Supermetrics, we can also help you update your Google Sheets reports; get in touch with our professional services team. 

Option 1: Create a new query

You can start fresh and build a new GA4 report from scratch. Use the cheat sheet to help you find the equivalent fields between UA and GA4.

Option 2 – Try our GA4 Query Conversion tool

One option is to try our Google Sheets Query Migration tool, which duplicates your spreadsheet and converts all UA queries to GA4 using direct or closest equivalent fields. Please note that you shouldn’t expect 1:1 reports since the data structures between the connectors and platforms differ.

Step 1: In your spreadsheet, open Sidebar, open Menu from the top left corner, click Google Analytics query conversion tool

Step 2: The conversion dialog opens. 

If you have not previously authenticated your GA4 account in Supermetrics, you will be prompted to log in.

If you have already authenticated to your GA4 account, select the connection you want to use for GA4. This is the email address used to authenticate to GA4.

Step 3: Map your UA views to your GA4 Properties. You can map all views to one property or even skip views.

Step 4: When you get to the final view, you’ll see a summary of what will happen. Click ‘Apply and convert’.

Step 5: The query tool has now created a copy of your spreadsheet. You can now verify that the results are okay. Visit the ‘ConversionSummary’ tab, which gives a summary of the metrics and dimensions that were migrated, and the ones that were left out due to not having an equivalent field in GA4.

Option 3: Adapt queries in your existing reports

Another option is duplicating your existing query by selecting the action from the Supermetrics sidebar and replacing the data source with Google Analytics 4. 

Step 1 (optional): Copy an existing sheet as new to have a backup. 

Step 2: Duplicate your first UA query.

Step 3: Select’ Google Analytics 4′ as a data source.

Step 4: Go through the authentication flow (you only need to do this once).

Step 5: Select the GA4 properties you want to track.

Step 6: Select your metrics and dimensions. You can use your cheat sheet to find the equivalent GA4 fields.

Step 7: Run your query, and check if you’re happy with the results.

Step 8 (optional): If you’re running several customer accounts, we have two handy features that will save you a lot of time!

• File duplication flow: Go to ‘Extensions’> ‘Supermetrics’> ‘Duplicate file for another account’. You can select another account to link your query in the duplication flow. 
• Save as template: Save your sheet as a template, and reuse it for your next reports. 

Update your Universal Analytics reports with GA4 data in Looker Studio

If you’ve built a UA dashboard in Looker Studio, here’s a guide on updating those reports with GA4 data using Supermetrics.

If you need help with updating your reports, you can also contact our professional services team.

Option 1: Create a new Looker Studio dashboard

The first option is to start fresh and create a report from scratch. Select the Supermetrics for Google Analytics 4 data source connector from Looker Studio gallery.

Step 1: On the ‘Add data source’ page > select ‘Google Analytics 4 by Supermetrics’.

Step 2: Authenticate the connector and give Supermetrics the necessary permissions.

Step 3: Start adding your charts, and build your queries based on our list of equivalent fields.

Step 4: Check if you’re happy with the results.

Option 2: Copy your existing dashboard as new and replace the data source

Another option is to copy your existing dashboard, which uses Universal Analytics, and replace the data source with Google Analytics 4. This way, you don’t have to create a report from scratch. However, we recommend you check the fields to make sure they match.

Step 1: Create a copy of your existing Looker Studio dashboard.

Step 2: In ‘New Data Source’ select Create data source’

Step 3: Select ‘Google Analytics 4 by Supermetrics’.

Step 4: Authenticate the connector and give Supermetrics the necessary permissions.

Step 5: Then your new data source will be Google Analytics 4. Looker Studio will match the old UA fields to the suitable GA4 fields.

We recommend you review each query and ensure the new fields make sense. Remember, you can always use our cheat sheet to check if the intended fields match.

Step 6 (optional): If you’re running several customer accounts, copy your new dashboard as new using the ‘Make a copy’ function of Looker Studio and change the account.

Update your UA schemas with GA4 data in a data warehouse

If you’re using a data warehouse for saving and reporting with your Universal Analytics data, changing to Google Analytics 4 is quite simple.

We’ve created a GA4 standard schema, which you can start using, and once you get your data in your data warehouse, use our cheat sheet to find the equivalent GA4 metrics and dimensions.

One of the main issues with recreating reports in GA4 is understanding which fields are compatible. Using Supermetrics’ Standard schema, you can ensure that each table only contains compatible fields.

UA equivalent schema

If you want to continue using a similar schema to the standard Universal Analytics schema, you can use the UA equivalent schema called UA_STANDARD_EQUIVALENT.

Custom schema

If you use the custom schema, contact our professional services team. We’ll be happy to help you set up your custom schema.

Over to you

Whether you’re using Google Sheets, Looker Studio, or data warehouse, we hope you can easily update your data and reports with Supermetrics, the cheat sheet, and this guide.

Remember, while transitioning your reporting to GA4, consider it an opportunity to rethink your data structures and data collection strategy. You may find yourself in a better place with your data than where you were before the project started. 

Whether you’re starting your UA to GA4 migration or you’re in the middle of the project, check out our GA4 Knowledge Center for useful resources.