You've successfully subscribed to GA4BigQuery
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info is updated.
Billing info update failed.

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

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

Join 1,800+ other professionals and receive a monthly newsletter packed with GA4 & BigQuery news, tips & tricks

Great! Check your inbox and click the link to confirm your subscription
Please enter a valid email address!
Already have an account? Sign in

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.