Как посчитать Referral Rate в SQL

Зачем Referral Rate

Referral — самый дешёвый канал. Юзер привёл друга — это free customer. Если Referral Rate высокий, ваш CAC рассчитывается включая «free» customers, реальный effective CAC ниже.

Формула

Referral Rate = users_who_referred / total_users × 100%

Или:

Avg Referrals per User = total_invites / total_users

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

Данные: referrals(referrer_id, referred_id, created_at).

WITH stats AS (
    SELECT
        COUNT(DISTINCT u.user_id) AS total_users,
        COUNT(DISTINCT r.referrer_id) AS users_who_referred,
        COUNT(*) AS total_referrals
    FROM users u
    LEFT JOIN referrals r ON r.referrer_id = u.user_id
    WHERE u.signup_date >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    total_users,
    users_who_referred,
    total_referrals,
    users_who_referred::NUMERIC * 100 / NULLIF(total_users, 0) AS referral_rate_pct,
    total_referrals::NUMERIC / NULLIF(total_users, 0) AS avg_referrals_per_user
FROM stats;

Связь с K-factor

K-factor = avg_invites_per_user × invite_acceptance_rate

K > 1 — viral growth (каждый user приводит больше одного нового).

WITH invites_stats AS (
    SELECT
        AVG(invites_sent) AS avg_invites,
        AVG(invites_accepted::NUMERIC / NULLIF(invites_sent, 0)) AS avg_acceptance_rate
    FROM (
        SELECT
            referrer_id,
            COUNT(*) AS invites_sent,
            COUNT(*) FILTER (WHERE status = 'accepted') AS invites_accepted
        FROM referrals
        WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
        GROUP BY referrer_id
    ) x
)
SELECT
    avg_invites,
    avg_acceptance_rate,
    avg_invites * avg_acceptance_rate AS k_factor
FROM invites_stats;

По cohort

WITH cohort AS (
    SELECT
        user_id,
        DATE_TRUNC('month', signup_date) AS cohort_month
    FROM users
)
SELECT
    c.cohort_month,
    COUNT(DISTINCT c.user_id) AS cohort_size,
    COUNT(DISTINCT r.referrer_id) AS referred,
    COUNT(DISTINCT r.referrer_id)::NUMERIC * 100 / NULLIF(COUNT(DISTINCT c.user_id), 0) AS referral_rate_pct
FROM cohort c
LEFT JOIN referrals r ON r.referrer_id = c.user_id
WHERE c.cohort_month >= '2026-01-01'
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

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

Ошибка 1. Self-referral. Юзер ввёл свой код. Filter referrer_id != referred_id.

Ошибка 2. Один referrer, много invites. Может быть spam-attempt. Cap acceptable invites per user.

Ошибка 3. Время to refer. Юзер 2 года в продукте — больше времени refer. Compare cohorts at same age.

Ошибка 4. Survivorship. Считаете только active referrers. Churned также имели возможность refer.

Ошибка 5. K-factor naive interpretation. K=1.2 — viral! Но эта формула предполагает constant rates. Real growth — diminishing.

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

• Как посчитать K-factor в SQL
• Как посчитать new users в SQL
• Как посчитать CAC в SQL
• K-factor простыми словами

FAQ

Какой Referral Rate ok?

Mass-market app: 5-15%. SaaS B2B: 10-30%. Premium с referral incentives: 20-50%.

Referral Rate vs K-factor?

Referral Rate — % refer хоть раз. K-factor — viral coefficient (count × acceptance).

K > 1 = виральный рост?

Theoretically yes. На практике даже K=1.1 sustainable выше organic.

Mass referral codes (e.g. influencers)?

Treat как paid (cost = incentive). Не часть organic referral rate.

Как повысить?

1. Make referring obvious / easy. 2) Incentives both sides. 3) Right moment to ask (after value moment).