How to calculate Attach Rate in SQL

What Attach Rate is and why retail teams obsess over it

Attach Rate is the share of orders containing a primary product that also include at least one related accessory, add-on, or service. It is the metric that explains why a phone case sits next to the checkout button on the Apple Store and why DoorDash quietly tests a one-tap drink upsell every quarter. The arithmetic is shallow; the data model decisions underneath it are not. When a retail PM at Amazon, Stripe, or Shopify asks for Attach Rate by laptop SKU before the next planning meeting, the analyst who treats the question as "just a ratio" presents a number that quietly lies.

Attach Rate matters out of proportion to its simplicity because it sits on a margin lever almost no other top-of-funnel metric touches. Primary products are often thin-margin anchors. The economics live in the attached AppleCare, the extended warranty, the protective case, the next-day delivery upgrade — things customers do not search for but accept once a primary purchase is committed. A two-point lift in Attach Rate on iPhones is worth more bottom-line dollars than a ten-point lift in iPhone conversion.

The benchmark numbers travel surprisingly well. Apple posts roughly a third of iPhone orders attaching a case, a quarter attaching AirPods, ten to twenty percent attaching AppleCare. SaaS teams at Notion, Linear, and Slack look at the share of paid teams that also pay for at least one add-on module, clustering around fifteen to thirty percent depending on catalog maturity. The SQL below produces those numbers cleanly in Postgres, with notes for Snowflake and BigQuery.

Formulas and the data model

The base definition is one fraction. Orders containing the primary product divided into orders that also contain a qualifying attached item, expressed as a percentage.

Attach Rate = orders_with_primary_AND_addon / orders_with_primary * 100
Per-addon   = orders_with_primary_AND_specific_addon / orders_with_primary * 100

Assume an order-items fact joined to a small products dimension.

order_items (order_id, user_id, product_id, qty, unit_price, status, created_at)
products    (product_id, sku, category, role, margin_pct)

products.role carries primary, accessory, addon, warranty, and bundle_component. Tagging a row once at the catalog level avoids the where-clause hardcoding that turns into tech debt the day a new SKU launches.

order_items.status includes at least paid, cancelled, and refunded. Most retail teams build a net_attach view that drops refunded rows from the numerator while keeping them in the denominator.

Six SQL queries you will actually ship

1. Base Attach Rate for a single primary SKU

The smallest useful query. Pick one primary product, count its paid orders, count the subset that also contains any qualifying accessory.

WITH primary_orders AS (
    SELECT DISTINCT order_id
    FROM order_items
    WHERE product_id = 'iphone-15-pro'
      AND status = 'paid'
),
attached_orders AS (
    SELECT DISTINCT oi.order_id
    FROM order_items oi
    JOIN products p USING (product_id)
    JOIN primary_orders po USING (order_id)
    WHERE p.role IN ('accessory', 'warranty', 'addon')
      AND oi.status = 'paid'
)
SELECT
    COUNT(po.order_id)                                  AS primary_orders,
    COUNT(ao.order_id)                                  AS orders_with_attach,
    COUNT(ao.order_id)::NUMERIC * 100
        / NULLIF(COUNT(po.order_id), 0)                 AS attach_rate_pct
FROM primary_orders po
LEFT JOIN attached_orders ao USING (order_id);

The NULLIF guards against zero-order weeks at launch. The ::NUMERIC cast prevents the Postgres integer-division trap that silently rounds Attach Rate to zero.

2. Attach Rate by primary product

Roll the same logic across the whole catalog and rank. This is the version that ships to a merchandising dashboard.

WITH primary_purchases AS (
    SELECT
        oi.order_id,
        oi.product_id AS primary_product
    FROM order_items oi
    JOIN products p USING (product_id)
    WHERE p.role = 'primary'
      AND oi.status = 'paid'
)
SELECT
    pp.primary_product,
    COUNT(DISTINCT pp.order_id) AS primary_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.role IN ('accessory', 'warranty', 'addon')
              AND oi2.status = 'paid'
        )
    )::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;

FILTER is the cleanest Postgres pattern for conditional counts. On BigQuery, swap to COUNTIF. EXISTS is faster than a self-join once the table crosses a few hundred million rows.

3. Attach Rate per addon

Different addons attach at different rates. AirPods attach to iPhones around a quarter of the time; AppleCare lands closer to fifteen percent. The per-addon view drives merchandising and recommendation tuning.

WITH primary_orders AS (
    SELECT DISTINCT order_id
    FROM order_items
    WHERE product_id = 'macbook-pro-14'
      AND status = 'paid'
)
SELECT
    oi.product_id                                          AS addon_product,
    (SELECT COUNT(*) FROM primary_orders)                  AS primary_orders,
    COUNT(DISTINCT oi.order_id)                            AS attached_orders,
    COUNT(DISTINCT oi.order_id)::NUMERIC * 100
        / NULLIF((SELECT COUNT(*) FROM primary_orders), 0) AS attach_pct
FROM order_items oi
JOIN products p USING (product_id)
JOIN primary_orders po USING (order_id)
WHERE p.role IN ('accessory', 'warranty', 'addon')
  AND oi.status = 'paid'
GROUP BY oi.product_id
ORDER BY attach_pct DESC;

4. Pre-checkout versus post-purchase attach

Some addons attach inside the cart; others are sold weeks later through email or push. Mixing them produces a single number that lies about both motions.

WITH primary_orders AS (
    SELECT order_id, user_id, MIN(created_at) AS primary_at
    FROM order_items
    WHERE product_id = 'macbook-pro-14'
      AND status = 'paid'
    GROUP BY order_id, user_id
),
addon_events AS (
    SELECT
        po.order_id,
        po.primary_at,
        oi.created_at        AS addon_at
    FROM primary_orders po
    JOIN order_items oi USING (user_id)
    JOIN products p USING (product_id)
    WHERE p.role IN ('accessory', 'warranty', 'addon')
      AND oi.status = 'paid'
      AND oi.created_at >= po.primary_at
      AND oi.created_at <  po.primary_at + INTERVAL '90 days'
)
SELECT
    CASE
        WHEN addon_at <= primary_at + INTERVAL '1 hour' THEN 'in_cart'
        WHEN addon_at <= primary_at + INTERVAL '7 days' THEN 'first_week'
        ELSE 'post_30d'
    END                                                       AS attach_window,
    COUNT(DISTINCT order_id)                                  AS attached_orders,
    COUNT(DISTINCT order_id)::NUMERIC * 100
        / NULLIF((SELECT COUNT(*) FROM primary_orders), 0)    AS attach_pct
FROM addon_events
GROUP BY 1
ORDER BY MIN(addon_at);

The in-cart slice answers "is the PDP recommendation working?" The first-week slice answers "is the welcome email working?" The thirty-day-plus slice is really cross-sell — separate funnel, separate team.

5. Attach Rate decomposition with margin

Attach Rate without margin context is half a metric. A high-attach but low-margin accessory does not move the P&L; a low-attach but high-margin warranty might.

WITH primary_orders AS (
    SELECT DISTINCT order_id
    FROM order_items
    WHERE product_id = 'iphone-15-pro'
      AND status = 'paid'
)
SELECT
    oi.product_id,
    COUNT(DISTINCT oi.order_id)                            AS attached_orders,
    COUNT(DISTINCT oi.order_id)::NUMERIC * 100
        / NULLIF((SELECT COUNT(*) FROM primary_orders), 0) AS attach_pct,
    SUM(oi.unit_price * oi.qty)::NUMERIC                   AS addon_revenue,
    SUM(oi.unit_price * oi.qty * p.margin_pct / 100)
        ::NUMERIC                                          AS addon_margin
FROM order_items oi
JOIN products p USING (product_id)
JOIN primary_orders po USING (order_id)
WHERE p.role IN ('accessory', 'warranty', 'addon')
  AND oi.status = 'paid'
GROUP BY oi.product_id, p.margin_pct
ORDER BY addon_margin DESC;

Sort by addon_margin and the next merchandising sprint priority list writes itself. Sort by attach_pct and you get crowd favorites that may not deserve the shelf space they hold.

6. Month-over-month dynamics

When somebody pings on Slack with "did Attach Rate move last month?", the answer should be one query.

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', oi.created_at)::DATE AS month,
        COUNT(DISTINCT CASE WHEN p.role = 'primary' THEN oi.order_id END) AS primary_orders,
        COUNT(DISTINCT CASE
            WHEN p.role = 'primary' AND EXISTS (
                SELECT 1
                FROM order_items oi2
                JOIN products p2 USING (product_id)
                WHERE oi2.order_id = oi.order_id
                  AND p2.role IN ('accessory', 'warranty', 'addon')
                  AND oi2.status = 'paid'
            )
            THEN oi.order_id END) AS attached_orders
    FROM order_items oi
    JOIN products p USING (product_id)
    WHERE oi.status = 'paid'
    GROUP BY 1
)
SELECT
    month,
    attached_orders::NUMERIC * 100 / NULLIF(primary_orders, 0)        AS attach_rate_pct,
    LAG(attached_orders::NUMERIC * 100 / NULLIF(primary_orders, 0))
        OVER (ORDER BY month)                                         AS prev_attach_pct,
    ROUND(
        attached_orders::NUMERIC * 100 / NULLIF(primary_orders, 0)
        - LAG(attached_orders::NUMERIC * 100 / NULLIF(primary_orders, 0))
              OVER (ORDER BY month),
        2
    ) AS mom_pp_change
FROM monthly
ORDER BY month;

The mom_pp_change column is in percentage points, which is the unit reviewers expect for rate changes. Reporting Attach Rate movement in relative percent confuses everyone in the room.

Common pitfalls

The first trap is counting bundle components as attach. A primary product that ships with a built-in accessory — phone with included cable, console with included controller — should not have that bundled accessory inflate the rate. The fix lives at the catalog layer: tag bundle components with role = 'bundle_component' and exclude that role from the numerator. Otherwise the dashboard reports a flattering number that no merchandising lever can move.

The second trap is ignoring returns. A buyer who attached a case and then returned it produced revenue and then took it back. Gross Attach Rate counts that buyer; net Attach Rate does not. Both are legitimate views, and the one finance signs off on is almost always the net version. Bake the return logic into the analytics layer and expose both flavors.

The third trap is reporting Attach Rate without revenue or margin context. A thirty-five percent attach on a SKU that contributes two dollars per attach is worth less than a twelve percent attach on a warranty that contributes fifty dollars per attach. Query 5 is the smallest decomposition that prevents the team from optimizing a vanity number.

The fourth trap is comparing Attach Rates across different primary products. Phones, laptops, and tablets sit on completely different distributions. A flat tablet rate next to a thirty-five percent phone rate is not a tablet problem; it is a difference in the merchandising motion. Always slice by primary_product and treat the global aggregate as a tracking metric.

The fifth trap is collapsing pre-checkout and post-purchase attach into one number. The first is driven by the PDP and cart; the second by lifecycle email and push. Query 4 separates them and gives each owner an unambiguous number to move.

Optimization tips

Past a few hundred million order-item rows, three things start to matter. First, partition order_items by created_at — monthly partitions are standard. Attach Rate queries are always range-bounded by date, and partition pruning collapses the scan to the months in scope.

Second, materialize the orders-with-attach mapping. The same EXISTS subquery appears in every Attach Rate query, and recomputing it on every dashboard load is pure waste. A nightly materialized view keyed on order_id with a boolean has_attach column lets every downstream query reduce to a fast group-by.

Third, watch the DISTINCT cost on order-item joins. A primary product with multiple paid line items inflates the join cardinality, and COUNT(DISTINCT order_id) is expensive at scale. Replace it with a pre-aggregated CTE keyed by order_id, then count rows in the outer query.

Related reading

• How to calculate AOV in SQL — the order-value counterpart to Attach Rate, with the same per-category, per-channel decomposition logic.
• How to calculate add-to-cart rate in SQL — the upstream funnel metric that feeds the attach denominator.
• SQL window functions interview questions — the LAG and ROW_NUMBER patterns from the cohort and decomposition queries.
• A/B testing peeking mistake — what to watch for when Attach Rate is the success metric of a checkout experiment.

If you want to drill SQL questions like this daily, NAILDD is launching with hundreds of analytics SQL problems built around this pattern.

FAQ

What is a healthy Attach Rate?

It depends entirely on category and the breadth of the addon catalog. Phones with cases typically land in the thirty to forty percent range. Extended warranties on laptops cluster around fifteen to twenty-five percent. Audio accessories on smartphones — earbuds, dongles — sit closer to twenty to thirty percent. SaaS add-on attach varies wildly with the catalog: a single-module add-on might attach at five percent of paid seats, while a mature catalog of fifteen modules can attach more than half of paying teams. Use industry benchmarks as a sanity check, not a target.

How do I lift Attach Rate without lowering margin?

Four levers in roughly decreasing order of impact. Recommend the highest-margin addon on the product detail page rather than the highest-volume one. Build a one-tap upsell into the cart so the friction is zero. Bundle a high-margin warranty with a small discount that still preserves most of the margin. Educate during onboarding — buyers who understand why a case extends the life of a phone attach a case more often than buyers who see it as a generic accessory.

Does Attach Rate apply to SaaS as well as retail?

Yes, with a renamed denominator. For SaaS, Attach Rate is the share of paying teams that also pay for at least one add-on module — premium analytics on a base seat, an admin SSO upgrade, a priority support tier. Notion, Linear, and Slack all measure this number. The same SQL structure works: replace order_items with subscription_items, primary with the base plan, and accessory with the add-on catalog.

How is Attach Rate different from cross-sell?

Attach happens inside the same order; cross-sell happens in a later order. The line is sharp on the data side — same order_id versus different order_id — and it matters because the surfaces, teams, and levers differ. Attach is owned by the PDP and cart team and lives in the checkout funnel. Cross-sell is owned by lifecycle marketing and lives in email, push, and in-app surfaces over weeks. A dashboard that conflates them produces a number neither team can act on.

Should I exclude returns from Attach Rate?

Report both. Gross Attach Rate counts every paid addon at order time and tells you how the cart and PDP are performing. Net Attach Rate subtracts the addons that were ultimately returned and tells you what revenue stuck. The gap is itself a number worth tracking — a widening gap signals a sizing, fit, or quality problem in the addon catalog.