How We Calculate Add-On Attach Rate — The Technical Breakdown

Technical companion to Add-On Attach Rate in The Numbers That Matter series.

---

Attach rate tells you what percentage of visits include an add-on purchase. The math is simple. The data model that makes it queryable is where the work lives.

The formulas

Attach Rate:

Attach Rate = Visits with ≥1 Add-On Purchase / Total Completed Visits × 100

Average Add-On Spend (when purchased):

Avg Add-On Spend = Total Add-On Revenue / Visits with ≥1 Add-On Purchase

Add-On Revenue Per Visit (across all visits):

Add-On RPV = Total Add-On Revenue / Total Completed Visits

Each tells you something different. Attach rate is about conversion. Average spend is about depth. Add-On RPV combines both into the overall contribution.

The query

SELECT
  COUNT(*) AS total_visits,
  COUNT(*) FILTER (WHERE add_on_count > 0) AS visits_with_addons,
  ROUND(
    COUNT(*) FILTER (WHERE add_on_count > 0)::decimal / COUNT(*) * 100, 1
  ) AS attach_rate_pct,
  ROUND(
    SUM(add_on_revenue) / NULLIF(COUNT(*) FILTER (WHERE add_on_count > 0), 0), 2
  ) AS avg_addon_spend,
  ROUND(SUM(add_on_revenue) / COUNT(*), 2) AS addon_rpv
FROM (
  SELECT
    v.id,
    COUNT(p.id) AS add_on_count,
    COALESCE(SUM(p.total), 0) AS add_on_revenue
  FROM visits v
  LEFT JOIN purchases p ON p.visit_id = v.id
    AND p.product_type = 'add_on'
  WHERE v.status = 'completed'
    AND v.checked_in_at >= NOW() - INTERVAL '30 days'
  GROUP BY v.id
) visit_addons;

Breaking it down by product

To see which products are carrying the category:

SELECT
  pr.name AS product_name,
  COUNT(DISTINCT p.visit_id) AS visits_purchased,
  SUM(p.quantity) AS units_sold,
  SUM(p.total) AS total_revenue,
  ROUND(
    COUNT(DISTINCT p.visit_id)::decimal / total_visits.cnt * 100, 1
  ) AS product_attach_rate
FROM purchases p
JOIN products pr ON p.product_id = pr.id
JOIN visits v ON p.visit_id = v.id
CROSS JOIN (
  SELECT COUNT(*) AS cnt FROM visits
  WHERE status = 'completed'
    AND checked_in_at >= NOW() - INTERVAL '30 days'
) total_visits
WHERE v.status = 'completed'
  AND v.checked_in_at >= NOW() - INTERVAL '30 days'
  AND p.product_type = 'add_on'
GROUP BY pr.name, total_visits.cnt
ORDER BY total_revenue DESC;

This shows you which products are popular (high attach rate) and which are valuable (high revenue). A product with 2% attach rate but $40 average spend is different from one with 30% attach rate and $3 average spend — both might be worth keeping, but for different reasons.

Attach rate by Host (shift performance)

Since every visit records which Host handled check-in, you can measure per-Host attach rates:

SELECT
  s.name AS host_name,
  COUNT(va.id) AS visits_handled,
  COUNT(va.id) FILTER (WHERE va.add_on_count > 0) AS visits_with_addons,
  ROUND(
    COUNT(va.id) FILTER (WHERE va.add_on_count > 0)::decimal
    / COUNT(va.id) * 100, 1
  ) AS attach_rate_pct
FROM (
  SELECT v.id, v.host_id,
    COUNT(p.id) AS add_on_count
  FROM visits v
  LEFT JOIN purchases p ON p.visit_id = v.id
    AND p.product_type = 'add_on'
  WHERE v.status = 'completed'
  GROUP BY v.id, v.host_id
) va
JOIN staff s ON va.host_id = s.id
GROUP BY s.name
ORDER BY attach_rate_pct DESC;

Use this for coaching, not punishment. A Host with a 45% attach rate is doing something the Host at 15% could learn from.

Timing analysis (when during the visit)

If purchases have timestamps, you can see when add-ons are bought relative to check-in:

SELECT
  CASE
    WHEN minutes_after_checkin < 5 THEN 'At check-in'
    WHEN minutes_after_checkin < 30 THEN 'First 30 min'
    WHEN minutes_after_checkin < 60 THEN '30-60 min'
    ELSE 'After 1 hour'
  END AS purchase_window,
  COUNT(*) AS purchases,
  SUM(total) AS revenue
FROM (
  SELECT p.*,
    EXTRACT(EPOCH FROM (p.created_at - v.checked_in_at)) / 60
      AS minutes_after_checkin
  FROM purchases p
  JOIN visits v ON p.visit_id = v.id
  WHERE p.product_type = 'add_on'
) timed
GROUP BY purchase_window
ORDER BY MIN(minutes_after_checkin);

If 90% of add-on purchases happen at check-in and almost none happen mid-visit, that's a signal: either members don't know they can buy things after check-in, or there's no convenient way to do it. Both are solvable.

A/B testing front desk scripts

To measure the impact of a new upselling approach, compare attach rates across two time periods:

SELECT
  CASE
    WHEN checked_in_at < '2026-03-15' THEN 'Before'
    ELSE 'After'
  END AS period,
  COUNT(*) AS visits,
  ROUND(
    COUNT(*) FILTER (WHERE add_on_count > 0)::decimal / COUNT(*) * 100, 1
  ) AS attach_rate_pct
FROM (
  SELECT v.id, v.checked_in_at, COUNT(p.id) AS add_on_count
  FROM visits v
  LEFT JOIN purchases p ON p.visit_id = v.id AND p.product_type = 'add_on'
  WHERE v.status = 'completed'
  GROUP BY v.id, v.checked_in_at
) sub
GROUP BY period;

If "After" shows a statistically meaningful increase, the new script works. If not, iterate.

---

Back to the main article: Add-On Attach Rate — The Revenue You're Leaving at the Front Desk