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

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

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

Содержание:

Зачем Order Frequency

Order Frequency (purchase frequency) = orders per customer / period. Critical для LTV: LTV = AOV × Frequency × Margin × Lifetime. Без Frequency нет accurate LTV.

Формула

Order Frequency = total_orders / unique_customers / period

Период обычно month / year.

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

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;
Закрепи формулу order frequency в Карьернике
Запомнить надолго — 5 коротких сессий с задачами на эту тему. Бесплатно
Тренировать order frequency в Telegram

Гистограмма

Distribution: сколько customers сделали 1, 2, 3+ orders:

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;

Hint: most retail has long tail. 80% customers — 1-2 orders. 20% — high frequency.

Days between orders

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. New vs existing customers mixed. Cohort separation. First-month customers have artificially low frequency.

Ошибка 2. Period choice. Frequency за месяц ≠ frequency за год / 12. Особенно для seasonal businesses.

Ошибка 3. Average vs median. Long tail makes average misleading. Median + percentiles.

Ошибка 4. Cancelled / refunded orders. Filter правильно — иначе inflated frequency.

Ошибка 5. Multi-account. Same person, multiple accounts → fragmented frequency. ID resolution important.

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

FAQ

Frequency vs Retention?

Frequency — orders / customer. Retention — % customers active over time.

Какой Frequency ok?

Subscription: 12-52 /year. E-com: 2-6 /year (varies by category).

Frequency растёт — хорошо?

Yes, but check whether new customers or existing. New cohort with high frequency = best signal.

Median > avg?

Yes when long tail of high-frequency power users. Median = typical customer.

Frequency in B2B?

Lower (1-12 /year typically). But contract value higher.

Ещё по теме