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

Зачем Basket Size

Basket Size — это среднее число товаров в заказе. Это один из драйверов AOV: AOV = Basket Size × средняя цена товара. Рост Basket Size даёт рост выручки без привлечения новых клиентов.

Формула

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;

По сегментам

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;

Распределение

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);

Состав корзины

Что покупают вместе:

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;

Топ категорий, которые покупают вместе, — основа для рекомендаций.

Среднее число товаров по стоимости заказа

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);

(Примечание: NTILE внутри GROUP BY работает капризно. На практике выносите его в подзапрос.)

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

Ошибка 1. Товары или SKU. Три штуки одного SKU — это 1 SKU, но 3 товара. Определитесь заранее, что именно считаете.

Ошибка 2. Возвраты. Чистый размер корзины после возвратов меньше валового. Решите, учитываете ли вы возвраты.

Ошибка 3. Как считать наборы. Набор — это 1 SKU, внутри которого 5 товаров. Что засчитывать — 1 или 5?

Ошибка 4. Искажение от акций. Акции вроде «два по цене одного» раздувают корзину. Смотрите на размер корзины отдельно от акций.

Ошибка 5. Автозаказы по подписке. Ежемесячная подписка даёт N товаров на N заказов. Сравнивайте такие заказы с обычными аккуратно.

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

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

FAQ

Чем товары отличаются от SKU?

Товары — это общее число единиц с учётом количества. SKU — число разных товаров. В расчёте AOV используют либо число SKU, либо число товаров — зависит от задачи.

Какой Basket Size считается нормальным?

Ориентир зависит от категории: в продуктовом ритейле — 10-30 товаров, в одежде — 2-4, в электронике — 1-2.

Как увеличить Basket Size?

Во-первых, бесплатная доставка от порога суммы. Во-вторых, наборы товаров со скидкой. В-третьих, рекомендации сопутствующих товаров. В-четвёртых, скидка за объём.

Как считать наборы?

Определитесь заранее. Если набор — это неделимый SKU, считайте его за 1 товар; иначе раскладывайте на отдельные позиции.

Как учитывать заказы по подписке?

Каждая отгрузка — это 1 заказ. Состав подписки обычно стабилен, поэтому разброс размера корзины по таким заказам низкий.