of decisions in Spanish small businesses are made without looking at a single piece of data
Casa Origen
This is one of them — until it wasn’t.
scroll ↓
Background and rationale
Madrid, Spain’s coffee capital.
The problem
of Spanish small businesses use BI
of decisions are made without data
THE OPPORTUNITY · SPECIALITY COFFEE SHOP
registered coffee shops
speciality coffee shops
sector revenue 2026
speciality vs traditional coffee
The case
Casa Origen
Speciality coffee shop in Madrid · Lavapiés + Malasaña · 3 years in business · runs on a traditional data model
receipts
Cumulative revenue
average ticket
gross margin
Project definition
The goal
To give management actionable visibility so they can answer, with data, five questions that are currently decided on gut feeling.
Hypotheses to test
D1 Executive overview· D2 Product & Menu Intelligence · D3 Operations & Time Intelligence · D4 Customer & Channel Analytics
Methodology · 5 steps
Synthetic data generated in Python (fixed seed).
Layered Medallion ETL (Bronze → Silver → Gold) in SQL Server.
Dimensional star model + DAX measures in Power BI.
2 machine learning modules: market basket and RFM.
4 decision-focused dashboards in Power BI.
Tools
Tech stack
Data architecture
A layered warehouse: Medallion.
Bronze
RAW INGESTION
3 simulated years · 7 CSVs
Deliberate errors: nulls, duplicates, odd dates and broken formats.
Silver
DATA CLEANING AND BUSINESS RULES
Removes duplicates and empty records, checks dates and data consistency. Enriched with Madrid public holidays.
Gold
STAR MODEL READY FOR POWER BI AND MACHINE LEARNING MODELS
1 fact table + 6 dimensions.
Data model
Star schema.
1 fact table (sales) + 6 dimensions
DATE · PRODUCT · SHOP · EMPLOYEE · CHANNEL · CUSTOMER
+ 30 DAX measures · disconnected tables for ML
Visualisation in Power BI
Four dashboards.
Executive Overview
Growth of +31.6% vs last year, in revenue, receipts and units sold.
Seasonality: December €64.8K vs August €39.3K. Lever: redesign the summer offer.
Flat average ticket (€8.69, −0.14%). Growth comes from volume, not price → premium menu and combos.
Finding H3. Midday is the profitable slot (€10.67 average ticket), 35% above opening (€7.10).
Product & Menu Intelligence
Finding H1. A clear Pareto. 13 products (A) = 70% of sales · 14 products (C) = 11% → simplify the menu.
Avocado toast = anchor product. €173K revenue. It carries midday and appears in 60% of the ML combos.
Finding H2. Main combo: Kombucha + Avocado toast (lift 3.5×) → "Casa Origen Brunch" menu.
Operations & Time Analytics
Overall RevPASH €5.26/seat-hour, but Opening (€2.28) and Afternoon (€2.01) don’t reach the €2.50 threshold.
Finding H4 — Opening and Afternoon earn half the threshold → close at 15:30 at weekends.
Weekends: Saturday and Sunday morning at €7.76 / €8.28 (2× the Mon–Fri average) → more staff.
Finding H3 — The ticket depends on the time slot (€7.10 → €10.67), not the day. Move afternoon spending to midday.
Toasts and brunch (savoury) sell, the most expensive items on the menu.
Customer & Channel Analytics
Finding H5. Champions + Loyal (52.7%) generate 70.7% of revenue.
Delivery brings in 20% of revenue, but a 30% commission leaves net margin at 43% vs 73% in-store.
Typical Casa Origen customer
15% of customers = 30.8% of revenue.
“Before this project, the coffee shop didn’t know who that 15% were. Now it does.”
Machine learning layer
RFM + K-Means = 5 segments · 5 campaigns
1,200 loyal customers · 3 years · 179,112 receipts
Champions
What they’re like
They come every 2 days · 307 receipts/year · spend €2,693. They’re the engine of the business.
Actionable decision
VIP programme (free coffee + early access to new items). Retention +5pp → + €24K
Loyal
What they’re like
They come every 3 days · 158 receipts/year · spend €1,391. The backbone of the business.
Actionable decision
Soft loyalty (5th drink free) and cross-recommendations. Raise average spend +8% → + €50K
At Risk
Top priorityWhat they’re like
History identical to Loyal (F=149, M=€1,313). They haven’t been back for 60–90 days.
Actionable decision
Urgent email/SMS reactivation. Win back 20% → + €70K
Potential
What they’re like
New or infrequent customers. Goal: move 30% up to Loyal within 6 months.
Actionable decision
Welcome pack, visit incentives and a loyalty app → + €40K in 6 months
Lost
What they’re like
303 days of silence on average.
Actionable decision
Stop communications after 180 days without a visit → saves €600/year
€1.11M concentrated in 632 customers · H5 confirmed
Market Basket + FP-Growth = 5 combos · 5 campaigns
H3Five combos proposed for the menu
Mid-Morning
Kombucha + Avocado toast
€11.70
+8% average ticket at midday
At the Bar
Espresso + Butter toast
€6.80
+5% attach rate on Espresso
No Rush
Filter Coffee + Banana Bread
€5.20
Suggested at the till at opening
Afternoon Treat
Cookie + Latte
€5.10
+6% average ticket in the afternoon
The House One
Butter croissant + Flat White
€5.40
+18% average ticket · highest volume
Campaigns built from the market basket
The Short Menu
ML finding
Top 5 combos by lift
Action
5 fixed combos on the menu at a better price.
The Bar Script
ML finding
Rules with confidence ≥ 25%
Action
A laminated cheat sheet for staff: "if they order a Latte, offer a Croissant".
The Pruning
ML finding
Products missing from the rules + low volume
Action
Take 2–3 products off the menu each quarter.
The Graft
ML finding
Anchor a new item to a compatible best-seller by time slot
Action
A seasonal new item always sells alongside the top product of its time slot.
Half Past Ten
ML finding
Rules with high lift in an unusual time slot
Action
Bring a midday product forward to the morning (Avocado toast at 10:30).
Casa Origen doesn’t improvise any more.
This is what it looks like when you decide with data.
Interactive dashboard published in the Power BI Service
Python notebooks: RFM segmentation and market basket (FP-Growth)