Skip to content

Instantly share code, notes, and snippets.

@khunreus
Created August 2, 2022 16:15
Show Gist options
  • Save khunreus/17d0f5377bd15b41b2e4956a7166710a to your computer and use it in GitHub Desktop.
Save khunreus/17d0f5377bd15b41b2e4956a7166710a to your computer and use it in GitHub Desktop.
Mapping Google Merchandise Store GA4 page titles to categories such as PLP, PDP and rest
WITH base_table AS (
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 (
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
)
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment