Как посчитать Time to First Purchase в SQL

Зачем Time to First Purchase

В e-com 60% покупателей делают первую покупку в первый день регистрации. 30% — в первую неделю. Оставшиеся 10% размазаны на месяцы. Знание distribution помогает: 1) определить «churned before purchase» (если >90 дней без покупки), 2) timing для marketing emails.

Формула

TFP = MIN(purchase_date) - signup_date
   для пользователей с >= 1 покупкой

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

WITH first_purchase AS (
    SELECT
        u.user_id,
        u.signup_date,
        MIN(o.created_at) AS first_purchase_date
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.user_id AND o.status = 'paid'
    WHERE u.signup_date >= CURRENT_DATE - INTERVAL '180 days'
    GROUP BY u.user_id, u.signup_date
)
SELECT
    AVG(EXTRACT(EPOCH FROM (first_purchase_date - signup_date)) / 86400) AS avg_days_tfp,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (first_purchase_date - signup_date)) / 86400
    ) AS median_days_tfp,
    COUNT(*) FILTER (WHERE first_purchase_date IS NOT NULL) AS buyers,
    COUNT(*) AS total_users
FROM first_purchase;

Distribution

WITH first_purchase AS (
    SELECT
        u.user_id,
        EXTRACT(EPOCH FROM (MIN(o.created_at) - u.signup_date)) / 86400 AS days_tfp
    FROM users u
    JOIN orders o ON o.user_id = u.user_id AND o.status = 'paid'
    GROUP BY u.user_id, u.signup_date
)
SELECT
    CASE
        WHEN days_tfp < 1 THEN 'Same day'
        WHEN days_tfp <= 3 THEN '1-3 days'
        WHEN days_tfp <= 7 THEN '4-7 days'
        WHEN days_tfp <= 30 THEN '8-30 days'
        WHEN days_tfp <= 90 THEN '31-90 days'
        ELSE '90+ days'
    END AS bucket,
    COUNT(*) AS users,
    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS pct
FROM first_purchase
GROUP BY 1
ORDER BY 1;

По сегментам

WITH first_purchase AS (
    SELECT
        u.user_id,
        u.acquisition_channel,
        EXTRACT(EPOCH FROM (MIN(o.created_at) - u.signup_date)) / 86400 AS days_tfp
    FROM users u
    JOIN orders o ON o.user_id = u.user_id AND o.status = 'paid'
    GROUP BY u.user_id, u.acquisition_channel, u.signup_date
)
SELECT
    acquisition_channel,
    COUNT(*) AS buyers,
    AVG(days_tfp) AS avg_days,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_tfp) AS median_days,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY days_tfp) AS p90_days
FROM first_purchase
GROUP BY acquisition_channel
ORDER BY median_days;

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

Ошибка 1. Survival bias. Считаете только тех, кто купил. Те, кто не купил вообще, исключены из distribution. Для full picture включите как «right-censored».

Ошибка 2. Same-day с регистрацией. Юзер зарегистрировался на странице оплаты — TFP = 0. Это «impulse buyers». Возможно отдельный сегмент.

Ошибка 3. Не учитывать guest checkout. Guest без user_id — нет attribution к signup. Track по device.

Ошибка 4. Average vs median на skewed. Avg включает long-tail buyers (купили через год). Median — типичный buyer.

Ошибка 5. Cohort matters. Cohort января vs июня — разная TFP (сезонность, изменения в onboarding).

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

• Как посчитать activation в SQL
• Как посчитать trial conversion в SQL
• Как посчитать days between orders в SQL
• Как посчитать funnel в SQL

FAQ

Какой TFP считается нормой?

E-com быстрый purchase: 50%+ same day. Subscription: <7 days (free trial). High-consideration (электроника): 7-30 days. Авто: 30-180 days.

TFP падает — хорошо или плохо?

Хорошо. Юзер быстрее окупает себя. Onboarding работает.

Как уменьшить TFP?

1. Welcome offer / first-order discount. 2) Personalized recommendations on day 1. 3) Email nudges. 4) Reduce friction at checkout.

TFP в SaaS?

Обычно совпадает с trial-to-paid period.

TFP для repeat buyer?

Это «days between orders», другая метрика.