Universal Analytics to GA4 - BigQuery Export Guide

*This is a live article, new definitions will be added and existing definitions may get updated.

**There have been additions to the schema since this article was originally written, particularly regarding the available traffic source data. Please review our Four Different GA4 Traffic Flavors in the BigQuery Export article for more details.

High-Level Differences

GA4 export offers a considerably changed schema to work with your Google Analytics data, however, the export is still available in the familiar and ever more powerful BigQuery. Here are some of the main differences between the two exports:

*streaming incurs additional cost ($0.01 per 200MB streamed) in GCP, as a rough estimate you can consider an event as 1kB. Storage and processing may also incur additional costs.

Field Translation

The new data set has a bigger focus on user acquisition. The traffic source fields now signify the campaign, source, and medium of the user's very first session. We also have the user's first touch timestamp and lifetime value available as a field. These features used to be harder to calculate and required seeking through the user's history.

Sample tables are available in the BigQuery public dataset:

• UA: bigquery-public-data.google_analytics_sample.ga_sessions_*
• GA4: bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*

Basic Metrics and Dimensions

Let's look through some basic queries that you may have used in the universal analytics export and how they translate under the new schema.

Events, Sessions and Users

With tables being event-scoped, the number of rows now directly corresponds to the number of events, therefore calculating all the events is as easy as counting the rows, and calculating the number of page views can be done by filtering these rows on event_name = "page_view".

As in the old export, sessions can be calculated using more than one approach. Since all sessions begin with a "session_start" event, we can take advantage of the event to calculate how many sessions started during the observed date range.

The number of users is calculated as it was in the past by looking at distinct ids (per the mapper above going from clientId to user_pseudo_id).

SELECT 
  count(*) as events,
  countif(event_name = "page_view") as pageViews,
  countif(event_name = "session_start") as sessions,
  count(distinct user_pseudo_id) as users
 FROM `analytics_12345678.events_20221002`

Note that GA4 uses estimates for many metrics in the UI. Therefore, you will likely not be able to get an exact match between your BQ data and the GA4 UI.

Sessions Advanced

In the Universal Analytics export, the number of sessions metric is available via a column (totals.visits). Which is either a 1 or a null and thus allows us to easily calculate the number of sessions by summing the values in that field (when working in a session-scoped table). If our UA table has been untested, we can take advantage of a combination of dimensions such as clientId and visitNumber to create unique ids for each session and calculate the number of sessions using a count distinct.

We can use the same approach with GA4. The user identifier (clientId in UA) is now available as a user_pseudo_id and session identifier is nested within the event parameters under the key ga_session_id.

SELECT 
    COUNT(DISTINCT 
        CONCAT(user_pseudo_id, "-", 
            (SELECT value.int_value FROM unnest(event_params) WHERE key="ga_session_id")
        )
    )  
FROM  
    analytics_12345678.events_20220101

Note that the two approaches for calculating the number of sessions will likely return different results. Since one is looking at sessions starting during the time range selected and the other one at total sessions during the time range. Therefore, if a session started before the observed time range and continues into it, it will be counted using the second, but not the first method.

Engagement Rate and Bounce Rate

The definition of bounce rate has changed from UA to GA4. From a percentage of single-page sessions without interactive events to the percentage of sessions that were not engaged sessions. Because user_engagement event can happen multiple times in a session, we calculate the engagement rate by a distinct count of session ids with the engagement event divided by all sessions.

To calculate the bounce rate we look at the opposite audience:
Bounce Rate = 1 - Engagement Rate

WITH sessionsEngaged AS (
  SELECT  
    count(distinct session_id) as sessions,
    count(DISTINCT if( session_engaged>0, session_id, null)) as engaged_sessions 
  FROM 
    (SELECT 
        (SELECT value.int_value FROM unnest(event_params) WHERE key="session_engaged")  as session_engaged ,    
        CONCAT(user_pseudo_id, "-", 
              (SELECT value.int_value FROM unnest(event_params) WHERE key="ga_session_id"))  as session_id 
          FROM `analytics_12345678.events_20221002`) 
)

SELECT  round(100*(engaged_sessions/sessions), 2) engagement rate, round(100*(1-engaged_sessions/sessions), 2) bounce_rate FROM sessionsEngaged

GA4 export also has a parameter called "engagement_time_msec" which will provide you with the exact time a user was engaged.

Total engagement time in msec:

 SUM((SELECT value.int_value FROM unnest(event_params) WHERE key="engagement_time_msec"))

Advanced Metrics and Dimensions

To get a better sense of these differences let's also build some slightly more complex queries.

User Entry and Exit

Landing page reports focus on the session's first page and the interactions on and following that page. In the example below we look at the number of users, sessions, and views per landing page along with the avg. a number of total events by users who landed on those pages.

A landing page is defined with the event_parameter key entrances. The value of the parameter on a landing page is 1. Note that the entrance page (first pageview) does not necessarily correspond to the first-page location of the session. Therefore using session_start instead of event parameter entrances will yield different results.

SELECT 
  (SELECT value.string_value FROM unnest(event_params) WHERE key = "page_location") as page_location,
  count(*) as sessions,
  count(distinct user_pseudo_id) as users
FROM `analyticspros.com:spotted-cinnamon-834.analytics_206551716.events_20221002`
WHERE  (SELECT value.int_value FROM unnest(event_params) WHERE key="entrances")=1
GROUP BY 1
ORDER BY 2 DESC

User Stickiness (DAU/MAU)

User stickiness is a new concept in Google Analytics 4 and it provides the ratio between active users over a narrower time frame the engagement over a broader time frame*. An example would be to compare the ratio of users active in the past day (DAU) with those active in the past thirty days (MAU).

To calculate the number of users we look at the distinct count of the user_pseudo_id. There are many ways to approach time ranges. To keep the example query simple, we select a 30-day window that is used for Monthly Active Users and then filter down the final week and day in the date range to generate Weekly and Daily Active Users.

SELECT ROUND(100*DAU/MAU, 2) DAU_MAU, ROUND(100*WAU/MAU, 2) WAU_MAU FROM (
    SELECT 
      count(distinct IF(event_date = "20221002", user_pseudo_id, null)) DAU,
      count(distinct IF(event_date BETWEEN "20220926" AND "20221002", user_pseudo_id, null)) WAU,
      count(distinct IF(event_date BETWEEN "20220904" AND "20221002", user_pseudo_id, null)) MAU,
    FROM `analytics_12345678.events_*`
    WHERE _table_suffix BETWEEN "20220903" AND "20221002"
)

GA4 to UA High-Level Metric Comparison

When transitioning from UA to GA4 tracking, it is recommended to run both implementations in parallel to learn about the differences in metric totals that you should expect. The query below joins similar metrics from GA4 and UA export and allows you to compare the two tracking methods.

WITH GA4_stats AS (
  SELECT 
    event_date as date,
    countif(event_name = "page_view") as GA4_pageviews,
    countif(event_name = "session_start") GA4_session_starts,
    COUNT(DISTINCT 
        CONCAT(user_pseudo_id, "-", 
            (SELECT value.int_value FROM unnest(event_params) WHERE key="ga_session_id")
        )
    )   as GA4_sessions,
    count(distinct user_pseudo_id) as GA4_users

  FROM
    `analytics_12345678.events_*`
  WHERE _table_suffix BETWEEN "20220901" AND "20221005"
    GROUP BY 1
),

UA_stats as (SELECT 
    date,
    sum(totals.pageviews) as UA_pageviews,
    sum(totals.visits) as UA_sessions,
    count(distinct clientId) as UA_users
   FROM `view_id.ga_sessions_*` WHERE _table_suffix BETWEEN "20220901" AND "20221005"
   GROUP BY 1)

SELECT * FROM 
  GA4_stats
  INNER JOIN
  UA_stats
  USING(date)

Additionally, you can take the results of the join to calculate the coefficient and standard deviation that will help you translate between UA and GA4.

SELECT 
  round(avg(GA4_users/UA_users), 3) UA2GA4_users_coeff, round(stddev(GA4_users/UA_users), 3) UA2GA4_users_stddev, 
  round(avg(GA4_sessions/UA_sessions), 3) UA2GA4_sessions_coeff, round(stddev(GA4_sessions/UA_sessions), 3) UA2GA4_sessions_stddev, 
  round(avg(GA4_pageviews/UA_pageviews ), 3) UA2GA4_pageviews_coeff, round(stddev(GA4_pageviews/UA_pageviews ), 3) UA2GA4_pageviews_stddev,  
 
  FROM 
  GA4_stats
  INNER JOIN
  UA_stats
  USING(date)

GA4 BigQuqery Guide

1. GA4 BQ Guide - Users and Sessions

Resources

[GA4] Bounce rate vs engagement rate

[GA4] Engagement Reports

[GA4] Automatically collected events