Simple 'Page View to Purchase' Funnel Analysis

The following query is helpful in determining how many purchases in a session included a visit to a specific page on your website.

To get started, update the FROM clause to use your BigQuery Project and Dataset. Then, replace 'https://example.com/example-page' with the page you want to test within this funnel.

-- CTE to identify all unique sessions
WITH all_sessions AS (
    SELECT DISTINCT
        event_param.value.int_value AS ga_session_id
    FROM 
        `PROJECT.DATASET.events_intraday_*`,
        UNNEST(event_params) AS event_param
    WHERE 
        event_param.key = 'ga_session_id'
),

-- CTE to identify sessions with the specified sales page view
sessions_with_sales_page AS (
    SELECT DISTINCT
        session_param.value.int_value AS ga_session_id
    FROM 
        `PROJECT.DATASET.events_intraday_*`,
        UNNEST(event_params) AS event_param,
        UNNEST(event_params) AS session_param
    WHERE 
        event_name = 'page_view'
        AND event_param.key = 'page_location'
        AND event_param.value.string_value = 'https://example.com/example-page'
        AND session_param.key = 'ga_session_id'
),

-- CTE to identify sessions with a purchase
sessions_with_purchase AS (
    SELECT DISTINCT
        session_param.value.int_value AS ga_session_id
    FROM 
        `PROJECT.DATASET.events_intraday_*`,
        UNNEST(event_params) AS event_param,
        UNNEST(event_params) AS session_param
    WHERE 
        event_name = 'purchase'
        AND session_param.key = 'ga_session_id'
)

-- Main query to compute the counts
SELECT
    (SELECT COUNT(ga_session_id) FROM all_sessions) AS total_unique_sessions,
    (SELECT COUNT(ga_session_id) FROM sessions_with_sales_page) AS sessions_with_sales_page,
    (SELECT COUNT(ga_session_id) FROM sessions_with_sales_page 
     WHERE ga_session_id IN (SELECT ga_session_id FROM sessions_with_purchase)) AS sessions_with_sales_page_and_purchase;