Как посчитать Order Frequency в SQL

Зачем Order Frequency

Order Frequency (purchase frequency, частота покупок) — это число заказов на клиента за период. Метрика критична для LTV: LTV = AOV × Frequency × Margin × Lifetime. Без частоты покупок не посчитать точный LTV.

Формула

Order Frequency = total_orders / unique_customers / period

Период обычно — месяц или год.

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

SELECT
    COUNT(*) AS total_orders,
    COUNT(DISTINCT user_id) AS unique_customers,
    COUNT(*)::NUMERIC / NULLIF(COUNT(DISTINCT user_id), 0) AS orders_per_customer,
    -- Annualized
    COUNT(*)::NUMERIC * 12 / NULLIF(COUNT(DISTINCT user_id), 0) AS orders_per_customer_year
FROM orders
WHERE status = 'paid'
  AND created_at >= CURRENT_DATE - INTERVAL '1 month';

По сегментам

SELECT
    u.country,
    COUNT(o.order_id) AS orders,
    COUNT(DISTINCT o.user_id) AS customers,
    COUNT(o.order_id)::NUMERIC / NULLIF(COUNT(DISTINCT o.user_id), 0) AS frequency,
    AVG(o.total) AS aov,
    -- Heavy approximation для LTV proxy
    COUNT(o.order_id)::NUMERIC / NULLIF(COUNT(DISTINCT o.user_id), 0) * AVG(o.total) AS revenue_per_customer
FROM orders o
JOIN users u ON u.user_id = o.user_id
WHERE o.status = 'paid'
  AND o.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY u.country
ORDER BY frequency DESC;

Гистограмма

Распределение: сколько клиентов сделали 1, 2, 3+ заказа:

WITH per_user AS (
    SELECT
        user_id,
        COUNT(*) AS order_count
    FROM orders
    WHERE status = 'paid'
      AND created_at >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY user_id
)
SELECT
    order_count AS orders_in_year,
    COUNT(*) AS customers,
    COUNT(*)::NUMERIC * 100 / SUM(COUNT(*)) OVER () AS pct
FROM per_user
GROUP BY order_count
ORDER BY order_count;

Подсказка: у большинства ритейл-продуктов длинный хвост. 80% клиентов делают 1–2 заказа, а оставшиеся 20% покупают часто.

Среднее время между заказами

WITH ordered AS (
    SELECT
        user_id,
        created_at,
        LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order
    FROM orders
    WHERE status = 'paid'
)
SELECT
    user_id,
    AVG(EXTRACT(EPOCH FROM (created_at - prev_order)) / 86400) AS avg_days_between
FROM ordered
WHERE prev_order IS NOT NULL
GROUP BY user_id
HAVING COUNT(*) >= 3;

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

Ошибка 1. Смешивать новых и текущих клиентов. Их нужно разделять по когортам: у клиентов первого месяца частота искусственно занижена.

Ошибка 2. Выбор периода. Частота за месяц ≠ частота за год, делённая на 12. Особенно это заметно в сезонном бизнесе.

Ошибка 3. Среднее вместо медианы. Длинный хвост делает среднее обманчивым — смотрите на медиану и перцентили.

Ошибка 4. Отменённые и возвращённые заказы. Фильтруйте их правильно — иначе частота будет завышена.

Ошибка 5. Мультиаккаунты. Один человек с несколькими аккаунтами дробит частоту на части. Важно склеивать идентификаторы (ID resolution).

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

• Как посчитать days between orders в SQL
• Как посчитать repeat purchase rate в SQL
• Как посчитать AOV в SQL
• Как посчитать LTV в SQL

FAQ

Frequency vs Retention?

Частота покупок (Frequency) — это число заказов на клиента. Удержание (Retention) — доля клиентов, которые остаются активными со временем. Первая метрика про интенсивность покупок, вторая — про то, возвращаются ли клиенты вообще.

Какая частота покупок нормальная?

В подписочных продуктах — примерно 12–52 заказа в год, в e-commerce — 2–6 в год. Но всё сильно зависит от категории товара.

Частота растёт — это хорошо?

Обычно да, но важно проверить, за счёт кого именно: новых или текущих клиентов. Высокая частота у свежей когорты — самый сильный сигнал.

Медиана информативнее среднего?

Да, когда есть длинный хвост из активных power-юзеров — они тянут среднее вверх. Медиана лучше отражает типичного клиента.

Какая частота в B2B?

В B2B частота обычно ниже — примерно 1–12 заказов в год. Зато стоимость каждого контракта заметно выше.