Когортный анализ на данных Яндекс.Метрики с использованием PostgreSQL и DBeaver для новичков

В этой статье мы исследуем простой метод построения когортного анализа (retention пользователей по источникам трафика) на сырых данных полученных из Яндекс.Метрики используя SQL. Статья подготовлена для тех, кто хочет получить больше информации из данных которые есть в Яндекс Метрике, на основе простых запросов, не требующих углубленного изучения языка SQL.

В будущем будет статья как сделать более элегантно и продвинуто когортный анализ используя SQL.

Если у вас нет данных Яндекс.Метрики, вы можете воспользоваться этими двумя гайдами, чтобы создать себе выгрузку данных Метрики в PostgreSQL без программиста.

Этап 1 — Создадим базу данных PostgreSQL для хранения данных из сервиса Яндекс Метрика.

Этап 2 - Произведем no code интеграцию исходных данных сервиса Яндекс Метрика в PostgreSQL с помощью сервиса (Используя сервис Veeneo)

Когортный анализ позволяет нам понять, как пользователи взаимодействуют с нашим веб-сайтом или приложением с течением времени. Он помогает выявить изменения в поведении пользователей, выделить успешные стратегии маркетинга и увеличить конверсию.

Допустим, вы воспользовались гайдами выше или у вас уже имеется своя база данных Яндекс.Метрики.

Для этого пишем запрос, либо переходим на вкладку «Данные». Название таблицы находим на вкладке «Свойства» загруженной вами таблицы в базу. Запрос выглядит так:

select * from yandex_metrika_project_1_visits limit 1000

- visit_id (идентификатор визита)
- date_time (время визита)
- client_id (идентификатор устройства пользователя)
- last_traffic_source (последний канал пользователя)
- last_referal_source (последний реферальный источник пользователя)
- is_new_user (новый пользователь или нет, 1 - да, 0 - нет)

Это малая часть данных, но цель у нас посчитать retention пользователей по разным источникам трафика. Вставляем этот код в редактор, не забываем изменять название вашей таблицы.

select date_time, client_id, visit_id, last_traffic_source, last_referal_source, is_new_user from yandex_metrika_project_1_visits

В результате получаем табличку:

Когортный анализ на данных Яндекс.Метрики с использованием PostgreSQL и DBeaver для новичков

Это необработанные данные в которых мы получаем дату визита, идентификатор устройства, идентификатор самого визита, признак нового посетителя, источник трафика и канал трафика

Далее нужно понять какие периоды/даты у нас есть, для этого пишем:

select distinct date from yandex_metrika_project_1_visits order by date

Получаем список дат которые у нас есть

В моем случае данные есть с 01.10.2023 по 06.11.2023. Данных не много, поэтому я покажу построение когорт по неделям, а не по месяцам.

В данных у нас есть 4 недели одного месяца, и одна неделя следующего. Основные источники трафика это google и yandex.

Дабы не писать сотни строк кода, я буду выписать полученные данные из одной части кода в табличку, для удобства.

Первая (нулевая) когорта - это пользователи посетившие впервые наш сайт с 01.10.2023 по 07.10.2023 с источником трафика - «yandex и google».

Вторая (первая) когорта - это пользователи посетившие впервые наш сайт с 08.10.2023 по 15.10.2023 с источником трафика - «yandex и google».

Третья (вторая) когорта - это пользователи посетившие впервые наш сайт с 16.10.2023 по 23.10.2023 с источником трафика - «yandex и google».

Четвертая (третья) когорта - это пользователи посетившие впервые наш сайт с 24.10.2023 по 31.10.2023 с источником трафика - «yandex и google».

Пятая (четвертая) когорта - это пользователи посетившие впервые наш сайт с 01.11.2023 по 07.11.2023 с источником трафика - «yandex и google».

Из таблицы мы достаем самую первую дату пользователя посетившего сайт в указанный выше период - первая (нулевая) неделя. Также указываем is_new_user = 1, 0 - не новый пользователь, 1 - новый пользователь и фильтруем трафик из яндекса

Запрос выглядит так:

select date, client_id from yandex_metrika_project_1_visits where date between '2023-10-01' and '2023-10-07' and last_referal_source = 'yandex.ru' and is_new_user = 1

Мы получили идентификатор устройства пользователя и дату его первого посещения сайта из яндекса.

Дальше нам нужно просто взять этих пользователей и проверить на наличие визитов на следующей неделе.

-- НАХОДИМ НУЛЕВУЮ НЕДЕЛЮ with week_0 as ( select date, client_id from yandex_metrika_project_1_visits where date between '2023-10-01' and '2023-10-07' and last_referal_source = 'yandex.ru' and is_new_user = 1 ), -- НАХОДИМ ВСЕХ КТО БЫЛ НА ПЕРВОЙ НЕДЕЛЕ (следующая после нулевой) week_1 as ( select distinct client_id from yandex_metrika_project_1_visits where date between '2023-10-08' and '2023-10-15' ) -- ДЖОЙНИМ К НУЛЕВОЙ КОГОРТЕ ТЕХ, КТО ПОСЕТИЛ СЛЕДУЮЩУЮ НЕДЕЛЮ SELECT 'Когорта - yandex (01.10 - 07.10)' as cohort_name, (SELECT count(client_id) FROM week_0) AS cohort_week_0, (SELECT count(client_id) FROM week_1 JOIN week_0 USING (client_id)) AS cohort_week_1

В результате мы получили два числа. Первый столбец («cohort_name») - это название нашей когорты. В столбце «cohort_week_o» данные за первую (нулевую) неделю, а «cohort_week_1» за следующую. То есть в первую неделю октября из яндекса на сайт пришло 15 325 пользователя, а из этих же пользователей на следующей неделе посетили уже 1 872.

И так для каждой последующей недели, полный запрос выглядит так:

with week_0 as ( select date, client_id from yandex_metrika_project_1_visits where date between '2023-10-01' and '2023-10-07' and last_referal_source = 'yandex.ru' and is_new_user = 1 ), week_1 as ( select distinct client_id from yandex_metrika_project_1_visits where date between '2023-10-08' and '2023-10-15' ), week_2 as ( select distinct client_id from yandex_metrika_project_1_visits where date between '2023-10-16' and '2023-10-23' ), week_3 as ( select distinct client_id from yandex_metrika_project_1_visits where date between '2023-10-24' and '2023-10-31' ), week_4 as ( select distinct client_id from yandex_metrika_project_1_visits where date between '2023-11-01' and '2023-11-07' ) SELECT 'Когорта - yandex (01.10 - 07.10)' as cohort_name, (SELECT count(client_id) FROM week_0) AS cohort_week_0, (SELECT count(client_id) FROM week_1 JOIN week_0 USING (client_id)) AS cohort_week_1, (SELECT count(client_id) FROM week_2 JOIN week_0 USING (client_id)) AS cohort_week_2, (SELECT count(client_id) FROM week_3 JOIN week_0 USING (client_id)) AS cohort_week_3, (SELECT count(client_id) FROM week_4 JOIN week_0 USING (client_id)) AS cohort_week_4

И так, мы получили количество пользователей по неделям из яндекса.

Выпишем эти данные в отдельную таблицу.

Теперь чтобы посчитать тоже для гугла, мы просто меняем last_referal_source = 'yandex.ru' на last_referal_source = 'google.com'

Запрос выглядит так:

with week_0 as ( select date, client_id from yandex_metrika_project_1_visits where date between '2023-10-01' and '2023-10-07' and last_referal_source = 'google.com' and is_new_user = 1 ), week_1 as ( select distinct client_id from yandex_metrika_project_1_visits where date between '2023-10-08' and '2023-10-15' ), week_2 as ( select distinct client_id from yandex_metrika_project_1_visits where date between '2023-10-16' and '2023-10-23' ), week_3 as ( select distinct client_id from yandex_metrika_project_1_visits where date between '2023-10-24' and '2023-10-31' ), week_4 as ( select distinct client_id from yandex_metrika_project_1_visits where date between '2023-11-01' and '2023-11-07' ) SELECT 'Когорта - google (01.10 - 07.10)' as cohort_name, (SELECT count(client_id) FROM week_0) AS cohort_week_0, (SELECT count(client_id) FROM week_1 JOIN week_0 USING (client_id)) AS cohort_week_1, (SELECT count(client_id) FROM week_2 JOIN week_0 USING (client_id)) AS cohort_week_2, (SELECT count(client_id) FROM week_3 JOIN week_0 USING (client_id)) AS cohort_week_3, (SELECT count(client_id) FROM week_4 JOIN week_0 USING (client_id)) AS cohort_week_4

Результат:

Выпишем эти данные также в таблицу.

Мы посчитали Retention когорты которая впервые посетила сайт с 01.10.23 по 07.10.23 из гугла и яндекса.

Для того чтобы посчитать retention следующей недели, то есть когорту людей посетивших сайт с 08.10.23 по 15.10.23 мы просто смещаемся как бы на одну неделю. И также, мы должны исключить людей которые повторно зашли с предыдущей недели. В первом запросе мы этого не делали, потому что ранее данных мы не получали, а так как данные уже есть за предыдущий период (неделю), то их следует исключить.

Теперь у нас в запросе будет в week_0 следующая дата.

with week_0 as ( select date, client_id from yandex_metrika_project_1_visits where date between '2023-10-08' and '2023-10-15' and last_referal_source = 'yandex.ru' and is_new_user = 1 and client_id not in ( select distinct client_id from yandex_metrika_project_1_visits where date between '2023-10-01' and '2023-10-07' ) ), week_1 as ( select distinct client_id from yandex_metrika_project_1_visits where date between '2023-10-16' and '2023-10-23' ), week_2 as ( select distinct client_id from yandex_metrika_project_1_visits where date between '2023-10-24' and '2023-10-31' ), week_3 as ( select distinct client_id from yandex_metrika_project_1_visits where date between '2023-11-01' and '2023-11-07' ) SELECT 'Когорта - yandex (08.10 - 15.10)' as cohort_name, (SELECT count(client_id) FROM week_0) AS cohort_week_0, (SELECT count(client_id) FROM week_1 JOIN week_0 USING (client_id)) AS cohort_week_1, (SELECT count(client_id) FROM week_2 JOIN week_0 USING (client_id)) AS cohort_week_2, (SELECT count(client_id) FROM week_3 JOIN week_0 USING (client_id)) AS cohort_week_3

В результате по яндексу:

И сразу посчитаем по гуглу:

Внесем в таблицу:

И так рассчитываем все остальные когорты. Запрос показывать опять не буду, и так понятно что убираем по одной неделе из запроса, и меняем дату в каждом последующем. Выведу сразу результат. Также эти данные можно перевести в проценты, кому как удобно.

Для более глубокого знакомства с когортным анализом - рекомендуем нашу статью из блога с сайта компании

-----

Наш сайт Услуги аналитики данных

Телеграм-канал (Канал про настройку и применение аналитики данных в бизнесе. Web/Mobile аналитика. Трекеры, DWH, ETL, BI)

Всем экспоненциального роста!

Когортный анализ на данных Яндекс.Метрики с использованием PostgreSQL и DBeaver для новичков

1. Проверяем какие данные у нас имеются.

2. Определяем какие данные (поля) из таблицы нам необходимы.

3. Определяем какие когорты мы будем рассчитывать.

4. Приступаем к обработке и подсчету данных