Как посчитать Viral Coefficient в SQL

Зачем Viral Coefficient

Viral coefficient (K) = avg invites per user × % invites convert. K > 1 — viral growth (exponential). 0.5 < K < 1 — strong amplification but not viral. Below 0.3 — minimal organic boost.

Формула

K = (Invites per user) × (Conversion rate of invites)

Если K > 1 → viral growth (exponential)
Если K = 0.5 → 50% amplification (each user brings 0.5)

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

WITH cohort AS (
    SELECT user_id, signed_up_at
    FROM users
    WHERE signed_up_at BETWEEN '2026-01-01' AND '2026-02-01'
),
invites_sent AS (
    SELECT
        i.inviter_id,
        COUNT(*) AS invites
    FROM invites i
    JOIN cohort c ON c.user_id = i.inviter_id
    WHERE i.sent_at <= c.signed_up_at + INTERVAL '30 days'
    GROUP BY i.inviter_id
),
invites_converted AS (
    SELECT
        i.inviter_id,
        COUNT(*) AS conversions
    FROM invites i
    JOIN cohort c ON c.user_id = i.inviter_id
    JOIN users u_new ON u_new.invited_by = i.inviter_id
    WHERE u_new.signed_up_at <= c.signed_up_at + INTERVAL '30 days'
    GROUP BY i.inviter_id
)
SELECT
    AVG(s.invites) AS avg_invites_per_user,
    SUM(c.conversions)::NUMERIC / NULLIF(SUM(s.invites), 0) AS invite_conversion_rate,
    AVG(s.invites) * (SUM(c.conversions)::NUMERIC / NULLIF(SUM(s.invites), 0)) AS k_factor
FROM invites_sent s
LEFT JOIN invites_converted c ON c.inviter_id = s.inviter_id;

Viral Cycle Time

Время от сигнапа inviter до сигнапа invited:

SELECT
    AVG(EXTRACT(EPOCH FROM (u_new.signed_up_at - u_inviter.signed_up_at)) / 86400) AS avg_cycle_days
FROM users u_new
JOIN users u_inviter ON u_inviter.user_id = u_new.invited_by
WHERE u_new.signed_up_at >= CURRENT_DATE - INTERVAL '90 days';

Short cycle + K > 1 = explosive growth. Long cycle = slow viral.

По cohort

WITH cohort_stats AS (
    SELECT
        DATE_TRUNC('week', u.signed_up_at) AS cohort,
        u.user_id,
        COUNT(DISTINCT i.invite_id) AS invites_sent,
        COUNT(DISTINCT u_new.user_id) AS conversions
    FROM users u
    LEFT JOIN invites i ON i.inviter_id = u.user_id
    LEFT JOIN users u_new ON u_new.invited_by = u.user_id
    WHERE u.signed_up_at >= CURRENT_DATE - INTERVAL '12 weeks'
    GROUP BY 1, u.user_id
)
SELECT
    cohort,
    AVG(invites_sent) AS avg_invites,
    SUM(conversions)::NUMERIC / NULLIF(SUM(invites_sent), 0) AS conv_rate,
    AVG(invites_sent) * (SUM(conversions)::NUMERIC / NULLIF(SUM(invites_sent), 0)) AS k_factor
FROM cohort_stats
GROUP BY cohort
ORDER BY cohort;

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

Ошибка 1. K > 1 ≠ infinite growth. Saturation (TAM hit). Eventually decays.

Ошибка 2. Self-invite. User invites their own second account. Bot-detection нужен.

Ошибка 3. Window matters. K within 30 days lower than K-infinite. Pick один window.

Ошибка 4. Active vs all users. Only active users invite. Use activated cohort.

Ошибка 5. K and CAC. K = 0.5 not «50% lower CAC». Mathematically: effective CAC = CAC × (1 - K).

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

• Как посчитать k-factor в SQL
• Кейс: K-factor упал — как диагностировать
• Как посчитать NPS в SQL
• Виральность: формула для PM

FAQ

Viral Coefficient vs K-factor?

Синонимы. K = viral coefficient.

K > 1 — что значит?

Каждый user приводит больше одного нового. Exponential growth. Rare в реале (только early stage).

K = 0.5 — это ок?

Хорошо. Effective CAC × 0.67. Strong amplification.

Viral cycle?

Time от inviter signup до invited signup. Short cycle multiplies K effect.

K decays — что делать?

1. Improve invite mechanic (UX). 2) Better incentive. 3) Reduce friction для recipient.