ds@mcpanalytics.aiEmail your data and your question and get an answer about your own data, with the method shown.Email your data and question. Get an answer.

Shopify Order Export CSV: Bundle Affinity Analysis

Updated October 2026

Category: Shopify Analytics | Reading Time: 12 minutes

Introduction to Product Bundle Affinity Analysis

Product bundle affinity analysis, also called market basket analysis, counts which products appear together in the same orders. The result is a short list of product pairs that are bought together more often than their individual popularity would predict. Those pairs are candidates for bundles, "frequently bought together" blocks and cross-sell emails.

This tutorial defines the three measures involved (support, confidence and lift), computes them with pandas from a Shopify order export, and shows how to check how much a lift value can move from one sample of orders to another. A pair with a high lift is a lead to test, not proof that a bundle will raise revenue.

Every output block below comes from a synthetic data set of 2,000 paid orders generated for this tutorial, with five product pairs planted on purpose. It is not a real store, so your numbers will differ. Because it is synthetic, we know which pairs are real and can see whether the method finds them.

Prerequisites and Data Requirements

What You'll Need Before Starting

Understanding Your Data Structure

Shopify's order export has one row per line item. The columns used here:

Check the header row of your own file against these names before running the code.

Step 1: Export Your Shopify Order Data

In your Shopify admin, open the Orders page and use its Export option to export orders as CSV for the period you want to analyze. Menu labels and export options change between admin versions, so follow Shopify's current help page for exporting orders. Export all orders, not only the paid ones: the code filters by status itself.

Expected Output

The file has one row per line item. A two-item order looks like this (illustrative rows):

Name,Created at,Financial Status,Lineitem quantity,Lineitem name,Lineitem sku
#1001,2025-01-15 10:02:11 +0000,paid,1,Wireless Mouse,MOUSE-001
#1001,,,1,USB Keyboard,KEYB-002

Multi-product orders appear on several rows with the same order name. That repeated name is what groups products into a basket.

Step 2: Prepare Your Data File

Data Cleaning Checklist

1. Keep Paid Orders Only

Pending orders may never complete, and refunded or cancelled ones did not end in a sale. The code keeps orders whose Financial Status is "paid". If you want to treat partially refunded orders as sales, widen the filter and say so when you report results.

2. Choose One Product Key

3. Decide How to Treat Variants

4. Remove Test Orders

Drop test orders and internal purchases, which would create pairs no customer chose.

Step 3: Build One Set of Products per Order

This code loads the file, keeps paid orders, and builds the set of distinct products in each order. Using a set means two units of the same product count once, so a pair means two different products in one order.

import numpy as np
import pandas as pd

df = pd.read_csv("orders_export.csv")
# Order-level fields sit on the first row of each order; groupby().first() skips the blanks
status = df.groupby("Name")["Financial Status"].first()
paid = status[status == "paid"].index
lines = df[df["Name"].isin(paid)]

label = lines.groupby("Lineitem sku")["Lineitem name"].first()    # SKU is the key, name is for display
baskets = lines.groupby("Name")["Lineitem sku"].apply(frozenset)  # one set of products per order

N = len(baskets)
print(f"Orders in export: {df['Name'].nunique():,}   paid: {N:,}")
print(f"Orders with 2+ different products: {(baskets.map(len) >= 2).sum():,} ({(baskets.map(len) >= 2).mean() * 100:.1f}%)")
print(f"Products: {lines['Lineitem sku'].nunique()}")

Expected Output

Orders in export: 2,080   paid: 2,000
Orders with 2+ different products: 955 (47.8%)
Products: 14

Step 4: Count Pairs and Set Thresholds

Definitions

Let N be the number of orders, n(A) the number of orders containing product A, and n(A, B) the number containing both.

Worked by hand from the output below: Coffee Maker appears in 180 of 2,000 orders, Coffee Filters in 337, and both in 115. Support is 115 / 2,000 = 5.8%. Confidence is 115 / 180 = 63.9%. Filters are in 337 / 2,000 = 16.9% of all orders, so lift is 63.9% / 16.9% = 3.79.

Thresholds

These three cutoffs are working choices to cut a long list down, not established standards. Change them and see what survives.

from itertools import combinations
from collections import Counter

MIN_SUPPORT, MIN_CONFIDENCE, MIN_LIFT = 0.015, 0.25, 1.2   # 1.5% of 2,000 orders = 30 orders

n_item = Counter(s for b in baskets for s in b)                          # orders containing each product
n_pair = Counter(p for b in baskets for p in combinations(sorted(b), 2))  # orders containing both products

rules = []
for (x, y), n_xy in n_pair.items():
    if n_xy / N < MIN_SUPPORT:
        continue
    for a, b in ((x, y), (y, x)):                    # a rule runs in both directions
        confidence = n_xy / n_item[a]                # share of orders with a that also have b
        lift = confidence / (n_item[b] / N)          # compared with how common b is overall
        if confidence >= MIN_CONFIDENCE and lift >= MIN_LIFT:
            rules.append((label[a], label[b], n_xy, n_item[a], n_item[b], n_xy / N, confidence, lift))

table = pd.DataFrame(rules, columns=["A", "B", "both", "n_A", "n_B", "support", "confidence", "lift"])
table = table.sort_values("lift", ascending=False).reset_index(drop=True)
show = table.assign(support=(table["support"] * 100).round(1), confidence=(table["confidence"] * 100).round(1), lift=table["lift"].round(2))
print(f"{len(table)} rules pass the three thresholds (N = {N:,} orders)")
print(show.to_string())

Expected Output

10 rules pass the three thresholds (N = 2,000 orders)
                A               B  both  n_A  n_B  support  confidence  lift
0     Laptop Case          Laptop    77  199  146      3.8        38.7  5.30
1          Laptop     Laptop Case    77  146  199      3.8        52.7  5.30
2      Phone Case           Phone    94  275  176      4.7        34.2  3.88
3           Phone      Phone Case    94  176  275      4.7        53.4  3.88
4  Coffee Filters    Coffee Maker   115  337  180      5.8        34.1  3.79
5    Coffee Maker  Coffee Filters   115  180  337      5.8        63.9  3.79
6     Yoga Blocks        Yoga Mat   122  240  286      6.1        50.8  3.55
7        Yoga Mat     Yoga Blocks   122  286  240      6.1        42.7  3.55
8  Athletic Socks   Running Shoes    82  326  211      4.1        25.2  2.38
9   Running Shoes  Athletic Socks    82  211  326      4.1        38.9  2.38

This version looks at pairs only (one product implies one product). Rules with two or more products on the left, such as {Phone, Case} to Screen Protector, need a larger search and many more orders per combination; they are not covered here.

Step 5: Interpret the Rules and Their Uncertainty

Reading the Table

Every column can be recomputed from the counts: support = both / N, confidence = both / n_A, lift = confidence / (n_B / N). Each pair appears twice, once per direction. Lift is the same in both directions, but confidence is not: 63.9% of orders with a Coffee Maker also have Filters, while only 34.1% of orders with Filters also have a Coffee Maker. Which direction matters depends on where you would show the suggestion.

How Much Can Lift Move?

This code resamples the orders with replacement 500 times and recomputes lift each time. The middle 95% of those values is a bootstrap interval: a rough range for lift if the orders had come out differently. Pairs with fewer co-occurrences give much wider intervals.

X = pd.DataFrame({s: baskets.map(lambda b: s in b) for s in label.index}).to_numpy()   # orders x products
col = {s: i for i, s in enumerate(label.index)}
inv = {v: k for k, v in label.items()}
rng = np.random.default_rng(11)

def lift_ab(M, a, b):
    both = (M[:, a] & M[:, b]).sum()
    return (both / M[:, a].sum()) / (M[:, b].sum() / len(M))

print("A -> B | lift | 95% bootstrap interval for lift")
for _, r in table[table["A"] < table["B"]].iterrows():   # lift is the same in both directions, so one row per pair
    a, b = col[inv[r["A"]]], col[inv[r["B"]]]
    boots = [lift_ab(X[rng.integers(0, N, N)], a, b) for _ in range(500)]
    lo, hi = np.percentile(boots, [2.5, 97.5])
    print(f"{r['A']} -> {r['B']} | {r['lift']:.2f} | {lo:.2f} to {hi:.2f}")

Expected Output

A -> B | lift | 95% bootstrap interval for lift
Laptop -> Laptop Case | 5.30 | 4.60 to 6.14
Phone -> Phone Case | 3.88 | 3.41 to 4.46
Coffee Filters -> Coffee Maker | 3.79 | 3.40 to 4.27
Yoga Blocks -> Yoga Mat | 3.55 | 3.17 to 4.00
Athletic Socks -> Running Shoes | 2.38 | 2.02 to 2.79

With 77 to 122 orders behind each pair, the intervals are narrow enough that every one of these pairs sits well above lift 1. A pair seen in 30 orders would have a much wider interval, so look at the count column before trusting a high lift. These are the pairs we planted, so in this synthetic set the method finds what is there. On real data, a high lift tells you the pair co-occurs, not why.

Step 6: Test Bundle Ideas Before Rolling Them Out

A rule with high lift and decent confidence is a candidate. Turning it into a bundle or a recommendation is a business decision with costs the order data does not show.

Before You Build

Ways to Use a Pair

Measuring the Effect

Do not assume an improvement. Compare average order value and the attach rate (the share of A orders that also contain B, which is the rule's confidence) before and after the change, or better, show the change to a random half of visitors and compare the halves. Seasonal swings can move both numbers on their own, so a before-and-after comparison alone can mislead.

Re-run the analysis on a fresh export when your catalog or customers change.

Advanced Interpretation Techniques

Rules With More Than Two Products

A rule such as {Laptop, Mouse} to Laptop Bag describes orders that contain all three products. These rules rest on far fewer orders than pairs, so their counts are small and their estimates unstable. Look at the count before reading anything into the confidence.

Negative Associations

A lift below 1 means two products appear together less often than their popularity predicts. That can point to substitutes, where a customer picks one or the other, or to products that are sold to different audiences. It is a prompt to look, not a diagnosis.

Seasonal and Temporal Patterns

To see whether pairs change over the year, run the same code on a date slice, for example the holiday months against the rest of the year, by filtering on the Created at column before building the baskets. Each slice has fewer orders, so check the counts and widen the bootstrap interval check before comparing slices. A pair that looks stronger in one slice but rests on few orders may be noise.

Run It on Your Own Shopify Export

Export your orders from Shopify as a CSV and upload it at MCP Analytics. The analysis runs association-rule mining (support, confidence and lift for product pairs) on your file and shows the result with the method used. You choose the columns; nothing connects to your Shopify account.

Try it on your data

Next Steps and Further Learning

Common Issues and Solutions

Issue 1: No Rules Pass the Thresholds

Issue 2: Too Many Rules

Issue 3: Obvious Pairs

A phone case and a phone, a coffee maker and its filters: obvious pairs show the method works. Look further down the list, or reverse the direction (Phone to Phone Case) to decide where to show the suggestion.

Issue 4: The File Will Not Load or Gives Wrong Counts

Issue 5: Different Results on Different Exports

Rules close to a threshold flip in and out when the order set changes slightly. Focus on rules that appear in every export, and use the bootstrap interval to see which are close calls.

Conclusion

Bundle affinity analysis turns an order export into a ranked list of product pairs with their counts. Support tells you how common a pair is, confidence tells you how often the second product appears alongside the first in an order, and lift tells you whether that is more than the second product's popularity alone would give. The bootstrap interval says how far to trust a value.

Treat the list as candidates. Check margin and stock, test a change against a comparison group, and re-run the analysis when your catalog or customers change.

Explore more: Shopify Analytics: all tools, tutorials, and guides →

Your turn

Bring your own data and the question you actually need answered.

MCP AnalyticsData science by email Send us your data and your question, and we will reply. ds@mcpanalytics.ai