WITH pages AS (
--- Достаем количество просмотренных страниц их название, количество сессий пользователя, количество новых пользователей, вовлеченность пользователя и время
SELECT
user_pseudo_id,event_name,
MAX(CASE WHEN key = "page_title" THEN value.string_value ELSE NULL END) AS page,
MAX(CASE WHEN event_name = 'page_view' and key = 'page_title' THEN value.string_value ELSE NULL END) AS page_title,
MAX (CASE WHEN params.key = "ga_session_id" THEN params.value.int_value ELSE 0 END) AS sessionId,
CASE WHEN event_name = "first_visit" then 1 else 0 END AS new_users,
MAX((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged')) as session_engaged,
MAX(CASE WHEN key = "engagement_time_msec" then value.int_value else 0 END) AS engagement_time_msec
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST (event_params) AS params
WHERE _table_suffix BETWEEN '20210101' AND '20210131'
GROUP BY
user_pseudo_id,
event_name),
--- Считаем количество страниц, переводим время в секунды
page_top AS (
SELECT
user_pseudo_id,
event_date,
event_timestamp,
event_name,
MAX(CASE WHEN event_name = 'page_view' AND params.key = "page_title" THEN params.value.string_value END) AS page_count,
MAX(CASE WHEN params.key = "page_title" THEN params.value.string_value ELSE NULL END) AS page,
MAX(CASE WHEN params.key = "engagement_time_msec" THEN params.value.int_value/1000 ELSE 0 END) AS engagement_time
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, unnest(event_params) as params
WHERE _table_suffix BETWEEN '20210101' AND '20210131'
GROUP BY user_pseudo_id, event_date, event_timestamp, event_name
),
--- Считаем среднее время на странице, количество просмотров, кол-во уникальных польз.,кол-во просмотров страниц на польз.
page_top_summary AS (
SELECT
page,
ROUND (SAFE_DIVIDE(SUM(engagement_time),COUNT(DISTINCT user_pseudo_id)),2) AS avg_engagement_time,
COUNT (page_count) AS views,
COUNT (DISTINCT user_pseudo_id) AS users,
ROUND(COUNT (page_count)/COUNT (DISTINCT user_pseudo_id),2) AS views_per_user
FROM
page_top
GROUP BY
page)
--- Результирующих запрос
SELECT
sub.page,
views,
users,
new_user,
views_per_user,
avg_engagement_time
FROM (
SELECT
page,
SUM (new_users) as new_user,
FROM
pages
WHERE page IS NOT NULL
GROUP BY
page) sub
--- Объединяем две временные таблицы в единую на основе страниц
LEFT JOIN page_top_summary
ON page_top_summary.page = sub.page
ORDER BY
users DESC