Session Based Funnel Using BigQuery with GA4 Data

Funnel analysis are very important for almost every web site and mobile apps. There are multiple funnel types available. Especially, funnel analysis take up a lot of space in digital marketing environment. You can easily identify areas where users abandon the journey and problems in your purchase flow. Today, We will discuss about the Session Based Funnels using GA4 Data.

Before starting, we need to decide on the funnel steps. I started with view_item event for e-commerce stores.

First of all, we have to start with preparing table. Preparing table includes must contain the required events and parameters. In this way, you can filter the dataset according to your needs and create your own mini dataset.

WITH dataset AS (
    SELECT
        user_pseudo_id,
        event_name,
        PARSE_DATE('%Y%m%d', event_date) AS event_date,
        session_id,
        device_category
    FROM `data-set-id-123456789`
    WHERE event_name IN ('view_item', 'add_to_cart', 'begin_checkout', 'purchase')
),

After that, the next step is creating tables for each event.

view_item AS (
    SELECT
        user_pseudo_id,
        event_date,
        session_id,
        device_category
    FROM dataset
    WHERE event_name = 'view_item'
),


add_to_cart AS (
    SELECT
        user_pseudo_id,
        event_date,
        session_id
    FROM dataset
    WHERE event_name = 'add_to_cart'
),


begin_checkout AS (
    SELECT
        user_pseudo_id,
        event_date,
        session_id
    FROM dataset
    WHERE event_name = 'begin_checkout'
),


purchase AS (
    SELECT
        user_pseudo_id,
        event_date,
        session_id
    FROM dataset
    WHERE event_name = 'purchase'
),

This method should be preferred to count all events in the same session. Below you can see in detail how each event is calculated and how sessions are combined.

funnel AS (
    SELECT
        vi.event_date,
        vi.device_category,
        COUNT(DISTINCT vi.user_pseudo_id) AS view_item,
        COUNT(DISTINCT atc.user_pseudo_id) AS add_to_cart,
        COUNT(DISTINCT bc.user_pseudo_id) AS begin_checkout,
        COUNT(DISTINCT p.user_pseudo_id) AS purchase
    FROM view_item vi
    LEFT JOIN add_to_cart atc
        ON vi.session_id = atc.session_id
        AND vi.event_date = atc.event_date
    LEFT JOIN begin_checkout bc
        ON atc.user_pseudo_id = bc.user_pseudo_id
        AND atc.session_id = bc.session_id
    LEFT JOIN purchase p
        ON bc.session_id = p.session_id
        AND bc.event_date = p.event_date
    GROUP BY 1,2
)

As you can see, there are conditions (session_id and event_date) for the calculating users in the same session.

Let’s finalize this query and see the results!

SELECT
    event_date,
    device_category,
    view_item,
    add_to_cart,
    begin_checkout,
    purchase,
    1 AS view_item_rate,
    ROUND(COALESCE(add_to_cart / NULLIF(view_item,0), 0), 2) AS  add_to_cart_rate,
    ROUND(COALESCE(begin_checkout / NULLIF(view_item,0), 0), 2) AS  begin_checkout_rate,
    ROUND(COALESCE(purchase / NULLIF(view_item,0), 0), 2) AS  purchase_rate
FROM funnel
ORDER BY 1 ASC

Here is the example results:

Don’t forget to visualise this results!