How to Create Views, Engaged Sessions, and Users in BigQuery

Byadmin

In this post, I am going to show you how you can recreate standard GA4 metrics in BigQuery.

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'

Engaged Sessions

Here is the SQL. 

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.

  1. 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.


BigQuery for Marketers and Marketing Analysts

BigQuery for Marketers and Marketing Analysts. Step-by-step course that teaches you everything from scratch.

academy.optizent.com/courses/bigquery-for-marketers-and-marketing-analysts

P.S. Need help with GA4 BigQuery? Email me at anil@optizent.com

Similar Posts