Last active
August 27, 2024 10:59
-
-
Save quincysmiith/752994100755b19a2a90795a7dd65049 to your computer and use it in GitHub Desktop.
Calculate ecommerce page value using GA4 export to BigQuery data
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- 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