Skip to content

Instantly share code, notes, and snippets.

@khunreus
Created August 4, 2022 17:33
Show Gist options
  • Save khunreus/8c2e10463ec6bb8b18e17dc558a6fdba to your computer and use it in GitHub Desktop.
Save khunreus/8c2e10463ec6bb8b18e17dc558a6fdba to your computer and use it in GitHub Desktop.
This query returns an ordered table of pages from Google Merchandise Store GA4 dataset. Pages are ordered from most to least viewed.
-- 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
)
SELECT
page_title,
COUNT(*) as event_count,
COUNT(DISTINCT user_pseudo_id) as users
FROM
unnested_events
GROUP BY 1
ORDER BY 2 DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment