Recreate GA4 Metrics in BigQuery

In the past several posts, I have shown you how to recreate various GA4 standard metrics in BigQuery.

In this post, I am going to bring all of these together so that you can have all of these in one post. This is the post that you might want to bookmark.

Views:

SELECT  count(*) 
FROM  `your-project.your-dataset.events_######`
WHERE event_name = 'page_view'

In addition to the Views metrics, GA4 also has “Views per session” and “Views per user” metrics.

More details at https://bigquery.optizent.com/p/page-view-related-metrics-ga4-bigquery

Sessions

There are three ways to get the “Session” metrics in GA4 and each one tells a different story. Here is the right one

SELECT COUNT(DISTINCT CONCAT(user_pseudo_id,value.int_value))
FROM your_project_id.your_dataset_id.your_table_id, unnest(event_params)
WHERE key = 'ga_session_id'

More details at https://bigquery.optizent.com/p/calculate-ga4-sessions-in-bigquery

Engaged Sessions

Here is the SQL. Check the details at https://bigquery.optizent.com/p/ga4-engaged-sessions-bigquery

SELECT
 COUNT(DISTINCT
 CASE
     WHEN (SELECT value.string_value FROM      UNNEST(event_params) WHERE key = 'session_engaged') = '1'
  THEN CONCAT(user_pseudo_id,(select value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) 
  END) as engaged_sessions
from
  `your-project.your-dataset.events_######`

Total Users

“Total Users” is the total number of people who visited your site or app in the specified date range. As mentioned above we will use two different methods to calculate the “Total Users” metrics.

1. Using user_pseudo_id column in GA4, the SQL will be:

SELECT COUNT(DISTINCT user_pseudo_id)
FROM your_project_id.your_dataset_id.event_######

Since each user’s ID is stored in the user_pseudo_id column, we just need to a distinct count (unique count) of those IDs.

2. Using user_id column in GA4, the SQL will be

SELECT COUNT(DISTINCT user_id)
FROM your_project_id.your_dataset_id.event_######

In the above SQL, instead of using user_pseudo_id, I used the user_id column, which stores the known user IDs.

To calculate Active Users check, https://bigquery.optizent.com/p/calculate-total-users-and-active-users-ga4-bigquery

GA4 and BigQuery Help

Need help with GA4 BigQuery?  Email us at support@optizent.com or fill out the contact us form.