+7 (499) 350 90 93

Несколько простых запросов для ознакомления с ситуацией на сайте (без семплирования)

Считаем количество уникальных пользователей за определенный период времени.

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"

То есть мы считаем уникальных пользователей которые имеют событие "first_visit" в данный промежуток времени

Количество сессий всего

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"