Как посчитать creator retention в SQL

Зачем creator retention

Только 5-10% signups создают content. Если они и churn — платформа умирает. Creator retention — главная health metric для UGC. Threshold 20% week-12 — норма для big networks. 30%+ — strong (YouTube, TikTok). < 10% — content drying up.

Формула

creator_retention(t) = creators_who_posted_in_week_t / total_creators_who_posted_in_week_0

«Active creator» — has at least 1 post в неделю.

Retention в SQL

WITH cohort AS (
    SELECT
        DATE_TRUNC('week', MIN(created_at))::DATE AS cohort_week,
        author_id
    FROM posts
    GROUP BY author_id
),
weekly_activity AS (
    SELECT
        c.cohort_week,
        c.author_id,
        FLOOR((p.created_at::DATE - c.cohort_week) / 7) AS week_offset
    FROM cohort c
    JOIN posts p USING (author_id)
    WHERE p.created_at >= c.cohort_week
)
SELECT
    cohort_week,
    week_offset,
    COUNT(DISTINCT author_id) AS active_creators,
    COUNT(DISTINCT author_id)::NUMERIC * 100
    / NULLIF(FIRST_VALUE(COUNT(DISTINCT author_id)) OVER (PARTITION BY cohort_week ORDER BY week_offset), 0) AS retention_pct
FROM weekly_activity
WHERE cohort_week >= CURRENT_DATE - INTERVAL '12 weeks'
  AND week_offset BETWEEN 0 AND 12
GROUP BY cohort_week, week_offset
ORDER BY cohort_week, week_offset;

Week-1 retention 40% — normal. Week-12 20% — норма. Week-52 > 10% — sticky platform.

По cohort

Compare cohorts: новые vs старые.

SELECT
    cohort_week,
    AVG(retention_pct) FILTER (WHERE week_offset = 4) AS w4_retention,
    AVG(retention_pct) FILTER (WHERE week_offset = 12) AS w12_retention,
    AVG(retention_pct) FILTER (WHERE week_offset = 26) AS w26_retention
FROM creator_retention_table
GROUP BY cohort_week
ORDER BY cohort_week;

Растущая retention week-over-week = platform improving. Падающая — flag.

Power vs casual creators

WITH author_activity AS (
    SELECT
        author_id,
        COUNT(*) AS total_posts,
        EXTRACT(EPOCH FROM (MAX(created_at) - MIN(created_at))) / 86400 AS active_days
    FROM posts
    WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY author_id
)
SELECT
    CASE
        WHEN total_posts >= 30 THEN 'power'
        WHEN total_posts >= 5 THEN 'regular'
        ELSE 'casual'
    END AS creator_segment,
    COUNT(*) AS creators
FROM author_activity
GROUP BY 1
ORDER BY 1;

Power 5-10%, regular 30-40%, casual 50-60% — типичная распределение.

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

Ошибка 1. Считать на login, не на post. Login != active creator. Real activity = created content.

Ошибка 2. One post = forever active. Single post 6 months ago — не active. Track recent (within last month).

Ошибка 3. Не учитывать cohort size. Bigger cohort может иметь lower retention. Normalize.

Ошибка 4. Mixing creator types. Power retention 80% vs casual 5% mixed = 40% — обманчиво.

Ошибка 5. Bots в creator count. Spam bot inflates total. Verify human authors.

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

• Как посчитать content velocity в SQL
• Как посчитать active creators в SQL
• Как посчитать cohort retention в SQL
• Как посчитать DAU в SQL

FAQ

Какой creator retention хороший?

Week-12: 15-25% norm. Week-52: 5-15%. Power creators: 50%+ at week 52.

Active creator threshold?

1+ post per week (или per month, в зависимости от platform).

Creator vs consumer retention?

Creator retention обычно ниже (effort harder). 10-20% creator vs 30-50% consumer.

Re-activation campaigns?

Email прежних creators «нам не хватает вас» — 5-15% conversion.

Trend over time?

YoY trend важнее single number. Например, YoY 15% → 20% = improvement.