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

Зачем WAU/MAU

WAU/MAU — habit для продуктов с weekly cadence (LinkedIn, Notion, Strava). Daily use unrealistic; weekly — true habit signal.

Формула

WAU/MAU = WAU / MAU × 100%

WAU = unique users за last 7 days. MAU = unique users за last 30 days.

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

WITH wau AS (
    SELECT COUNT(DISTINCT user_id) AS wau
    FROM activity
    WHERE DATE >= CURRENT_DATE - INTERVAL '7 days'
),
mau AS (
    SELECT COUNT(DISTINCT user_id) AS mau
    FROM activity
    WHERE DATE >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    w.wau,
    m.mau,
    w.wau::NUMERIC * 100 / NULLIF(m.mau, 0) AS wau_mau_pct
FROM wau w, mau m;

Daily snapshot

WITH days AS (
    SELECT generate_series(
        CURRENT_DATE - INTERVAL '60 days',
        CURRENT_DATE,
        '1 day'::INTERVAL
    )::DATE AS d
)
SELECT
    d.d AS DATE,
    (SELECT COUNT(DISTINCT user_id) FROM activity WHERE DATE BETWEEN d.d - INTERVAL '6 days' AND d.d) AS wau,
    (SELECT COUNT(DISTINCT user_id) FROM activity WHERE DATE BETWEEN d.d - INTERVAL '29 days' AND d.d) AS mau
FROM days d;

(Heavy на large datasets — use materialized views в продакшене.)

По сегментам

SELECT
    u.country,
    COUNT(DISTINCT CASE WHEN a.DATE >= CURRENT_DATE - INTERVAL '7 days' THEN a.user_id END) AS wau,
    COUNT(DISTINCT a.user_id) AS mau,
    COUNT(DISTINCT CASE WHEN a.DATE >= CURRENT_DATE - INTERVAL '7 days' THEN a.user_id END)::NUMERIC * 100
    / NULLIF(COUNT(DISTINCT a.user_id), 0) AS wau_mau_pct
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 wau_mau_pct DESC;

Когда использовать

Daily habit — strict. Weekly — realistic для many products.

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

Ошибка 1. Apply DAU/MAU to weekly product. Notion DAU/MAU 15% звучит плохо. WAU/MAU 50% — true picture.

Ошибка 2. Window misalignment. WAU last 7 days, MAU last 28 days, или 30? Pick one и придерживайся.

Ошибка 3. DAU/WAU. Often missed. Show daily-among-weekly users.

Ошибка 4. Trends compare apples-oranges. WAU/MAU при growing user base — denominator растёт быстрее numerator → looks decline.

Ошибка 5. Cohort matters. New users have artificially high WAU/MAU (recently signed up). Strip.

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

• Как посчитать DAU/MAU в SQL
• Как посчитать stickiness в SQL
• Как посчитать cohort retention в SQL
• Как посчитать active cohort в SQL

FAQ

Какой WAU/MAU ok?

50%+ — strong habit. 30-50% — moderate. <20% — weak.

WAU/MAU vs DAU/MAU?

WAU/MAU relaxed for weekly products. DAU/MAU strict for daily products.

Both метрики track?

Yes. WAU/MAU stable + DAU/MAU growing = healthy.

When 7d vs 14d window?

7d — strict weekly. 14d — bi-weekly cadence (some B2B).

WAU/MAU growing — engagement up?

Маybe. But also could be MAU not growing (new users not coming).