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

Зачем active creators

Active creators — те, кто реально публикует content. North Star для UGC-платформ. YouTube measure «monthly active creators», TikTok «daily active creators». В отличие от DAU (consumers), DAC сильно меньше — обычно 5-10% of DAU.

DAC, WAC, MAC

• DAC (Daily Active Creators) — опубликовал 1+ post today
• WAC (Weekly Active Creators) — 1+ post в неделю
• MAC (Monthly Active Creators) — 1+ post в месяц

Stickiness = DAC / MAC (как DAU / MAU).

Active creators в SQL

SELECT
    DATE_TRUNC('day', created_at)::DATE AS day,
    COUNT(DISTINCT author_id) AS dac,
    COUNT(DISTINCT CASE WHEN created_at >= DATE_TRUNC('day', created_at) - INTERVAL '7 days' THEN author_id END) AS wac,
    COUNT(DISTINCT CASE WHEN created_at >= DATE_TRUNC('day', created_at) - INTERVAL '30 days' THEN author_id END) AS mac
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '60 days'
GROUP BY DATE_TRUNC('day', created_at);

Проще считать отдельными запросами для каждого window.

Stickiness creators

WITH daily_creators AS (
    SELECT DATE_TRUNC('day', created_at)::DATE AS day, author_id
    FROM posts
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY DATE_TRUNC('day', created_at), author_id
),
mac_set AS (
    SELECT DISTINCT author_id FROM posts WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    COUNT(DISTINCT d.author_id) AS dac_average,
    (SELECT COUNT(*) FROM mac_set) AS mac,
    COUNT(DISTINCT d.author_id) * 30.0 / 30 / NULLIF((SELECT COUNT(*) FROM mac_set), 0) AS stickiness
FROM daily_creators d;

Stickiness 0.1 = средне (10% MAC posts each day). 0.3+ = excellent.

Тренд

SELECT
    DATE_TRUNC('week', created_at)::DATE AS week,
    COUNT(DISTINCT author_id) AS wac
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '26 weeks'
GROUP BY DATE_TRUNC('week', created_at)
ORDER BY week;

WAC растёт = healthy. Flat / падает — flag.

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

Ошибка 1. Считать MAU как MAC. DAU и DAC — разные. Consumer ≠ creator.

Ошибка 2. Включать spam bots. Spam bot posts много, но не «real creator».

Ошибка 3. WAC vs MAC interchangeable. MAC всегда ≥ WAC ≥ DAC. Если WAC > MAC — bug.

Ошибка 4. Single window comparison. Compare 30-day vs 30-day, not 7 vs 30.

Ошибка 5. Игнорировать power law. Median creator might make 1 post/quarter. 1 post/month threshold каждого считает «active».

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

• Как посчитать DAU в SQL
• Как посчитать WAU в SQL
• Как посчитать MAU в SQL
• Как посчитать stickiness в SQL

FAQ

Active creator definition?

1+ posted item в window. Threshold можно повысить (e.g., 3+ posts/month).

DAC vs DAU?

DAU включает consumers. DAC — only creators. Обычно DAC = 5-10% DAU.

MAC growth target?

20-30% YoY для healthy growth. < 10% — saturation.

Stickiness creators benchmark?

0.1-0.15 — норма. 0.2+ — power user heavy. 0.05 — casual platform.

Track new vs returning creators?

Yes — new creator MAC growth важна. Returning shows retention.