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

Зачем Customer Tenure

Customer Tenure = время от signup до сегодня (active) или до churn date. Average tenure × ARPPU = quick LTV proxy. Tenure growth — retention working.

Формула

Tenure (active) = today - signup_date
Tenure (churned) = churned_at - signup_date

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

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;

Aggregate:

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;

Distribution

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 days'
        WHEN days <= 90 THEN '31-90 days'
        WHEN days <= 180 THEN '91-180 days'
        WHEN days <= 365 THEN '181-365 days'
        ELSE '365+ days'
    END AS tenure_bucket,
    COUNT(*) AS users,
    COUNT(*)::NUMERIC * 100 / SUM(COUNT(*)) OVER () AS pct
FROM tenure
GROUP BY 1
ORDER BY MIN(days);

Active vs churned 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;

Active users часто имеют longer tenure (selection bias — short-lived churned first).

Tenure × ARPPU (LTV proxy)

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;

По acquisition source

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;

Long-tenure channels — invest more. Short-tenure — low quality / mismatch.

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

Ошибка 1. Survivor bias. Avg tenure только active users → overestimate. Include churned.

Ошибка 2. Recently-acquired skew. Last month signups have tenure < 1 month. Filter or bucket.

Ошибка 3. Tenure ≠ retention. Tenure days don't mean active days. Engaged tenure separate.

Ошибка 4. Subscription pause. Paused subscription — counted in tenure? Define.

Ошибка 5. ID resolution. Same person re-signed up. Same tenure or separate?

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

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

FAQ

Tenure vs retention?

Tenure — calendar age. Retention — % active at time X.

Tenure days vs months?

Days — granular. Months — readable. Convert when reporting.

Negative tenure?

Bug в data (signup_date > churned_at). Fix.

Tenure inflated by inactive accounts?

Yes — paused / sleeping accounts. Filter by «engaged tenure».

B2B tenure?

Longer than B2C обычно. Contracts annual, multi-year.