Market Basket Analysis on Your Own Orders: Which Products Actually Sell Together

Most cross-sells are picked by hand. Market basket analysis reads your order history to find the pairs that really sell together. Here is how support, confidence and lift work, a SQL query for WooCommerce, the traps that produce fake rules, and how to turn rules into tested cross-sells.

Every store has a cart page that suggests something else to buy. In most stores, a person chose those suggestions by instinct. WooCommerce’s own documentation is clear that cross-sells and upsells are selected manually for each product, while related products are generated from shared categories and tags. Neither method looks at what customers actually put in the same order.

Market basket analysis does. It counts which products appear together in orders, compares that with what chance alone would produce, and gives you a ranked list of pairs worth acting on. The method goes back to a 1993 IBM paper by Agrawal, Imielinski and Swami on mining association rules in large databases of customer transactions, and it still works on a WooCommerce or Magento order table today. This guide covers the three numbers behind it, a query you can run, and the mistakes that turn a basket report into a list of false leads.

The three numbers behind market basket analysis

An association rule reads “customers who buy A also buy B”. Three measures describe how good that rule is. The definitions below follow the mlxtend library’s documentation, which is the most common Python implementation.

MeasureFormulaQuestion it answers
SupportOrders containing A and B, divided by all ordersHow common is this pair at all?
Confidence (A to B)Orders with A and B, divided by orders with AOf the people who buy A, how many also buy B?
LiftConfidence (A to B) divided by the share of orders containing BDoes buying A make B more likely than usual?
LeverageSupport of the pair minus support of A times support of BHow many extra co-purchases, as a share of orders, beyond chance?

Lift is the one to sort by. The mlxtend documentation notes that if two items are independent, their lift is exactly 1. Above 1, the first item makes the second more likely. Below 1, the two tend to replace each other, which is useful too: two products with lift well under 1 are often substitutes competing for the same order.

A worked example

Take a camping store with 10,000 orders in the last year. A tent appears in 400 orders, tent stakes in 860, and both together in 107.

MeasureCalculationResult
Support107 / 10,0001.07%
Confidence, tent to stakes107 / 40026.8%
Confidence, stakes to tent107 / 86012.4%
Lift26.8% / 8.6%3.1
Leverage1.07% minus (4.0% x 8.6%)0.73 points
Illustrative figures. Note that confidence is not symmetric, while lift is.

Tent buyers are about three times as likely as the average shopper to take stakes too. The asymmetry in confidence matters for where you place the suggestion: offering stakes to tent buyers converts at roughly a quarter, while offering a tent to someone buying stakes converts at about one in eight, and most of those people already own a tent.

From order lines to rules

Market basket analysis pipeline from order lines to tested cross-sells Five steps from left to right: order lines, one row per order and product; baskets, deduplicated and filtered; pair counts with support, confidence and lift; filters for minimum count and lift; then a tested cross-sell. Order lines one row per order and product Baskets paid orders only, variants merged Pair counts support, confidence, lift per pair Filters minimum orders, promo bundles removed Tested cross-sell attach rate measured against a holdout Most of the judgement sits in the two middle steps: what counts as a basket, and which pairs are too rare to trust.
The analysis itself is simple counting. Deciding what a basket is and which pairs to ignore takes most of the effort.

Getting the baskets out of WooCommerce

WooCommerce keeps a reporting table that already has the shape you need. According to its list of installed database tables, wc_order_product_lookup links orders to products for reporting, and wc_order_stats holds the order-level figures WooCommerce Analytics uses. The query below works on MySQL 8 or MariaDB 10.2 and later. It merges variations into their parent product, keeps paid orders from the last year, and returns every pair seen in at least 20 orders.

WITH baskets AS (
  SELECT DISTINCT l.order_id, l.product_id
  FROM wp_wc_order_product_lookup l
  JOIN wp_wc_order_stats s ON s.order_id = l.order_id
  WHERE s.status IN ('wc-completed', 'wc-processing')
    AND s.date_created >= NOW() - INTERVAL 12 MONTH
),
n AS (SELECT COUNT(DISTINCT order_id) AS orders FROM baskets),
item AS (
  SELECT product_id, COUNT(*) AS orders_with
  FROM baskets GROUP BY product_id
),
pairs AS (
  SELECT a.product_id AS a_id, b.product_id AS b_id, COUNT(*) AS both_orders
  FROM baskets a
  JOIN baskets b ON a.order_id = b.order_id AND a.product_id < b.product_id
  GROUP BY a.product_id, b.product_id
  HAVING COUNT(*) >= 20
)
SELECT p.a_id, p.b_id, p.both_orders,
       p.both_orders / n.orders                     AS support,
       p.both_orders / ia.orders_with               AS conf_a_to_b,
       p.both_orders / ib.orders_with               AS conf_b_to_a,
       (p.both_orders * n.orders)
         / (ia.orders_with * ib.orders_with)        AS lift
FROM pairs p
JOIN item ia ON ia.product_id = p.a_id
JOIN item ib ON ib.product_id = p.b_id
CROSS JOIN n
ORDER BY lift DESC;

On Magento, the same logic runs against sales_order_item joined to sales_order. Use the parent item rows (where parent_item_id is null) so a configurable product and its child simple product are not counted as a pair. If your catalogue is large, run it for categories first; category pairs have far more support than product pairs and are easier to act on.

Before reading any rules, check one number: the share of orders with two or more distinct products. If 80 percent of your orders contain a single item, every pair is built from the remaining fifth, and the analysis will have little to say until you grow basket size by other means.

Six traps that create false rules

Most vendor introductions stop at the definitions. ThoughtSpot’s overview, for example, describes a five-stage process using Apriori or FP-Growth and warns that thresholds set too strict or too loose change the rules you get, but it does not go into how lift itself misleads. These are the problems that show up on real store data.

TrapWhat you seeWhat to do
Bestsellers in every ruleHigh confidence for “anything leads to your top product”Sort by lift, not confidence; a bestseller has high confidence with everything
Tiny countsLift of 15 built on 3 ordersSet a minimum pair count (20 is a reasonable start) and treat lift on small counts as noise
Your own cross-sellsThe pairs you already promote rank highestFlag pairs that were linked in the store during the period; their lift is partly your doing
Bundles and promotionsTwo products with lift of 40Exclude orders where the pair came from a bundle or a “buy both” coupon
Split variants“Shirt size M” pairs with “Shirt size L”Merge variations to the parent product, or analyse at category level
Season mixRules that only hold in one quarterCompare rules across two periods and keep the stable ones

The first trap is well known. An Alteryx community introduction to the method makes the same point with bread and eggs: very popular items produce high support even when the rule tells you little about shoppers. The third trap is the one stores miss most often. If tent stakes have been in the tent’s cross-sell slot all year, some of their lift was created by the slot. That is not a reason to drop the pair, but it is a reason not to treat the analysis as independent proof that the slot works.

The same Alteryx post also says plainly that association rules show co-occurrence, not cause. A high lift tells you two products are bought together. It does not tell you that showing one will sell the other. That question needs a test, covered below.

Turning rules into actions

Support and lift together decide what a rule is good for. A pair with strong lift but low support is a niche; a pair with strong lift and decent support deserves a permanent place in the cart.

What to do with a product pair based on its support and lift A two by two grid. High lift and high support: cart cross-sell or bundle. High lift and low support: targeted email or product page note. Low lift and high support: popular items bought together by chance, no action. Low lift and low support: ignore. Support (how common the pair is) High Low Lift Above 1.5 Near 1 Cart cross-sell or bundle Enough volume to matter and a real association. Test it. Targeted follow-up Post-purchase email or a note on the product page. Coincidence Two bestsellers in the same order by chance. No action. Ignore Rare and unrelated.
The top left box is where a store’s cart cross-sells should come from. The bottom left box is where most hand-picked cross-sells end up.
UseWooCommerceMagento / Adobe Commerce
Cart suggestionCross-sells field in the product’s Linked Products tab; the cart must include a cross-sells block to show themCross-sells per product; in Adobe Commerce, related product rules can apply them by condition
Product page suggestionUpsells field, or a block that reads your own rule listRelated products; rule based in Adobe Commerce
BundleA bundle or grouped product with its own priceBundle product type
Email follow-upExport the rule list to your email platform and trigger on purchase of ASame

Two details from the platform documentation are worth knowing. WooCommerce notes that assigning cross-sells does not add a cross-sells section to a block cart; you have to add it. Adobe Commerce offers related product rules that target the products shown as related items, upsells and cross-sells, but that feature is exclusive to Adobe Commerce and is not in Magento Open Source.

Test before you trust a rule

A rule is a hypothesis. To find out whether a cross-sell sells anything, show it to half of the eligible carts and not the other half, then compare the attach rate: the share of orders containing A that also contain B. Run it long enough to reach a few hundred orders with A in each group, and fix that length before you start rather than stopping when the numbers look good.

Watch two side effects as well. A cross-sell that adds stakes to tent orders but lowers the tent conversion rate, because the cart now feels more expensive, is not a win. And if B is often returned, the gain may disappear once returns are counted; the method for that is in our post on return-adjusted contribution.

A quarterly routine

  1. Run the pair query for the last 12 months and for the last 3 months separately.
  2. Remove pairs from bundles, promotions and your current cross-sell slots, or mark them.
  3. Keep pairs with at least 20 shared orders and lift above 1.5 in both periods.
  4. Sort the survivors into the four boxes above and pick two or three for the cart.
  5. Test each against a holdout and record the attach rate and the conversion rate of the main product.
  6. Replace hand-picked cross-sells that never appear in the list.

Sources