Turning Transaction Data Into Smarter Cross-Sell Tests

At a glance

Client

Independent food-and-beverage operator (anonymous)

Engagement

Sales, product-performance, and basket-affinity analysis

Data reviewed

Two months of anonymized point-of-sale order-item data; more than 1,000 completed orders

My role

Independently scoped the analysis, cleaned and validated the data, built the association logic, and designed the dashboard

The client had useful purchase data in its point-of-sale system, but no reliable way to turn it into merchandising decisions. It was easy to see which products sold often. It was much harder to answer the questions that could help the business act:

• Which products do customers genuinely choose together?
• Which combinations look frequent only because each product is popular on its own?
• Where could a prompt or placement increase attachment rate?
• When would a discount simply give away margin on a purchase customers already make?

The aim was to turn raw transaction data into small, measurable commercial tests while protecting customer and business confidentiality.

The objective

Build a practical way to assess bundles and cross-sells using customer behaviour and the numbers behind each option.

Success did not mean finding the most common pair. It meant identifying opportunities that were large enough to matter, showed evidence of genuine affinity, and could be tested without compromising margin.

My approach

1. Start with evidence, not assumptions

Before looking at product relationships, I checked the raw exports for consistency, missing values, order statuses, returns, discounts, zero-value items, duplicates, and product-group coverage.

This mattered because bad basket data can quickly lead to misleading results. A returned item or an invalid order ID can make two products look related when they are not.

# Fail early when an export does not match the expected analytical schema.
missing_columns = set(COLUMN_MAP) - set(raw.columns)
if missing_columns:
    raise ValueError(f"Export is missing: {sorted(missing_columns)}")
 
profile = {
    "duplicate_rows": int(raw.duplicated().sum()),
    "missing_order_numbers": int(raw["order no"].isna().sum()),
    "blank_order_statuses": int(raw["order status"].isna().sum()),
}

2. Create a privacy-safe, reproducible dataset

The cleaned dataset kept only the fields needed for the analysis: order ID, timing, product and product group, quantity, sales value, cost, profit, and order status. I removed personal, staff, payment, address, and note fields.

Cleaning decisions were documented rather than applied silently. For example:

• Return or reversal rows without a valid sale status were excluded from sales and basket calculations.
• Clean discounted and zero-value product lines were retained so that real customer behaviour was not removed.
• Non-product and operational line items were kept out of product-association calculations.
• Each product was counted once per order for basket analysis, while quantity remained available for unit-sales analysis.

The result was a dataset that can be refreshed when new months of data are available.

Representative anonymized source-data extract

The dashboard uses a fully synthetic, schema-faithful sample to show the shape of the original export. Order IDs, dates, product labels, and financial values below are illustrative; no client transaction data is published.

# Apply documented rules, then validate the resulting analytical dataset.
is_blank_status = combined["order_status"].isna() | combined["order_status"].eq("")
is_out_of_scope_group = combined["item_group"].str.casefold().isin({"add on", "custom"})
is_operational_item = combined["item_name"].isin(OPERATIONAL_ITEM_NAMES)
 
cleaned = combined.loc[
    ~(is_blank_status | is_out_of_scope_group | is_operational_item)
].copy()
 
assert cleaned["order_status"].notna().all()
assert cleaned["order_no"].notna().all()
assert cleaned.columns.is_unique

3. Establish the baseline before looking for pairs

I first looked at overall sales and basket behaviour: order volume, units sold, average order value, product mix, and the number of different products in each order.

Roughly two-thirds of completed orders contained more than one product. Customers were already buying more than one thing at a time, which made cross-selling a realistic opportunity to test.

def baseline_metrics(order_data: pd.DataFrame) -> pd.Series:
    return pd.Series({
        "total_orders": len(order_data),
        "total_units_sold": order_data["units"].sum(),
        "average_order_value": order_data["sales"].mean(),
        "average_distinct_products": order_data["distinct_products"].mean(),
        "single_product_order_pct": order_data["distinct_products"].eq(1).mean() * 100,
        "multi_product_order_pct": order_data["distinct_products"].gt(1).mean() * 100,
    })

4. Put product popularity in context

I ranked products and product groups by orders containing the item, units, sales, and profit before drawing conclusions. This avoided a common mistake: assuming a frequent pair is meaningful when it may simply contain two popular products.

def performance_metrics(data: pd.DataFrame, dimension: str) -> pd.DataFrame:
    total_orders = data["order_no"].nunique()
    metrics = (
        data.groupby(dimension)
        .agg(
            orders_containing_product=("order_no", "nunique"),
            units_sold=("qty", "sum"),
            sales=("amount", "sum"),
            profit=("profit", "sum"),
        )
        .reset_index()
    )
    metrics["pct_of_orders"] = metrics["orders_containing_product"].div(total_orders) * 100
    return metrics

5. Measure affinity at the right level

I examined three views of the basket:

1. Product × product: for specific menu-pair tests.
2. Product group × product group: for menu structure and broader merchandising choices.
3. Product × product group: for flexible prompts, such as suggesting a suitable add-on group after a chosen anchor item.

Every relationship was evaluated using more than frequency:

from collections import Counter
from itertools import combinations
 
def directed_associations(pair_counts, entity_counts, total_baskets):
    records = []
    for (a, b), pair_orders in pair_counts.items():
        for antecedent, consequent in ((a, b), (b, a)):
            confidence = pair_orders / entity_counts[antecedent]
            records.append({
                "antecedent": antecedent,
                "consequent": consequent,
                "pair_orders": pair_orders,
                "support_pct": pair_orders / total_baskets * 100,
                "attachment_rate_pct": confidence * 100,
                "lift": confidence / (entity_counts[consequent] / total_baskets),
            })
    return pd.DataFrame(records)
 
# Count each item only once per order before generating product pairs.
item_baskets = order_items.groupby("basket_id")["item_name"].agg(lambda x: sorted(set(x)))
pair_counts = Counter(pair for basket in item_baskets for pair in combinations(basket, 2))

The key insight

The work separated two different opportunities that are often treated as the same thing:

Several combinations still showed positive affinity after adjusting for popularity. One was a high-volume dessert-style item and a complementary baked-good group. In the anonymized data, roughly three in ten orders with that item also included a baked good. That was enough to justify testing a menu placement or checkout prompt.

Just as importantly, I identified combinations that appeared frequent but did not show positive affinity once product popularity was considered. I did not prioritize those for discount-led bundles.

Example: turning a pattern into a decision

To protect confidentiality, the following is a representative, rounded example rather than a client-specific menu recommendation.

This is the decision logic I wanted the dashboard to show. A recommendation is not just a high-ranking result. It is an idea backed by evidence, commercial context, and a plan to measure it.

Techniques used

• Exploratory data analysis: profiled schema consistency, distributions, missing values, duplicate rows, order statuses, returns, discounts, and product coverage before transformation.
• Data cleaning and validation: created explicit, reproducible rules; preserved valid discounts and zero-value items; excluded reversal records and non-product lines; and validated row and basket counts after cleaning.
• Privacy-aware data design: removed personally identifiable and sensitive operational fields before creating the analysis dataset and outputs.
• Market-basket analysis: built distinct-product baskets by order and calculated pair frequency, support, confidence/attachment rate, and lift across product, product-group, and product-to-group levels.
• Commercial prioritization: looked at sales and profit alongside customer behaviour instead of automatically choosing the strongest statistical relationship.
• Interactive data communication: built a Streamlit dashboard with drill-down views, filters for minimum volume and lift, and plain-language metric definitions for non-technical users.
• Repeatable workflow: kept exploration, cleaning, baseline analysis, product performance, and basket analysis in separate Python notebooks, with exported tables feeding the dashboard.

Tools: Python, pandas, Jupyter notebooks, matplotlib, Streamlit, and CSV-based analytical outputs.

The dashboard recording below shows how decision-makers can filter potential cross-sell opportunities by volume and lift, then inspect the supporting commercial context.

Play recording

From analysis to action

I did not recommend an immediate price promotion. Instead, I proposed a staged test plan:

1. Start with non-discounted prompts. Surface a relevant complementary product at checkout, in staff scripts, or beside the anchor item on the menu.
2. Test one change at a time. Compare a clear test period or location against a comparable baseline.
3. Measure attachment and profit, not just sales. Track the share of anchor-item orders that add the complement, the resulting basket value, and gross-profit change.
4. Only then test bundle pricing. If a pairing needs an incentive, quantify the discount, margin retained, and incremental attachment required to break even.

This approach keeps the upside of cross-selling without discounting purchases customers may have made anyway.

Deliverables

• A documented data-quality review and cleaning logic
• A non-PII, analysis-ready order-item dataset
• Sales and basket-size baseline tables
• Product and product-group performance views
• Product, group, and product-to-group association tables
• An interactive dashboard that lets decision-makers filter opportunities by volume and lift, then inspect the business interpretation behind each recommendation
• A practical metric guide explaining support, attachment rate, and lift in plain language

Why this matters

The project turned a transaction export into a repeatable way to make decisions. Instead of asking, “What sells together?”, the client can now ask:

“Which next action is most likely to increase the size and profitability of a customer’s basket, and how will I know if it worked?”

Measurement plan for future tests

The analysis provided evidence-backed hypotheses, not a claim that an intervention had already created incremental revenue. Future tests should be judged against a pre-test baseline using:

• attachment rate of the recommended complement,
• average basket value,
• gross profit per order,
• uptake of the proposed prompt or bundle, and
• whether the result holds across comparable trading periods.

Methodology note

Association results show what customers bought together. They do not prove that one item caused another purchase. That is why recommendations start with controlled, low-risk tests and measure the impact before a wider rollout.