How to create a custom user segment based on a sequence of GA4 events in BigQuery

If you aggregate metrics across your entire Google Analytics data and don't use segments, you are in trouble. At least, that is what Avinash Kaushik strongly believes. 'Segment or die!', as he puts it.

A segment is a subset of your Google Analytics 4 data, that you can isolate and compare to the complete data set or other segments. The scope of a segment can be on user, session or event level. Kaushik suggests choosing your segments based on three categories:

1. Acquisition
2. Behaviour
3. Outcomes

For the first category - acquisition - a simple condition can be sufficient to segment users in BigQuery. Like this next query: a count of users that visited this website on a specific day, where the acquisition medium of their first visit was organic.

select
  count(distinct user_pseudo_id) as users
from
  `ga4bigquery.analytics_250794857.events_20221126`
where
  traffic_source.medium = 'organic'

This example becomes already more complicated if we want to segment users whose first acquisition medium was organic and viewed at least two website pages (behaviour).

with prep as (
select
  user_pseudo_id,
  countif(event_name = 'page_view') as page_views
from
  `ga4bigquery.analytics_250794857.events_20221126`
where
  traffic_source.medium = 'organic'
group by
  user_pseudo_id
having
  page_views >= 2)

select
  count(distinct user_pseudo_id)
from
  prep

Great, this already rules out non-active users. But if we really want to make impact, we have to focus on outcomes and at the same time be able to isolate users based on the sequence of events in their customer journey, across all sessions or within the same session.

• across all sessions: all events must be present in the user journey, but did not necessarily all happen in the same session
• within the same session: all events must have happened in the same session

In this tutorial I will show you how to create a user segment based on a sequence of GA4 events in BigQuery, and how to combine this with other conditions, such as time constraints between events.