Created
July 18, 2021 14:07
-
-
Save markrittman/f100a26640e067b8d2b827457c3e4f40 to your computer and use it in GitHub Desktop.
GA4 Individual page views with device and geography information SQL query
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
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