SELECT
COUNT(DISTINCT user_pseudo_id) AS unique_user_count,
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
_table_suffix BETWEEN "20201201" AND "20201231"
SELECT
COUNT(DISTINCT
CASE WHEN event_name = "first_visit" THEN user_pseudo_id
END
) AS new_users
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
_table_suffix BETWEEN "20201201" AND "20201231"
SELECT
COUNT(DISTINCT
CASE WHEN event_name = "session_start" THEN CONCAT(user_pseudo_id,event_timestamp)
END
) AS sessions
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
_table_suffix BETWEEN "20201201" AND "20201231"
SELECT
COUNT(DISTINCT
CASE WHEN event_name = "page_view" THEN CONCAT(user_pseudo_id,event_timestamp)
END
) AS page_views
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
_table_suffix BETWEEN "20201201" AND "20201231"
SELECT
ROUND(COUNT(DISTINCT CASE WHEN event_name = "page_view" THEN CONCAT(user_pseudo_id,event_timestamp)
END)/ COUNT(DISTINCT CASE WHEN event_name = "session_start" THEN CONCAT(user_pseudo_id,event_timestamp)
END),2) AS page_per_session
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _table_suffix BETWEEN "20201201" AND "20201231"
--- Считаем количество уникальных сессий пользователя и делим на количество уникальных пользователей
SELECT
ROUND(COUNT(DISTINCT CASE WHEN event_name = "session_start" THEN CONCAT(user_pseudo_id,event_timestamp)
END)/ COUNT(DISTINCT user_pseudo_id),2) AS sessions_per_user
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _table_suffix BETWEEN "20201201" AND "20201231"
--- II - суммируем время взаимодействия (делим на 1000, чтобы перевести время в секунды) и делим на количество пользователей
SELECT
ROUND(SUM(engagement_time_msec)/1000/COUNT(DISTINCT CONCAT(user_pseudo_id,ga_session_id)),2) AS session_duration,
FROM (
SELECT user_pseudo_id,
(
--- I - достаем значения параметра "ga_session_id" и максимальное значение взаимодействия в миллисекундах "engagement_time_msec"
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE key = 'ga_session_id') AS ga_session_id,
MAX(( SELECT value.int_value
FROM
UNNEST(event_params)
WHERE key = 'engagement_time_msec')) AS engagement_time_msec
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _table_suffix BETWEEN "20201201" AND "20201231"
GROUP BY
user_pseudo_id,
ga_session_id)
WITH new_table AS (
SELECT
--- II - Будем считать, что если пользователь просмотрел только одну страницу, то это отказ
--- Считаем количество уникальных просмотренных страниц которые были просмотрены один раз и делим на количество уникальных пользователей
COUNT(DISTINCT CASE WHEN page_views = 1 THEN ga_session_id END)
/COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id)) AS session_bounce_rate
FROM (
SELECT user_pseudo_id,
(
--- I - Достаем значение параметра "ga_session_id" и считаем количество уникальных просмотров страниц
SELECT value.int_value
FROM UNNEST(event_params)
WHERE key = 'ga_session_id') AS ga_session_id,
COUNT(DISTINCT CASE WHEN event_name = "page_view" THEN CONCAT(user_pseudo_id,event_timestamp)
END
) AS page_views
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _table_suffix BETWEEN "20201201" AND "20201231"
GROUP BY user_pseudo_id, ga_session_id))
--- Для удобства переведем в проценты
SELECT ROUND((session_bounce_rate * 100),2) as percent_session_bounce_rate
FROM new_table
--- II - Изменяем тип поля на числовой и суммируем количество "session_engaged" и делим на количество уникальных пользователей
SELECT
ROUND(SUM(CAST(session_engaged AS int))/ COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id)),2) AS session_engagement_rate
FROM (
SELECT user_pseudo_id,
(
--- I - достаем значения параметра "ga_session_id" и максимальное значение "session_engaged" (значение может быть ноль или единица)
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE key = 'ga_session_id') AS ga_session_id,
MAX((SELECT value.string_value
FROM
UNNEST(event_params)
WHERE key = 'session_engaged')) AS session_engaged,
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
_table_suffix BETWEEN "20201201" AND "20201231"
GROUP BY
user_pseudo_id,ga_session_id)
WITH session_details AS (
SELECT
--- Показатель отказов
ROUND(COUNT(DISTINCT CASE WHEN page_views = 1 THEN ga_session_id END)/COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id))*100,2) AS session_bounce_rate,
--- Уровень вовлеченности
ROUND(SUM(CAST(session_engaged AS int))/COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id)),2) AS session_engagement_rate,
--- Длительность сессии
ROUND(SUM(engagement_time_msec)/1000/COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id)),2) AS session_duration,
FROM(
SELECT
user_pseudo_id,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
COUNT(DISTINCT CASE WHEN event_name = "page_view" THEN CONCAT(user_pseudo_id,event_timestamp) END) AS page_views,
MAX((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged')) AS session_engaged,
MAX((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')) AS engagement_time_msec
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _table_suffix BETWEEN "20201201" AND "20201231"
GROUP BY user_pseudo_id,ga_session_id)
)
SELECT
--- Уникальные пользователи
COUNT(DISTINCT user_pseudo_id) AS unique_user_count,
--- Новые пользователи
COUNT(DISTINCT CASE WHEN event_name = "first_visit" THEN user_pseudo_id END) AS new_users,
--- Количество сессий
COUNT(DISTINCT CASE WHEN event_name = "session_start" THEN CONCAT(user_pseudo_id,event_timestamp) END) AS sessions,
--- Количество просмотренных страниц
COUNT(DISTINCT CASE WHEN event_name = "page_view" THEN CONCAT(user_pseudo_id,event_timestamp) END) AS page_views,
--- Количество просмотренных страниц за сеанс
ROUND(COUNT(DISTINCT CASE WHEN event_name = "page_view" THEN CONCAT(user_pseudo_id,event_timestamp) END)/
COUNT(DISTINCT CASE WHEN event_name = "session_start" THEN CONCAT(user_pseudo_id,event_timestamp) END),2) AS page_per_session,
--- Количество сессий на пользователя
ROUND(COUNT(DISTINCT CASE WHEN event_name = "session_start" THEN CONCAT(user_pseudo_id,event_timestamp) END)/
COUNT(DISTINCT user_pseudo_id),2) AS sessions_per_user,
MAX(session_bounce_rate) AS session_bounce_rate,
MAX(session_engagement_rate) AS session_engagement_rate,
MAX(session_duration) AS session_duration,
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
CROSS JOIN (SELECT * FROM session_details)
WHERE _table_suffix BETWEEN "20201201" AND "20201231"