Дней с последнего сеанса Google Analytics 4 на данных BigQuery
В предыдущем материале мы с вами разобрали настройку, которая позволяет воспроизвести логику определения дней с последнего сеанса в Google Analytics 4 с помощью Google Tag Manager. В этом руководстве выполним SQL-запрос со схожим функционалом в BigQuery.
Если вы ознакомились с публикацией и решением от Anatolii Shulhat, то знаете, что существенным недостатком такого отслеживания является то, что все данные в разрезе дней с последнего сеанса в GA4 вы увидите только после того, как выполните саму настройку через диспетчер тегов Google с использованием шаблона тега Time Since Last Session for GA4. А до этого момента никаких данных в ваших отчетах и Исследованиях присутствовать не будет. Нет настройки - нет и дней с последнего сеанса. Поэтому такую настройку целесообразно производить сразу же после создания ресурса Google Analytics 4.
Но есть и другой способ - Google BigQuery. Да, большинство владельцев счетчиков не работают в облачном хранилище данных Google, а весь анализ стараются проводить в интерфейсе Google Analytics 4. Но тот, кто использует в своей работе сырые данные и знает, как выполнять SQL-запросы, приведенный ниже код покажется интересным.
Конечно, и этот вариант будет работать только при условии, что вы связали аккаунты Google Analytics 4 и BigQuery, и данные в вашем проекте уже собираются. Если это так, то вы можете скопировать SQL ниже:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
WITH session_data AS ( SELECT user_pseudo_id, -- Текущий сеанс CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS current_session_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS current_session_number, event_timestamp AS current_session_timestamp, -- Использование LAG для получения данных о предыдущем сеансе LAG(CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS previous_session_id, LAG((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number')) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS previous_session_number, LAG(event_timestamp) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS previous_session_timestamp FROM `osipenkovru-373609.analytics_206854065.events_*` WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240930' AND event_name = 'session_start' ) SELECT user_pseudo_id, current_session_id, -- Если предыдущего сеанса нет, отобразить «Нет предыдущего сеанса» COALESCE(previous_session_id, 'Нет предыдущего сеанса') AS previous_session_id, current_session_number, COALESCE(CAST(previous_session_number AS STRING), 'Нет предыдущего сеанса') AS previous_session_number, -- Если предыдущего сеанса нет, вернуть «Нет предыдущего сеанса» для расчета времени COALESCE(CAST(ROUND((current_session_timestamp - previous_session_timestamp) / 60000, 0) AS STRING), 'Нет предыдущего сеанса') AS mins_since_last_session, COALESCE(CAST(ROUND((current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24), 0) AS STRING), 'Нет предыдущего сеанса') AS days_since_last_session, COALESCE(CAST(ROUND((current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24 * 7), 0) AS STRING), 'Нет предыдущего сеанса') AS weeks_since_last_session, COALESCE(CAST(ROUND((current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24 * 30), 0) AS STRING), 'Нет предыдущего сеанса') AS months_since_last_session, -- Интервалы сессий пользователей (Adobe Analytics) CASE WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) < 1 THEN 'Менее 1 дня' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) BETWEEN 1 AND 3 THEN 'от 1 до 3 дней' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) BETWEEN 3 AND 7 THEN 'от 3 до 7 дней' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) BETWEEN 7 AND 14 THEN 'от 7 до 14 дней' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) BETWEEN 14 AND 30 THEN 'от 14 до 30 дней' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) > 30 THEN 'Более 30 дней' ELSE 'Нет предыдущего сеанса' END AS adobe_analytics, -- Интервалы сессий пользователей (Universal Analytics) CASE WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) = 0 THEN '0' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) = 1 THEN '1' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) = 2 THEN '2' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) = 3 THEN '3' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) = 4 THEN '4' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) = 5 THEN '5' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) = 6 THEN '6' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) = 7 THEN '7' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) BETWEEN 8 AND 14 THEN '8–14' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) BETWEEN 15 AND 30 THEN '15–30' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) BETWEEN 31 AND 60 THEN '31–60' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) BETWEEN 61 AND 120 THEN '61–120' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) BETWEEN 121 AND 364 THEN '121–364' WHEN (current_session_timestamp - previous_session_timestamp) / (60000 * 60 * 24) >= 365 THEN '365+' ELSE 'Нет предыдущего сеанса' END AS universal_analytics FROM session_data ORDER BY user_pseudo_id, current_session_timestamp; |
, где вместо osipenkovru-373609.analytics_206854065.events_* вы должны указать свой путь к таблицам с данными Google Analytics 4, а в _TABLE_SUFFIX задать собственный диапазон дат, за который вы анализируете статистику.
И запустить его в BigQuery, получив схожий результат:
Результатом выполнения этого SQL-запроса будет таблица, показывающая, как часто пользователи возвращаются на сайт и какое время прошло между их последними сеансами. Она состоит из следующего количества столбцов:
- user_pseudo_id- уникальный идентификатор пользователя в Google Analytics 4 (он же Client ID);
- current_session_id - текущий идентификатор сеанса;
- previous_session_id - предыдущий идентификатор сеанса;
- current_session_number - текущий номер сеанса;
- previous_session_number - предыдущий номер сеанса;
- mins_since_last_session - количество минут с последнего сеанса;
- days_since_last_session - количество дней с последнего сеанса;
- weeks_since_last_session - количество недель с последнего сеанса;
- months_since_last_session - количество месяцев с последнего сеанса;
- adobe_analytics - группы периодичности/давности сеансов из этой аналитической системы Adobe Analytics;
- universal_analytics - интервалы дней с последней сессии согласно функционалу Universal Analytics.
По сути, эта таблица отображает ровно те же данные в BigQuery, что и решение от Anatolii Shulhat в материале про Google Analytics 4 и Google Tag Manager, предусматривающее вывод информации на уровень данных (dataLayer) с возможностью ее передачи в ваш ресурс.
Далее вы можете исследовать полученный результат с помощью загрузки данных в Looker Studio.
Результат в Looker Studio:
Примечание: с 9 сентября 2024 года сервис Looker Studio (Google Data Studio) заблокирован на территории Российской Федерации. Для его открытия используйте инструменты обхода, например, VPN.