Как посчитать Monthly Active Customers в SQL

Зачем MAC

MAC = Monthly Active Customers — users, paid AND active в last month. Better health indicator чем MAU (which includes inactive paid). Critical для subscription businesses.

MAC vs MAU

MAC focus on monetized users. MAU includes free / inactive.

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

WITH paying_users AS (
    SELECT DISTINCT user_id
    FROM transactions
    WHERE status = 'paid'
      AND created_at <= CURRENT_DATE
),
active_users AS (
    SELECT DISTINCT user_id
    FROM activity
    WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    COUNT(*) AS total_paying_users,
    COUNT(au.user_id) AS monthly_active_customers,
    COUNT(au.user_id)::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS mac_pct_of_paying
FROM paying_users pu
LEFT JOIN active_users au USING (user_id);

Тренд

WITH days AS (
    SELECT generate_series(
        CURRENT_DATE - INTERVAL '90 days',
        CURRENT_DATE,
        '1 day'::INTERVAL
    )::DATE AS d
)
SELECT
    d.d AS snapshot_date,
    (SELECT COUNT(DISTINCT a.user_id)
     FROM activity a
     JOIN transactions t ON t.user_id = a.user_id AND t.status = 'paid' AND t.created_at <= d.d
     WHERE a.DATE BETWEEN d.d - INTERVAL '29 days' AND d.d) AS mac
FROM days d
ORDER BY d.d;

По cohort

WITH first_paid AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month
    FROM transactions
    WHERE status = 'paid'
    GROUP BY user_id
)
SELECT
    fp.cohort_month,
    COUNT(*) AS cohort_size,
    COUNT(*) FILTER (
        WHERE EXISTS (
            SELECT 1 FROM activity
            WHERE user_id = fp.user_id
              AND DATE >= CURRENT_DATE - INTERVAL '30 days'
        )
    ) AS active_now,
    COUNT(*) FILTER (
        WHERE EXISTS (
            SELECT 1 FROM activity
            WHERE user_id = fp.user_id
              AND DATE >= CURRENT_DATE - INTERVAL '30 days'
        )
    )::NUMERIC * 100 / COUNT(*) AS mac_retention_pct
FROM first_paid fp
GROUP BY fp.cohort_month
ORDER BY fp.cohort_month;

Quality of activity

WITH activity_quality AS (
    SELECT
        u.user_id,
        COUNT(DISTINCT DATE(a.activity_time)) AS active_days,
        SUM(CASE WHEN e.event_type = 'core_action' THEN 1 ELSE 0 END) AS core_actions
    FROM users u
    JOIN transactions t ON t.user_id = u.user_id AND t.status = 'paid'
    LEFT JOIN activity a ON a.user_id = u.user_id AND a.DATE >= CURRENT_DATE - INTERVAL '30 days'
    LEFT JOIN events e ON e.user_id = u.user_id AND e.event_time >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY u.user_id
)
SELECT
    CASE
        WHEN active_days = 0 THEN 'paying, inactive'
        WHEN active_days < 5 THEN 'paying, light'
        WHEN active_days < 15 THEN 'paying, regular'
        ELSE 'paying, power'
    END AS engagement_segment,
    COUNT(*) AS customers,
    SUM(core_actions) AS total_actions
FROM activity_quality
GROUP BY 1
ORDER BY MIN(active_days);

«Paying inactive» — silent churn risk. High % здесь = problem.

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

Ошибка 1. MAC = MAU + paying status. Both активность AND paying status required.

Ошибка 2. Snapshot vs trailing 30. «Active в last 30 days» vs «active right now». Different.

Ошибка 3. Cancelled subscriptions counted. User cancelled, остался active 30 days в access — count? Define.

Ошибка 4. Activity definition. Login = active? Or specific events? Define crisp.

Ошибка 5. Multi-currency. Paying customers across currencies. Track converted USD revenue.

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

• Как посчитать DAU/MAU в SQL
• Как посчитать paying users в SQL
• Как посчитать inactive users в SQL
• Как посчитать engagement rate в SQL

FAQ

MAC vs MAU?

MAC paying. MAU все active (free + paid).

Какой MAC ok?

Subscription products: 80%+ paying customers should be MAC. Below — silent churn looming.

Snapshot vs cohort?

Both. Snapshot — current. Cohort — retention trajectory.

Multi-product MAC?

If sells multiple subscriptions: per-product MAC + total MAC.

MAC trending down?

Either fewer payings (churn) or fewer active (engagement issue). Drill.