Created
August 3, 2022 18:51
-
-
Save khunreus/399baa529c6d02ac09fb3abd0bee008a to your computer and use it in GitHub Desktop.
this query performs aggregations to find Power Users on GA4 sample data from Google Merchandise Score
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
-- pulling user page views from GA4 events | |
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 | |
) | |
, unnested_events_categorised AS ( | |
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 | |
) | |
, user_aggregations AS ( | |
-- power users can be based on the distribution of users across the number of days they interacted with the feature on a monthly basis, but it does not work as well in this case | |
-- another approach is to see user distribution per action count | |
SELECT | |
page_title_adjusted, | |
user_pseudo_id, | |
COUNT(*) AS views, | |
COUNT(DISTINCT date) days | |
FROM | |
unnested_events_categorised | |
WHERE page_title_adjusted IN ('PDP', 'PLP', 'Store search results', 'The Google Merchandise Store - Log In', 'Frequently Asked Questions', 'Page Unavailable') | |
GROUP BY 1,2 | |
) | |
SELECT | |
page_title_adjusted, | |
views, | |
COUNT(DISTINCT user_pseudo_id) users | |
FROM | |
user_aggregations | |
GROUP BY 1,2 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment