Как посчитать Basket Size в SQL

Зачем Basket Size

Basket Size = avg items per order. Драйвер AOV. AOV = Basket Size × avg item price. Basket Size growth → revenue growth без acquiring new customers.

Формула

Basket Size = total_items / total_orders

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

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

По segments

SELECT
    u.country,
    COUNT(DISTINCT oi.order_id) AS orders,
    SUM(oi.quantity)::NUMERIC / NULLIF(COUNT(DISTINCT oi.order_id), 0) AS avg_basket,
    AVG(oi.unit_price) AS avg_item_price,
    AVG(oi.total) AS aov
FROM order_items oi
JOIN orders o USING (order_id)
JOIN users u USING (user_id)
WHERE o.status = 'paid'
  AND o.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY u.country
ORDER BY avg_basket DESC;

Distribution

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

Basket Composition

What goes together:

WITH order_categories AS (
    SELECT DISTINCT
        oi.order_id,
        p.category
    FROM order_items oi
    JOIN products p USING (product_id)
)
SELECT
    a.category AS category_a,
    b.category AS category_b,
    COUNT(*) AS co_purchase_orders
FROM order_categories a
JOIN order_categories b ON a.order_id = b.order_id AND a.category < b.category
GROUP BY a.category, b.category
ORDER BY co_purchase_orders DESC
LIMIT 20;

Top co-purchased categories — basis для recommendations.

Average Items by Order Value

WITH stats AS (
    SELECT
        order_id,
        SUM(quantity) AS items,
        SUM(total) AS order_value
    FROM order_items
    WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY order_id
)
SELECT
    NTILE(10) OVER (ORDER BY order_value) AS value_decile,
    AVG(items) AS avg_basket,
    MIN(order_value) AS decile_min_value,
    MAX(order_value) AS decile_max_value
FROM stats
GROUP BY NTILE(10) OVER (ORDER BY order_value);

(Note: NTILE в GROUP BY tricky. Use subquery в practice.)

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

Ошибка 1. Items vs SKUs. 3 quantities of same SKU = 1 SKU but 3 items. Define.

Ошибка 2. Returned items. Net basket size after returns < gross.

Ошибка 3. Bundles count. Bundle = 1 SKU containing 5 items. 1 or 5 в count?

Ошибка 4. Promotions skew. BOGO (buy one get one) inflates basket. Track gross of promotions.

Ошибка 5. Subscription auto-orders. Subscription monthly = N items / N orders. Compare carefully.

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

• Как посчитать AOV в SQL
• Как посчитать items per order в SQL
• Как посчитать attach rate в SQL
• Как посчитать cross-sell rate в SQL

FAQ

Items vs SKUs?

Items — total units (with quantities). SKUs — distinct products. AOV usually uses SKU count or items, depends.

Какой Basket Size ok?

Grocery: 10-30 items. Apparel: 2-4 items. Electronics: 1-2.

Increase basket size как?

1. Free shipping threshold. 2) Bundle deals. 3) Recommendations. 4) Bulk discount.

Bundle counting?

Define upfront. Atomic SKU = 1 item, иначе break out.

Subscription orders?

Each fulfilment = 1 order. Items на subscription often consistent → low variance.