PrepBro
← Назад к вопросам

SQL: Расчет ARPU по источникам трафика

2.0 Middle🔥 161 комментариев
#SQL и базы данных#Атрибуция и маркетинг

Условие

У вас есть таблица users:

  • user_id (integer)
  • registration_date (date)
  • traffic_source (varchar)

И таблица payments:

  • payment_id (integer)
  • user_id (integer)
  • amount (decimal)
  • payment_date (date)

Посчитайте ARPU (Average Revenue Per User) по источникам трафика.

Ожидаемый результат:

Таблица с колонками: traffic_source, users_count, total_revenue, arpu

Источник: задача для маркетинговой аналитики

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

SQL: Расчет ARPU по источникам трафика

Базовый расчет ARPU

SELECT
    u.traffic_source,
    COUNT(DISTINCT u.user_id) as users_count,
    ROUND(SUM(p.amount), 2) as total_revenue,
    ROUND(SUM(p.amount) / COUNT(DISTINCT u.user_id), 2) as arpu
FROM users u
LEFT JOIN payments p ON u.user_id = p.user_id
GROUP BY u.traffic_source
ORDER BY arpu DESC;

С учетом конверсии платящих пользователей

SELECT
    u.traffic_source,
    COUNT(DISTINCT u.user_id) as total_users,
    COUNT(DISTINCT p.user_id) as paying_users,
    ROUND(100.0 * COUNT(DISTINCT p.user_id) / COUNT(DISTINCT u.user_id), 2) as conversion_pct,
    ROUND(SUM(COALESCE(p.amount, 0)), 2) as total_revenue,
    ROUND(SUM(COALESCE(p.amount, 0)) / COUNT(DISTINCT u.user_id), 2) as arpu,
    ROUND(SUM(COALESCE(p.amount, 0)) / COUNT(DISTINCT p.user_id), 2) as arppu
FROM users u
LEFT JOIN payments p ON u.user_id = p.user_id
GROUP BY u.traffic_source
ORDER BY arpu DESC;

Метрики:

  • ARPU = Revenue / All Users (включая неплатящих)
  • ARPPU = Revenue / Paying Users (только платящие)
  • Conversion % = Paying Users / Total Users

По когортам (месяцы регистрации)

SELECT
    u.traffic_source,
    DATE_TRUNC(u.registration_date, MONTH) as signup_month,
    COUNT(DISTINCT u.user_id) as users_count,
    ROUND(SUM(p.amount), 2) as total_revenue,
    ROUND(SUM(p.amount) / COUNT(DISTINCT u.user_id), 2) as arpu
FROM users u
LEFT JOIN payments p ON u.user_id = p.user_id
GROUP BY u.traffic_source, DATE_TRUNC(u.registration_date, MONTH)
ORDER BY signup_month DESC, arpu DESC;

Анализ по типам пользователей

WITH user_stats AS (
    SELECT u.user_id, u.traffic_source, COUNT(p.payment_id) as payment_count, SUM(p.amount) as user_revenue
    FROM users u
    LEFT JOIN payments p ON u.user_id = p.user_id
    GROUP BY u.user_id, u.traffic_source
)
SELECT
    traffic_source,
    COUNT(*) as total_users,
    SUM(CASE WHEN payment_count = 0 THEN 1 ELSE 0 END) as non_payers,
    SUM(CASE WHEN payment_count = 1 THEN 1 ELSE 0 END) as one_time,
    SUM(CASE WHEN payment_count > 1 THEN 1 ELSE 0 END) as repeat,
    ROUND(SUM(COALESCE(user_revenue, 0)), 2) as total_revenue,
    ROUND(SUM(COALESCE(user_revenue, 0)) / COUNT(*), 2) as arpu
FROM user_stats
GROUP BY traffic_source
ORDER BY arpu DESC;

Интерпретация

Высокий ARPU означает:

  • Качественный трафик
  • Пользователи с высокой платежеспособностью
  • Эффективная монетизация

Низкий ARPU означает:

  • Неправильный таргетинг
  • Нужна оптимизация воронки
  • Проблемы с качеством трафика

Правило прибыльности: ARPU должен быть > CAC (Customer Acquisition Cost)

SQL: Расчет ARPU по источникам трафика | PrepBro