Comment on page
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;