Blog · How-to guides · Distribution
Market basket analysis measures which products appear in the same order. Build an order-level table from an order-line export, calculate support, confidence and lift, and check the value of the baskets behind each pair.
Market basket analysis finds products bought together by counting their presence in the same order. Start with an order-line export, reduce repeated product lines to a single presence flag, and compare pair frequency with each product's overall frequency. Order value adds commercial context, but co-purchase patterns alone do not prove that a cross-sell offer will increase sales.
For a US industrial distributor, a basket can be a completed customer order. Use the original order ID across split shipments; using invoice numbers instead can separate products that the customer bought together. Choose one order-date period and a consistent status, such as completed orders excluding canceled transactions.
The required export columns are order ID, product code and net line revenue in USD. Keep customer segment and order date if you want comparable groups later. Quantities help interpret the purchase, but a customer buying several units of one product still contributes only one order containing that product.
Oracle's association documentation describes market basket analysis as examining items associated within transactions. Here the business question is deliberately narrow: do seals accompany pump orders often enough to justify reviewing an offer?
An expected basket describes what a customer might be expected to buy. This analysis starts with what customers actually ordered.
Put this small export in a worksheet named Lines, with headers in row 1 and the records in rows 2 through 14. Revenue is already after line discounts; this example has no returns, freight or sales tax.
| Order ID | Product | Net line revenue (USD) |
|---|---|---|
| O1 | Pump | 100 |
| O1 | Seal | 40 |
| O2 | Pump | 150 |
| O2 | Seal | 60 |
| O3 | Pump | 100 |
| O4 | Seal | 60 |
| O5 | Pump | 100 |
| O5 | Seal | 30 |
| O5 | Hose | 50 |
| O6 | Hose | 90 |
| O7 | Pump | 100 |
| O7 | Hose | 60 |
| O8 | Seal | 70 |
Create a second sheet with each distinct order ID once in A2:A9. Set B1 to Pump, C1 to Seal, D1 to Hose and E1 to Order revenue. In B2, enter this formula and fill across through D, then down:
=--(COUNTIFS(Lines!$A$2:$A$14,$A2,Lines!$B$2:$B$14,B$1)>0)
COUNTIFS counts records meeting all the supplied conditions, as Microsoft's function documentation specifies. Testing whether the count exceeds zero makes repeated lines a single presence flag. The formulas here use Excel.
Calculate E2 and fill down:
=SUMIFS(Lines!$C$2:$C$14,Lines!$A$2:$A$14,$A2)
The resulting table is the calculation base:
| Order ID | Pump | Seal | Hose | Order revenue (USD) |
|---|---|---|---|---|
| O1 | 1 | 1 | 0 | 140 |
| O2 | 1 | 1 | 0 | 210 |
| O3 | 1 | 0 | 0 | 100 |
| O4 | 0 | 1 | 0 | 60 |
| O5 | 1 | 1 | 1 | 180 |
| O6 | 0 | 0 | 1 | 90 |
| O7 | 1 | 0 | 1 | 160 |
| O8 | 0 | 1 | 0 | 70 |
| Total | 5 | 5 | 3 | 1,010 |
There are eight orders, five containing pumps, five containing seals and three containing both. Preserve orders containing neither selected product: removing them changes the population used for support and lift.
For the direction Pump to Seal:
Support = Orders containing both / All orders
Confidence = Orders containing both / Orders containing Pump
Lift = Confidence / Share of all orders containing Seal
These follow Oracle's definitions of support, confidence and lift. Confidence has a direction; lift compares that conditional rate with the second product's baseline frequency.
| Measure | Calculation from the order table | Result |
|---|---|---|
| Paired orders | O1, O2, O5 | 3 |
| Support | 3 / 8 | 37.5% |
| Confidence, Pump to Seal | 3 / 5 | 60.0% |
| Seal baseline frequency | 5 / 8 | 62.5% |
| Lift | 60.0% / 62.5% | 0.96 |
Use these formulas against the order-level sheet, in the same sequence:
=COUNTIFS(B2:B9,1,C2:C9,1)
=COUNTIFS(B2:B9,1,C2:C9,1)/COUNTA(A2:A9)
=COUNTIFS(B2:B9,1,C2:C9,1)/SUM(B2:B9)
=SUM(C2:C9)/COUNTA(A2:A9)
=(COUNTIFS(B2:B9,1,C2:C9,1)/SUM(B2:B9))/(SUM(C2:C9)/COUNTA(A2:A9))
The pair looks common, yet pump buyers include seals slightly less frequently than orders overall. Lift of 0.96 provides no evidence of a positive association in this small set. Always show the counts: a rate based on a handful of orders is a lead to investigate, not a stable buying rule.
Paired orders have revenue of 140 + 210 + 180 = $530. Their average order value is $176.67, compared with $130.00 for pump orders without seals: (100 + 160) / 2. Display dollar averages rounded to cents and percentages to one decimal place; calculate lift from unrounded rates.
=SUMIFS(E2:E9,B2:B9,1,C2:C9,1)
=SUMIFS(E2:E9,B2:B9,1,C2:C9,1)/COUNTIFS(B2:B9,1,C2:C9,1)
=SUMIFS(E2:E9,B2:B9,1,C2:C9,0)/COUNTIFS(B2:B9,1,C2:C9,0)
The higher average does not isolate the value added by seals: the pump prices differ and one paired order includes a hose. Check gross profit margin before turning the association into a discounted bundle.
Two controls prove the construction. The order table's $1,010 must equal the sum of the original line revenue. Paired orders must never exceed either product's order count. A pump split across repeated lines may increase line counts, but it must leave the pump presence flag unchanged.
Repeat the comparison over comparable periods before operationalizing it. A market basket analysis dashboard can organize recurring review; the calculation and its population should remain visible behind the display.
Bring an order-line export and the product pairs you want to compare: Covirage maps the columns, and its tools compute and check the supplied counts and values. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.
See Covirage for distributors for the wider business context. Covirage is not affiliated with Microsoft, Google or Oracle.
Support divides orders containing both products by all eligible orders. Confidence divides those same paired orders by orders containing the starting product. Support describes how common the pair is across the business; confidence describes how often the second product accompanies the first.
Lift below one means the second product appears less often among buyers of the first product than among orders overall. A pair can have substantial sales and high confidence while still having lift below one because the second product is already widely purchased.
No. Co-purchases describe observed orders and can reflect customer type, availability or an existing promotion. Use the result to choose a commercial test, then compare incremental contribution and customer response. Historical basket value alone does not establish the extra revenue a new offer will produce.