Skip to content

Instantly share code, notes, and snippets.

@markrittman
Created July 18, 2021 14:07
Show Gist options
  • Save markrittman/f100a26640e067b8d2b827457c3e4f40 to your computer and use it in GitHub Desktop.
Save markrittman/f100a26640e067b8d2b827457c3e4f40 to your computer and use it in GitHub Desktop.
GA4 Individual page views with device and geography information SQL query
SELECT
event_timestamp,
(
SELECT
value.string_value
FROM
UNNEST(event_params)
WHERE
event_name = 'page_view'
AND key = 'page_location') AS page_path,
(
SELECT
value.string_value
FROM
UNNEST(event_params)
WHERE
event_name = 'page_view'
AND key = 'page_title') AS page_title,
(
SELECT
value.string_value
FROM
UNNEST(event_params)
WHERE
event_name = 'page_view'
AND key = 'page_referrer') AS referrer,
user_pseudo_id,
user_id,
device.category AS device_category,
device.mobile_brand_name AS device_mobile_brand_name,
device.mobile_model_name AS device_mobile_model_name,
device.operating_system,
device.operating_system_version,
device.LANGUAGE,
device.is_limited_ad_tracking,
device.web_info.browser,
device.web_info.browser_version,
device.web_info.hostname,
geo.continent,
geo.country,
geo.region,
geo.city,
geo.sub_continent,
geo.metro,
traffic_source.name,
traffic_source.medium,
traffic_source.source,
stream_id
FROM
`ra-development.analytics_277223877.events_*` -- modify to your project
WHERE
event_name = 'page_view'
ORDER BY
event_timestamp
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment