Recipe Tableau and SQL

Which products are really bought together?

Flag every basket that holds your anchor product, count how many of those baskets hold each other product (confidence), then divide by that product's share of all baskets (lift). Lift above 1 means the pair sells together more than chance. The most common add-on is often a false lead: compost sits in 37% of tomato-plant baskets, but it is in 41% of all baskets, so its lift is 0.91.

Updated October 30, 2026

The data

Fernhill Garden Centre till receipts, March to June 2025

Synthetic databaskets.csv

A garden centre's spring: 790 baskets across 26 products, from tomato plants and hanging baskets to bird seed and greeting cards. Each tomato plant scans as its own line.

`order_id`	`order_date`	`product`	`category`	`quantity`
T-20266	2025-03-01	Slow-Release Plant Food	Feed	2
T-20266	2025-03-01	Gardening Gloves	Garden Tools	1
T-20704	2025-03-01	Hanging Basket 14in	Pots & Baskets	2
T-20704	2025-03-01	Bedding Plants Tray	Plants	1

Step 1

How many baskets hold each product?

Count baskets, not lines: COUNTD in Tableau, DISTINCT order and product pairs in SQL. Compost is in 41% of every basket in the shop. Remember that number; it is the baseline every pairing has to beat.

Tableau

COUNTD([order_id]) / MIN({ FIXED : COUNTD([order_id]) })

The xmetric engine ran this formula and got the SQL’s Basket share column, row for row.

SQL

WITH b AS (
  SELECT DISTINCT order_id, product
  FROM input
)
SELECT
  product,
  COUNT(*) AS baskets,
  COUNT(*)
    / (
    SELECT COUNT(DISTINCT order_id)
    FROM input
  ) AS basket_share
FROM b
GROUP BY product
ORDER BY baskets DESC, product

Rows

Product	Baskets	Basket share
Peat-Free Compost 40L	326	41.3%
Herb Plant 9cm	140	17.7%
Seed Packet	139	17.6%
Bedding Plants Tray	135	17.1%
Tomato Plant 9cm	131	16.6%
Houseplant Medium	95	12%
Terracotta Pot 30cm	78	9.9%
Greeting Card	73	9.2%
Hanging Basket 14in	69	8.7%
Slow-Release Plant Food	60	7.6%
Bamboo Canes 10pk	55	7%
Suet Balls 50pk	52	6.6%
Lawn Seed 1kg	51	6.5%
Wild Bird Seed 2kg	51	6.5%
Gardening Gloves	44	5.6%
Cafe Voucher	42	5.3%
Slug Pellets	40	5.1%
Basket Liner	36	4.6%
Garden Twine	35	4.4%
Tomato Feed 1L	35	4.4%
Lawn Feed & Weed	31	3.9%
Watering Can 10L	30	3.8%
Bird Feeder	27	3.4%
Ceramic Planter	27	3.4%
Garden Rake	14	1.8%
Copper Slug Tape	2	0.3%

Step 2

Flag the baskets that hold the anchor

Save the Tableau formula as [Has anchor]. The FIXED expression stamps every line of a basket with 1 if any line in it is the anchor, so the next steps need no self-join. In a workbook, make the anchor a string parameter instead of typing the name.

Anchor share

16.6%

131 anchor baskets

Tableau

{ FIXED [order_id] : MAX(IF [product] = "{knob.anchor}" THEN 1 ELSE 0 END) }

SQL

WITH f AS (
  SELECT
    order_id,
    MAX(CASE WHEN product = '{knob.anchor}' THEN 1 ELSE 0 END) AS has_anchor
  FROM input
  GROUP BY order_id
)
SELECT
  SUM(has_anchor) AS anchor_baskets,
  AVG(has_anchor) AS anchor_share
FROM f

Rows

Has anchor	Has anchor
13100%	16.6%

Anchor product

Step 3

Of the anchor baskets, how many hold each product?

This is confidence, and it crowns compost: it is in more tomato baskets than anything else. Filter the anchor itself out (product <> anchor); its own confidence is always 100%.

Tableau

COUNTD(IF [Has anchor] = 1 THEN [order_id] END) / MIN({ FIXED : COUNTD(IF [Has anchor] = 1 THEN [order_id] END) })

SQL

WITH b AS (
  SELECT DISTINCT order_id, product
  FROM input
), a AS (
  SELECT order_id
  FROM b
  WHERE product = '{knob.anchor}'
)
SELECT
  b.product,
  COUNT(*) AS with_anchor,
  COUNT(*) / (
    SELECT COUNT(*)
    FROM a
  ) AS confidence
FROM b JOIN a USING (order_id)
WHERE b.product <> '{knob.anchor}'
GROUP BY b.product
ORDER BY confidence DESC, b.product

Rows

Product	With anchor	Confidence
Peat-Free Compost 40L	49	37.4%
Bamboo Canes 10pk	29	22.1%
Tomato Feed 1L	24	18.3%
Herb Plant 9cm	19	14.5%
Garden Twine	16	12.2%
Houseplant Medium	13	9.9%
Seed Packet	13	9.9%
Greeting Card	11	8.4%
Terracotta Pot 30cm	11	8.4%
Gardening Gloves	9	6.9%
Cafe Voucher	8	6.1%
Slug Pellets	7	5.3%
Slow-Release Plant Food	4	3.1%
Watering Can 10L	4	3.1%
Ceramic Planter	3	2.3%
Basket Liner	2	1.5%
Copper Slug Tape	2	1.5%
Suet Balls 50pk	2	1.5%
Garden Rake	1	0.8%
Lawn Feed & Weed	1	0.8%

Why isn't the most common add-on the best one?

Because it is in every basket anyway. Peat-Free Compost 40L is the product most often bought with Tomato Plant 9cm, but confidence alone can't tell a real pairing from a product everyone buys. For tomato plants, compost is in 37% of the baskets and in 41% of all baskets: a lift of 0.91, a touch below chance. Lift asks the better question: how much more often does this product turn up with the anchor than without it?

Step 4

Divide by how common each product is anyway

Lift is confidence over the product's share of all baskets. The reference line at 1 is chance. Compost drops below it; tomato feed, bamboo canes and twine rise to the top. [Confidence] is the previous step saved as a field. In Tableau, add a filter on COUNTD(IF [Has anchor] = 1 THEN [order_id] END) >= the minimum.

Tableau

[Confidence] / (COUNTD([order_id]) / MIN({ FIXED : COUNTD([order_id]) }))

SQL

WITH b AS (
  SELECT DISTINCT order_id, product
  FROM input
), a AS (
  SELECT order_id
  FROM b
  WHERE product = '{knob.anchor}'
), s AS (
  SELECT
    product,
    COUNT(*)
      / (
      SELECT COUNT(DISTINCT order_id)
      FROM b
    ) AS basket_share
  FROM b
  GROUP BY product
), c AS (
  SELECT
    b.product,
    COUNT(*) AS with_anchor,
    COUNT(*)
      / (
      SELECT COUNT(*)
      FROM a
    ) AS confidence
  FROM b JOIN a USING (order_id)
  WHERE b.product <> '{knob.anchor}'
  GROUP BY b.product
)
SELECT
  c.product,
  c.with_anchor,
  c.confidence,
  c.confidence / s.basket_share AS lift
FROM c JOIN s USING (product)
WHERE c.with_anchor >= 10
ORDER BY lift DESC, c.product

Rows

Product	With anchor	Confidence	Lift
Tomato Feed 1L	24	0.18	4.14
Bamboo Canes 10pk	29	0.22	3.18
Garden Twine	16	0.12	2.76
Greeting Card	11	0.08	0.91
Peat-Free Compost 40L	49	0.37	0.91
Terracotta Pot 30cm	11	0.08	0.85
Houseplant Medium	13	0.1	0.83
Herb Plant 9cm	19	0.15	0.82
Seed Packet	13	0.1	0.56

With at least 10 shared baskets, the strongest partner for Tomato Plant 9cm is Tomato Feed 1L, at 4.1 times chance. Switch the anchor: hanging baskets pull in basket liners, bird feeders pull in wild bird seed, lawn seed pulls in lawn feed. Compost, the most common item in the shop, is the top partner for none of them.

Common mistake

Ranking add-ons by confidence

Confidence rewards products that are in every basket. Compost tops the tomato list at 37% only because it is in 41% of all baskets; its lift is 0.91, slightly below chance. Rank by lift, with a minimum count.

WrongStarts with Peat-Free Compost 40L, Bamboo Canes 10pk, Tomato Feed 1L…

Point at a bar to read it.

Tableau

COUNTD(IF [Has anchor] = 1 THEN [order_id] END) / MIN({ FIXED : COUNTD(IF [Has anchor] = 1 THEN [order_id] END) })
// sorted descending

SQL

WITH b AS (
  SELECT DISTINCT order_id, product
  FROM input
), a AS (
  SELECT order_id
  FROM b
  WHERE product = 'Tomato Plant 9cm'
)
SELECT
  b.product,
  COUNT(*) / (
    SELECT COUNT(*)
    FROM a
  ) AS confidence
FROM b JOIN a USING (order_id)
WHERE b.product <> 'Tomato Plant 9cm'
GROUP BY b.product
ORDER BY confidence DESC, b.product
LIMIT 3

RightStarts with Tomato Feed 1L, Bamboo Canes 10pk, Garden Twine…

Point at a bar to read it.

Tableau

[Confidence] / (COUNTD([order_id]) / MIN({ FIXED : COUNTD([order_id]) }))
// sorted descending, minimum 10 baskets

SQL

WITH b AS (
  SELECT DISTINCT order_id, product
  FROM input
), a AS (
  SELECT order_id
  FROM b
  WHERE product = 'Tomato Plant 9cm'
), s AS (
  SELECT
    product,
    COUNT(*)
      / (
      SELECT COUNT(DISTINCT order_id)
      FROM b
    ) AS basket_share
  FROM b
  GROUP BY product
), c AS (
  SELECT
    b.product,
    COUNT(*) AS with_anchor,
    COUNT(*)
      / (
      SELECT COUNT(*)
      FROM a
    ) AS confidence
  FROM b JOIN a USING (order_id)
  WHERE b.product <> 'Tomato Plant 9cm'
  GROUP BY b.product
)
SELECT c.product, c.confidence / s.basket_share AS lift
FROM c JOIN s USING (product)
WHERE c.with_anchor >= 10
ORDER BY lift DESC, c.product
LIMIT 3

Step 5

Read every pairing in one table

Support says whether a pair is common enough to act on, confidence says how often the anchor brings the product along, lift says whether that is more than chance. A bundle needs all three.

Tableau

IF COUNTD(IF [Has anchor] = 1 THEN [order_id] END) < 10 THEN "Too few baskets"
ELSEIF [Lift] >= 1.5 THEN "Bought together"
ELSEIF [Lift] <= 0.67 THEN "Bought apart"
ELSE "Chance"
END

SQL

WITH b AS (
  SELECT DISTINCT order_id, product
  FROM input
), a AS (
  SELECT order_id
  FROM b
  WHERE product = '{knob.anchor}'
), s AS (
  SELECT
    product,
    COUNT(*) AS baskets,
    COUNT(*)
      / (
      SELECT COUNT(DISTINCT order_id)
      FROM b
    ) AS basket_share
  FROM b
  GROUP BY product
), c AS (
  SELECT
    b.product,
    COUNT(*) AS with_anchor,
    COUNT(*)
      / (
      SELECT COUNT(*)
      FROM a
    ) AS confidence
  FROM b JOIN a USING (order_id)
  WHERE b.product <> '{knob.anchor}'
  GROUP BY b.product
)
SELECT
  c.product,
  s.baskets,
  c.with_anchor,
  c.with_anchor
    / (
    SELECT COUNT(DISTINCT order_id)
    FROM b
  ) AS support,
  c.confidence,
  c.confidence / s.basket_share AS lift,
  CASE
    WHEN c.with_anchor < 10 THEN 'Too few baskets'
    WHEN c.confidence / s.basket_share >= 1.5 THEN 'Bought together'
    WHEN c.confidence / s.basket_share <= 0.67 THEN 'Bought apart'
    ELSE 'Chance'
    END AS reading
FROM c JOIN s USING (product)
ORDER BY lift DESC, c.product

Rows

Product	Baskets	With anchor	Support	Confidence	Lift	Reading
Copper Slug Tape	2	2	0.3%	1.5%	6.03	Too few baskets
Tomato Feed 1L	35	24	3%	18.3%	4.14	Bought together
Bamboo Canes 10pk	55	29	3.7%	22.1%	3.18	Bought together
Garden Twine	35	16	2%	12.2%	2.76	Bought together
Gardening Gloves	44	9	1.1%	6.9%	1.23	Too few baskets
Cafe Voucher	42	8	1%	6.1%	1.15	Too few baskets
Slug Pellets	40	7	0.9%	5.3%	1.06	Too few baskets
Greeting Card	73	11	1.4%	8.4%	0.91	Chance
Peat-Free Compost 40L	326	49	6.2%	37.4%	0.91	Chance
Terracotta Pot 30cm	78	11	1.4%	8.4%	0.85	Chance
Houseplant Medium	95	13	1.6%	9.9%	0.83	Chance
Herb Plant 9cm	140	19	2.4%	14.5%	0.82	Chance
Watering Can 10L	30	4	0.5%	3.1%	0.8	Too few baskets
Ceramic Planter	27	3	0.4%	2.3%	0.67	Too few baskets
Seed Packet	139	13	1.6%	9.9%	0.56	Bought apart
Garden Rake	14	1	0.1%	0.8%	0.43	Too few baskets
Slow-Release Plant Food	60	4	0.5%	3.1%	0.4	Too few baskets
Basket Liner	36	2	0.3%	1.5%	0.34	Too few baskets
Suet Balls 50pk	52	2	0.3%	1.5%	0.23	Too few baskets
Lawn Feed & Weed	31	1	0.1%	0.8%	0.19	Too few baskets

How many baskets are enough?

Enough that one or two shoppers can't move the number. Two rare items bought together once get an enormous lift.

Minimum baskets with both10120

Point at the line to read it.

Common mistake

Trusting a lift with no minimum count

Copper Slug Tape sold twice all spring, both times with tomatoes, so its lift is 6.0, higher than tomato feed at 4.1. Two baskets prove nothing. Drag the minimum down to 1 and watch it jump to the top.

WrongStarts with Copper Slug Tape…

Point at a bar to read it.

SQL

WITH b AS (
  SELECT DISTINCT order_id, product
  FROM input
), a AS (
  SELECT order_id
  FROM b
  WHERE product = 'Tomato Plant 9cm'
), s AS (
  SELECT
    product,
    COUNT(*)
      / (
      SELECT COUNT(DISTINCT order_id)
      FROM b
    ) AS basket_share
  FROM b
  GROUP BY product
), c AS (
  SELECT
    b.product,
    COUNT(*) AS with_anchor,
    COUNT(*)
      / (
      SELECT COUNT(*)
      FROM a
    ) AS confidence
  FROM b JOIN a USING (order_id)
  WHERE b.product <> 'Tomato Plant 9cm'
  GROUP BY b.product
)
SELECT
  c.product,
  c.with_anchor,
  c.confidence / s.basket_share AS lift
FROM c JOIN s USING (product)
ORDER BY lift DESC, c.product
LIMIT 1

RightStarts with Tomato Feed 1L…

Point at a bar to read it.

SQL

WITH b AS (
  SELECT DISTINCT order_id, product
  FROM input
), a AS (
  SELECT order_id
  FROM b
  WHERE product = 'Tomato Plant 9cm'
), s AS (
  SELECT
    product,
    COUNT(*)
      / (
      SELECT COUNT(DISTINCT order_id)
      FROM b
    ) AS basket_share
  FROM b
  GROUP BY product
), c AS (
  SELECT
    b.product,
    COUNT(*) AS with_anchor,
    COUNT(*)
      / (
      SELECT COUNT(*)
      FROM a
    ) AS confidence
  FROM b JOIN a USING (order_id)
  WHERE b.product <> 'Tomato Plant 9cm'
  GROUP BY b.product
)
SELECT
  c.product,
  c.with_anchor,
  c.confidence / s.basket_share AS lift
FROM c JOIN s USING (product)
WHERE c.with_anchor >= 10
ORDER BY lift DESC, c.product
LIMIT 1

Lines or baskets?

Baskets. Tills and order systems often write one line per unit or per scan, and a plain COUNT counts the same basket several times.

Common mistake

Counting lines instead of baskets

Each tomato plant scans as its own line, so a basket with four plants joins four times. Counted by lines, compost appears in 112 tomato baskets; there are only 49. Deduplicate to one row per basket and product first, or use COUNTD.

WrongStarts with Peat-Free Compost 40L…

Point at a bar to read it.

Tableau

COUNT(IF [Has anchor] = 1 THEN [order_id] END)

SQL

WITH a AS (
  SELECT order_id
  FROM input
  WHERE product = 'Tomato Plant 9cm'
)
SELECT i.product, COUNT(*) AS with_anchor
FROM input i JOIN a USING (order_id)
WHERE i.product = 'Peat-Free Compost 40L'
GROUP BY i.product

RightStarts with Peat-Free Compost 40L…

Point at a bar to read it.

Tableau

COUNTD(IF [Has anchor] = 1 THEN [order_id] END)

SQL

WITH b AS (
  SELECT DISTINCT order_id, product
  FROM input
), a AS (
  SELECT order_id
  FROM b
  WHERE product = 'Tomato Plant 9cm'
)
SELECT b.product, COUNT(*) AS with_anchor
FROM b JOIN a USING (order_id)
WHERE b.product = 'Peat-Free Compost 40L'
GROUP BY b.product

Run it on your own data

Any table with an order or basket id and a product column works. Drop your CSV, pick the two columns and an anchor; the steps above recompute in your browser, and nothing is uploaded. If every size and colour is its own SKU, roll them up to the parent product first, or the pairs split across dozens of rows.

Drop a CSV anywhere on this page

Up to 10,000 rows. The sample, baskets.csv, shows the shape.

Questions

What are support, confidence and lift?

Support is the share of all baskets that hold both products. Confidence is the share of anchor baskets that also hold the other product. Lift is confidence divided by the other product's share of all baskets: how much more often the pair meets than it would by chance.

What lift counts as strong?

Above 1.5 is worth a look, above 3 is a clear pairing, near 1 is chance, and below about 0.67 means the products tend to be bought apart, often because one replaces the other. Read lift only with enough baskets behind it.

Is lift symmetric?

Yes. The lift of tomato plants with tomato feed equals the lift of tomato feed with tomato plants. Confidence is not: most tomato-feed baskets hold a tomato plant, but most tomato-plant baskets don't hold feed.

What if the second product is bought a week later?

Basket analysis works per order. For cross-sell over time, flag customers instead of baskets: replace [order_id] with [customer_id] in the FIXED flag and count later purchases.

Is my data uploaded?

No. Your CSV is read and calculated in this browser tab.

Sources

Tableau: FIXED level of detail expressions checked October 30, 2026
DuckDB: aggregate functions checked October 30, 2026