Как посчитать Attach Rate в SQL

Зачем Attach Rate

Attach Rate — % покупателей primary product, кто купил accessory / add-on / warranty. Classic retail metric. Apple ~30-40% attach iPhone → case. SaaS — % subscribers, кто платит за add-on модули.

Формула

Attach Rate = orders_with_primary_AND_addon / orders_with_primary × 100%

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

WITH primary_orders AS (
    SELECT
        order_id,
        user_id
    FROM order_items
    WHERE product_id = 'iphone-pro'  -- primary
),
addon_attach AS (
    SELECT DISTINCT
        po.order_id
    FROM primary_orders po
    JOIN order_items oi ON oi.order_id = po.order_id
    WHERE oi.product_id IN ('iphone-CASE', 'iphone-charger', 'applecare')
)
SELECT
    COUNT(DISTINCT po.order_id) AS primary_orders,
    COUNT(DISTINCT aa.order_id) AS orders_with_attach,
    COUNT(DISTINCT aa.order_id)::NUMERIC * 100
    / NULLIF(COUNT(DISTINCT po.order_id), 0) AS attach_rate_pct
FROM primary_orders po
LEFT JOIN addon_attach aa USING (order_id);

По primary product

WITH primary_purchases AS (
    SELECT
        oi.order_id,
        oi.user_id,
        oi.product_id AS primary_product
    FROM order_items oi
    JOIN products p USING (product_id)
    WHERE p.category = 'primary'  -- e.g. laptops, phones
)
SELECT
    pp.primary_product,
    COUNT(DISTINCT pp.order_id) AS orders,
    COUNT(DISTINCT pp.order_id) FILTER (
        WHERE EXISTS (
            SELECT 1 FROM order_items oi2
            JOIN products p2 USING (product_id)
            WHERE oi2.order_id = pp.order_id
              AND p2.category = 'accessory'
        )
    ) AS orders_with_accessory,
    COUNT(DISTINCT pp.order_id) FILTER (
        WHERE EXISTS (
            SELECT 1 FROM order_items oi2
            JOIN products p2 USING (product_id)
            WHERE oi2.order_id = pp.order_id
              AND p2.category = 'accessory'
        )
    )::NUMERIC * 100 / NULLIF(COUNT(DISTINCT pp.order_id), 0) AS attach_rate_pct
FROM primary_purchases pp
GROUP BY pp.primary_product
ORDER BY attach_rate_pct DESC;

Attach Rate per addon

Different addons have different attach rates:

SELECT
    accessory.product_id AS accessory,
    COUNT(DISTINCT primary.order_id) AS primary_orders,
    COUNT(DISTINCT acc.order_id) AS attached_orders,
    COUNT(DISTINCT acc.order_id)::NUMERIC * 100 / NULLIF(COUNT(DISTINCT primary.order_id), 0) AS attach_pct
FROM (
    SELECT order_id, user_id FROM order_items WHERE product_id = 'macbook-pro'
) primary
LEFT JOIN (
    SELECT order_id, product_id FROM order_items WHERE product_id LIKE 'mb-accessory-%'
) acc USING (order_id)
GROUP BY acc.product_id
ORDER BY attach_pct DESC;

Attach Rate vs Cross-sell

Attach — immediate. Cross-sell — over time.

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

Ошибка 1. «Bundle» considered as attach. Pre-bundled product (warranty included) — already attached. Don't double count.

Ошибка 2. Returns ignored. Customer attached case, returned. Net attach rate.

Ошибка 3. Threshold value. Attach rate without revenue context. Low-margin accessory не moves bottom line.

Ошибка 4. Different primaries. Phone attach rate ≠ laptop attach rate. Compare like-with-like.

Ошибка 5. Pre vs post checkout. Some accessories sold post-purchase (insurance offered later). Separate funnel.

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

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

FAQ

Какой Attach Rate ok?

Phone case: 30-40%. Warranty: 10-20%. AirPods с iPhone: 25%.

Improve attach rate как?

1. Recommend at PDP. 2) Bundle offer. 3) Cart-level prompt. 4) Educate (need-based).

Attach rate metric для SaaS?

Yes. % subscribers с paid add-on. Slack: % paid teams with apps.

Multiple accessories?

Attach rate per accessory + overall (any accessory).

Attach rate after warranty period?

Different — that's cross-sell. Attach = same order.