Skip to content

Instantly share code, notes, and snippets.

@khunreus
Created August 3, 2022 18:51
Show Gist options
  • Save khunreus/399baa529c6d02ac09fb3abd0bee008a to your computer and use it in GitHub Desktop.
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
-- 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