Как посчитать Customer Tenure в SQL

Зачем Customer Tenure

Customer Tenure — это срок жизни клиента: сколько времени прошло от регистрации до сегодняшнего дня (для активного) или до момента оттока (для ушедшего). Метрика показывает, как долго люди остаются с продуктом, и служит быстрым индикатором здоровья удержания: если средний tenure по свежим когортам растёт, значит retention улучшается.

Практическая польза в двух вещах. Во-первых, tenure — это грубый прокси LTV: средний срок жизни, умноженный на средний доход с платящего (ARPPU), даёт прикидку пожизненной ценности клиента без построения полной модели. Во-вторых, tenure в разрезе каналов привлечения показывает, откуда приходят «долгие» клиенты, а откуда — те, кто быстро отваливается; это прямой сигнал, куда перекладывать маркетинговый бюджет. На собесе по продуктовой/retention-аналитике важно не столько написать формулу, сколько не попасться на survivor bias — об этом ниже.

Формула

Tenure (активный) = сегодня - дата регистрации
Tenure (ушедший)  = дата оттока - дата регистрации

Ключевой момент — граница отсчёта зависит от статуса клиента. Для активного tenure «тикает» до текущей даты, для ушедшего фиксируется на дате оттока. Смешивать их нельзя: если считать всем tenure до сегодняшнего дня, ушедшим клиентам «нарастёт» лишнее время, которого они с продуктом не провели.

Базовый расчёт

SELECT
    user_id,
    signup_date,
    CASE
        WHEN churned_at IS NULL THEN CURRENT_DATE - signup_date  -- активный: до сегодня
        ELSE churned_at::DATE - signup_date                      -- ушедший: до даты оттока
    END AS tenure_days
FROM users
WHERE signup_date IS NOT NULL;

Агрегаты по всей базе — среднее и медиана. Медиану считаем через PERCENTILE_CONT, потому что среднее сильно тянут вверх редкие «старожилы»:

SELECT
    COUNT(*) AS total_users,
    AVG(CASE WHEN churned_at IS NULL THEN CURRENT_DATE - signup_date ELSE churned_at::DATE - signup_date END) AS avg_tenure_days,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY CASE WHEN churned_at IS NULL THEN CURRENT_DATE - signup_date ELSE churned_at::DATE - signup_date END
    ) AS median_tenure_days
FROM users;

Медиана и среднее вместе информативнее одной цифры: если среднее заметно выше медианы, значит распределение перекошено «длинным хвостом» лояльных клиентов, и ориентироваться только на среднее опасно.

Распределение по бакетам

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

WITH tenure AS (
    SELECT
        user_id,
        CASE WHEN churned_at IS NULL THEN CURRENT_DATE - signup_date ELSE churned_at::DATE - signup_date END AS days
    FROM users
)
SELECT
    CASE
        WHEN days <= 30 THEN '0-30 дней'
        WHEN days <= 90 THEN '31-90 дней'
        WHEN days <= 180 THEN '91-180 дней'
        WHEN days <= 365 THEN '181-365 дней'
        ELSE '365+ дней'
    END AS tenure_bucket,
    COUNT(*) AS users,
    COUNT(*)::NUMERIC * 100 / SUM(COUNT(*)) OVER () AS pct  -- доля бакета от всех
FROM tenure
GROUP BY 1
ORDER BY MIN(days);

Оконная функция SUM(COUNT(*)) OVER () в знаменателе даёт долю каждого бакета от общего числа — без отдельного подзапроса на тотал. Такой профиль распределения на собесе ценят выше, чем одно среднее: он показывает, есть ли ранний отток и как быстро формируется ядро лояльных.

Tenure активных и ушедших

SELECT
    is_active,
    COUNT(*) AS users,
    AVG(tenure_days) AS avg_tenure,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tenure_days) AS median_tenure
FROM (
    SELECT
        user_id,
        CASE WHEN churned_at IS NULL THEN TRUE ELSE FALSE END AS is_active,
        CASE WHEN churned_at IS NULL THEN CURRENT_DATE - signup_date ELSE churned_at::DATE - signup_date END AS tenure_days
    FROM users
) t
GROUP BY is_active;

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

Tenure × ARPPU (прокси LTV)

Быстрая прикидка пожизненной ценности: соединяем tenure с выручкой на клиента и приводим к годовому масштабу.

WITH user_metrics AS (
    SELECT
        u.user_id,
        CASE WHEN u.churned_at IS NULL THEN CURRENT_DATE - u.signup_date ELSE u.churned_at::DATE - u.signup_date END AS tenure_days,
        COALESCE(SUM(t.amount), 0) AS revenue  -- суммарная выручка по оплаченным транзакциям
    FROM users u
    LEFT JOIN transactions t ON t.user_id = u.user_id AND t.status = 'paid'
    GROUP BY u.user_id, u.churned_at, u.signup_date
)
SELECT
    AVG(tenure_days) AS avg_tenure,
    AVG(revenue) AS avg_revenue_per_user,
    AVG(revenue / NULLIF(tenure_days, 0) * 365) AS annualized_revenue_per_user  -- нормируем к году
FROM user_metrics
WHERE tenure_days > 0;

Это именно прокси, а не строгий LTV: он игнорирует дисконтирование будущих потоков и предполагает, что доход распределён по tenure равномерно. Для быстрой оценки юнит-экономики на собесе такого приближения обычно достаточно, но оговорить его ограничения стоит. NULLIF(tenure_days, 0) здесь защищает от деления на ноль для клиентов, зарегистрировавшихся сегодня.

Tenure по каналам привлечения

SELECT
    acquisition_source,
    COUNT(*) AS users,
    AVG(CASE WHEN churned_at IS NULL THEN CURRENT_DATE - signup_date ELSE churned_at::DATE - signup_date END) AS avg_tenure_days
FROM users
WHERE signup_date >= CURRENT_DATE - INTERVAL '24 months'
GROUP BY acquisition_source
ORDER BY avg_tenure_days DESC;

Разрез по каналам отвечает на денежный вопрос: откуда приходят клиенты, которые остаются надолго. Каналы с высоким средним tenure заслуживают большего бюджета, каналы с коротким — это либо низкое качество трафика, либо несоответствие ожиданий (product-market fit для этой аудитории хуже). Фильтр по последним 24 месяцам отсекает совсем древние когорты, у которых другая динамика продукта.

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

Survivor bias. Средний tenure только по активным клиентам завышает картину: вы считаете лишь выживших и не учитываете тех, кто быстро ушёл. Всегда включайте ушедших (с tenure до даты оттока), иначе метрика систематически оптимистична.

Перекос свежими когортами. Клиенты, зарегистрировавшиеся в прошлом месяце, физически не могут иметь tenure больше месяца. Если их много, они занижают среднее. Либо фильтруйте по дате регистрации, либо смотрите распределение по бакетам, а не одно среднее.

Tenure ≠ retention. Календарный tenure не означает, что клиент всё это время был активен. Человек мог зарегистрироваться год назад и не заходить полгода. Если важна вовлечённость — считайте «engaged tenure» по активным дням отдельно.

Паузы подписки. Если подписку можно приостановить, решите заранее: входит период паузы в tenure или нет. От этого зависит цифра, и определение надо зафиксировать в документации метрики.

Склейка идентификаторов. Один и тот же человек мог зарегистрироваться повторно под новым ID. Считать это одним клиентом с непрерывным tenure или двумя разными — вопрос определения, который стоит проговорить до расчёта.

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

• Как посчитать retention в SQL
• Как посчитать churn в SQL
• Как посчитать LTV в SQL
• Как посчитать reactivation в SQL

FAQ

Чем tenure отличается от retention?

Tenure — это календарный возраст клиента: сколько времени прошло с регистрации до оттока или до сегодня. Retention — доля клиентов когорты, остающихся активными на момент X (день 7, день 30). Tenure измеряет длительность отношений одного клиента, retention — выживаемость когорты в целом.

В днях или в месяцах считать tenure?

В днях считать точнее и удобнее для арифметики и бакетов. В отчётах и презентациях цифры обычно переводят в месяцы или годы — так читаемее. Практично хранить tenure в днях, а при выводе конвертировать в нужную единицу.

Откуда берётся отрицательный tenure?

Отрицательное значение означает, что дата регистрации оказалась позже даты оттока (signup_date > churned_at) — это баг в данных, а не реальная ситуация. Такие строки надо не прятать, а найти причину: ошибку загрузки, перепутанные поля или разные источники дат. До исправления их лучше исключать из агрегатов.

Не завышают ли tenure спящие аккаунты?

Да. У приостановленных или давно неактивных аккаунтов календарный tenure продолжает расти, хотя клиент фактически не пользуется продуктом. Если важна реальная вовлечённость, считайте «engaged tenure» — по дням с активностью, а не по календарю, и определите порог, после которого аккаунт считается спящим.

Отличается ли tenure в B2B и B2C?

Как правило, в B2B tenure заметно выше: контракты годовые или многолетние, а порог переключения на другого поставщика высокий. В B2C отношения короче и волатильнее. Поэтому бенчмарки tenure имеет смысл сравнивать только внутри одной модели, а не переносить B2B-цифры на B2C и наоборот.