About Client
A leading restaurant group operating 150+ locations across 12+ regional markets in the quick-service
and casual dining segments. Annual systemwide revenue exceeds $500M, with 4,000+ staff across
front-of-house, back-of-house, and corporate operations.
What They Do
- Operate 150+ quick-service and casual dining locations
- Run 18+ marketing campaigns per year across the full network
- Process 20,000+ transactions daily across all locations
- Manage 7,000+ menu items and 15,000+ guest interactions per campaign cycle
Their Scale Includes
190M+ rows of historical data, 10+ data sources, 4,000+ employees, and millions of guests served
annually through dine-in, delivery, and corporate catering.
Problem Statement
The restaurant group was running its business on broken data. Campaign reports contradicted sales
numbers, workforce metrics told a different story depending on which table you pulled from, and reports
took so long to load that managers stopped using them. Leadership had dashboards — they just
couldn’t trust any of them.
The Challenge
Two critical parts of the business — marketing campaigns and workforce operations — each had their
own analytics setup, and both were failing.
On the campaign side, 18+ promotions ran simultaneously across 150+ restaurants, but 15% of the
data linking campaigns to sales was simply wrong. On the workforce side, KPIs were pulled from 10+
disconnected sources totalling 190M+ rows — causing reports to take over 5 minutes to load, when
they loaded at all.
Managers needed fast, accurate answers. Instead, they got conflicting numbers and spinning loading
screens. Exillar was brought in to tear down both systems and rebuild them as one unified analytics
platform.
What Was Going Wrong
1. Campaign Data Didn't Match Sales Data
The marketing team reported one set of numbers. Finance pulled another. A 15% error rate in campaign-to-transaction data meant every campaign report was suspect. With 18+ campaigns per year across 150+ locations, those misallocated dollars compounded with every cycle — and decisions about what to repeat, what to kill, and where to shift budget were all built on flawed data.
2. Overlapping Campaigns Made Comparison Impossible
With 25+ campaigns running concurrently across different locations and time periods, there was no clean way to isolate what drove results. No reliable pre-campaign baseline. No automated year-over-year comparison. Every analysis was a manual exercise with a disclaimer attached.
3. Workforce Metrics Were Inconsistent Across Teams
The same KPI returned three different answers depending on which source table HR, operations, or finance queried. With 10+ disconnected data sources, there was no single version of the truth — and no one trusted the numbers enough to act on them.
4. Reports Were Too Slow to Be Useful
190M+ rows were processed on the fly every time someone opened a report. Five-minute load times in a fast-moving restaurant operation is the same as no data at all. Managers stopped checking dashboards and went back to gut instinct.
Solution Provided
Exillar rebuilt the restaurant group’s entire analytics infrastructure from the ground up — collapsing two
broken systems into one unified platform.
We designed and deployed a semantic model built on star-schema architecture, powered by Databricks
and Azure Data Lake using the Medallion (Bronze/Silver/Gold) pattern. Raw data from 10+ sources
flows into Bronze, gets cleaned and standardised in Silver, and surfaces as business-ready metrics in
Gold. On top of this, we built two purpose-built Power BI dashboards — one for campaign analytics with
dynamic period-over-period comparison, and one for turnover analytics with 50+ redesigned KPIs. One
platform. Same numbers. Every team.
What Is a Semantic Model in Simple Terms?
Think of it as a translation layer between raw data and the questions your team actually asks. Without
it, every analyst manually hunts for the right tables, hopes the joins are correct, and presents numbers
with a disclaimer. A semantic model pre-defines the relationships between tables, standardises how
every metric is calculated, and ensures “turnover rate” means the exact same thing whether HR pulls it
or operations does.
How the System Works:
01
Ingest: Collect Everything, Break Nothing
- Raw data from 10+ sources flows into the Bronze layer of Azure Data Lake untouched
- Captures 20,000+ daily transactions and 190M+ historical rows in original format
- Complete audit trail preserved — nothing filtered, nothing altered at source
02
Clean: Standardise and Reconcile
- Silver layer deduplicates, unifies naming conventions, and resolves the 15% attribution error
- Campaign overlap fixed with composite key joins, daily grain normalisation, and priority attribution rules
- Workforce data from 10+ sources merged into consistent, query-ready tables
03
Model: Build the Intelligence Layer
- Gold layer houses the semantic model with all 50+ KPI definitions and business logic
- Dynamic period-over-period comparisons built in — pre-campaign, month-over-month, year-over-year
- 30+ operational KPIs redesigned from scratch for actual restaurant decision-making
04
Deliver: Dashboards That Get Used
- Campaign Analytics dashboard — compare any campaign vs. any baseline across all 150+ locations
- Turnover Analytics dashboard — real-time workforce metrics loading in under 30 seconds
- Both dashboards pull from the same semantic model — same numbers, every team, every time
Results
System Performance
| Metric | Value |
|---|---|
| Data Accuracy | >98% (up from ~85%) |
| Report Load Time | Under 30 seconds (down from 5+ minutes) |
| Rows Processed | 190M+ |
| Campaigns Tracked Simultaneously | 25+ overlapping campaigns |
| KPIs Tracked Daily | 50+ |
| Operational KPIs Redesigned | 30+ |
| Locations Covered | 150+ |
| Data Sources Unified | 10+ |
Operational Impact
- Campaign attribution errors dropped from 15% to under 2% — marketing and finance finally report the same numbers
- Workforce metrics load in under 30 seconds — HR and ops work from one consistent dataset
- Month-over-month and year-over-year comparisons run automatically — no more rebuilding analysis every cycle
- Dashboard adoption went from near-zero back to daily use across all operations teams
Before vs After
| Area | Before | After |
|---|---|---|
| Data Accuracy | ~85% — 15% campaign attribution errors | >98% across all campaign & workforce data |
| Report Load Time | 5+ minutes | Under 30 seconds |
| Campaign Comparison | Manual, unreliable, no standard baseline | Automated: pre-campaign, MoM, YoY |
| Overlapping Campaigns | Impossible to isolate impact | 25+ concurrent campaigns tracked separately |
| Workforce Data | 10+ disconnected systems, conflicting values | Unified semantic model, single source of truth |
| KPI Quality | Generic metrics, limited operational value | 50+ KPIs tracked daily, 30+ redesigned |
| Dashboard Adoption | Low — managers stopped using them | High — fast, accurate, used daily |
| Cross-Team Alignment | Marketing, finance, HR, ops all had different numbers | All teams pull from same model, same definitions |
Tech Stack
| Layer | Technology | Role |
|---|---|---|
| Data Platform | Databricks | Data processing, transformation & pipeline orchestration |
| Storage | Azure Data Lake | Scalable cloud storage using Medallion architecture |
| Data Architecture | Medallion (Bronze/Silver/Gold) | Three-tier pattern — raw ingestion, cleaning, business-ready metrics |
| Data Modelling | Semantic Model (Star Schema) | Centralised business logic defining all KPIs & table relationships |
| Visualisation | Power BI | Interactive dashboards for campaign and turnover analytics |
| Calculation Engine | DAX | Measure definitions, dynamic comparisons & KPI calculations |