0 added
0 removed
Original
2026-01-01
Modified
2026-03-10
1
<ul><li><a>Исходные данные</a></li>
1
<ul><li><a>Исходные данные</a></li>
2
<li><a>Первая покупка</a></li>
2
<li><a>Первая покупка</a></li>
3
<li><a>Построение когорт</a></li>
3
<li><a>Построение когорт</a></li>
4
<li><a>Удержание по месяцам</a></li>
4
<li><a>Удержание по месяцам</a></li>
5
<li><a>Edge-кейсы</a></li>
5
<li><a>Edge-кейсы</a></li>
6
<li><a>LTV по когортам</a></li>
6
<li><a>LTV по когортам</a></li>
7
<li><a>RFM-анализ с сегментацией</a></li>
7
<li><a>RFM-анализ с сегментацией</a></li>
8
<li><a>Архитектура</a></li>
8
<li><a>Архитектура</a></li>
9
</ul><h3><strong>Исходные данные</strong></h3>
9
</ul><h3><strong>Исходные данные</strong></h3>
10
<p>Работать будем с таблицей orders - реальной историей всех покупок. </p>
10
<p>Работать будем с таблицей orders - реальной историей всех покупок. </p>
11
<p>Предварительные данные </p>
11
<p>Предварительные данные </p>
12
<p>| user_id | order_date | amount |</p>
12
<p>| user_id | order_date | amount |</p>
13
<p>|---|----|---|</p>
13
<p>|---|----|---|</p>
14
<p>| 1 | 2024-01-10 | 200 |</p>
14
<p>| 1 | 2024-01-10 | 200 |</p>
15
<p>| 1 | 2024-02-05 | 150 |</p>
15
<p>| 1 | 2024-02-05 | 150 |</p>
16
<p>| 2 | 2024-01-15 | 300 |</p>
16
<p>| 2 | 2024-01-15 | 300 |</p>
17
<p>| 3 | 2024-02-10 | 120 |</p>
17
<p>| 3 | 2024-02-10 | 120 |</p>
18
<p>| 3 | 2024-04-10 | 80 |</p>
18
<p>| 3 | 2024-04-10 | 80 |</p>
19
<h3>Первая покупка</h3>
19
<h3>Первая покупка</h3>
20
<p>Определяем, когда пользователь в первый раз проявил активность </p>
20
<p>Определяем, когда пользователь в первый раз проявил активность </p>
21
<p>Или другой вариант: </p>
21
<p>Или другой вариант: </p>
22
<p>Так можно получить все данные с сохранением первых покупок </p>
22
<p>Так можно получить все данные с сохранением первых покупок </p>
23
<h3><strong>Построение когорт</strong></h3>
23
<h3><strong>Построение когорт</strong></h3>
24
<p>Когорта - это просто группа клиентов, которые совершили первую покупку в один и тот же период. Чаще всего когорты формируют по месяцам первой покупки - так проще анализировать динамику.</p>
24
<p>Когорта - это просто группа клиентов, которые совершили первую покупку в один и тот же период. Чаще всего когорты формируют по месяцам первой покупки - так проще анализировать динамику.</p>
25
<p>Соединяем с заказами:</p>
25
<p>Соединяем с заказами:</p>
26
<p>Отлично! Теперь у нас на каждый заказ есть своя когорта</p>
26
<p>Отлично! Теперь у нас на каждый заказ есть своя когорта</p>
27
<h3><strong>Удержание по месяцам</strong></h3>
27
<h3><strong>Удержание по месяцам</strong></h3>
28
<p>Сейчас переходим к самому интересному - будем смотреть, сколько пользователей из каждой когорты продолжают покупать со временем. Конкретно нас интересует: какой процент клиентов возвращается через месяц, два, три и так далее после первой покупки. Это и есть классический retention-анализ, который показывает "живучесть" клиентской базы.</p>
28
<p>Сейчас переходим к самому интересному - будем смотреть, сколько пользователей из каждой когорты продолжают покупать со временем. Конкретно нас интересует: какой процент клиентов возвращается через месяц, два, три и так далее после первой покупки. Это и есть классический retention-анализ, который показывает "живучесть" клиентской базы.</p>
29
<p>Если нужны не просто количества, а именно доли (проценты) - используем COUNT(*) OVER (PARTITION BY cohort_month).</p>
29
<p>Если нужны не просто количества, а именно доли (проценты) - используем COUNT(*) OVER (PARTITION BY cohort_month).</p>
30
<h3>Edge-кейсы</h3>
30
<h3>Edge-кейсы</h3>
31
<ol><li><strong>Проблемы с часовыми поясами</strong><strong></strong>Всегда обрезайте даты до месяца через DATE_TRUNC(‘month’, order_date::date). TIMESTAMP WITH TIME ZONE - опасная штука, если вам не нужна точность до секунд с учётом поясов.</li>
31
<ol><li><strong>Проблемы с часовыми поясами</strong><strong></strong>Всегда обрезайте даты до месяца через DATE_TRUNC(‘month’, order_date::date). TIMESTAMP WITH TIME ZONE - опасная штука, если вам не нужна точность до секунд с учётом поясов.</li>
32
<li><strong>Нюансы расчёта месяцев</strong><strong></strong>Функция AGE иногда возвращает 0 месяцев для периодов до 29 дней. Проверьте, подходит ли вам такое<a>округление</a>- возможно, нужен более точный расчёт.</li>
32
<li><strong>Нюансы расчёта месяцев</strong><strong></strong>Функция AGE иногда возвращает 0 месяцев для периодов до 29 дней. Проверьте, подходит ли вам такое<a>округление</a>- возможно, нужен более точный расчёт.</li>
33
<li><strong>Учёт повторных регистраций</strong><strong></strong>Когда пользователь заводит новый аккаунт (с другим ID или email), его история покупок начинается заново. Стоит это учитывать </li>
33
<li><strong>Учёт повторных регистраций</strong><strong></strong>Когда пользователь заводит новый аккаунт (с другим ID или email), его история покупок начинается заново. Стоит это учитывать </li>
34
</ol><h3>LTV по когортам</h3>
34
</ol><h3>LTV по когортам</h3>
35
<p>LTV (пожизненная ценность клиента) показывает, сколько денег в среднем приносит один пользователь из когорты за весь период взаимодействия с бизнесом. Для когорт: </p>
35
<p>LTV (пожизненная ценность клиента) показывает, сколько денег в среднем приносит один пользователь из когорты за весь период взаимодействия с бизнесом. Для когорт: </p>
36
<p>Теперь, когда у нас есть динамика LTV по месяцам, можно посчитать кумулятивное значение - просто добавьте оконную функцию: SUM() OVER (…).</p>
36
<p>Теперь, когда у нас есть динамика LTV по месяцам, можно посчитать кумулятивное значение - просто добавьте оконную функцию: SUM() OVER (…).</p>
37
<h3><strong>RFM-анализ с сегментацией</strong></h3>
37
<h3><strong>RFM-анализ с сегментацией</strong></h3>
38
<p>Переходим к RFM-анализу</p>
38
<p>Переходим к RFM-анализу</p>
39
<p>Считаем ключевые показатели: </p>
39
<p>Считаем ключевые показатели: </p>
40
<p>Делим по квантилям:</p>
40
<p>Делим по квантилям:</p>
41
<p>RFM_segment = "555" - идеальный пользователь! : недавно совершал покупку, приносит много денег и покупки совершает регулярно </p>
41
<p>RFM_segment = "555" - идеальный пользователь! : недавно совершал покупку, приносит много денег и покупки совершает регулярно </p>
42
<h3><strong>Архитектура</strong></h3>
42
<h3><strong>Архитектура</strong></h3>
43
<p>Чтобы ваш когортный анализ не превратился в беспорядочную коллекцию SQL-запросов, нужно продумать архитектуру. Начните с создания материализованных представлений для ключевых данных:</p>
43
<p>Чтобы ваш когортный анализ не превратился в беспорядочную коллекцию SQL-запросов, нужно продумать архитектуру. Начните с создания материализованных представлений для ключевых данных:</p>
44
<ol><li><strong>user_first_order</strong>- хранит дату первой покупки каждого клиента</li>
44
<ol><li><strong>user_first_order</strong>- хранит дату первой покупки каждого клиента</li>
45
<li><strong>orders_with_cohort</strong>- все заказы с указанием когорты и временного смещения</li>
45
<li><strong>orders_with_cohort</strong>- все заказы с указанием когорты и временного смещения</li>
46
<li><strong>rfm_scores</strong>- результаты RFM-анализа с готовой сегментацией</li>
46
<li><strong>rfm_scores</strong>- результаты RFM-анализа с готовой сегментацией</li>
47
</ol><p>Дальше выстраиваем стандартный pipeline:</p>
47
</ol><p>Дальше выстраиваем стандартный pipeline:</p>
48
<ul><li>Автоматическое обновление представлений (например, раз в день через Airflow/Dagster)</li>
48
<ul><li>Автоматическое обновление представлений (например, раз в день через Airflow/Dagster)</li>
49
<li>Визуализация в Metabase/Superset</li>
49
<li>Визуализация в Metabase/Superset</li>
50
<li>Настройка алертов по ключевым показателям</li>
50
<li>Настройка алертов по ключевым показателям</li>
51
</ul><p>Такой подход превращает разрозненные запросы в полноценную систему мониторинга, где вы сразу заметите, если:</p>
51
</ul><p>Такой подход превращает разрозненные запросы в полноценную систему мониторинга, где вы сразу заметите, если:</p>
52
<ul><li>Ухудшился retention</li>
52
<ul><li>Ухудшился retention</li>
53
<li>Снизился LTV в отдельных когортах</li>
53
<li>Снизился LTV в отдельных когортах</li>
54
<li>Изменилось качество клиентской базы</li>
54
<li>Изменилось качество клиентской базы</li>
55
</ul><p>Вот что у нас получилось в результате:</p>
55
</ul><p>Вот что у нас получилось в результате:</p>
56
<ol><li>Готовый SQL-пайплайн для когортного анализа</li>
56
<ol><li>Готовый SQL-пайплайн для когортного анализа</li>
57
<li>Все ключевые метрики: Retention, LTV и RFM-сегментация</li>
57
<li>Все ключевые метрики: Retention, LTV и RFM-сегментация</li>
58
<li>Возможность глубокой аналитики по разным группам пользователей</li>
58
<li>Возможность глубокой аналитики по разным группам пользователей</li>
59
<li>Решение для неочевидных кейсов и подводных камней</li>
59
<li>Решение для неочевидных кейсов и подводных камней</li>
60
</ol><p><strong>Куда двигаться дальше?</strong></p>
60
</ol><p><strong>Куда двигаться дальше?</strong></p>
61
<ul><li>Продуктовая аналитика: анализ воронок и конверсий</li>
61
<ul><li>Продуктовая аналитика: анализ воронок и конверсий</li>
62
<li>Прогнозная аналитика: машинное обучение на основе когорт</li>
62
<li>Прогнозная аналитика: машинное обучение на основе когорт</li>
63
<li>Скоринг клиентов: расширение RFM внешними данными</li>
63
<li>Скоринг клиентов: расширение RFM внешними данными</li>
64
</ul>
64
</ul>