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.
| Measure | Formula | Question it answers |
|---|---|---|
| Support | Orders containing A and B, divided by all orders | How common is this pair at all? |
| Confidence (A to B) | Orders with A and B, divided by orders with A | Of the people who buy A, how many also buy B? |
| Lift | Confidence (A to B) divided by the share of orders containing B | Does buying A make B more likely than usual? |
| Leverage | Support of the pair minus support of A times support of B | How 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.
| Measure | Calculation | Result |
|---|---|---|
| Support | 107 / 10,000 | 1.07% |
| Confidence, tent to stakes | 107 / 400 | 26.8% |
| Confidence, stakes to tent | 107 / 860 | 12.4% |
| Lift | 26.8% / 8.6% | 3.1 |
| Leverage | 1.07% minus (4.0% x 8.6%) | 0.73 points |
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
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.
| Trap | What you see | What to do |
|---|---|---|
| Bestsellers in every rule | High confidence for “anything leads to your top product” | Sort by lift, not confidence; a bestseller has high confidence with everything |
| Tiny counts | Lift of 15 built on 3 orders | Set a minimum pair count (20 is a reasonable start) and treat lift on small counts as noise |
| Your own cross-sells | The pairs you already promote rank highest | Flag pairs that were linked in the store during the period; their lift is partly your doing |
| Bundles and promotions | Two products with lift of 40 | Exclude 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 mix | Rules that only hold in one quarter | Compare 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.
| Use | WooCommerce | Magento / Adobe Commerce |
|---|---|---|
| Cart suggestion | Cross-sells field in the product’s Linked Products tab; the cart must include a cross-sells block to show them | Cross-sells per product; in Adobe Commerce, related product rules can apply them by condition |
| Product page suggestion | Upsells field, or a block that reads your own rule list | Related products; rule based in Adobe Commerce |
| Bundle | A bundle or grouped product with its own price | Bundle product type |
| Email follow-up | Export the rule list to your email platform and trigger on purchase of A | Same |
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
- Run the pair query for the last 12 months and for the last 3 months separately.
- Remove pairs from bundles, promotions and your current cross-sell slots, or mark them.
- Keep pairs with at least 20 shared orders and lift above 1.5 in both periods.
- Sort the survivors into the four boxes above and pick two or three for the cart.
- Test each against a holdout and record the attach rate and the conversion rate of the main product.
- Replace hand-picked cross-sells that never appear in the list.
Sources
- Agrawal, Imielinski and Swami, Mining Association Rules between Sets of Items in Large Databases, ACM SIGMOD 1993
- mlxtend, association_rules user guide
- WooCommerce, Set up Related Products, Up-Sells and Cross-Sells
- WooCommerce, Installed database tables
- Adobe Experience League, Related product rules
- ThoughtSpot, How to use market basket analysis to support retail operations
- Alteryx Community, Market Basket Analysis 101: An Introduction


