How 145,830 rows of point-of-sale receipts turned into a Lift score of 101.89, a menu combo, and a very different picture of when this “café” actually does business.
The Question I Was Actually Trying to Answer
Every point-of-sale system is quietly writing a diary. Every swipe, every printed receipt, every “Bill Number” is a sentence in a much longer story about what people actually want — not what the menu says they should want. Most of the time nobody reads that diary. It just sits in an Excel export, gathering dust between financial-year audits.
That’s exactly the situation I walked into with this project. A café chain handed over one full financial year of raw POS exports — April 2010 through March 2011 — and a fairly open brief: figure out how we make more money from the customers we already have. No customer IDs, no loyalty numbers, no survey data. Just a Bill Number, an Item Description, a timestamp, and a price.
I like problems like this. There’s nowhere to hide behind a fancy model. The entire job is to squeeze every bit of signal out of a plain transaction log, and then translate that signal into something a restaurant manager can act on before their next shift starts. So I split the work into two halves: a Python-driven Exploratory Data Analysis to understand the business rhythm, and a KNIME-driven Market Basket Analysis to understand what people buy together. This post walks through both, in the order I actually did them — mistakes included.
The business problem: a marketing budget going somewhere, but nobody's sure where
Here’s the setup. Austo Motor Company sells three types of vehicles — SUVs, Sedans, and Hatchbacks — and their board had raised a concern that’s painfully common in mid-size companies: we’re spending on marketing, but we’re not confident it’s reaching the right people.
My job, as the analyst brought in, was to dig into customer demographic and financial data and answer a very grounded set of questions:
- Who is actually buying which car type — and does it match who marketing is targeting?
- Are there financial or demographic signals (income, loans, marital status, profession) that predict a purchase?
- Where is the campaign budget likely being wasted on the wrong audience?
Notice what’s not in that list — there’s no ask for a fancy model, no request for a dashboard. Just: understand the customer, and tell us how to spend smarter. That’s a good reminder that most business analytics work starts and ends with clean EDA, not machine learning.
Why I Picked This Problem
I’ll be honest: the first time I opened this dataset, my instinct was to reach for a recommendation engine. Collaborative filtering, a customer-item matrix, maybe something to predict lifetime value. Then I looked for a customer ID column. There wasn’t one.
That absence turned out to be the most useful constraint in the whole project. Without a way to tie transactions to a person over time, there’s no legitimate way to model an individual customer’s journey. But there is a Bill Number, and a Bill Number is something arguably more useful for this specific business question — it’s a basket. One bill, one sitting, one set of decisions made at the same table, in the same 10 minutes, by the same group of people. That’s precisely the unit Market Basket Analysis is built for.
So the constraint didn’t kill the project, it clarified it. Instead of “who is this customer and what will they want next,” the question became “what does this table already have in front of it, and what should we be putting on the table alongside it.” That’s a question a café can act on tomorrow morning, without building a loyalty program first.
Understanding the Business Problem
Translating a modeling exercise into something a restaurant owner cares about is, in my experience, the step most tutorials skip — and it’s the step that actually matters. So before I opened a notebook, I wrote down what a wrong answer would cost.
If I bundle two items that customers were already buying together at full price, I’ve just handed away margin for nothing — the combo would have happened anyway. If I bundle two items that don’t actually belong together, I get a dead promotion, wasted menu real estate, and a kitchen that now has to prep for something nobody orders. Both mistakes are expensive. Both are avoidable if the association rules are trustworthy rather than just statistically present.
That reframing set the bar for the whole analysis: I wasn’t hunting for any pattern, I was hunting for patterns strong enough to survive contact with a real POS terminal and a real cashier script.
Understanding the Dataset
The data came from the café’s internal POS system as a single Excel export — one full financial year, April 2010 to March 2011. Ten columns, no target variable, because this was never a supervised problem. Here’s what I was working with:
| Column | Type | What it's for |
|---|---|---|
|
Date |
DateTime |
Transaction date — weekly & monthly seasonality |
|
Bill Number |
Object |
Unique transaction ID — the “basket” for MBA |
|
Item Desc |
Object |
Menu item sold — the core feature for rule mining |
|
Time |
Object |
Timestamp—source of the Hour feature |
|
Quantity |
||
|
Rate |
||
|
Tax |
||
|
Discount |
||
|
Total |
||
|
Category |
Two columns turned out to matter more than the rest: Item Desc defined what could be associated with what, and Bill Number defined the boundary of a “basket.” Everything downstream depends on getting those two right.
Setting Up the Python Environment
Nothing exotic here — the whole EDA phase ran on Pandas, NumPy, Matplotlib and Seaborn inside Google Colab, with the dataset pulled straight from Drive. I always start a notebook the same way: set the visual theme once, unlock Pandas’ display limits so I’m never squinting at a truncated dataframe, and silence the warning spam before it buries a real one.
# Libraries to help with reading and manipulating data
import numpy as np
import pandas as pd
# Libraries to help with data visualization
import matplotlib.pyplot as plt
import seaborn as sns
sns.set_theme(style='darkgrid')
# Removes the limit for the number of displayed columns
pd.set_option("display.max_columns", None)
# Sets the limit for the number of displayed rows
pd.set_option("display.max_rows", 200)
# to suppress warnings
import warnings
warnings.filterwarnings("ignore")Small habit, worth mentioning: I set display.max_columns to None on every project now. This dataset only has 10 columns so it wasn’t strictly necessary, but on wider POS exports I’ve been burned before by a hidden column silently determining half my summary stats.
Loading and Inspecting the Data
With the environment ready, I mounted Drive and loaded the raw export straight into a DataFrame.
# loading the data from drive
from google.colab import drive
drive.mount('/content/drive/')
data_1 = pd.read_excel('/content/drive/My Drive/Cafe_Data.xlsx')First move on any new dataset, always: head(), shape, info(), describe(). In that order. It’s boring and I do it every single time anyway, because it’s the fastest way to catch a schema surprise before it wastes an afternoon.
data_1.head()Already, two things caught my eye in that first glance. Look at rows 0 and 1: same Bill Number, same timestamp, two different drinks. That’s exactly what a basket looks like — one visit, multiple items — and it’s the reason Bill Number, not Date, is the grouping key for everything that follows. And row 4, at 01:20:18, on the very first day of the dataset, is an early hint that this “café” keeps hours a normal café doesn’t.
data_1.shape
# Output: (145830, 10)
data_1.info()145,830 rows, 10 columns, and — on paper — every column fully populated. info() reported 145,830 non-null entries across the board. I never take that at face value on POS data, and neither should you (more on that in a minute).
Data Quality: Duplicates & Missing Values
info() said nothing was missing. It did not say nothing was wrong. Those are different claims, and POS systems are notorious for conflating them — a network hiccup or a double-tap on the terminal doesn’t produce a null value, it produces a perfectly well-formed duplicate row.
# checking for duplicate values
data_1.duplicated().sum()
# Output: 680
# checking for missing values in the data
data_1.isnull().sum()Zero nulls, confirmed. But 680 rows were exact duplicates — identical date, bill number, item, time, and amount, down to the second. It’s theoretically possible for a terminal to log the same item twice on the same bill at the exact same second, but it’s not plausible at scale. This has the fingerprints of double-scans and sync retries, not genuine repeat orders.
# Remove duplicates
data_cleaned = data_1.drop_duplicates()
data_cleaned.shape
# Output: (145150, 10)I dropped all 680. This matters more here than in a typical EDA project, because Market Basket Analysis literally counts co-occurrences to estimate Support. A phantom duplicate line item doesn’t just skew a chart — it inflates the apparent popularity of specific items and can manufacture association rules that don’t really exist. Cleaning wasn’t optional prep work; it was risk management for the model I hadn’t built yet.
One thing I deliberately didn’t do was strip outliers from Rate, Tax, or Total, even though the boxplots below make them look dramatic. In a café that also serves liquor, bottle service, and premium hookah blends, a ₹14,231 transaction is not a data error — it’s a large party’s bar tab. Removing it would have quietly deleted some of the most profitable behavior in the dataset.
Statistical Summary
data_cleaned.describe().TA few numbers I circled immediately: the median Quantity is 1 — most line items are a single unit of a single product, which is normal for restaurant POS data where a “large order” shows up as multiple line items on one bill, not one line item with a big quantity. The 75th percentile for Discount is exactly ₹0, and the mean is a barely-there ₹0.095. Discounting, whatever the intent behind it, essentially wasn’t happening.
data_cleaned.describe(include='object').TThis single table quietly set the entire shape of the rest of the project. 69,982 unique bills across 145,150 line items works out to roughly 2.07 items per basket on average — baskets here are small. 580 unique items means the menu is enormous and fragmented. And “NIRVANA HOOKAH SINGLE” alone shows up in 8,392 transactions, more than 11% of every unique bill in the year. Small baskets plus a huge, long-tailed menu plus one dominant anchor product — that combination is exactly what makes naive Apriori settings fall apart later in this post.
I also ran a quick pass over the unique values in every categorical column, mostly as a sanity check before trusting Category for anything:
# Counting unique values in each categorical column
for col in data_cleaned.select_dtypes(include='object').columns:
unique_counts = data_cleaned[col].value_counts()
print(f"Unique values in '{col}':\n{unique_counts}\n")Notice anything odd in there? LIQUOR appears twice — once with 6,199 transactions, and once, mysteriously, with exactly 1. That’s almost certainly a stray whitespace character sitting inside an otherwise identical category label, left over from manual POS entry. isnull().sum() would never catch this — the field isn’t missing, it’s just inconsistently typed. It’s a good reminder that “zero missing values” and “clean data” are not the same claim, and it’s the kind of thing worth flagging even when it’s too minor to fix for this particular analysis.
Exploratory Data Analysis: Outliers
With the data cleaned, the EDA phase properly began. First stop: boxplots across every numeric column, to see how much variance I was actually dealing with.
# outlier detection using boxplot
numeric_columns = data_cleaned.select_dtypes(include=np.number).columns.tolist()
plt.figure(figsize=(15, 12))
for i, variable in enumerate(numeric_columns):
plt.subplot(4, 4, i + 1)
plt.boxplot(data_cleaned[variable], whis=1.5)
plt.tight_layout()
plt.title(variable)
plt.show()
Every financial column is dragged by a long tail on the high end, which lines up with a menu that spans everything from a ₹40 masala chai to premium liquor bottle service. As I mentioned above, I chose to keep every one of these — they represent real, valuable transactions the business would very much like more of, not sensor noise to be trimmed away.
Correlation Analysis
Next question: does discounting actually move the needle on revenue? A Seaborn heatmap over the numeric columns answers that faster than any bar chart.
# Plotting heatmap
cols_list = data_cleaned.select_dtypes(include=np.number).columns.tolist()
plt.figure(figsize=(12, 7))
sns.heatmap(
data_cleaned[cols_list].corr(), annot=True, vmin=-1, vmax=1, fmt=".2f", cmap="Spectral"
)
plt.show()
No surprises in the top-left to bottom-right diagonal, but the off-diagonal numbers told a clean story. Tax and Total sit at 0.99 — almost mechanically linked, exactly as you’d expect from a fixed tax formula. Rate and Total at 0.83 confirms unit price is the primary revenue driver, more than quantity (Quantity and Total at just 0.35). And Discount and Total sit at a barely-there 0.05.
That last number is the one I actually flagged for the business. It quantifies what the describe() table already hinted at: discounting isn’t correlated with bigger transactions in this dataset. Either the discount strategy isn’t being used enough to have a measurable effect, or when it is used, it isn’t unlocking incremental spend. Either way, it’s not doing its job as a revenue lever — and that’s a genuinely useful, slightly uncomfortable thing to be able to tell a stakeholder with a straight face and a correlation coefficient behind you.
Temporal Feature Engineering
The raw dataset had a Date and a Time column, but neither one directly answered “when is this business actually busy.” So I engineered two new features: the hour of day, and the day of the week.
# Convert 'Time' to datetime format to extract hour information
data_cleaned["Hour"] = pd.to_datetime(data_cleaned["Time"], format="%H:%M:%S").dt.hour
# Extract day of the week
data_cleaned["Day_of_Week"] = data_cleaned["Date"].dt.day_name()Simple transformation, but it’s the one that turned a flat receipt log into something I could actually aggregate by operational shift. I later added a third derived feature, Month, using the same pattern with dt.month_name(), to check for seasonality across the financial year.
Hourly, Weekly & Monthly Sales Trends
With Hour and Day_of_Week in hand, I aggregated revenue both ways and plotted them side by side.
# Aggregate sales by hour
hourly_sales = data_cleaned.groupby("Hour")["Total"].sum()
# Aggregate sales by day of the week
weekly_sales = data_cleaned.groupby("Day_of_Week")["Total"].sum()
day_order = ["Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"]
fig, ax = plt.subplots(1, 2, figsize=(15, 5))
sns.lineplot(x=hourly_sales.index, y=hourly_sales.values, marker="o", ax=ax[0], color="orange")
ax[0].set_title("Sales Trend by Hour")
sns.barplot(x=weekly_sales.index, y=weekly_sales.values, order=day_order, ax=ax[1], palette=day_palette)
ax[1].set_title("Sales Trend by Day of the Week")
plt.tight_layout()
plt.show()
The hourly chart is where this project stopped feeling like a “café” analysis and started feeling like a late-night lounge analysis. Here’s the raw hourly_sales output, worth reading closely:
Two details in that table that never made it into a chart title, but that I think matter more than the headline: first, hours 7 and 8 don’t appear at all — not low, absent. There isn’t a single transaction logged between roughly 6 AM and 9 AM in an entire financial year. This place doesn’t do breakfast rush. Second, look at hour 0 (midnight): ₹25.8 lakh, the second-highest hour of the day, right behind 23:00’s ₹30.6 lakh. Read on their own, those look like two separate peaks. Read together, they’re the same peak — a single continuous prime-time window that starts around 19:00, keeps climbing straight through midnight, and only really breaks after 1 AM. The “day” in this dataset doesn’t end at the clock’s midnight; the business’s real day does.
The weekly chart confirmed the other half of the operational picture: Saturday is the single highest-grossing day, with Sunday close behind, and Monday through Wednesday forming a consistent mid-week trough.
# Extract month and aggregate sales
data_cleaned["Month"] = data_cleaned["Date"].dt.month_name()
monthly_sales = data_cleaned.groupby("Month")["Total"].sum()
month_order = ["January","February","March","April","May","June",
"July","August","September","October","November","December"]
plt.figure(figsize=(12, 5))
sns.barplot(x=monthly_sales.index, y=monthly_sales.values, order=month_order, palette=month_palette)
plt.title("Sales Trend by Month")
plt.xticks(rotation=45)
plt.show()
December is the standout month, comfortably ahead of the rest — holiday-season footfall, most likely. There’s a visible dip through April to June before the business builds back up toward the winter peak. January through March, closing out the financial year, also hold up strongly.
Item & Category Performance
Timing tells you when to staff up. It doesn’t tell you what to stock. For that, I grouped revenue by item and by category.
# Aggregate total sales by item description
item_sales = data_cleaned.groupby("Item Desc")["Total"].sum().sort_values(ascending=False)
top_items = item_sales.head(10)
low_selling_items = item_sales.tail(10)
plt.figure(figsize=(12, 5))
sns.barplot(x=top_items.values, y=top_items.index, palette="viridis")
plt.title("Top 10 Best-Selling Items")
plt.show()
plt.figure(figsize=(12, 5))
sns.barplot(x=low_selling_items.values, y=low_selling_items.index, palette="coolwarm")
plt.title("Bottom 10 Lowest-Selling Items")
plt.show()
Nirvana Hookah Single tops the list by revenue as well as frequency, trailed by Sambuca, Mint Flavour Single, and Calcutta Mint. Four of the top five entries are hookah- or tobacco-adjacent. Whatever this venue’s brand identity is on paper, its revenue identity is a hookah lounge that happens to also serve food and coffee.
The long tail on the other end — items like Mothers Day Spl, Add Buttered Toast as a standalone line, and Cutting Glass — barely register. Across 580 unique items, a meaningful chunk of the menu is doing almost no commercial work while still consuming kitchen prep time, supplier relationships, and POS screen space.
# Group by Category and sum Total Sales
category_sales = data_cleaned.groupby("Category")["Total"].sum().reset_index()
category_sales = category_sales.sort_values(by="Total", ascending=False)
plt.figure(figsize=(12, 6))
plt.bar(category_sales["Category"], category_sales["Total"], color='royalblue')
plt.title("Total Sales by Category")
plt.xticks(rotation=45, ha="right")
plt.show()
Category-level revenue confirms the item-level story: Tobacco leads at roughly ₹14.5M, ahead of Food at roughly ₹10M and Beverage at roughly ₹5.7M, with Liquor, Wines, Misc, and Merchandise trailing well behind. For a business that self-identifies as a “café,” having its top category be tobacco by a wide margin is the single most important strategic fact this EDA surfaced — and it’s the fact that shapes every recommendation later in this post.
Key EDA Discoveries
✅ What the exploration actually proved
- This isn’t a daytime café, it’s a night operation. Zero transactions between roughly 6–9 AM, a steady ramp from 10 AM, and a single continuous peak running from 19:00 straight through past midnight into the 00:00 hour. Staffing, prep, and inventory restocking need to be built around a 7 PM–1 AM core, with Saturday and Sunday as the anchor days.
- Tobacco, not food or coffee, is the financial engine. “Nirvana Hookah Single” alone appears in over 8,300 of 69,982 baskets, and Tobacco leads every other category in total revenue. Any cross-selling strategy that ignores the hookah is ignoring the business’s actual center of gravity.
- The menu has real bloat. Across 580 unique items, a long tail contributes almost nothing to annual revenue while still adding operational overhead. That’s a menu-rationalization conversation waiting to happen, independent of anything the market basket model would later find.
- Discounting isn’t earning its keep. A 0.05 correlation with Total, and a 75th-percentile discount value of ₹0, both point the same direction: whatever the discount strategy is meant to achieve, the data doesn’t show it working.
Preparing the Data for Market Basket Analysis
EDA answered “when” and “what sells.” Now I needed “what sells together,” and that meant leaving the flat transaction table behind for a transactional format — one row per basket, containing the full set of items in it. Apriori-style algorithms don’t work on row-per-line-item data; they need row-per-basket data.
# Saving the cleaned dataset for Market Basket Analysis in KNIME
import os
os.makedirs('/content/drive/MyDrive/Datasets', exist_ok=True)
data_cleaned.to_excel('/content/drive/MyDrive/Datasets/Cleaned_Cafe_Data.xlsx', index=False)From there, the reshaping happened visually, inside KNIME, using a short chain of nodes:
| Category | Columns |
|---|---|
|
Demographics |
Age, Gender, Marital Status, Education, Profession |
|
Household |
No. of Dependents, Partner Working |
|
Finances |
Salary, Partner Salary, Total Salary, Personal Loan, House Loan |
|
Purchase |
Make (car type), Price |
The GroupBy step is the one that’s easy to get subtly wrong — it needs to concatenate item descriptions into a list per bill, not count them. Get that setting wrong and every basket collapses into a number instead of a set of items, and the whole pipeline downstream produces nothing.
Support, Confidence & Lift, Explained
Market Basket Analysis doesn’t have a validation accuracy or an ROC curve to lean on — it’s unsupervised, and every rule it produces looks like {A} → {B} (“if a customer buys A, they also buy B”). Trusting a rule means understanding three numbers.
Lift is the one that actually answers the business question, because it normalizes Confidence against how popular B would be anyway, with no relationship to A at all:
Given what a false positive costs — a wasted combo, a misplaced upsell script — I chose Lift as the deciding metric for this project, using Support only to filter out statistical noise and Confidence only as a secondary sanity check.
Building the First Model (and Watching It Fail)
I built the pipeline in KNIME using the Association Rule Learner node — KNIME’s implementation of the Apriori algorithm — and ran it first with the kind of default settings you’d use on a standard grocery basket dataset: a minimum Support of 5% (0.05). My honest expectation was that I’d get back something boring and obviously correct, like coffee-plus-pastry, as a sanity check before tightening things up.
The result was, essentially, an empty ruleset. Nothing.
It took me a minute to actually believe the output, because a 5% threshold isn’t an aggressive setting in most retail contexts. But do the arithmetic on this dataset: 5% support over 69,982 baskets means an itemset has to appear in roughly 3,500 baskets to survive. Spread that requirement across 580 unique items, and almost nothing clears the bar — the Apriori algorithm was pruning the entire search space before Confidence or Lift ever got calculated on anything. The default wasn’t wrong in general. It was wrong for a menu this fragmented.
Python vs. KNIME: Choosing My Weapon
A dead ruleset is a good moment to question the tool, not just the parameters. I seriously considered scrapping the KNIME workflow and moving the whole Market Basket Analysis into Python, using mlxtend‘s TransactionEncoder, apriori, and association_rules functions instead.
The empty ruleset wasn’t a KNIME problem. It was a parameter problem sitting on top of a genuinely sparse dataset. Switching tools would have cost me memory efficiency and stakeholder-readability for no real gain, so I stayed in KNIME and turned my attention to the thresholds instead.
Tuning the Model
Grocery-store Market Basket Analysis gets away with high Support thresholds because the product catalog is consolidated — “milk,” “bread,” “eggs.” This café’s catalog is the opposite: dozens of niche hookah flavors, granular coffee customizations, and a sprawling food menu, all splitting the basket volume across 580 distinct labels. A high Support threshold doesn’t filter noise here — it erases the entire signal, including the strongest, narrowest associations in the dataset.
So I rebuilt the Association Rule Learner’s configuration from the ground up:
Dropping Support to 0.1% means an itemset only needs to appear in roughly 145 of the 69,982 baskets to be considered — rare enough that a niche pairing can survive, common enough that it isn’t pure coincidence. Confidence at 40% then does the real filtering work, and the Lift > 1 cutoff is the final noise gate before I even look at a rule.
The Final Pipeline
The finished KNIME workflow, end to end:
The last node, Column Renamer, is a small thing that mattered for a non-technical audience: KNIME’s default output labels rules by internal field names, and I renamed them to plain-English “Antecedent,” “Consequent,” “Confidence,” “Lift” columns before exporting a single results table. When you’re about to hand a spreadsheet to a restaurant manager, that’s not a cosmetic step.
Final Results
With tuned thresholds in place, the Association Rule Learner returned a manageable, high-quality set of rules — ranked by Lift, exactly as I’d planned. Here are the six that mattered most:
The Buttered Toasts → Kheema Ghotala rule is the one I kept staring at. A Lift of 101.89 means that pairing shows up almost 102 times more often than pure chance would predict — for context, most “strong” retail association rules I’ve seen elsewhere top out in the single or low double digits. This wasn’t a coincidence the default 5% threshold could ever have found; it was hiding specifically in the long tail my baseline model pruned away.
What the Rules Actually Reveal
Three distinct behavioral patterns fell out of this ruleset, and each one tells a different story about the customer sitting at the table.
1. Latte customers are premium-syrup customers
Vanilla, Caramel, and Hazelnut flavour add-ons all showed strong pulls toward Caffe Latte, with Confidence up to 80% and Lift ranging from 18 to nearly 27. Anyone ordering a latte here is already primed to customize it — that’s a near-automatic upsell sitting in plain sight.
2. Regional food pairings are mathematically real, not anecdotal
Buttered Toasts and Kheema Ghotala isn’t a rule Apriori invented out of nowhere — it’s a classic regional breakfast pairing that the algorithm rediscovered purely from 145,150 anonymous transaction lines, with zero culinary knowledge fed into the model. That’s the kind of result that builds trust with a stakeholder who’s naturally skeptical of “the algorithm.”
3. The hookah anchors a hydration ecosystem
Nirvana Hookah Single pulling Mineral Water and Moroccan Mint Tea (Lift 4.68, more modest than the food rules but still meaningfully above 1) reflects a real physical need — sessions at a hookah table run long, and throats get dry. The beverage side of this business is, in part, functionally downstream of the tobacco side.
Business Recommendations
Challenges Encountered
The sparsity of a 580-item menu spread across a large, mostly-small-basket transaction log was the central technical hurdle of this project, and I don’t think that’s an overstatement — my first fully-configured model came back empty, which is about as unambiguous a failure signal as data science gives you. Getting Support tuned to a level that kept rare, high-value pairings alive without flooding the output with noise took real iteration, not a single lucky guess.
The Python-to-KNIME handoff was the second friction point. It’s easy to assume a GroupBy node “just works,” but the difference between aggregating item descriptions into a concatenated list versus aggregating them into a count is the difference between a working pipeline and a silently broken one. I lost time here before double-checking the node configuration against the raw output.
The third challenge was more of a mindset correction than a technical one: my early read of the results over-trusted Confidence, because a couple of high-Confidence rules looked impressive at first glance — until I noticed they were being inflated by items that were simply popular on their own, independent of any real association. Re-anchoring the entire evaluation around Lift is what actually separated genuine synergy from statistical mirage.
Lessons Learned
- Match the tool to the phase, not the project. Pandas and Seaborn were unbeatable for cleaning, feature engineering, and visualization. KNIME’s visual node graph was the better fit for the sparse-matrix Apriori step and for handing off a self-documenting pipeline to non-technical stakeholders. I don’t think either tool “wins” this project on its own.
- Metric choice is a business decision, not a technical footnote. Confidence and Lift answer different questions, and picking the wrong one to optimize against is the difference between a profitable combo and a wasted promotion.
- A number is not a recommendation. A Lift of 101.89 is a nice sentence to say out loud, but it didn’t generate a rupee of revenue until it became a named “Power Breakfast” combo a cashier could actually offer.
Alternative Approaches
A fully Python-native pipeline using mlxtend‘s TransactionEncoder, apriori, and association_rules functions remains a completely valid path, and it’s the one I’d reach for if this needed to run unattended inside a cloud scheduler rather than as a stakeholder-facing report — it also opens the door to secondary metrics like Leverage and Conviction natively.
A category-level Market Basket Analysis is another angle worth exploring: instead of mining rules across all 580 granular item labels, grouping baskets by the 9-value Category column first would surface broader, more strategic questions — does Liquor genuinely lift Food purchases as a category, independent of which specific dish or bottle is involved?
Future Improvements
- Customer-level loyalty data. Capturing a real Customer ID at the terminal would unlock RFM (Recency, Frequency, Monetary) segmentation on top of the existing basket-level analysis, opening the door to genuinely personalized promotions instead of blanket combos.
- Time-series forecasting. The Hour, Day_of_Week, and Month features engineered here are already shaped for ARIMA or Prophet-style forecasting of daily and weekly revenue, moving staffing from reactive to predictive.
- A live BI dashboard. Wiring Tableau or Power BI directly into the POS database would let management track hourly revenue velocity and combo-deal conversion in real time, instead of waiting for the next static report.
- A scheduled monthly MBA refresh. Re-running this exact KNIME pipeline every month on fresh exports would keep the association rules current as the menu and customer preferences evolve.
Complete Project Workflow
Project Files
Conclusion
This project turned a year of anonymous café receipts into a strategic playbook, and it did it without ever needing a customer ID. Cleaning and exploring the data first debunked an assumption — that discounting was helping — and replaced it with a much sharper picture of the business: a night-and-weekend operation whose real financial engine is tobacco, not coffee.
The Market Basket Analysis then took that picture one level deeper, moving past “what sells” into “what sells together,” and it only got there after a failed baseline forced a real rethink of the Support threshold. The payoff was a Lift of 101.89 on a pairing the café’s own menu hadn’t formally acknowledged yet. If there’s one thing I’d want a reader to take from this case study, it’s that the mathematics was never the finish line — the finish line was a named combo meal a cashier can say out loud and a customer will actually buy.
FAQs ~
What is Market Basket Analysis in retail data science?
Market Basket Analysis is an unsupervised technique that finds items customers tend to purchase together within the same transaction, or “basket.” Instead of predicting a single target variable, it produces if-then association rules (for example, “if a customer buys A, they’re also likely to buy B”) that retailers use to design combo deals, cross-sell prompts, and store or menu layouts.
How does the Apriori algorithm generate association rules?
Apriori starts by finding individual items frequent enough to clear a minimum Support threshold, then progressively combines them into larger itemsets, testing each candidate against the same threshold. Its core efficiency trick, the “Apriori principle,” is that if an itemset is infrequent, every larger itemset built on top of it must also be infrequent, so the algorithm can safely prune huge portions of the search space early.
What is the mathematical difference between Confidence and Lift?
Confidence is a conditional probability: of all the baskets containing item A, what percentage also contain item B. It can be misleadingly high if B is simply popular on its own. Lift corrects for that by dividing Confidence by the baseline Support of B, so a Lift of 1 means no real relationship, and a Lift meaningfully above 1 means A and B genuinely co-occur more than chance alone would predict.
Why is KNIME an effective tool for Market Basket Analysis?
KNIME’s Association Rule Learner node consumes list-based baskets directly, without requiring the large one-hot-encoded matrix that a code-based approach like mlxtend needs, which is a real memory advantage on a wide product catalog. Its visual, node-based pipeline is also easier for non-technical stakeholders to audit than an equivalent block of Python code.
How do data scientists handle sparse data in large retail menus?
The main lever is the minimum Support threshold. A high threshold (5% or more) works fine for a consolidated catalog like a grocery store, but on a fragmented menu with hundreds of niche items, it prunes away rare-but-genuine associations before they’re ever evaluated. Lowering Support dramatically (in this project, down to 0.1%) lets those niche itemsets survive the initial filter, while a stricter Confidence threshold and a Lift-above-1 cutoff downstream keep the final ruleset free of noise.
Add comment
You must be logged in to post a comment.