Как посчитать DAU/MAU в SQL

Зачем DAU/MAU

DAU/MAU (stickiness) — habit metric. % MAU returning daily. Facebook 50%+, Instagram 50%+, Snapchat 50%+. SaaS типично 20-30%. Below 10% — weak habit.

Формула

DAU/MAU = DAU / MAU × 100%

DAU и MAU за same day (DAU = today, MAU = last 30 days including today).

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

WITH dau AS (
    SELECT
        DATE,
        COUNT(DISTINCT user_id) AS dau
    FROM activity
    WHERE DATE >= CURRENT_DATE - INTERVAL '60 days'
    GROUP BY DATE
),
mau AS (
    SELECT
        DATE,
        COUNT(DISTINCT user_id) AS mau
    FROM (
        SELECT
            d.DATE,
            a.user_id
        FROM (
            SELECT DISTINCT DATE
            FROM activity
            WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
        ) d
        JOIN activity a
          ON a.DATE BETWEEN d.DATE - INTERVAL '29 days' AND d.DATE
    ) AS x
    GROUP BY DATE
)
SELECT
    d.DATE,
    d.dau,
    m.mau,
    d.dau::NUMERIC * 100 / NULLIF(m.mau, 0) AS dau_mau_pct
FROM dau d
JOIN mau m ON m.DATE = d.DATE
ORDER BY d.DATE;

Простой alternative (если activity огромный):

SELECT
    DATE_TRUNC('day', activity_date) AS day,
    COUNT(DISTINCT user_id) FILTER (WHERE activity_date = DATE_TRUNC('day', activity_date)) AS dau,
    COUNT(DISTINCT user_id) AS mau_window,
    -- DAU / MAU
    COUNT(DISTINCT user_id) FILTER (WHERE activity_date = DATE_TRUNC('day', activity_date))::NUMERIC * 100
    / NULLIF(COUNT(DISTINCT user_id), 0) AS stickiness
FROM activity
WHERE activity_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1 DESC
LIMIT 1;

По сегментам

SELECT
    u.country,
    COUNT(DISTINCT a.user_id) FILTER (WHERE a.DATE = CURRENT_DATE - INTERVAL '1 day') AS dau,
    COUNT(DISTINCT a.user_id) AS mau,
    COUNT(DISTINCT a.user_id) FILTER (WHERE a.DATE = CURRENT_DATE - INTERVAL '1 day')::NUMERIC * 100
    / NULLIF(COUNT(DISTINCT a.user_id), 0) AS stickiness
FROM activity a
JOIN users u ON u.user_id = a.user_id
WHERE a.DATE >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY u.country
ORDER BY stickiness DESC;

Тренд

WITH daily AS (
    SELECT
        a.DATE,
        COUNT(DISTINCT a.user_id) AS dau,
        (SELECT COUNT(DISTINCT user_id)
         FROM activity
         WHERE DATE BETWEEN a.DATE - INTERVAL '29 days' AND a.DATE) AS mau
    FROM activity a
    WHERE a.DATE >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY a.DATE
)
SELECT
    DATE,
    dau,
    mau,
    dau::NUMERIC * 100 / NULLIF(mau, 0) AS stickiness,
    -- 7-day MA для stability
    AVG(dau::NUMERIC * 100 / NULLIF(mau, 0)) OVER (
        ORDER BY DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS stickiness_ma7
FROM daily
ORDER BY DATE;

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

Ошибка 1. MAU = trailing 30 days. Not «MAU at end of month». Use rolling 30 days for daily computation.

Ошибка 2. Confused with WAU/MAU. WAU/MAU другая метрика (weak habit threshold).

Ошибка 3. New users скew. First-day signups inflate DAU. Cohort-based stickiness more honest.

Ошибка 4. Bot / spam traffic. Filter to real users. Bots crank DAU but не habit.

Ошибка 5. Multi-device. Same user, multiple devices → multiple user_ids? ID resolution.

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

• Как посчитать stickiness в SQL
• Как посчитать WAU/MAU в SQL
• Как посчитать cohort retention в SQL
• Метрики продукта: DAU/MAU/ARPU

FAQ

Какой DAU/MAU ok?

Social: 50%+. Messaging: 60%+. SaaS: 20-30%. Below 10% — habit weak.

DAU/MAU vs WAU/MAU?

DAU/MAU strict daily habit. WAU/MAU — weekly habit (more relaxed).

Stickiness падает — что значит?

Either DAU down (engagement drop) или MAU up too fast (новые users not retaining).

Why 30 days for MAU?

Convention. Could be 28 days (4 weeks). 30 — calendar match.

DAU/MAU per segment?

Yes! Segment by acquisition channel, country, tenure. Diff stickiness per segment.