Как посчитать UGC rate в SQL

Зачем UGC rate

UGC (User Generated Content) rate — доля юзеров, создающих контент vs только consuming. На большинстве платформ работает 90-9-1 правило: 90% lurkers, 9% occasional, 1% power creators. Высокий UGC rate (10%+) = healthy participation, low (3%-) = audience-driven, not creator-driven.

Формула

ugc_rate = users_who_posted / total_active_users

В period (30 / 90 days обычно).

UGC в SQL

WITH active_users AS (
    SELECT DISTINCT user_id
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
),
creators AS (
    SELECT DISTINCT author_id AS user_id
    FROM posts
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    (SELECT COUNT(*) FROM active_users) AS active_users,
    (SELECT COUNT(*) FROM creators) AS creators,
    (SELECT COUNT(*) FROM creators)::NUMERIC * 100
    / NULLIF((SELECT COUNT(*) FROM active_users), 0) AS ugc_rate_pct;

UGC rate 10% — норма для consumer apps. 30%+ — creator-first apps (Twitter, TikTok). < 5% — read-only (news sites).

По cohort

SELECT
    DATE_TRUNC('month', u.created_at)::DATE AS cohort_month,
    COUNT(DISTINCT u.user_id) AS cohort_size,
    COUNT(DISTINCT CASE WHEN c.user_id IS NOT NULL THEN u.user_id END) AS posted,
    COUNT(DISTINCT CASE WHEN c.user_id IS NOT NULL THEN u.user_id END) * 100.0
    / NULLIF(COUNT(DISTINCT u.user_id), 0) AS ugc_pct
FROM users u
LEFT JOIN creators c USING (user_id)
WHERE u.created_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', u.created_at)
ORDER BY cohort_month;

Старые cohorts должны иметь higher UGC (lifetime contributors). Если падает — onboarding broken.

По типу контента

SELECT
    post_type,
    COUNT(DISTINCT author_id) AS creators,
    COUNT(*) AS posts
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY post_type
ORDER BY creators DESC;

Photo posts обычно 5× больше creators чем longform. Different barriers to entry.

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

Ошибка 1. UGC rate based on registered. Many register, few active. Better: rate on active.

Ошибка 2. Counting comments как UGC. Comments easier than posts. Track separately.

Ошибка 3. Включать spam. Spam-creators inflate UGC rate. Verify quality.

Ошибка 4. Snap-shot vs cumulative. Lifetime UGC rate (ever created) высокий. Recent (last 30d) ниже.

Ошибка 5. UGC rate at startups. Early users часто all creators (FOMO). UGC rate 50%+ может быть artifact.

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

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

FAQ

Какой UGC rate good?

Twitter / TikTok: 30-50%. Instagram: 15-25%. News-style apps: 1-3%.

90-9-1 правило?

90% lurk, 9% comment, 1% create. Industry rule of thumb, не закон.

UGC включает stories?

Yes — temporary content тоже UGC. Track как separate type.

UGC rate падает?

Часто после feature launches (e.g., new format). Investigate funnel.

Можно ли увеличить UGC rate?

Через creator tools, contests, gamification. 10-30% bump possible.