Skip to content

Instantly share code, notes, and snippets.

@quincysmiith
Last active August 27, 2024 10:59
Show Gist options
  • Select an option

  • Save quincysmiith/752994100755b19a2a90795a7dd65049 to your computer and use it in GitHub Desktop.

Select an option

Save quincysmiith/752994100755b19a2a90795a7dd65049 to your computer and use it in GitHub Desktop.
Calculate ecommerce page value using GA4 export to BigQuery data
-- marquin smith
-- google analytics 4 eCommerce page value
-- set start and end dates for the query. In format YYYYMMDD
-- change these dates to get eCommerce page value for a different time period
DECLARE my_start_date STRING DEFAULT "20240301";
DECLARE end_date STRING DEFAULT "20240331";
-- get unique user ids and session ids for each page in date range
WITH page_table AS (
SELECT DISTINCT
user_pseudo_id,
(
SELECT
value.int_value
FROM UNNEST(event_params)
WHERE key = 'ga_session_id'
) AS session_id,
REGEXP_REPLACE(
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
r'\?.*',
''
) AS page_location,
FROM `<project>.<database>.events_*`
WHERE
_table_suffix BETWEEN my_start_date AND end_date
GROUP BY all
),
-- get revenue for each session
purchase_table as(
SELECT
user_pseudo_id,
(
SELECT
value.int_value
FROM UNNEST(event_params)
WHERE key = 'ga_session_id'
) AS session_id,
SUM(ecommerce.purchase_revenue) as revenue,
FROM `<project>.<database>.events_*`
WHERE
_table_suffix BETWEEN my_start_date AND end_date
AND event_name = "purchase"
GROUP BY all
)
SELECT
page_location,
COUNT(DISTINCT CONCAT(user_pseudo_id,session_id )) AS unique_page_views,
-- Ecommerce page value = Ecommerce Revenue / unique pageviews
SUM(revenue)
/
COUNT(DISTINCT
CASE
-- only count unique pageviews in sessions that generated revenue
WHEN revenue IS NOT NULL THEN concat(user_pseudo_id,session_id )
END
) AS page_value,
FROM page_table
LEFT JOIN purchase_table USING (user_pseudo_id, session_id)
GROUP BY all
ORDER BY
unique_page_views DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment