Create GA4 Closed Funnel in BigQuery [2024]

One of the challenging aspects in working with BigQuery is recreating the same reports you use in GA4. In this tutorial, I will show you how to create a closed funnel similar to the “Purchase Journey” report in GA4.

Prefer watching Instead?

What is a closed funnel?

When the user needs to start from a specific event to be included in a funnel, let’s say he needs to view an item, we are talking about a closed funnel. in other words, a closed funnel follows a rigid structure A –> B –> C.

Which one is better?

In my opinion, there are few cases where you will find yourself using an open funnel. They make it hard to understand the dropoff for each funnel step. If a user can add a product to cart without viewing it, then create a funnel containing only the essential steps.

This is not to say that they are totally useless, but you find yourself using closed funnels more often.

How to create GA4 closed funnel in BigQuery?

There are at least a couple of ways to do this. I’ve seen some tutorials that rely on CTEs (common table expressions) to define each step of the funnel. Besides the lengthy query, it works just as fine.

My approach is a bit different, I use the string_agg() function to define the chronology of events.

Step 1: preparing the data

First, we need to pull fields from GA4 sample dataset. The funnel we are building is going to be similar to the one you find in the monetization section. The goal is to track users journey from the moment they view a product to the purchase.

with prep_query as (
select
    user_pseudo_id,
    event_name,
    parse_date('%Y%m%d', event_date) as date,
    TIMESTAMP_MICROS(event_timestamp) as event_timestamp,
       FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`
    WHERE event_name IN (
        'view_item', 
        'add_to_cart', 
        'begin_checkout', 
        'add_shipping_info',
        'add_payment_info',
        'purchase')
),

The goal with prep_query is to pull the event names by adding the where clause. The user_pseudo_id and event_date will help us later on with defining what a user for each date.

funnel_prep as(
select date,user_pseudo_id, string_agg(event_name, ">" order by event_timestamp) as funnel_order from prep_query group by user_pseudo_id,date)

Step 2: ordering funnel steps

In funnel_prep, we used the event_timestamp to define the chronology of events. This important because we want our funnel to start from view_item then move to add_to_cart and so on, not the other way around. Here is an example of the output of the query to understand what I’m talking about:

Step 3: count values

In the last step, we merely count the values for each funnel step by using the count(). A simple regex function will detect if the funnel has the step we want in the order we specify.

select
    date,
    count(case when regexp_contains(funnel_order,r'view_item') then 1 end) as view_item,
    count(case when regexp_contains(funnel_order,r'view_item.*add_to_cart') then 1 end) as add_to_cart,
    count(case when regexp_contains(funnel_order,r'view_item.*add_to_cart.*begin_checkout') then 1 end) as begin_checkout,
    count(case when regexp_contains(funnel_order,r'view_item.*add_to_cart.*begin_checkout.*add_shipping_info') then 1 end) as add_shipping_info,
    count(case when regexp_contains(funnel_order,r'view_item.*add_to_cart.*begin_checkout.*add_shipping_info.*add_payment_info') then 1 end) as add_payment_info,
    count(case when regexp_contains(funnel_order,r'view_item.*add_to_cart.*begin_checkout.*add_shipping_info.*add_payment_info.*purchase') then 1 end) as purchase,
 from funnel_prep 
 group by date

The output is table order by funnel step