Как посчитать Inactive Users в SQL

Зачем Inactive Users

Inactive users — users без activity за X days. Pre-churn signal. Re-engage до churn — easier чем re-activate churned. Inactive count growing → potential churn ahead.

Threshold inactive

Зависит от product cadence:

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

WITH last_activity AS (
    SELECT
        user_id,
        MAX(activity_date) AS last_active_date
    FROM activity
    GROUP BY user_id
)
SELECT
    COUNT(*) AS total_users,
    COUNT(*) FILTER (WHERE last_active_date < CURRENT_DATE - INTERVAL '7 days') AS inactive_7d,
    COUNT(*) FILTER (WHERE last_active_date < CURRENT_DATE - INTERVAL '30 days') AS inactive_30d,
    COUNT(*) FILTER (WHERE last_active_date < CURRENT_DATE - INTERVAL '90 days') AS inactive_90d,
    COUNT(*) FILTER (WHERE last_active_date < CURRENT_DATE - INTERVAL '180 days') AS inactive_180d
FROM last_activity;

По сегментам

WITH inactivity AS (
    SELECT
        u.user_id,
        u.country,
        u.plan,
        MAX(a.activity_date) AS last_active,
        CURRENT_DATE - MAX(a.activity_date) AS days_inactive
    FROM users u
    LEFT JOIN activity a USING (user_id)
    WHERE u.created_at < CURRENT_DATE - INTERVAL '90 days'  -- exclude new
    GROUP BY u.user_id, u.country, u.plan
)
SELECT
    plan,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE days_inactive > 30) AS inactive_30d,
    COUNT(*) FILTER (WHERE days_inactive > 30)::NUMERIC * 100 / COUNT(*) AS inactive_pct
FROM inactivity
GROUP BY plan
ORDER BY inactive_pct DESC;

Dormant vs Churned

WITH stages AS (
    SELECT
        user_id,
        CASE
            WHEN days_inactive <= 7 THEN 'active'
            WHEN days_inactive <= 30 THEN 'at-risk'
            WHEN days_inactive <= 90 THEN 'dormant'
            ELSE 'churned'
        END AS stage
    FROM (
        SELECT user_id, CURRENT_DATE - MAX(activity_date) AS days_inactive
        FROM activity
        GROUP BY user_id
    ) t
)
SELECT
    stage,
    COUNT(*) AS users,
    COUNT(*)::NUMERIC * 100 / SUM(COUNT(*)) OVER () AS pct
FROM stages
GROUP BY stage
ORDER BY MIN(CASE stage
    WHEN 'active' THEN 1 WHEN 'at-risk' THEN 2
    WHEN 'dormant' THEN 3 WHEN 'churned' THEN 4 END);

Re-engagement targeting

Get list of users в at-risk / dormant для targeted campaign:

SELECT
    u.user_id,
    u.email,
    a.last_active,
    CURRENT_DATE - a.last_active AS days_inactive,
    -- Likelihood proxy
    (SELECT COUNT(*) FROM orders WHERE user_id = u.user_id AND status = 'paid') AS lifetime_orders,
    (SELECT SUM(amount) FROM transactions WHERE user_id = u.user_id AND status = 'paid') AS lifetime_value
FROM users u
JOIN (
    SELECT user_id, MAX(activity_date) AS last_active
    FROM activity
    GROUP BY user_id
) a USING (user_id)
WHERE CURRENT_DATE - a.last_active BETWEEN 14 AND 30
  AND u.email IS NOT NULL
ORDER BY lifetime_value DESC
LIMIT 1000;

Prioritize high-LTV inactives.

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

Ошибка 1. New users считать inactive. Just signed up, не logged in для X days — но не churned. Exclude users < X days old.

Ошибка 2. Wrong threshold. 30 days inactive для daily app = late. 30 days inactive для annual product = normal.

Ошибка 3. Activity definition. «Login» != «engagement». Track meaningful actions, not just login.

Ошибка 4. Email opens count? Email open = passive. App use = active. Separate.

Ошибка 5. Bot traffic. Bots have predictable activity. Filter.

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

• Как посчитать churn в SQL
• Как посчитать win-back rate в SQL
• Как посчитать reactivation в SQL
• Как посчитать customer tenure в SQL

FAQ

Inactive vs Churned?

Inactive — temporal (not active). Churned — permanent (canceled или > threshold).

Threshold как выбирать?

Look at usage histograms. Если 95% active users used product в last 7 days, 7 days = good threshold.

Inactive users — % normal?

Mature product: 20-40% inactive monthly. SaaS B2B: 10-20%. Mobile games: 60%+.

Re-engagement window?

Sweet spot — 14-30 days inactive. Earlier = too pushy. Later = too late.

Inactive ≠ no value?

LTV users могут быть inactive temporarily. Don't auto-write-off.