Which products are most often bought together?
By the Analistable team · Updated · 3 min read
Join the order-lines table to itself on order ID, keep pairs where product A sorts before product B (so each pair counts once), and count orders per pair. In the example, Desk + Lamp appear together in 3 of 5 orders; 3 of the 4 orders with a Desk also include a Lamp (75% confidence) — a natural bundle or recommendation.
Part of our guide: How to answer questions across multiple spreadsheets
The data
| Order | Products |
|---|---|
| O1 | Desk, Lamp |
| O2 | Desk, Chair |
| O3 | Desk, Lamp |
| O4 | Chair, Lamp |
| O5 | Desk, Lamp, Chair |
The export has one row per order line (order ID, product), not one row per order.
In SQL
SELECT a.product AS product_a, b.product AS product_b, COUNT(*) AS orders
FROM lines a
JOIN lines b ON a.order_id = b.order_id AND a.product < b.product
GROUP BY a.product, b.product
ORDER BY orders DESC;| product_a | product_b | orders |
|---|---|---|
| Desk | Lamp | 3 |
| Chair | Desk | 2 |
| Chair | Lamp | 2 |
Support and confidence
| Measure | Calculation | Value |
|---|---|---|
| Support | orders with both ÷ all orders | 3 ÷ 5 = 60% |
| Confidence | orders with both ÷ orders with Desk | 3 ÷ 4 = 75% |
High confidence with reasonable support is what makes a good “customers also bought” suggestion. With small numbers of orders, treat the percentages as hints.
In a spreadsheet
Pivot the order lines with Order in rows, Product in columns and Count as values to get a 0/1 matrix; then =SUMPRODUCT((Matrix[Desk]>0) * (Matrix[Lamp]>0)) counts orders with both. This gets unwieldy beyond a few dozen products — SQL scales better.
Ask it in Analistable: “Which pairs of products are most often bought in the same order?” Combine exports from several stores first: top products across stores.
Across several stores
Stack order lines from each store first, but keep the store in the order ID (for example WEB-O1, AMZ-O1) so lines from different stores never pair up by accident. Then run the same self-join.
Lift: is the pair more than coincidence?
WITH n AS (SELECT COUNT(DISTINCT order_id) AS total FROM lines),
p AS (SELECT product, COUNT(DISTINCT order_id) AS orders FROM lines GROUP BY product),
pairs AS (
SELECT a.product AS a, b.product AS b, COUNT(*) AS both_orders
FROM lines a JOIN lines b ON a.order_id = b.order_id AND a.product <> b.product
GROUP BY a.product, b.product
)
SELECT pairs.a, pairs.b, both_orders,
ROUND(100.0 * both_orders / pa.orders, 1) AS confidence_pct,
ROUND((1.0 * both_orders / pa.orders) / (1.0 * pb.orders / n.total), 2) AS lift
FROM pairs
JOIN p pa ON pa.product = pairs.a
JOIN p pb ON pb.product = pairs.b
CROSS JOIN n
ORDER BY lift DESC;| a | b | both_orders | confidence_pct | lift |
|---|---|---|---|---|
| Desk | Lamp | 3 | 75.0 | 0.94 |
| Lamp | Desk | 3 | 75.0 | 0.94 |
| Chair | Desk | 2 | 66.7 | 0.83 |
| Chair | Lamp | 2 | 66.7 | 0.83 |
Lift = confidence ÷ the share of all orders containing product B. Lamps are in 4 of 5 orders (80%), so 75% of Desk orders including a Lamp is actually slightly below what you'd expect by chance (lift 0.94). A lift above 1 means the products attract each other; below 1, the pair is mostly explained by one product being popular. In this tiny example no pair is above 1 — with real volumes, rank recommendations by lift among pairs with enough orders.
Practical thresholds
- Set a minimum number of orders per pair (for example 20) before trusting confidence or lift.
- Exclude items in almost every order (gift wrap, shipping protection) — they pair with everything and add nothing.
- Count orders, not quantities: three lamps in one order is still one co-occurrence. The self-join above counts order lines, so de-duplicate lines per order and product first if an order can list the same product twice.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Each pair appears twice | Join uses <> instead of < | Use a.product < b.product for counts |
| Product paired with itself | Join condition missing the product comparison | Add a.product < b.product |
| Query very slow | Huge orders (wholesale) create many pairs | Exclude orders with more than, say, 20 lines |
| No pairs found | Export is one row per order with products in one cell | Split products into one row per line first |
Frequently asked questions
- How do I find products bought together?
- Self-join order lines on order ID, keep each pair once (product A < product B) and count orders per pair.
- What are support and confidence?
- Support is the share of all orders containing both products; confidence is the share of orders with product A that also contain product B.
- Can I do this in Excel?
- For a handful of products, with a pivot-table matrix and SUMPRODUCT. For many products, SQL is far simpler.