WITH ga4_search_data AS(
SELECT
event_date,
-- The ga_session_id is only unique per user, so we need to concatenate the two
-- to generate a globally unique session ID
CONCAT(user_pseudo_id, "#", (SELECT value.int_value from UNNEST(event_params) where key = 'ga_session_id')) AS unique_session_id,
event_timestamp,
event_name,
(SELECT value.string_value from UNNEST(event_params) where key = 'search_term') AS search_term,
-- Window function to count all page views in a session after this event
COALESCE(
SUM(IF(event_name = 'page_view', 1, 0)) OVER (
PARTITION BY CONCAT(user_pseudo_id, "#", (SELECT value.int_value from UNNEST(event_params) where key = 'ga_session_id'))
ORDER BY event_timestamp, event_name
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
), 0
) AS subsequent_pageviews
-- Replace <project> and <dataset> with your project id and dataset id
FROM `project.dataset.events_*`
WHERE
-- Parameters DS_START_DATE and DS_END_DATE are replaced by Looker Studio
(_TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE) AND
(event_name = 'page_view' OR event_name='view_search_results')
ORDER BY 2, 3
)
SELECT
event_date,
event_timestamp,
unique_session_id,
search_term,
-- Add the previous search_term as the refined search
LAG(search_term) OVER (
PARTITION BY unique_session_id, event_name ORDER BY event_timestamp, event_name
) AS search_term_refined,
-- Subtract the followup pageviews of the next search event to count every
-- subsequent pageview only once
COALESCE(
subsequent_pageviews - LEAD(subsequent_pageviews) OVER (
PARTITION BY unique_session_id, event_name ORDER BY event_timestamp, event_name
),
subsequent_pageviews
) AS pageviews_after_search
FROM
ga4_search_data
WHERE
-- Only export search events to our dashboard
event_name = "view_search_results"
ORDER BY
1, 3, 2