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

Зачем Items Per Order

Items Per Order (IPO) — частный случай basket size, но узкий: unique line items (SKUs), не quantities. Indicator продуктовой adoption.

Формула

IPO (lines) = total_distinct_lines / total_orders
IPO (units) = total_quantities / total_orders

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

WITH order_stats AS (
    SELECT
        order_id,
        COUNT(*) AS lines,
        SUM(quantity) AS units
    FROM order_items
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY order_id
)
SELECT
    COUNT(*) AS orders,
    AVG(lines) AS avg_lines_per_order,
    AVG(units) AS avg_units_per_order,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lines) AS median_lines
FROM order_stats;

Distribution

WITH order_lines AS (
    SELECT order_id, COUNT(*) AS lines
    FROM order_items
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY order_id
)
SELECT
    CASE
        WHEN lines = 1 THEN '1 SKU'
        WHEN lines = 2 THEN '2 SKUs'
        WHEN lines = 3 THEN '3 SKUs'
        WHEN lines <= 5 THEN '4-5 SKUs'
        ELSE '6+ SKUs'
    END AS bucket,
    COUNT(*) AS orders,
    COUNT(*)::NUMERIC * 100 / SUM(COUNT(*)) OVER () AS pct
FROM order_lines
GROUP BY bucket
ORDER BY MIN(lines);

Если 80%+ orders = 1 SKU → no cross-sell happening.

По channels

SELECT
    o.acquisition_channel,
    COUNT(DISTINCT oi.order_id) AS orders,
    COUNT(*)::NUMERIC / NULLIF(COUNT(DISTINCT oi.order_id), 0) AS avg_lines_per_order,
    SUM(oi.quantity)::NUMERIC / NULLIF(COUNT(DISTINCT oi.order_id), 0) AS avg_units_per_order,
    AVG(oi.total) AS aov
FROM order_items oi
JOIN orders o USING (order_id)
WHERE o.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY o.acquisition_channel
ORDER BY avg_lines_per_order DESC;

IPO trends

SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(DISTINCT order_id) AS orders,
    COUNT(*)::NUMERIC / NULLIF(COUNT(DISTINCT order_id), 0) AS ipo_lines,
    SUM(quantity)::NUMERIC / NULLIF(COUNT(DISTINCT order_id), 0) AS ipo_units,
    -- AOV breakdown
    AVG(unit_price * quantity) AS avg_line_value,
    SUM(total) / NULLIF(COUNT(DISTINCT order_id), 0) AS aov
FROM order_items
WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

IPO × AOV relationship

WITH order_summary AS (
    SELECT
        order_id,
        COUNT(*) AS lines,
        SUM(total) AS order_value
    FROM order_items
    WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY order_id
)
SELECT
    lines AS items_in_order,
    COUNT(*) AS orders,
    AVG(order_value) AS avg_order_value
FROM order_summary
WHERE lines <= 10
GROUP BY lines
ORDER BY lines;

Linear relationship usually: more items = higher AOV. But marginal value of Nth item часто declines.

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

Ошибка 1. Lines vs Units. 2 of same SKU = 1 line, 2 units. Report both.

Ошибка 2. Bundles. Bundle SKU = 1 line, but contains 5 sub-SKUs. Count?

Ошибка 3. Cancelled lines. Items cancelled within order. Net items count.

Ошибка 4. Composite products. Subscription bundle = «1 line»? Depends.

Ошибка 5. IPO vs AOV trade-off. Higher AOV through fewer-but-pricier items vs more items. Strategy choice.

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

• Как посчитать basket size в SQL
• Как посчитать AOV в SQL
• Как посчитать attach rate в SQL
• Как посчитать cross-sell rate в SQL

FAQ

IPO vs Basket Size?

Synonyms typically. IPO often «lines», Basket «units». Define usage.

Improve IPO как?

1. Recommendations. 2) «Customers also bought». 3) Free shipping threshold (motivates adding items).

Bundle сложности?

Atomic SKU. If you sell bundles, decide счёт early.

Subscription IPO?

Recurring same items → IPO stable. Cross-sell hidden.

IPO падает — что значит?

Single-item orders growing share. Could be: shipping costs penalize bundling, или promotional single-item drives.