Support, confidence and lift explained, with an Excel example

The three formulas

With N baskets, n(A) baskets containing A and n(A and B) baskets containing both:

• Support(A) = n(A) ÷ N and Support(A, B) = n(A and B) ÷ N
• Confidence(A → B) = n(A and B) ÷ n(A), the probability of B given A
• Lift(A → B) = Confidence(A → B) ÷ Support(B) = Support(A, B) ÷ (Support(A) × Support(B))

Support and lift are symmetric: A → B and B → A have the same values. Confidence is not, because it divides by the baskets of the item on the left.

Step 1: baskets as 0/1 rows and item support

Put one basket per row and one item per column, with 1 if the item is in the basket and 0 if not. Then counting is adding: =SUM(B2:B17) gives the 10 baskets with bread, and dividing by the 16 baskets gives a support of 62.5%. Butter is in 37.5% of the baskets, milk in 75.0% and coffee in 31.3%. Support is a share of baskets, not of items sold, so always divide by the number of baskets.

Step 2: count pairs with SUMPRODUCT

A basket contains both items when both cells are 1, so their product is 1. =SUMPRODUCT(B2:B17,C2:C17) counts the 5 baskets with bread and butter, a pair support of 5 ÷ 16 = 31.3%. The same formula on bread and milk gives 7, and on milk and coffee 3. For many items, fill a whole co-occurrence matrix with this formula, as the pair support lab does.

Worked example: 16 baskets, three rules

Rows 2 to 17 are the baskets of a fictional grocery store, 1 = item in the basket. Rows 18 to 30 calculate support, confidence and lift.

Worked example: 16 baskets, three rules

	A	B	C	D	E
1	Basket	Bread	Butter	Milk	Coffee
2	#1	1	1	1	0
3	#2	1	1	0	0
4	#3	1	0	1	0
5	#4	0	0	1	1
6	#5	1	1	1	0
7	#6	1	0	0	1
8	#7	0	0	1	0
9	#8	1	1	1	0
10	#9	1	0	1	1
11	#10	0	1	1	0
12	#11	1	0	1	0
13	#12	0	0	1	1
14	#13	1	1	0	0
15	#14	0	0	0	1
16	#15	1	0	1	0
17	#16	0	0	1	0
18	Baskets with item	10	6	12	5
19	Support (share of baskets)	62.5%	37.5%	75.0%	31.3%
20	Rule metric	Value
21	Baskets with Bread and Butter	5
22	Support {Bread, Butter}	31.3%
23	Confidence Bread → Butter	50.0%
24	Confidence Butter → Bread	83.3%
25	Lift Bread ↔ Butter	1.33
26	Baskets with Bread and Milk	7
27	Confidence Bread → Milk	70.0%
28	Lift Bread ↔ Milk	0.93
29	Baskets with Milk and Coffee	3
30	Lift Milk ↔ Coffee	0.80

Illustrative data. A real analysis uses thousands of baskets and minimum support and confidence thresholds.

Step 3: confidence in both directions

Confidence of Bread → Butter is 5 ÷ 10 = 50.0%: half of the bread buyers also take butter. Confidence of Butter → Bread is 5 ÷ 6 = 83.3%: butter buyers nearly always take bread. Same pair, two different rules. If you want to suggest butter at the bread shelf, the first number is the relevant one.

Step 4: lift, the check against chance

Confidence alone flatters popular items. Bread → Milk has a confidence of 70.0%, which sounds like a strong rule, but 75.0% of all baskets contain milk. The lift is 70.0% ÷ 75.0% = 0.93: bread buyers take milk slightly less often than shoppers in general. Bread → Butter has a lower confidence, 50.0%, but butter is only in 37.5% of baskets, so the lift is 50.0% ÷ 37.5% = 1.33. Knowing that a basket contains bread makes butter a third more likely.

• Lift above 1: the items go together more often than chance, candidates for cross-selling, bundles and joint displays.
• Lift around 1: no relationship, however high the confidence.
• Lift below 1: the items appear together less often than chance, often substitutes. Milk and coffee here have a lift of 0.80.

Thresholds, sample size and what to do with a rule

Real analyses first drop rules below a minimum support, so rare pairs do not produce huge but meaningless lifts, then a minimum confidence, and rank what is left by lift. With 16 baskets every number here is fragile: one basket more or less changes the lift visibly. On real data, check that a rule rests on enough baskets, and compare it with a simple test of independence such as the chi-square test in Excel. Lift is a ratio like the relative risk in our odds ratio guide, and it says nothing about volume or margin, so value a rule by the extra sales it can bring before acting on it.

Common mistakes

• Ranking rules by confidence alone. Rules ending in a very popular item always look strong. Check the lift before you act.
• Dividing by items instead of baskets. Support is the share of baskets. Dividing by the total of all 1s understates it.
• Treating confidence as symmetric. Bread → Butter (50.0%) and Butter → Bread (83.3%) are different rules. Divide by the baskets of the left-hand item.
• Trusting a big lift on a tiny support. Two rare items bought together twice can show a lift of 5 or more. Set a minimum support first.
• Reading lift as causation. A lift above 1 shows that items are bought together, not that one causes the other. Both may follow a third factor, such as a weekend shop.
• Counting quantities instead of presence. Three loaves in one basket still count as one basket with bread. Convert quantities to 0/1 first.