Как посчитать customer segments в SQL

Зачем нужна сегментация

Все клиенты разные, но продукт по умолчанию общается с ними одинаково. Сегментация клиентов (customer segments) — это способ разбить базу на группы с похожим поведением и ценностью, чтобы принимать по ним разные решения: кому дать скидку, кого возвращать, кому поднять уровень поддержки, кому показать более дорогой тариф. Подход «одно решение на всех» почти всегда проигрывает: удержание, ценообразование и маркетинг работают лучше, когда заточены под конкретный сегмент.

На собесе аналитика сегментацию любят давать как практическую SQL-задачу: «разбей пользователей на группы по обороту», «найди клиентов в зоне риска оттока», «посчитай RFM». Проверяют не только синтаксис, но и продуктовое мышление — понимаете ли вы, зачем этот разрез бизнесу и что с сегментом делать дальше. Ниже — четыре базовых типа сегментации с готовыми запросами и разбором, где что применяют.

Сегментация по ценности

Самый частый разрез: делим клиентов по тому, сколько денег они приносят. Обычно считают lifetime value (сумму всех оплаченных транзакций) и режут базу на децили функцией NTILE(10). Топ-10% — «киты» (whales), на которых держится значительная доля выручки; их важно удерживать в первую очередь. Такой разрез используют для приоритизации: куда направить усилия поддержки и удержания.

WITH user_value AS (
    SELECT
        user_id,
        SUM(amount) AS lifetime_value      -- суммарная выручка по клиенту
    FROM transactions
    WHERE status = 'paid'
    GROUP BY user_id
)
SELECT
    user_id,
    lifetime_value,
    CASE
        WHEN NTILE(10) OVER (ORDER BY lifetime_value) = 10 THEN 'whale (top 10%)'
        WHEN NTILE(10) OVER (ORDER BY lifetime_value) >= 8 THEN 'high-value'
        WHEN NTILE(10) OVER (ORDER BY lifetime_value) >= 5 THEN 'mid-value'
        ELSE 'low-value'
    END AS value_segment
FROM user_value;

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

Сегментация по поведению

Ценность отвечает на вопрос «сколько платит», поведение — «как пользуется». Здесь смотрят на активность за окно (обычно 30 дней): сколько дней был активен, средняя длительность сессии, сколько раз покупал. По этим признакам собирают понятные группы — от power user до dormant. Такой разрез нужен для продуктовых решений: кого стимулировать к покупке, кого реактивировать.

WITH user_behavior AS (
    SELECT
        user_id,
        COUNT(DISTINCT DATE(activity_time)) AS active_days_30d,      -- сколько дней был активен
        AVG(session_minutes) AS avg_session_min,
        COUNT(*) FILTER (WHERE event = 'purchase') AS purchases_30d  -- покупок за 30 дней
    FROM events
    WHERE activity_time >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    user_id,
    CASE
        WHEN active_days_30d >= 20 AND purchases_30d >= 3 THEN 'power user'
        WHEN active_days_30d >= 10 THEN 'engaged'
        WHEN active_days_30d >= 3 THEN 'casual'
        ELSE 'dormant'
    END AS behavior_segment
FROM user_behavior;

Обратите внимание на COUNT(*) FILTER (WHERE ...) — это условная агрегация: считаем только покупки, не трогая остальные события, без отдельного подзапроса. На собесе умение свернуть несколько метрик в один проход по таблице ценят.

Сегментация по жизненному циклу

Жизненный цикл (lifecycle) отвечает на вопрос «на каком этапе отношений с продуктом клиент». Новичок первой недели, уходящий новичок, активный платящий, отвалившийся платящий — это разные состояния, и работать с ними надо по-разному. Разрез строится на комбинации давности регистрации, недавней активности и факта оплаты. Это главный разрез для удержания: он показывает, кто вот-вот отвалится и кого ещё можно вернуть.

WITH user_lifecycle AS (
    SELECT
        u.user_id,
        u.created_at,
        CURRENT_DATE - u.created_at::DATE AS days_since_signup,
        EXISTS (SELECT 1 FROM events WHERE user_id = u.user_id AND DATE::DATE >= CURRENT_DATE - 7) AS active_7d,
        EXISTS (SELECT 1 FROM transactions WHERE user_id = u.user_id AND status = 'paid') AS ever_paid
    FROM users u
)
SELECT
    user_id,
    CASE
        WHEN days_since_signup < 7 THEN 'new (week 1)'
        WHEN days_since_signup < 30 AND NOT active_7d THEN 'churning new'
        WHEN active_7d AND ever_paid THEN 'active paying'
        WHEN active_7d AND NOT ever_paid THEN 'active non-paying'
        WHEN NOT active_7d AND ever_paid THEN 'lapsed paying'
        ELSE 'dormant'
    END AS lifecycle_segment
FROM user_lifecycle;

Здесь работает EXISTS — он проверяет наличие хотя бы одной строки и не раздувает результат, в отличие от JOIN, который бы задвоил пользователя при нескольких событиях. Это частая ловушка: наивный JOIN вместо EXISTS ломает подсчёт.

RFM

RFM — классика CRM-аналитики. Три оси: Recency (как давно покупал), Frequency (как часто), Monetary (на сколько). По каждой оси клиент получает балл от 1 до 5 (через NTILE(5)), а из комбинации баллов складываются осмысленные группы: champions, loyal, at risk, lost. Важная тонкость с recency — чем свежее покупка, тем лучше, поэтому балл инвертируется: 6 - NTILE(5).

WITH rfm_stats AS (
    SELECT
        user_id,
        CURRENT_DATE - MAX(transaction_date)::DATE AS recency_days,  -- дней с последней покупки
        COUNT(*) AS frequency,                                        -- сколько покупок
        SUM(amount) AS monetary                                       -- на какую сумму
    FROM transactions
    WHERE status = 'paid'
      AND transaction_date >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY user_id
),
rfm_scored AS (
    SELECT
        user_id,
        recency_days,
        frequency,
        monetary,
        6 - NTILE(5) OVER (ORDER BY recency_days) AS r_score,  -- меньше дней = выше балл
        NTILE(5) OVER (ORDER BY frequency) AS f_score,
        NTILE(5) OVER (ORDER BY monetary) AS m_score
    FROM rfm_stats
)
SELECT
    user_id,
    r_score, f_score, m_score,
    r_score * 100 + f_score * 10 + m_score AS rfm_code,
    CASE
        WHEN r_score = 5 AND f_score >= 4 AND m_score >= 4 THEN 'champions'
        WHEN r_score >= 3 AND f_score >= 3 THEN 'loyal'
        WHEN r_score <= 2 AND f_score >= 3 THEN 'at risk'   -- покупал часто, но давно — зона риска
        WHEN r_score <= 2 AND f_score <= 2 THEN 'lost'
        ELSE 'other'
    END AS rfm_segment
FROM rfm_scored;

Самый ценный сегмент здесь — «at risk»: клиенты, которые раньше покупали часто, но давно не возвращались. Это те, на кого стоит потратить бюджет удержания в первую очередь, пока они не перешли в «lost». Подробный разбор RFM — в отдельной статье.

Комбинация сегментов

Один разрез редко даёт полную картину — сила сегментации в пересечениях. Например, комбинация ценности и поведения показывает, где деньги и где риск: «high-value + dormant» — это клиенты, которые много платили и вдруг перестали заходить, самый дорогой сигнал оттока. Такой сводный запрос удобно строить поверх заранее посчитанной витрины сегментов.

SELECT
    value_segment,
    behavior_segment,
    COUNT(*) AS users,
    AVG(lifetime_value) AS avg_ltv
FROM user_segments
GROUP BY value_segment, behavior_segment
ORDER BY users DESC;

Частые ошибки

Одна универсальная сегментация на всё. Разные решения требуют разных разрезов: ценность — для приоритизации, поведение — для продуктовых стимулов, жизненный цикл — для удержания. Пытаться закрыть все задачи одним разрезом — значит не закрыть ни одну.

Статичные сегменты. Клиенты переходят из группы в группу: сегодня «champion», через два месяца «at risk». Если пересчитывать сегменты раз в квартал, вы работаете с устаревшей картиной. Обновляйте регулярно, в идеале ежедневно.

Слишком много сегментов. 20+ групп выглядят детально, но с ними невозможно работать: под каждую не придумаешь отдельную кампанию. Цельтесь в 4–8 действенных сегментов.

Границы «с потолка». Считать «китом» того, кто потратил 1000 или 5000, — вопрос без ответа, если брать числа наугад. Границы лучше выводить из данных: перцентили, децили, квартили адаптируются под реальное распределение.

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

Связанные темы

• Как посчитать RFM segmentation в SQL
• Как посчитать LTV в SQL
• Как посчитать retention в SQL
• Как посчитать propensity score в SQL

FAQ

Какая сегментация важнее — по ценности, поведению или жизненному циклу?

Зависит от задачи. Жизненный цикл — для удержания и работы с оттоком. Ценность — для приоритизации, куда вложить усилия. Поведение — для продуктовых стимулов. Сильный аналитик использует несколько разрезов и их пересечения, а не выбирает один.

Статичные или динамические сегменты?

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

Сколько сегментов оптимально?

4–8 действенных. Больше 10 — как правило, перебор: под каждый сегмент не получится придумать отдельное действие, и разрез превращается в отчёт ради отчёта.

Границы сегментов задаются произвольно?

Лучше не произвольно, а от распределения данных: перцентили, децили, квартили (NTILE). Тогда границы адаптируются под реальную базу, а сегменты остаются осмысленными при изменении метрик.

Как персонализировать работу по сегментам?

Под сегмент подстраивают контент писем, ценообразование, уровень поддержки и предложения. Например, «at risk» получают возвращающую кампанию, «champions» — программу лояльности, «active non-paying» — стимул к первой оплате.

Тренируйте SQL для аналитики — откройте тренажёр с 1500+ вопросами для собесов.