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

Зачем First Order Value

First Order Value (FOV) — сколько customer тратит при первой покупке. Indicator of customer quality at acquisition. Высокий FOV → клиент готов platить, low FOV → discount-driven (часто bad retention).

Формула

FOV = first_order_amount  (per customer)
Average FOV = AVG(first_order_amount)

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

WITH first_orders AS (
    SELECT
        user_id,
        order_id,
        total AS first_order_value,
        created_at AS first_order_at,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
    FROM orders
    WHERE status = 'paid'
)
SELECT
    COUNT(*) AS new_customers,
    AVG(first_order_value) AS avg_fov,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY first_order_value) AS median_fov,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY first_order_value) AS p90_fov
FROM first_orders
WHERE rn = 1
  AND first_order_at >= CURRENT_DATE - INTERVAL '90 days';

FOV vs Subsequent AOV

WITH order_seq AS (
    SELECT
        user_id,
        total,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num
    FROM orders
    WHERE status = 'paid'
)
SELECT
    CASE
        WHEN order_num = 1 THEN 'first'
        WHEN order_num = 2 THEN 'second'
        WHEN order_num <= 5 THEN '3-5'
        ELSE '6+'
    END AS order_position,
    COUNT(*) AS orders,
    AVG(total) AS avg_aov,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) AS median_aov
FROM order_seq
WHERE user_id IN (
    SELECT user_id FROM orders
    WHERE status = 'paid' AND created_at >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY user_id HAVING COUNT(*) >= 1
)
GROUP BY 1
ORDER BY MIN(order_num);

Typical pattern: FOV ниже subsequent (people test small first). Если FOV > AOV — discount-driven first purchase, churn после.

По channels

WITH first_orders AS (
    SELECT
        user_id,
        total AS fov,
        acquisition_channel,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
    FROM orders o
    JOIN users u USING (user_id)
    WHERE o.status = 'paid'
)
SELECT
    acquisition_channel,
    COUNT(*) AS new_customers,
    AVG(fov) AS avg_fov,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY fov) AS median_fov
FROM first_orders
WHERE rn = 1
  AND acquisition_channel IS NOT NULL
GROUP BY acquisition_channel
ORDER BY avg_fov DESC;

Premium channels (referral, organic) часто higher FOV than discount channels.

FOV vs CAC

WITH cac_per_channel AS (
    SELECT
        channel,
        SUM(spend) AS spend,
        COUNT(DISTINCT user_id) AS acquired
    FROM marketing_attribution
    WHERE DATE >= CURRENT_DATE - INTERVAL '3 months'
    GROUP BY channel
)
SELECT
    c.channel,
    c.spend / NULLIF(c.acquired, 0) AS cac,
    AVG(o.total) FILTER (WHERE order_num = 1) AS avg_fov,
    AVG(o.total) FILTER (WHERE order_num = 1) - c.spend / NULLIF(c.acquired, 0) AS fov_minus_cac
FROM cac_per_channel c
JOIN ...
GROUP BY c.channel, c.spend, c.acquired;

FOV >= CAC — break-even на first order. FOV < CAC — need retention для payback.

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

Ошибка 1. AOV vs FOV. AOV — all orders. FOV — first order only. Often confused.

Ошибка 2. Discount-driven FOV. Customer used $50 off → FOV $50 lower. Track gross FOV.

Ошибка 3. Sample period. First-time customers in last week — too few для stable FOV.

Ошибка 4. Cancelled first orders. Order placed, never paid. Filter status='paid'.

Ошибка 5. Multi-account. «Same person, new account» — looks like new customer. Real FOV higher.

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

• Как посчитать AOV в SQL
• Как посчитать CAC в SQL
• Как посчитать repeat buyer rate в SQL
• Как посчитать LTV в SQL

FAQ

FOV vs AOV?

FOV — first order. AOV — average order overall. Different metrics, both useful.

FOV > AOV или <?

Зависит. Often FOV < AOV (people test). Если FOV > AOV — discount-driven first.

Какой FOV ok?

Зависит от business. >50% of CAC — okay. Below — depend on LTV.

Filter cancelled orders?

Yes. Cancelled / refunded не «real» first order.

FOV per channel?

Yes — diff channels = diff quality customers. Premium channels: higher FOV.