§ P17 — DAX-кейс · удержание

Когортный анализ удержание на DAX

Модель · Меры · Heatmap · Кейсы

Как построить когорта-удержание в Power BI — с нуля и без костылей на Power Query. Только DAX: правильная модель данных, меры через VAR+TREATAS, визуализация heatmap. С примерами для ритейла, SaaS и e-commerce.

Когортный анализ — это способ посмотреть на клиентов не «в среднем», а группами, привязанными к моменту первой покупки/регистрации. Берёте всех, кто пришёл в январе, и следите, сколько из них вернулось в феврале, марте, апреле. Потом то же для февральской когорты. Получается треугольная таблица, где строка — месяц первой покупки, колонка — сколько месяцев прошло, ячейка — процент возвратившихся.

Зачем это нужно: средний удержание по компании скрывает, что последние когорты покупают всё хуже и хуже, а старые держат среднее за счёт лояльного ядра. Когорты показывают это за пять секунд.

Построить когорты в Excel — боль. В Power Query — тяжёлая трансформация с self-join. На DAX — несколько мер и правильная модель данных. Разберём второй путь — он самый чистый и быстрее перестраивается при новых фильтрах.

Модель данных: что нужно подготовить

Минимальная модель для удержание:

fact_orders — транзакции: order_id, customer_id, order_date, выручка. Это факт.
dim_customer — справочник клиентов: customer_id, first_order_date (дата первой покупки), first_order_когорта (первое число месяца первой покупки, например 2026-01-01).
dim_date — календарная таблица, помечена как Date Table.
dim_cohort — справочник когорт (первое число каждого месяца, с 2023-01-01 по текущий). Не связан с fact_orders напрямую — используется как ось для визуала.
dim_periods — справочник «месяц от когорты»: period_number (0, 1, 2, ... 24). Тоже не связан с fact_orders.

Важно: dim_customer[first_order_cohort] нужно считать на уровне ETL (в SQL), а не на лету в DAX. Это избавляет от тяжёлых EARLIER-конструкций и ускоряет меры в разы.

-- В ETL: dim_customer с готовыми полями когорт
SELECT
    customer_id,
    MIN(order_date) AS first_order_date,
    CAST(
        DATEFROMPARTS(
            YEAR(MIN(order_date)),
            MONTH(MIN(order_date)),
            1
        ) AS DATE
    ) AS first_order_cohort
FROM fact_orders
GROUP BY customer_id;

Базовые меры: строим с нуля

Мера 1: Общее число клиентов в когорте

В матрице «когорта × период» строка — это когорта, колонка — period. Первое, что нужно — сколько клиентов было в когорте изначально (это знаменатель для удержание%).

Cohort Size =
CALCULATE(
    DISTINCTCOUNT(dim_customer[customer_id]),
    TREATAS(
        VALUES(dim_cohort[cohort_month]),
        dim_customer[first_order_cohort]
    )
)

TREATAS связывает dim_cohort[cohort_month] с dim_customer[first_order_cohort] через виртуальную связь. Получаем: для каждой когорты из оси показывается число клиентов, чья first_order_когорта совпадает с ней.

Мера 2: Число активных клиентов в периоде N

Теперь нужно посчитать, сколько клиентов из когорты сделали хотя бы одну покупку в месяце (когорта + N). То есть для когорты «Январь», period=3 нужна активность в апреле.

Active Customers =
VAR _cohort_month = SELECTEDVALUE(dim_cohort[cohort_month])
VAR _period_n = SELECTEDVALUE(dim_periods[period_number])
VAR _target_month =
    EOMONTH(_cohort_month, _period_n) - DAY(EOMONTH(_cohort_month, _period_n)) + 1
    // первое число месяца (cohort + N)
RETURN
    CALCULATE(
        DISTINCTCOUNT(fact_orders[customer_id]),
        TREATAS(
            VALUES(dim_cohort[cohort_month]),
            dim_customer[first_order_cohort]
        ),
        FILTER(
            ALL(dim_date),
            dim_date[date] >= _target_month
            && dim_date[date] < EOMONTH(_target_month, 0) + 1
        )
    )

Разбор по частям:

_cohort_month — берём текущую когорту из контекста визуала
_period_n — номер периода (0, 1, 2, ... из оси)
_target_month — целевой месяц = первый день месяца (когорта + N)
Фильтр по dim_клиент через TREATAS оставляет только клиентов когорты
Фильтр по dim_date оставляет только транзакции целевого месяца
DISTINCTCOUNT считает уникальных клиентов

Мера 3: удержание %

Retention % =
DIVIDE(
    [Active Customers],
    [Cohort Size]
)

Деление с защитой от нуля. При period=0 всегда даёт 100% (все клиенты когорты «активны» в первом месяце по определению). Дальше цифры падают.

Что получается в визуале

Кладём в матрицу: строки — dim_cohort[cohort_month], колонки — dim_periods[period_number], значения — [Retention %]. Применяем условное форматирование (градиент от красного к зелёному). Получается классический треугольник когорт:

КогортаM0M1M2M3M4M5M6M7M8M9M10M11

Янв 2025100%62%48%41%37%34%32%30%29%28%27%26%

Фев 2025100%59%46%39%35%32%30%28%27%25%24%

Мар 2025100%60%47%40%36%33%31%29%28%26%

Апр 2025100%57%44%37%33%30%28%26%24%

Май 2025100%55%42%35%31%28%26%24%

Июн 2025100%53%40%33%29%26%23%

Считывается легко: смотрим столбец M3 — видно, что удержание январской когорты к апрелю был 41%, а июньской к сентябрю — уже 33%. Падение на 8 п.п. за полгода — повод разбираться: что изменилось в продукте, маркетинге, качестве клиентов.

Продвинутые варианты

выручка удержание вместо клиент удержание

клиент удержание говорит «сколько человек вернулось». выручка удержание говорит «какую долю выручки они принесли относительно первого месяца». Для SaaS это критично: если клиенты уходят, но оставшиеся увеличивают apgrade — картина в выручка может быть лучше, чем в head-count.

Cohort Revenue M0 =
CALCULATE(
    [Revenue],
    TREATAS(
        VALUES(dim_cohort[cohort_month]),
        dim_customer[first_order_cohort]
    ),
    USERELATIONSHIP(dim_customer[first_order_cohort], dim_date[date])
)
// Здесь нужна связь first_order_cohort → date (inactive)
// либо отдельная ветка через CALCULATETABLE

Revenue Retention % =
VAR _revenue_now = [Active Revenue]  // revenue активных клиентов когорты в периоде N
VAR _revenue_baseline = [Cohort Revenue M0]
RETURN
    DIVIDE(_revenue_now, _revenue_baseline)

Для полноценного выручка удержание часто вводят две меры: GRR (Gross выручка удержание — без upgrade-ов) и NRR (Net выручка удержание — с upgrade-ами). Последняя >100% означает, что когорта приносит больше, чем в первом месяце — мечта SaaS-бизнеса.

Weekly cohorts вместо monthly

Для быстрых циклов (SaaS, мобильные приложения) месячные когорты слишком грубые. Нужны недельные или даже дневные. Алгоритм тот же, но вместо first_order_cohort считается первая неделя года/ISO-неделя, период — period_week_number.

Нужна поддержка в dim_date (колонки iso_year + iso_week) и пересчёт _target_month → _target_week. Структура мер не меняется.

Segmented cohorts: разрез по источнику привлечения

удержание может сильно отличаться по каналу: органика держится в 2-3 раза лучше платного трафика. Добавляем фильтр по dim_customer[acquisition_channel] — и видим правду.

// В визуал добавляем slicer или серии по каналу.
// Ничего в DAX не меняется — меры работают как есть,
// просто фильтр по каналу применяется через модель.

Если нужна уже агрегация «средний удержание по каналу на период M6» поверх всех когорт, используйте SUMX-итерации:

Avg M6 Retention by Channel =
AVERAGEX(
    FILTER(
        VALUES(dim_cohort[cohort_month]),
        [Active Customers] > 0
    ),
    CALCULATE([Retention %], dim_periods[period_number] = 6)
)

Типичные ошибки при построении когорт

Считать first_order в DAX на лету. Через EARLIER или FIRSTDATE работает, но тормозит чудовищно на больших базах. Всегда выносите first_order_когорта в ETL и материализуйте в dim_клиент.
Не помечена Date Table. Без этого time intelligence работает через раз. Model → Mark as Date Table → выбрать dim_date[date].
Связь dim_когорта ↔ fact_orders. Не соединяйте их напрямую — это ломает TREATAS-логику. dim_когорта должна быть «осью» визуала, без физических связей.
Забыть фильтр first_order_когорта ≤ cohort_month. Если хотите показывать только будущие покупки когорты, проверьте что меры не учитывают покупки ДО даты first_order — это теоретически невозможно, но при некорректных данных может всплыть.
Period > количества прошедших месяцев. Для февральской когорты M12 — это будущее, показывать не нужно. Добавьте в меру фильтр IF(_target_month <= TODAY(), [Retention %], BLANK()).
Исключить дубли первого месяца. Если клиент купил 2 раза в январе (первой покупки), он в когорте «Январь», в period 0 активен (100%). Всё корректно, но если случайно считаете count рассечений, а не distinct customers — получаете 200% удержание в M0.
Не учитывать возвраты. В ритейле клиент может отменить заказ. Если в fact_orders есть строки с status=canceled, отфильтруйте их — иначе «активные клиенты» включают отменённые заказы.

Бизнес-интерпретация: на что смотреть

Ритейл, e-com. Типичный M1 удержание — 20-40%, M3 — 15-25%, M12 — 10-15%. Смотрим: (а) насколько свежие когорты хуже старых — сигнал о качестве привлечения; (б) есть ли «столб» на M12 (годовая цикличность — люди возвращаются в тот же сезон).

SaaS / подписочный. Здоровый M1 удержание — 80-95%, M12 — 60-75%. Если ниже — продукт недо-доставляет ценность в первый месяц. выручка удержание важнее клиент удержание: NRR > 110% — unicorn-траектория.

HoReCa / доставка. Короткий цикл покупки: M1 удержание нужно считать не месячно, а понедельно. Здоровый W1 удержание — 40-50%, W4 — 25-35%. Провалы говорят о проблемах с качеством обслуживания в конкретные периоды (например, после праздников).

Производительность и оптимизация

Матрица 24 когорты × 24 периода = 576 ячеек. Если модель небольшая (до 100 тыс. клиентов, до 1 млн транзакций), визуал отрисовывается 1-3 секунды. На бо́льших моделях время растёт нелинейно — каждая ячейка считает свой DISTINCTCOUNT. Решения:

Агрегационная таблица. Предрассчитайте agg_cohort_retention в ETL: row = (cohort_month, period_number, customer_count, выручка). DAX-меры читают агрегат. Работает мгновенно, но теряется гибкость фильтров.
Aggregation mappings. Тот же агрегат, но помеченный через Manage Aggregations — Power BI сам выбирает когда идти в факт, когда в агрегат. Гибко и быстро одновременно.
Cutoff когорт. Ограничьте фильтром последние 24 месяца. Более старые когорты редко смотрят, но они тяжелят пересчёт всех ячеек.
VertiPaq-оптимизация. Урежьте cardinality order_date до дня (без времени). Проверьте размер fact_orders в Vertipaq Analyzer — если > 100 MB на 1 млн строк, что-то не так.

Шаги внедрения

Решите единицу времени: месяц, неделя, день. От этого зависит dim_когорта и dim_periods.
В ETL обогатите dim_клиент: first_order_date + first_order_когорта (обязательно как DATE первого числа периода).
Создайте вспомогательные таблицы dim_когорта и dim_periods в модели (можно как Calculated Tables через CALENDAR + GENERATESERIES).
Напишите три базовые меры: когорта Size, Active Customers, удержание %.
Постройте матрицу с условным форматированием (градиент). Добавьте slicers по каналу, сегменту, региону.
Проверьте на нескольких когортах вручную в SQL — убедитесь, что цифры сходятся.
Если модель большая — добавьте агрегационную таблицу и Aggregation mappings.

Что делать дальше

Нужен когортный анализ под ваши данные — 30-минутный звонок. Пришлёте схему источников, за 2-3 недели сделаем production-dashboard.
Модель уже есть, но когорты тормозят — смотрите P13 про рефакторинг, особенно секции про cardinality и агрегации.
Если нужны другие DAX-кейсы — базовый справочник на 15 формул, RLS-паттерны.

Связанные материалы:

DAX для управленцев: 15 формул для 80% задач
Рефакторинг Power BI в 10 раз
BI для e-commerce — где когорты особенно важны