Как выявить сегменты пользователей с максимальным LTV?

Выявление сегментов с максимальным LTV

LTV (Lifetime Value) — сумма всех доходов от пользователя за весь период. Это ключевая метрика для определения ценности сегментов.

Шаг 1: Базовый расчёт LTV

WITH user_ltv AS (
  SELECT 
    user_id,
    SUM(amount) as ltv,
    COUNT(order_id) as orders,
    MAX(order_date) as last_purchase
  FROM orders
  WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR)
  GROUP BY user_id
)
SELECT * FROM user_ltv ORDER BY ltv DESC;

Шаг 2: RFM сегментация

Использую Recency, Frequency, Monetary для определения групп:

WITH rfm AS (
  SELECT 
    user_id,
    DATEDIFF(day, MAX(order_date), CURRENT_DATE) as recency,
    COUNT(*) as frequency,
    SUM(amount) as monetary,
    NTILE(5) OVER (ORDER BY DATEDIFF(day, MAX(order_date), CURRENT_DATE) DESC) as r,
    NTILE(5) OVER (ORDER BY COUNT(*)) as f,
    NTILE(5) OVER (ORDER BY SUM(amount)) as m
  FROM orders
  GROUP BY user_id
)
SELECT 
  CONCAT(r,f,m) as segment,
  COUNT(*) as users,
  ROUND(AVG(monetary), 2) as avg_ltv,
  ROUND(SUM(monetary), 2) as total_revenue
FROM rfm
GROUP BY segment
ORDER BY avg_ltv DESC;

Шаг 3: Демографическая сегментация

SELECT 
  u.country,
  u.device_type,
  COUNT(u.user_id) as segment_size,
  ROUND(AVG(l.ltv), 2) as avg_ltv,
  ROUND(MAX(l.ltv), 2) as max_ltv,
  ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY l.ltv), 2) as p75_ltv
FROM users u
JOIN user_ltv l ON u.user_id = l.user_id
GROUP BY u.country, u.device_type
HAVING COUNT(u.user_id) >= 50
ORDER BY avg_ltv DESC;

Шаг 4: Когортный анализ

SELECT 
  DATE_TRUNC('month', u.created_at) as cohort,
  COUNT(u.user_id) as cohort_size,
  ROUND(AVG(l.ltv), 2) as cohort_ltv,
  ROUND(SUM(l.ltv), 0) as total_cohort_revenue
FROM users u
LEFT JOIN user_ltv l ON u.user_id = l.user_id
GROUP BY cohort
ORDER BY cohort DESC;

Шаг 5: Машинное обучение (Python)

import pandas as pd
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler

# Загрузка данных
df = pd.read_sql(query, conn)

# Выбираем признаки
features = ['ltv', 'order_frequency', 'avg_order_value', 'days_since_first']
X = df[features]

# Нормализация
scaler = StandardScaler()
X_scaled = scaler.fit_transform(X)

# Кластеризация
kmeans = KMeans(n_clusters=5, random_state=42)
df['segment'] = kmeans.fit_predict(X_scaled)

# Анализ
print(df.groupby('segment')[features].mean())

Ключевые метрики TOP LTV сегментов

• Размер: минимум 5-10% от базы (статистическая значимость)
• Тренд: растущая или стабильная LTV
• Потенциал: возможность расширения подобных пользователей
• Рентабельность: CAC < LTV * 3

Практическое применение

TOP сегменты: увеличивать retention, премиум фичи, VIP программы

LOW сегменты: анализировать причину (новые пользователи, неправильный тип), но не удалять

На практике 20-30% пользователей приносят 70-80% доходов. Выявление этих сегментов позволяет сосредоточить маркетинг и развитие на перспективных направлениях.