Как посчитать App Uninstall Rate в SQL

Зачем App Uninstall Rate

Uninstall = customer удалил app. Strong negative signal. Mobile games: 60-70% uninstall в первую неделю — норма. SaaS apps: 20-30%. High uninstall = bad UX / wrong audience.

Формула

Uninstall Rate D7 = users_uninstalled_within_7_days / users_installed × 100%

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

WITH installs AS (
    SELECT user_id, installed_at
    FROM app_installs
    WHERE installed_at BETWEEN CURRENT_DATE - INTERVAL '37 days' AND CURRENT_DATE - INTERVAL '7 days'
),
uninstalls AS (
    SELECT DISTINCT user_id, MIN(uninstall_time) AS uninstalled_at
    FROM app_uninstalls
    GROUP BY user_id
)
SELECT
    COUNT(i.user_id) AS installs,
    COUNT(u.user_id) AS uninstalls_30d,
    COUNT(u.user_id)::NUMERIC * 100 / NULLIF(COUNT(i.user_id), 0) AS uninstall_30d_pct
FROM installs i
LEFT JOIN uninstalls u ON u.user_id = i.user_id
  AND u.uninstalled_at <= i.installed_at + INTERVAL '30 days';

D1 / D7 / D30

WITH cohort AS (
    SELECT user_id, installed_at
    FROM app_installs
    WHERE installed_at >= CURRENT_DATE - INTERVAL '60 days'
      AND installed_at < CURRENT_DATE - INTERVAL '30 days'
),
uninstalls AS (
    SELECT user_id, MIN(uninstall_time) AS uninstalled_at
    FROM app_uninstalls
    GROUP BY user_id
)
SELECT
    COUNT(c.user_id) AS installs,
    COUNT(u.user_id) FILTER (WHERE u.uninstalled_at <= c.installed_at + INTERVAL '1 day') AS uninstall_d1,
    COUNT(u.user_id) FILTER (WHERE u.uninstalled_at <= c.installed_at + INTERVAL '7 days') AS uninstall_d7,
    COUNT(u.user_id) FILTER (WHERE u.uninstalled_at <= c.installed_at + INTERVAL '30 days') AS uninstall_d30,
    -- Percentages
    COUNT(u.user_id) FILTER (WHERE u.uninstalled_at <= c.installed_at + INTERVAL '1 day')::NUMERIC * 100 / COUNT(c.user_id) AS d1_pct,
    COUNT(u.user_id) FILTER (WHERE u.uninstalled_at <= c.installed_at + INTERVAL '7 days')::NUMERIC * 100 / COUNT(c.user_id) AS d7_pct,
    COUNT(u.user_id) FILTER (WHERE u.uninstalled_at <= c.installed_at + INTERVAL '30 days')::NUMERIC * 100 / COUNT(c.user_id) AS d30_pct
FROM cohort c
LEFT JOIN uninstalls u USING (user_id);

По acquisition source

SELECT
    i.acquisition_source,
    COUNT(*) AS installs,
    COUNT(u.user_id) AS uninstalls,
    COUNT(u.user_id)::NUMERIC * 100 / COUNT(*) AS uninstall_pct,
    AVG(EXTRACT(EPOCH FROM (u.uninstalled_at - i.installed_at)) / 86400) FILTER (WHERE u.uninstalled_at IS NOT NULL) AS avg_days_to_uninstall
FROM app_installs i
LEFT JOIN app_uninstalls u USING (user_id)
WHERE i.installed_at >= CURRENT_DATE - INTERVAL '90 days'
  AND i.installed_at < CURRENT_DATE - INTERVAL '30 days'
GROUP BY i.acquisition_source
ORDER BY uninstall_pct DESC;

Discount-driven sources обычно высокий uninstall.

По активности перед uninstall

SELECT
    sessions_before_uninstall AS sessions,
    COUNT(*) AS users
FROM (
    SELECT
        u.user_id,
        COUNT(s.session_id) AS sessions_before_uninstall
    FROM app_uninstalls u
    LEFT JOIN app_sessions s ON s.user_id = u.user_id
      AND s.session_time < u.uninstalled_at
    WHERE u.uninstalled_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY u.user_id
) t
GROUP BY sessions_before_uninstall
ORDER BY sessions_before_uninstall;

Many users uninstall after 0-1 sessions — first impression critical.

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

Ошибка 1. iOS uninstall не detectable. iOS doesn't directly report uninstall. Use «no sessions for N days» as proxy.

Ошибка 2. Reinstall same user. Same user reinstalls → reset counter or continue cohort.

Ошибка 3. Direct vs indirect uninstall. Phone wiped vs deliberate uninstall — same signal, different intent.

Ошибка 4. Different platforms compare. iOS uninstall < Android usually (premium audience).

Ошибка 5. Window too short. D1 uninstall — onboarding issue. D30 — engagement issue. Window matters.

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

• Как посчитать D1/D7/D30 retention в SQL
• Как посчитать crash rate в SQL
• Как посчитать inactive users в SQL
• Как посчитать churn в SQL

FAQ

Какой Uninstall Rate ok?

Games D1: 50-60%. SaaS D1: 30-40%. D30 games: 80%+. D30 SaaS: 50-60%.

iOS uninstall track?

Native API не reports. Use «inactive > X days» as proxy.

D1 uninstall high — что значит?

Bad first impression. Crash, no value, bad UX.

Uninstall vs Churn?

Uninstall — explicit delete. Churn — broader (inactive).

Reinstall handling?

Count cohort by first install. Reinstalls = new event, не reset.