-
-
Save brunomertins/6026ab9a3640791fb4a415f4a661f890 to your computer and use it in GitHub Desktop.
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
WITH | |
-- Subquery to define static and/or dynamic start and end date for the whole query | |
period AS ( | |
SELECT | |
'20200220' AS start_date, | |
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS end_date), | |
-- Subquery to prepare and calculate page view data | |
pages AS ( | |
SELECT | |
user_pseudo_id, | |
( | |
SELECT | |
value.int_value | |
FROM | |
UNNEST(event_params) | |
WHERE | |
event_name = 'page_view' | |
AND key = 'ga_session_id') AS session_id, | |
event_timestamp, | |
event_name, | |
( | |
SELECT | |
device.web_info.hostname | |
FROM | |
UNNEST(event_params) | |
WHERE | |
event_name = 'page_view' | |
AND key = 'page_location') AS hostname, | |
( | |
SELECT | |
value.string_value | |
FROM | |
UNNEST(event_params) | |
WHERE | |
event_name = 'page_view' | |
AND key = 'page_location') AS page, | |
LAG(( | |
SELECT | |
value.string_value | |
FROM | |
UNNEST(event_params) | |
WHERE | |
event_name = 'page_view' | |
AND key = 'page_location'), 1) OVER (PARTITION BY user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) | |
WHERE | |
event_name = 'page_view' | |
AND key = 'ga_session_id') | |
ORDER BY | |
event_timestamp ASC) AS previous_page, | |
CASE | |
WHEN SPLIT(SPLIT(( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location'),'/')[SAFE_ORDINAL(4)],'?')[SAFE_ORDINAL(1)] = '' THEN NULL | |
ELSE | |
CONCAT('/',SPLIT(SPLIT(( | |
SELECT | |
value.string_value | |
FROM | |
UNNEST(event_params) | |
WHERE | |
event_name = 'page_view' | |
AND key = 'page_location'),'/')[SAFE_ORDINAL(4)],'?')[SAFE_ORDINAL(1)]) | |
END | |
AS pagepath_level_1, | |
CASE | |
WHEN SPLIT(SPLIT(( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location'),'/')[SAFE_ORDINAL(5)],'?')[SAFE_ORDINAL(1)] = '' THEN NULL | |
ELSE | |
CONCAT('/',SPLIT(SPLIT(( | |
SELECT | |
value.string_value | |
FROM | |
UNNEST(event_params) | |
WHERE | |
event_name = 'page_view' | |
AND key = 'page_location'),'/')[SAFE_ORDINAL(5)],'?')[SAFE_ORDINAL(1)]) | |
END | |
AS pagepath_level_2, | |
CASE | |
WHEN SPLIT(SPLIT(( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location'),'/')[SAFE_ORDINAL(6)],'?')[SAFE_ORDINAL(1)] = '' THEN NULL | |
ELSE | |
CONCAT('/',SPLIT(SPLIT(( | |
SELECT | |
value.string_value | |
FROM | |
UNNEST(event_params) | |
WHERE | |
event_name = 'page_view' | |
AND key = 'page_location'),'/')[SAFE_ORDINAL(6)],'?')[SAFE_ORDINAL(1)]) | |
END | |
AS pagepath_level_3, | |
CASE | |
WHEN SPLIT(SPLIT(( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location'),'/')[SAFE_ORDINAL(7)],'?')[SAFE_ORDINAL(1)] = '' THEN NULL | |
ELSE | |
CONCAT('/',SPLIT(SPLIT(( | |
SELECT | |
value.string_value | |
FROM | |
UNNEST(event_params) | |
WHERE | |
event_name = 'page_view' | |
AND key = 'page_location'),'/')[SAFE_ORDINAL(7)],'?')[SAFE_ORDINAL(1)]) | |
END | |
AS pagepath_level_4, | |
( | |
SELECT | |
value.string_value | |
FROM | |
UNNEST(event_params) | |
WHERE | |
event_name = 'page_view' | |
AND key = 'page_title') AS page_title, | |
CASE | |
WHEN ( SELECT value.int_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'entrances') = 1 THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location') | |
END | |
AS landing_page, | |
CASE | |
WHEN ( SELECT value.int_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'entrances') = 1 THEN LEAD(( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location'), 1) OVER (PARTITION BY user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'ga_session_id') ORDER BY event_timestamp ASC) | |
ELSE | |
NULL | |
END | |
AS second_page, | |
CASE | |
WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location') = FIRST_VALUE(( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location')) OVER (PARTITION BY user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'ga_session_id') ORDER BY event_timestamp DESC) THEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'page_location') | |
ELSE | |
NULL | |
END | |
AS exit_page | |
FROM | |
-- Change this to your Google Analytics 4 export location in BigQuery | |
`ga4.analytics_1234567890.events_*`, | |
period | |
WHERE | |
_table_suffix BETWEEN period.start_date | |
AND period.end_date | |
AND event_name = 'page_view' | |
ORDER BY | |
1, | |
2, | |
3, | |
4) | |
SELECT | |
-- Hostname (dimension | the hostname from which the tracking request was made) | |
hostname, | |
-- Page (dimension | a page on the website specified by path and/or query parameters) | |
page, | |
-- Previous Page Path (dimension | a page visited before another page on the same property) | |
previous_page, | |
-- Page path level 1 (dimension | this dimension rolls up all the page paths in the first hierarchical level) | |
pagepath_level_1, | |
-- Page path level 2 (dimension | this dimension rolls up all the page paths in the second hierarchical level) | |
pagepath_level_2, | |
-- Page path level 3 (dimension | this dimension rolls up all the page paths in the third hierarchical level) | |
pagepath_level_3, | |
-- Page path level 4 (dimension | this dimension rolls up all the page paths in the fourth hierarchical level) | |
pagepath_level_4, | |
-- Page Title (dimension | the web page's title, multiple pages might have the same page title) | |
page_title, | |
-- Landing Page (dimension | the first page in users' sessions) | |
landing_page, | |
-- Second Page (dimension | the second page in users' sessions) | |
second_page, | |
-- Exit Page (dimension | the last page in users' sessions) | |
exit_page, | |
-- Entrances (metric | the number of entrances to the property measured as the first pageview in a session) | |
COUNT(landing_page) AS entrances, | |
-- Pageviews (metric | the total number of pageviews for the property) | |
COUNT(page) AS pageviews, | |
-- Unique Pageviews (metric | the number of sessions during which the specified page was viewed at least once, a unique pageview is counted for each page URL + page title combination) | |
COUNT(DISTINCT CONCAT(page,page_title,session_id)) AS unique_pageviews, | |
-- Pages / Session (metric | the average number of pages viewed during a session, including repeated views of a single page) | |
COUNT(page)/COUNT(DISTINCT session_id) AS pages_per_session, | |
-- Exits (metric | the number of exits from the property) | |
COUNT(exit_page) AS exits, | |
-- Exit % (metric | the percentage of exits from the property that occurred out of the total pageviews) | |
COUNT(exit_page)/COUNT(page) AS exit_rate | |
FROM | |
pages, | |
period | |
GROUP BY | |
1, | |
2, | |
3, | |
4, | |
5, | |
6, | |
7, | |
8, | |
9, | |
10, | |
11 | |
LIMIT | |
10 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment