Last active
April 22, 2024 12:10
-
-
Save lfy79001/0e3eaf361ed11a1ae8b5f8ddb92ed6e8 to your computer and use it in GitHub Desktop.
80b981ba-be6a-48dd-b4c6-180331a668a9
This file contains 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
WITH base_table AS ( | |
-- pulls relevant columns from relevant dates to decrease the size of data scanned | |
SELECT | |
event_name, | |
event_date, | |
event_timestamp, | |
user_pseudo_id, | |
user_id, | |
device, | |
geo, | |
traffic_source, | |
event_params, | |
user_properties | |
FROM | |
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` | |
WHERE | |
_table_suffix = '20210101' | |
AND event_name IN ('page_view') | |
) | |
, unnested_events AS ( | |
-- unnests event parameters to get to relevant keys and values | |
SELECT | |
event_date AS date, | |
event_timestamp AS event_timestamp_microseconds, | |
user_pseudo_id, | |
MAX(CASE WHEN c.key = 'ga_session_id' THEN c.value.int_value END) AS visitID, | |
MAX(CASE WHEN c.key = 'ga_session_number' THEN c.value.int_value END) AS visitNumber, | |
MAX(CASE WHEN c.key = 'page_title' THEN c.value.string_value END) AS page_title, | |
MAX(CASE WHEN c.key = 'page_location' THEN c.value.string_value END) AS page_location | |
FROM | |
base_table, | |
UNNEST (event_params) c | |
GROUP BY 1,2,3 | |
) | |
,unnested_events_categorised AS ( | |
-- categorizing Page Titles into PDPs and PLPs | |
SELECT | |
*, | |
CASE WHEN ARRAY_LENGTH(SPLIT(page_location, '/')) >= 5 | |
AND | |
CONTAINS_SUBSTR(ARRAY_REVERSE(SPLIT(page_location, '/'))[SAFE_OFFSET(0)], '+') | |
AND (LOWER(SPLIT(page_location, '/')[SAFE_OFFSET(4)]) IN | |
('accessories','apparel','brands','campus+collection','drinkware', | |
'electronics','google+redesign', | |
'lifestyle','nest','new+2015+logo','notebooks+journals', | |
'office','shop+by+brand','small+goods','stationery','wearables' | |
) | |
OR | |
LOWER(SPLIT(page_location, '/')[SAFE_OFFSET(3)]) IN | |
('accessories','apparel','brands','campus+collection','drinkware', | |
'electronics','google+redesign', | |
'lifestyle','nest','new+2015+logo','notebooks+journals', | |
'office','shop+by+brand','small+goods','stationery','wearables' | |
) | |
) | |
THEN 'PDP' | |
WHEN NOT(CONTAINS_SUBSTR(ARRAY_REVERSE(SPLIT(page_location, '/'))[SAFE_OFFSET(0)], '+')) | |
AND (LOWER(SPLIT(page_location, '/')[SAFE_OFFSET(4)]) IN | |
('accessories','apparel','brands','campus+collection','drinkware', | |
'electronics','google+redesign', | |
'lifestyle','nest','new+2015+logo','notebooks+journals', | |
'office','shop+by+brand','small+goods','stationery','wearables' | |
) | |
OR | |
LOWER(SPLIT(page_location, '/')[SAFE_OFFSET(3)]) IN | |
('accessories','apparel','brands','campus+collection','drinkware', | |
'electronics','google+redesign', | |
'lifestyle','nest','new+2015+logo','notebooks+journals', | |
'office','shop+by+brand','small+goods','stationery','wearables' | |
) | |
) | |
THEN 'PLP' | |
ELSE page_title | |
END AS page_title_adjusted | |
FROM | |
unnested_events | |
) | |
SELECT * FROM unnested_events_categorised; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment