Как посчитать Cross-Sell Rate в SQL

Зачем Cross-Sell Rate

В e-com купили смартфон + чехол. В банке — карта + кредит + страховка. Cross-sell rate показывает product penetration: больше продуктов на customer = больше LTV + sticky.

Формула

Cross-Sell Rate = customers_with_2plus_products / total_customers × 100%
Avg Products per Customer = total_product_count / total_customers

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

Данные: user_products(user_id, product_id, purchased_at).

WITH user_product_count AS (
    SELECT
        user_id,
        COUNT(DISTINCT product_id) AS products
    FROM user_products
    GROUP BY user_id
)
SELECT
    COUNT(*) AS total_customers,
    COUNT(*) FILTER (WHERE products >= 2) AS multi_product,
    COUNT(*) FILTER (WHERE products >= 2)::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS cross_sell_rate_pct,
    AVG(products) AS avg_products_per_customer
FROM user_product_count;

Attach Rate

Attach rate — конкретный «if X then Y» pattern:

WITH customers_x AS (
    SELECT DISTINCT user_id
    FROM user_products
    WHERE product_id = 'main_product'  -- e.g. smartphone
),
customers_xy AS (
    SELECT DISTINCT user_id
    FROM user_products
    WHERE user_id IN (SELECT user_id FROM customers_x)
      AND product_id = 'accessory'  -- e.g. CASE
)
SELECT
    (SELECT COUNT(*) FROM customers_x) AS x_customers,
    (SELECT COUNT(*) FROM customers_xy) AS xy_customers,
    (SELECT COUNT(*) FROM customers_xy)::NUMERIC * 100
        / NULLIF((SELECT COUNT(*) FROM customers_x), 0) AS attach_rate_pct;

По сегментам

SELECT
    u.segment,
    COUNT(DISTINCT u.user_id) AS customers,
    AVG(p_count.products) AS avg_products
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(DISTINCT product_id) AS products FROM user_products GROUP BY user_id
) p_count ON p_count.user_id = u.user_id
GROUP BY u.segment
ORDER BY avg_products DESC;

Enterprise обычно avg products higher.

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

Ошибка 1. Включать non-customers. Юзер не купил ничего — в знаменателе? Должен быть отдельный «non-customer» сегмент.

Ошибка 2. Same-day vs over-time cross-sell. Same-day = «added accessory at checkout». Over-time = «returned for second purchase».

Ошибка 3. Categorization. Что считать «cross-sell» vs «upsell»? Upsell — higher version same product. Cross-sell — different product.

Ошибка 4. Refunded items. Refunded product — counts as purchased? В стандартной логике no.

Ошибка 5. Bundled products. Bundle = 1 SKU или multiple? Зависит от data model.

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

• Как посчитать AOV в SQL
• Как посчитать LTV в SQL
• Как посчитать upgrade rate в SQL
• Как посчитать frequency в SQL

FAQ

Какой Cross-Sell Rate ok?

E-com: 15-30% multi-product customers. Banking: 30-50% (multiple products per client). SaaS: 10-25%.

Cross-Sell vs Upsell?

Cross-Sell — different product. Upsell — same product, higher tier.

Cross-Sell важнее acquisition?

Часто — да. Existing customer + new product convert in 4-5x easier than acquired customer + first purchase.

Тime между purchases?

Same-day cross-sell обычно accessory. Long-term — repeat customer engagement.

Cross-Sell в SaaS?

Customer + extra product / module. Often via account-based marketing.