13 мая 2026 г.·2 мин чтения

Как посчитать Free-to-Paid в SQL

Закрепи формулу free to paid в Карьернике
Запомнить надолго — 5 коротких сессий с задачами на эту тему. Бесплатно
Тренировать free to paid в Telegram

Содержание:

Зачем Free-to-Paid

Freemium core funnel: signup → free use → convert. Без healthy Free-to-Paid невозможно масштабироваться. Slack, Dropbox, Notion — все built on этом.

Формула

Free-to-Paid = users_paid_within_X / total_signups × 100%

Window обычно 30/60/90 days post-signup.

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

WITH signups AS (
    SELECT user_id, signed_up_at
    FROM users
    WHERE signed_up_at BETWEEN '2026-01-01' AND '2026-02-01'
),
first_paid AS (
    SELECT
        user_id,
        MIN(created_at) AS first_payment_at
    FROM transactions
    WHERE status = 'paid'
      AND amount > 0
    GROUP BY user_id
)
SELECT
    COUNT(s.user_id) AS signups,
    COUNT(p.user_id) AS converted_to_paid,
    COUNT(p.user_id) FILTER (WHERE p.first_payment_at <= s.signed_up_at + INTERVAL '30 days') AS converted_30d,
    COUNT(p.user_id) FILTER (WHERE p.first_payment_at <= s.signed_up_at + INTERVAL '90 days') AS converted_90d,
    COUNT(p.user_id) FILTER (WHERE p.first_payment_at <= s.signed_up_at + INTERVAL '30 days')::NUMERIC * 100 / COUNT(s.user_id) AS free_to_paid_30d_pct
FROM signups s
LEFT JOIN first_paid p ON p.user_id = s.user_id;

По cohort

WITH cohort AS (
    SELECT
        DATE_TRUNC('week', signed_up_at) AS cohort_week,
        user_id
    FROM users
    WHERE signed_up_at >= CURRENT_DATE - INTERVAL '12 weeks'
),
paid AS (
    SELECT user_id, MIN(created_at) AS paid_at
    FROM transactions
    WHERE status = 'paid'
    GROUP BY user_id
)
SELECT
    c.cohort_week,
    COUNT(c.user_id) AS signups,
    COUNT(p.user_id) AS paid,
    COUNT(p.user_id)::NUMERIC * 100 / NULLIF(COUNT(c.user_id), 0) AS conversion_pct
FROM cohort c
LEFT JOIN paid p ON p.user_id = c.user_id
  AND p.paid_at - (
    SELECT signed_up_at FROM users WHERE user_id = c.user_id
  ) <= INTERVAL '30 days'
GROUP BY c.cohort_week
ORDER BY c.cohort_week;
Закрепи формулу free to paid в Карьернике
Запомнить надолго — 5 коротких сессий с задачами на эту тему. Бесплатно
Тренировать free to paid в Telegram

Time-to-conversion

WITH conversions AS (
    SELECT
        u.user_id,
        u.signed_up_at,
        MIN(t.created_at) AS first_paid_at,
        EXTRACT(EPOCH FROM (MIN(t.created_at) - u.signed_up_at)) / 86400 AS days_to_pay
    FROM users u
    JOIN transactions t ON t.user_id = u.user_id
    WHERE t.status = 'paid'
      AND u.signed_up_at >= CURRENT_DATE - INTERVAL '6 months'
    GROUP BY u.user_id, u.signed_up_at
)
SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY days_to_pay) AS p25_days,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_pay) AS median_days,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY days_to_pay) AS p75_days,
    AVG(days_to_pay) AS avg_days
FROM conversions;

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

Ошибка 1. Snapshot vs cohort. «Free-to-Paid 10%» today — including users who signed up 5 years ago? Use cohort.

Ошибка 2. Window choice. 30d vs 90d очень разные. Long-tail conversions (paid после 6 months) common.

Ошибка 3. Trial vs free. Trial-to-paid и Free-to-paid — разные funnels. Не путать.

Ошибка 4. Multiple price points. Paid for $1 trial — does it count as paid? Define «paid».

Ошибка 5. Refunds. Paid, refunded в week 2. Filter.

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

FAQ

Какой Free-to-Paid ok?

SaaS B2C: 2-5% — average, 5-10% — good. SaaS B2B: 5-15%. Slack: ~30% (workspace-level).

30d vs 90d window?

30d — early signal. 90d — more realistic для long sales cycles.

Free-to-Paid падает?

Check funnels: signup quality, activation, time-to-value, paywall friction.

Conversion от activated users?

Activated → Paid лучше signup → Paid. Filter to activated.

Long-tail conversions?

В SaaS many users convert после года+. Track full lifetime curve.

Ещё по теме