Когортный анализ в BigQuery на данных Google Analytics 4
Наверняка вы знаете, что в GA4 есть отдельное Исследование, посвященное когортам. В этом руководстве я покажу вам процесс создания таблицы когорт удержания пользователей в Google BigQuery с использованием ваших данных Google Analytics 4.
Читайте мои другие материалы по когортам:
Примечание: материал основан на статье Йохана ван де Веркена (Johan van de Werken), автора ga4bigquery.com и онлайн-курса Query GA4 Data in Google BigQuery. Дополнен моими комментариями и примерами.
Начало
Важной составляющей Google Analytics 4 является удержание пользователей. Например, в стандартном отчете Обзор удержания (Retention overview) содержится сводная информация о том, насколько хорошо ваш сайт или мобильное приложение удерживают пользователей, включая:
- динамику возвращения определенных групп пользователей (группы по дням 1,7 и 30 характеризуют время, проведенное на сайте или в приложении на 1-й, 7-й и 30-й день с даты первого посещения);
- среднее количество времени, которое вовлеченные пользователи проводят на вашем сайте или в приложении, после того как они были привлечены впервые (для каждой когорты показывается до трех кривых - День 1, День 7 и День 30 соответственно);
- процент пользователей, возвращавшихся ежедневно в течение 42 дней после первого посещения. Для первого дня это значение равно 100 %. Затем оно уменьшается по мере сокращения числа возвращающихся пользователей;
- показатель среднего времени взаимодействия для удержанных пользователей, которые возвращаются на ваш ресурс в течение 42 дней после первого посещения (при расчете учитываются только вернувшиеся пользователи);
- показатель общей ценности (lifetime value) - среднего дохода от событий покупки и рекламы в приложении, полученного благодаря новым пользователям в течение первых 120 дней.
Стандартный отчет удержания пользователей
Эти срезы будут вам полезны для определения наиболее эффективных рекламных источников и каналов привлечения, оценки поведения пользователей, которые недавно ушли, чтобы понять, почему они ушли, а также для понимания того, как вовлеченность и удержание пользователей меняются с течением времени для разных сегментов.
Не забывайте, что для одного типа веб-сайтов и мобильных приложений степень удержания может быть высокой, а для другого типа - низкой. Например, новостные сайты, информационные проекты и социальные сети могут иметь короткую продолжительность посещения, но высокий уровень возврата, поскольку люди пользуются ими каждый день по несколько раз. А сайты электронной торговли, посадочные страницы конкретных товаров и услуг, напротив, могут иметь продолжительные посещения, но низкий уровень возврата, поскольку люди тщательно подходят к вопросу выбора товара/услуги, делают это осознанно, в момент осознания проблемы и поиска решения, и не так часто. Все зависит от конкретного проекта.
Так или иначе, удержание пользователей - важнейший показатель в интернет-маркетинге. Он может дать вам ценную информацию по тому, как пользователи взаимодействуют с вашим приложением или веб-сайтом, и поможет вам принимать взвешенные/рациональные решения для улучшения удержания и возврата пользователей.
Примечание: удержание пользователей не является новым функционалом Google Analytics 4. Такие отчеты были и в предыдущей версии Google Analytics. Подробнее об отчете Общая ценность (Lifetime Value) в Universal Analytics читайте в этой статье.
Перед тем, как начать работу с когортами в BigQuery на данных Google Analytics 4, нам необходимо познакомиться/вспомнить такое понятие, как когорта. Когорта - это группа пользователей, которые выполнили нужное нам действие на сайте или в мобильном приложении в определенный промежуток времени. Например, впервые зашли на сайт, скачали приложение, оформили заказ, подписались на рассылку, оплатили покупку. А определенный промежуток времени - это сегодня, вчера, за неделю, в прошлом месяце, в 2000-ых и т.д.
С помощью когортного анализа можно изучить такие группы людей с течением времени и заметить, как их поведение отличается. Например, когда вы отправляете e-mail рассылку, вы понимаете, что из всего количества отправленных писем его откроют не все, а лишь какая-то часть. Еще меньшая часть перейдет из письма на сайт, чтобы посмотреть ваш товар или услугу. И из оставшейся аудитории самое меньшинство сделает заказ. Это классическая воронка перехода из одного состояния в другое. Но что, если мыслить несколько другими категориями?
Предположим, вы отправили 1000 писем и ждете с этой рассылки реальных продаж. В первый день их было 50, во второй день - 30, в третий - 10, в пятый - 2 и т.д. Но если через неделю вы сделаете еще одну e-mail рассылку для 1000 человек, они будут покупать ваш продукт/услугу в их «нулевой (=тот же) день», в то время как другое электронное письмо было отправлено за 7 дней до этого. Вполне вероятно, что их поведение будет сильно отличаться. Когортный анализ может помочь вам сравнить этих людей по способу и времени покупки, а также выявить различия в таких показателях, как: вовлеченность, удержание, привлечение или реакция на рекламные активности.
Основной задачей когортного анализа является нахождений таких групп потребителей, которым мы должны помочь потратить больше денег на наши продукты или услуги, чтобы, в свою очередь, заработать больше денег для компании/себя. Для этого в Google Analytics 4 используется когортное исследование.
Каждая строка Исследования представляет отдельную когорту, а каждый столбец - разный период времени. По умолчанию GA4 покажет вам удержание активных пользователей (Active users) за период в пять недель для пользователей, у которых произошло какое-либо событие.
Пример когортного исследования в Google Analytics 4
Примечание: когорты в интерфейсе Google Analytics 4 зависят только от типа устройства пользователя. User ID (идентификатор пользователя) не влияет на определение когорты.
Ниже мы попробуем воспроизвести данное Исследование. Но сразу отмечу, что полученные результаты в BigQuery могут отличаться от цифр, что вы видите в интерфейсе Google Analytics 4.
Весь процесс когортного анализа в BigQuery на данных Google Analytics 4 состоит из следующих этапов:
- создать таблицу когорт удержания пользователей в BigQuery с использованием данных Google Analytics 4
- определить строительные блоки, необходимые из данных экспорта GA4, чтобы иметь возможность получить желаемый результат
- визуализируйте данные своей когорты в Looker Studio
Разберем каждый шаг подробнее.
Примечание: перед тем, как выполнять все нижеописанные этапы, вам необходимо связать ресурс Google Analytics 4 с BigQuery.
Все нижеприведенные настройки вы сможете выполнить только после того, как привяжите свою банковскую карту к Google Cloud и будете иметь активный платежный аккаунт. В связи с текущими событиями в мире Google приостановил работу для пользователей из России. На момент публикации этого материала вы не сможете привязать свою банковскую карту, выпущенную на территории РФ. Наиболее простое и эффективное решение - выпустить карту другой страны (Казахстан, Киргизия, Армения и т.д.), чтобы иметь возможность пользоваться Google Cloud и оплачивать счета.
Подготовка запроса
Начнем с определения необходимых нам полей из экспортированных данных GA4, чтобы получить желаемый результат. Возьмем такие:
- идентификатор пользователя (user_pseudo_id или user_id);
- идентификатор сессии (session_id);
- дата сессии;
- время взаимодействия (чтобы иметь возможность подсчитывать активных пользователей, а не общее количество пользователей);
- дата первого сеанса.
Поскольку мы будем воспроизводить когортное исследование по умолчанию, то возьмем пятинедельный временной интервал (Неделя 0 - Неделя 4). А это динамический диапазон дат, начиная с пяти недель назад и заканчивая текущей неделей.
Скопируйте нижеприведенный SQL-запрос и запустите его в BigQuery:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
select user_pseudo_id, (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id, max((select value.int_value from unnest(event_params) where key = 'ga_session_number')) as session_number, min(event_date) as session_date from `ga4bigquery.analytics_250794857.events_*` where _table_suffix between format_date('%Y%m%d',date_sub(current_date(), interval 4 week)) and format_date('%Y%m%d',date_sub(current_date(), interval 0 week)) group by user_pseudo_id, session_id |
, где вместо ga4bigquery.analytics_250794857.events_* укажите путь к своему проекту, набору данных и таблице.
Результат в BigQuery будет выглядеть так:
Результат запроса в Google BigQuery
Этот SQL-запрос выбирает данные из вашей таблицы за последние 4 недели, сгруппированные по уникальному идентификатору пользователя (Client ID, он же user_pseudo_id в Google BigQuery) и идентификатору сеанса session_id. Он извлекает значение идентификатора сеанса ga_session_id и ga_session_number из параметров событий event_params, а затем выбирает максимальное значение для ga_session_number и минимальную дату события для каждой сессии из event_date.
Обратите внимание, что мы не объединяем ga_session_id с user_pseudo_id, поскольку мы будем считать не сеансы, а пользователей.
Следующим шагом будет добавление времени взаимодействия (суммы общего времени взаимодействия за сеанс) и оконной функции FIRST_VALUE, которая вернет дату первого сеанса для каждого идентификатора пользователя.
SQL-запрос:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
select user_pseudo_id, (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id, max((select value.int_value from unnest(event_params) where key = 'ga_session_number')) as session_number, min(event_date) as session_date, sum((select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as engagement_time_msec, first_value(min(event_date)) over (partition by user_pseudo_id order by min(event_date) rows between unbounded preceding and unbounded following) as first_session_date, from `ga4bigquery.analytics_250794857.events_*` where _table_suffix between format_date('%Y%m%d',date_sub(current_date(), interval 4 week)) and format_date('%Y%m%d',date_sub(current_date(), interval 0 week)) group by user_pseudo_id, session_id |
Результат в Google BigQuery:
Результат запроса
В выделенной строке выше вы можете видеть, что дата первого сеанса отличается от даты сеанса, как мы и ожидали. А значение null в столбце engagement_time_msec гарантирует, что соответствующий пользователь не будет учитываться для этой даты сеанса, поскольку мы хотим учитывать только активных пользователей.
Чтобы иметь возможность вычислить разницу во времени между датами сеанса, нам сначала необходимо преобразовать поля даты сеанса и даты первого сеанса в фактический тип данных даты. Сделать это можно с помощью функции PARSE_DATE, которая преобразует отформатированную строку YYYYMMDD значения в объект DATE.
Пример SQL-команды:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
select user_pseudo_id, (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id, max((select value.int_value from unnest(event_params) where key = 'ga_session_number')) as session_number, min(parse_date('%Y%m%d',event_date)) as session_date, sum((select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as engagement_time_msec, first_value(min(parse_date('%Y%m%d',event_date))) over (partition by user_pseudo_id order by min(event_date) rows between unbounded preceding and unbounded following) as first_session_date, from `ga4bigquery.analytics_250794857.events_*` where _table_suffix between format_date('%Y%m%d',date_sub(current_date(), interval 4 week)) and format_date('%Y%m%d',date_sub(current_date(), interval 0 week)) group by user_pseudo_id, session_id |
Результат в Google BigQuery после выполнения запроса и преобразованию даты:
Результат запроса
Основной запрос
Теперь создадим новый запрос, превратив наш текущий запрос в подзапрос prep. Основной запрос будет ссылаться на подзапрос, созданный на предыдущем шаге.
Сам 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 |
with prep as ( select user_pseudo_id, (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id, max((select value.int_value from unnest(event_params) where key = 'ga_session_number')) as session_number, min(parse_date('%Y%m%d',event_date)) as session_date, sum((select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as engagement_time_msec, first_value(min(parse_date('%Y%m%d',event_date))) over (partition by user_pseudo_id order by min(event_date) rows between unbounded preceding and unbounded following) as first_session_date, from `ga4bigquery.analytics_250794857.events_*` where _table_suffix between format_date('%Y%m%d',date_sub(current_date(), interval 4 week)) and format_date('%Y%m%d',date_sub(current_date(), interval 0 week)) group by user_pseudo_id, session_id) select distinct concat(extract(isoyear from first_session_date),'-',format('%02d',extract(isoweek from first_session_date))) as year_week, count(distinct user_pseudo_id) as week_0 from prep group by year_week order by year_week |
Первое поле - это время, номер недели, объединенный с годом (2024-22, 2024-23, 2024-24 и т.д.). Далее:
- extract(isoyear from first_session_date) извлекает четыре цифры (например, 2024);
- format('%02d',extract(isoweek from first_session_date)) гарантирует, что извлеченный номер недели всегда содержит две цифры (например, 1 станет 01, а для 52 вернется 52);
- concat(<year>,'-',<week>) отвечает за конкатенацию, сцепление номера недели и года.
Результат в Google BigQuery:
Результат запроса
Если кратко, то этот SQL-запрос анализирует данные событий пользователей за последние 4 недели и затем подсчитывает количество уникальных идентификаторов пользователей в каждой неделе, начиная с недели, в которой пользователь совершил свой первый сеанс. Полученные данные сгруппированы по году и неделе, а затем отсортированы по этим параметрам. Первая неделя - это нулевая неделя (Неделя 0).
Обратите внимание, что в запросе и результате выше мы считаем общее количество пользователей, а не активных пользователей. Чтобы подсчитать количество активных пользователей для столбцов от недели 0 (week_0) до недели 4 (week_4), нам нужна более сложная логика, предполагающая, что мы:
- должны знать разницу во времени (в данном случае количество недель) между первым сеансом и текущим сеансом;
- хотим выбрать только вернувшихся посетителей;
- не хотим учитывать пользователей несколько раз, если они повторно посещают сайт в течение одной недели
- хотим учитывать пользователей только для сеансов, время взаимодействия которых превышает 0.
Разница во времени
На предыдущем шаге мы подготовили поля даты сеанса для дальнейших манипуляций со временем. Теперь мы можем рассчитать разницу во времени между датами, используя функцию DATE_DIFF.
date_diff(session_date,first_session_date,isoweek) покажет количество недель между первым сеансом и любым сеансом. Если это значение равно 1, то это означает, что пользователь вернулся в week_1 (через неделю после первого привлечения/захода в week_0).
Вернувшиеся пользователи
Чтобы исключить новых пользователей и оставить только вернувшихся посетителей, мы можем использовать session_number (номер сеанса) в качестве условия. Для недели 0 нас не интересуют новые или вернувшиеся, поэтому мы используем session_number >= 1, а начиная с недели 1 session_number > 1.
Подсчет уникальных значений
Чтобы исключить повторный подсчет пользователей, когда они возвращаются в течение одной недели, мы можем использовать функцию COUNT (DISTINCT).
Время взаимодействия
Возможным условием выбора только сеансов с временем взаимодействия является engagement_time_msec > 0.
Итоговый SQL-запрос
Когда мы объединяем всю эту логику в одном 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 |
with prep as ( select user_pseudo_id, (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id, max((select value.int_value from unnest(event_params) where key = 'ga_session_number')) as session_number, min(parse_date('%Y%m%d',event_date)) as session_date, sum((select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as engagement_time_msec, first_value(min(parse_date('%Y%m%d',event_date))) over (partition by user_pseudo_id order by min(event_date) rows between unbounded preceding and unbounded following) as first_session_date, from `ga4bigquery.analytics_250794857.events_*` where _table_suffix between format_date('%Y%m%d',date_sub(current_date(), interval 4 week)) and format_date('%Y%m%d',date_sub(current_date(), interval 0 week)) group by user_pseudo_id, session_id) select distinct concat(extract(isoyear from first_session_date),'-',format('%02d',extract(isoweek from first_session_date))) as year_week, count(distinct case when date_diff(session_date,first_session_date,isoweek) = 0 and session_number >= 1 and engagement_time_msec > 0 then user_pseudo_id end) as week_0, count(distinct case when date_diff(session_date,first_session_date,isoweek) = 1 and session_number > 1 and engagement_time_msec > 0 then user_pseudo_id end) as week_1, count(distinct case when date_diff(session_date,first_session_date,isoweek) = 2 and session_number > 1 and engagement_time_msec > 0 then user_pseudo_id end) as week_2, count(distinct case when date_diff(session_date,first_session_date,isoweek) = 3 and session_number > 1 and engagement_time_msec > 0 then user_pseudo_id end) as week_3, count(distinct case when date_diff(session_date,first_session_date,isoweek) = 4 and session_number > 1 and engagement_time_msec > 0 then user_pseudo_id end) as week_4 from prep group by year_week order by year_week |
Результат выполнения запроса в BigQuery может выглядеть так:
Результат запроса
Теперь эта таблица похожа на результат когортного исследования в интерфейсе Google Analytics 4. Как я писал ранее, цифры могут отличаться...
Визуализация данных в Looker Studio
После выполнения запроса вы можете создать быструю визуализацию в Looker Studio. Для этого нажмите Explore data - Explore with Looker Studio:
Explore data - Explore with Looker Studio
Откроется новая вкладка с визуализациями:
Визуализация данных в Looker Studio на основе SQL-запроса
Удалите диаграмму, оставьте только таблицу. Выделите ее измените тип на Таблица с тепловой картой:
Таблица с тепловой картой
Отредактируйте таблицу таким образом, чтобы она стала похоже на когортное исследование в Google Analytics 4 - добавьте дополнительные показатели по другим неделям, строку итоговых значений, измените названия столбцов, порядок сортировки и т.д. В результате вы должны получить примерно похожее:
Итоговая таблица по когортам
К сожалению, тепловая карта не покажет такое же распределение цветов, что и когортное исследование в интерфейсе Google Analytics 4, поскольку стиль в таблице задается для каждого столбца отдельно, а не на уровне всей таблицы. Чтобы сымитировать визуализацию GA4, нам нужно немного скорректировать наш SQL-запрос.
Вернитесь обратно в BigQuery и выполните следующий запрос:
|
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 |
with prep as ( select user_pseudo_id, (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id, max((select value.int_value from unnest(event_params) where key = 'ga_session_number')) as session_number, min(parse_date('%Y%m%d',event_date)) as session_date, sum((select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as engagement_time_msec, first_value(min(parse_date('%Y%m%d',event_date))) over (partition by user_pseudo_id order by min(event_date) rows between unbounded preceding and unbounded following) as first_session_date, from `ga4bigquery.analytics_250794857.events_*` where _table_suffix between format_date('%Y%m%d',date_sub(current_date(), interval 4 week)) and format_date('%Y%m%d',date_sub(current_date(), interval 0 week)) group by user_pseudo_id, session_id) select distinct concat(extract(isoyear from first_session_date),'-',format('%02d',extract(isoweek from first_session_date))) as year_week, concat('week ',date_diff(session_date,first_session_date,isoweek)) as retention_week, count(distinct case when session_number > 1 and engagement_time_msec > 0 then user_pseudo_id end) as active_users from prep group by year_week, retention_week order by year_week |
Итоговая таблица будет содержать три столбца:
- year_week (остался без изменений);
- retention_week (результат date_diff(session_date,first_session_date,isoweek), которому предшествует слово week и дополнительный пробел между номером недели);
- active_users (количество активных пользователей, которые вы получите черезcount(distinct case when session_number > 1 and engagement_time_msec > 0 then user_pseudo_id end), сгруппированных по year_week и retention_week).
Результат запроса в BigQuery:
Результат запроса
После этого еще раз нажмите на Explore data - Explore with Looker Studio. Удалите столбчатую диаграмму, а таблицу измените на Сводная таблица с тепловой картой:
Сводная таблица с тепловой картой
Отредактируйте сводную таблицу таким образом, чтобы она стала похоже на когортное исследование в Google Analytics 4 - добавьте строку итоговых значений, измените параметра и показателя, порядок сортировки и т.д. В результате вы должны получить итоговую таблицу когортного анализа:
Итоговая таблица когортного анализа
Преимущество подхода со сводной таблицей в том, что вы можете расширить диапазон дат (например, изменить interval 4 week на interval 8 week), и тогда визуализация будет легко перестроена с учетом этого временного окна и когорт:
Сводная таблица с большим периодом когортного анализа
Дополнение к руководству
В комментариях к статье Йохана один из пользователей заметил сильное отличие результатов текущего SQL-запроса и интерфейсных значений в когортном исследовании Google Analytics 4. Он провел ряд экспериментов и обнаружил, что данные в BigQuery и интерфейсе GA4 будут ближе тогда, когда используется другая логика, а именно объединяется два запроса по user_pseudo_id - один содержит event_timestamp (функция CASE WHEN и событием является first_visit), а второй содержит разницу в дате между временной меткой любого события и той, что в событии first_visit. Таким образом можно избежать разделения по номеру сеанса, и данные получаются ближе к интерфейсным.
Пример 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 |
WITH first_visit_data AS ( SELECT user_pseudo_id, MIN(CASE WHEN event_name = 'first_visit' THEN TIMESTAMP_MICROS(event_timestamp) END) AS first_visit_timestamp FROM `ga4bigquery.analytics_250794857.events_*` WHERE _table_suffix BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 0 WEEK)) GROUP BY user_pseudo_id ), prep AS ( SELECT e.user_pseudo_id, (SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = 'ga_session_id') AS session_id, MAX((SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = 'ga_session_number')) AS session_number, TIMESTAMP(PARSE_DATE('%Y%m%d', e.event_date)) AS session_date, SUM((SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = 'engagement_time_msec')) AS engagement_time_msec, f.first_visit_timestamp, e.event_date FROM `ga4bigquery.analytics_250794857.events_*` e JOIN first_visit_data f ON e.user_pseudo_id = f.user_pseudo_id WHERE _table_suffix BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 0 WEEK)) AND f.first_visit_timestamp IS NOT NULL GROUP BY e.user_pseudo_id, session_id, f.first_visit_timestamp, e.event_date ) SELECT DISTINCT CONCAT(EXTRACT(ISOYEAR FROM first_visit_timestamp),'-',FORMAT('%02d', EXTRACT(ISOWEEK FROM CAST(first_visit_timestamp AS DATE)))) AS year_week, COUNT(DISTINCT CASE WHEN DATE_DIFF(CAST(session_date AS DATE), CAST(first_visit_timestamp AS DATE), ISOWEEK) = 0 AND session_number >= 1 AND engagement_time_msec > 0 THEN user_pseudo_id END) AS week_0, COUNT(DISTINCT CASE WHEN DATE_DIFF(CAST(session_date AS DATE), CAST(first_visit_timestamp AS DATE), ISOWEEK) = 1 AND session_number > 1 AND engagement_time_msec > 0 THEN user_pseudo_id END) AS week_1, COUNT(DISTINCT CASE WHEN DATE_DIFF(CAST(session_date AS DATE), CAST(first_visit_timestamp AS DATE), ISOWEEK) = 2 AND session_number > 1 AND engagement_time_msec > 0 THEN user_pseudo_id END) AS week_2, COUNT(DISTINCT CASE WHEN DATE_DIFF(CAST(session_date AS DATE), CAST(first_visit_timestamp AS DATE), ISOWEEK) = 3 AND session_number > 1 AND engagement_time_msec > 0 THEN user_pseudo_id END) AS week_3, COUNT(DISTINCT CASE WHEN DATE_DIFF(CAST(session_date AS DATE), CAST(first_visit_timestamp AS DATE), ISOWEEK) = 4 AND session_number > 1 AND engagement_time_msec > 0 THEN user_pseudo_id END) AS week_4 FROM prep WHERE first_visit_timestamp IS NOT NULL GROUP BY year_week ORDER BY year_week |
Подзапрос first_visit_data идентифицирует первый визит каждого пользователя (по user_pseudo_id), используя минимальную метку времени события (event_timestamp), когда event_name равно событию first_visit, и фильтрует данные за последние 4 недели.
Подзапрос prep собирает данные о сеансах пользователей, включая идентификатор сессии, номер сессии, дату сессии и общее время взаимодействия в миллисекундах. Затем он присоединяет данные о первом визите из подзапроса first_visit_data, а потом группирует данные по user_pseudo_id, session_id, first_visit_timestamp и event_date.
Основной запрос вычисляет количество уникальных пользователей для каждой когорты на основе недели первого визита (year_week). Он использует функцию DATE_DIFF для определения, на какой неделе после первого визита произошла сессия, и считает только те сессии, где было некоторое взаимодействие (engagement_time_msec > 0). Запрос различает пользователей по неделям от нулевой до четвертой после первого визита, где week_0 - это неделя первого визита, а week_1 до week_4 - последующие недели.
Результат запроса
Приведенный выше SQL - лишь пример того, как это может быть реализовано. Вы можете взять его за основу и усовершенствовать итоговую конструкцию, стараясь снизить % расхождения данных между интерфейсными значениями GA4 и BigQuery.
Сравнение интерфейсных данных GA4 и BigQuery
Самое близкое значение мне удалось получить, когда я удалил условие engagement_time_msec > 0 для всех последующих недель, кроме первой. После этого общая разница в количестве активных пользователей по неделям составила меньше 3-4% (16 879 vs 16 575):
Снижение % расхождения за счет изменения SQL-запроса
Неделя - GA4 - BigQuery:
- Неделя 0 - 16 008 - 15 701 (разница в процентах - 1,92%);
- Неделя 1 - 531 - 536 (разница в процентах - 0,94%);
- Неделя 2 - 224 - 221 (разница в процентах - 1,34%);
- Неделя 3 - 115 - 111 (разница в процентах - 3,48%);
Как пишет Google в своей официальной документации - расхождение между общим количеством событий в GA4 и BigQuery не должно превышать 2–5 %. То есть присутствует определенная погрешность в данных, которую изначально вам нужно учитывать и закладывать в расчеты!
