SQL: Расчет времени до первой покупки

Решение

Это задача на расчет time-to-conversion — критична для понимания скорости монетизации и эффективности онбординга.

Логика решения:

1. Находим первую покупку для каждого пользователя — MIN(order_date) по user_id
2. Вычисляем разницу между registration_date и первой покупкой
3. Анализируем распределение по количеству дней до первой покупки

SQL запрос для общей статистики:

WITH first_purchase AS (
  SELECT 
    u.user_id,
    u.registration_date,
    MIN(o.order_date) as first_purchase_date,
    MIN(o.amount) as first_purchase_amount
  FROM users u
  LEFT JOIN orders o ON u.user_id = o.user_id
  GROUP BY u.user_id, u.registration_date
)
SELECT 
  COUNT(*) as total_users,
  COUNT(CASE WHEN first_purchase_date IS NOT NULL THEN 1 END) as users_with_purchase,
  ROUND(100.0 * COUNT(CASE WHEN first_purchase_date IS NOT NULL THEN 1 END) / COUNT(*), 2) as conversion_rate,
  ROUND(AVG(first_purchase_date - registration_date)) as avg_days_to_purchase,
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY first_purchase_date - registration_date)) as median_days,
  ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY first_purchase_date - registration_date)) as p75_days,
  ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY first_purchase_date - registration_date)) as p95_days,
  MIN(first_purchase_date - registration_date) as min_days,
  MAX(first_purchase_date - registration_date) as max_days
FROM first_purchase
WHERE first_purchase_date IS NOT NULL;

SQL запрос для распределения по дням:

WITH first_purchase AS (
  SELECT 
    u.user_id,
    (o.order_date - u.registration_date) as days_to_purchase
  FROM users u
  INNER JOIN (
    SELECT user_id, MIN(order_date) as order_date
    FROM orders
    GROUP BY user_id
  ) o ON u.user_id = o.user_id
),
day_bins AS (
  SELECT 
    CASE 
      WHEN days_to_purchase = 0 THEN '0_same_day'
      WHEN days_to_purchase = 1 THEN '1_next_day'
      WHEN days_to_purchase BETWEEN 2 AND 6 THEN '2_6_days'
      WHEN days_to_purchase BETWEEN 7 AND 13 THEN '7_13_days'
      WHEN days_to_purchase BETWEEN 14 AND 29 THEN '14_29_days'
      WHEN days_to_purchase BETWEEN 30 AND 89 THEN '30_89_days'
      ELSE '90plus_days'
    END as time_bucket,
    COUNT(*) as users_count
  FROM first_purchase
  GROUP BY time_bucket
)
SELECT 
  time_bucket,
  users_count,
  ROUND(100.0 * users_count / SUM(users_count) OVER (), 2) as percentage,
  SUM(users_count) OVER (ORDER BY time_bucket) as cumulative_users,
  ROUND(100.0 * SUM(users_count) OVER (ORDER BY time_bucket) / SUM(users_count) OVER (), 2) as cumulative_pct
FROM day_bins;

Детальное распределение (дни 0-30):

WITH first_purchase AS (
  SELECT 
    (o.order_date - u.registration_date) as days_to_purchase
  FROM users u
  INNER JOIN (
    SELECT user_id, MIN(order_date) as order_date
    FROM orders
    GROUP BY user_id
  ) o ON u.user_id = o.user_id
)
SELECT 
  days_to_purchase,
  COUNT(*) as users_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) as percentage,
  SUM(COUNT(*)) OVER (ORDER BY days_to_purchase) as cumulative_users
FROM first_purchase
WHERE days_to_purchase BETWEEN 0 AND 30
GROUP BY days_to_purchase
ORDER BY days_to_purchase;

Когортный анализ (по датам регистрации):

WITH first_purchase AS (
  SELECT 
    DATE_TRUNC('week', u.registration_date) as reg_week,
    (o.order_date - u.registration_date) as days_to_purchase
  FROM users u
  INNER JOIN (
    SELECT user_id, MIN(order_date) as order_date
    FROM orders
    GROUP BY user_id
  ) o ON u.user_id = o.user_id
)
SELECT 
  reg_week,
  ROUND(AVG(days_to_purchase), 2) as avg_days,
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_purchase), 2) as median_days,
  COUNT(*) as users_with_purchase,
  COUNT(CASE WHEN days_to_purchase = 0 THEN 1 END) as same_day,
  COUNT(CASE WHEN days_to_purchase = 1 THEN 1 END) as day_1,
  COUNT(CASE WHEN days_to_purchase BETWEEN 2 AND 7 THEN 1 END) as week_1
FROM first_purchase
GROUP BY DATE_TRUNC('week', u.registration_date)
ORDER BY reg_week DESC;

Ожидаемый результат:

ОБЩАЯ СТАТИСТИКА:
Total Users: 10,000
Users with Purchase: 2,500 (25% conversion)
Avg Days to Purchase: 4.5 дней
Median Days to Purchase: 2 дня
P75: 8 дней
P95: 20 дней

РАСПРЕДЕЛЕНИЕ:
Day 0 (same day): 200 (8%)
Day 1 (next day): 350 (14%)
Day 2-6: 800 (32%)
Day 7-13: 600 (24%)
Day 14-29: 350 (14%)
Day 30-89: 150 (6%)
Day 90+: 50 (2%)

Ключевые моменты:

Что анализировать:

1. Same-day conversion (0 дней) — очень вовлечённые (5-10% норма)
2. Quick converters (1-3 дня) — хорошая целевая аудитория (30-50%)
3. Delayed converters (7+ дней) — требуют реактивации
4. Never converters — не покупают (75% в норме)

Применение в бизнесе:

• Если median = 20 дней, нужна реактивация email на день 5-7
• Если same-day < 5%, проблема в очевидности покупки
• Если P95 > 30 дней, нужны тесты по ускорению монетизации
• Same-day converters часто дают лучший LTV (выше пятикратно)