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

Зачем ARPPU

ARPPU = Average Revenue Per Paying User. ARPU размывает revenue across всех users (вкл. free). ARPPU — только monetizing users. Показывает qualityplatящих, не зависит от free user pool size.

Формула

ARPPU = total_revenue / paying_users

«Paying user» = made at least один paid transaction в период.

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

SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount) AS revenue,
    COUNT(DISTINCT user_id) AS paying_users,
    SUM(amount)::NUMERIC / NULLIF(COUNT(DISTINCT user_id), 0) AS arppu
FROM transactions
WHERE status = 'paid'
  AND amount > 0
  AND created_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

ARPU vs ARPPU

WITH revenue AS (
    SELECT SUM(amount) AS total_revenue
    FROM transactions
    WHERE status = 'paid'
      AND created_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
      AND created_at < DATE_TRUNC('month', CURRENT_DATE)
),
all_users AS (
    SELECT COUNT(DISTINCT user_id) AS cnt
    FROM activity
    WHERE DATE >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
      AND DATE < DATE_TRUNC('month', CURRENT_DATE)
),
paying AS (
    SELECT COUNT(DISTINCT user_id) AS cnt
    FROM transactions
    WHERE status = 'paid'
      AND created_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
      AND created_at < DATE_TRUNC('month', CURRENT_DATE)
)
SELECT
    r.total_revenue,
    a.cnt AS all_users,
    p.cnt AS paying_users,
    r.total_revenue::NUMERIC / NULLIF(a.cnt, 0) AS arpu,
    r.total_revenue::NUMERIC / NULLIF(p.cnt, 0) AS arppu,
    p.cnt::NUMERIC * 100 / NULLIF(a.cnt, 0) AS paying_pct
FROM revenue r, all_users a, paying p;

По сегментам

SELECT
    u.country,
    COUNT(DISTINCT t.user_id) AS paying_users,
    SUM(t.amount) AS revenue,
    SUM(t.amount)::NUMERIC / NULLIF(COUNT(DISTINCT t.user_id), 0) AS arppu
FROM transactions t
JOIN users u ON u.user_id = t.user_id
WHERE t.status = 'paid'
  AND t.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY u.country
ORDER BY arppu DESC;

По cohort (acquisition month)

SELECT
    DATE_TRUNC('month', u.registered_at) AS cohort,
    SUM(t.amount) AS cohort_revenue,
    COUNT(DISTINCT t.user_id) AS cohort_paying,
    SUM(t.amount)::NUMERIC / NULLIF(COUNT(DISTINCT t.user_id), 0) AS arppu
FROM transactions t
JOIN users u ON u.user_id = t.user_id
WHERE t.status = 'paid'
  AND u.registered_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

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

Ошибка 1. ARPU vs ARPPU confusion. ARPU includes free users. ARPPU — payers only.

Ошибка 2. Refunds. Exclude refunds или вычесть. Иначе ARPPU inflated.

Ошибка 3. Period choice. ARPPU monthly vs lifetime ARPU очень разные.

Ошибка 4. Multi-product. Один user platит за А и B. Considered one or two paying users?

Ошибка 5. Outliers. Whales (1% pays 50%) — median ARPPU and percentiles важны.

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

• Как посчитать ARPU в SQL
• Как посчитать paying users в SQL
• Как посчитать LTV в SQL
• ARPU vs ARPPU — разница

FAQ

ARPPU vs ARPU?

ARPU = revenue / all users. ARPPU = revenue / paying users. ARPPU > ARPU всегда.

Median ARPPU?

Use median + percentiles. Whales skew average.

ARPPU растёт — хорошо?

Yes — paying users spending more. Но check paying %. Может быть paying base уменьшилась.

ARPPU стабилен, ARPU растёт?

More users monetizing (paying % up). Healthy growth.

ARPPU upsell tracked?

Yes. ARPPU = LTV proxy для paying segment.