Case study · Retail
Every store and category, on one page by 9am
Retailers make daily decisions about stock, pricing and promotions, often from reports that arrive a week late. This is how we built Power BI analytics that combine till, online and ERP data into yesterday's sales, margin and stock position every morning.
Project overview
Retail analytics on Power BI.
- Industry
- Retail
- Client
- Confidential
- Solution
- Sales and margin analytics
- Platform
- Power BI + Fabric
- Timeline
- 8–12 weeks
- Team
- 3 people
- Sources
- POS, eCommerce, ERP
- Refresh
- Daily, intraday for sales
- Users
- Buyers, store managers, board
Expected business outcomes
What the client set out to gain
Speed of insight is the main change; better decisions follow.
-
Daily
Sales and margin visibility
Yesterday's figures by store, category and channel every morning.
Refresh schedule set per source
-
60–80%
Less time building reports
Weekly trading packs refresh themselves.
Target range; depends on how many reports move to the model
-
Earlier
Stock and promotion decisions
Low cover and underperforming promotions flagged while they can still be acted on.
Where buyers use the dashboards in trading meetings
Business challenges
How retail reporting worked before
Every channel reports differently.
Stores
Till data exported nightly into spreadsheets.
Online
eCommerce reports from the shop platform's own dashboard.
Margin
Cost prices in the ERP, so margin is calculated separately.
Promotions
Promotion results analysed weeks after they end.
Trading meeting
Monday's meeting starts with an hour of reconciling numbers.
Proposed solution
What the Power BI build covers
A retail data model that makes stores and online comparable.
-
Data pipelines
Till, online and ERP data loaded daily into one model.
-
Retail data model
One definition of sales, margin, returns and stock cover across channels.
-
Trading dashboard
Sales and margin versus plan and last year by store, category and channel.
-
Stock cover
Weeks of cover and low-stock alerts by store and product.
-
Promotion analysis
Uplift and margin impact of each promotion.
-
Store manager view
Each store's figures on phones for store managers.
Project requirements
What the client asked us for
The request was one trading dashboard. The work is making online and store sales comparable, with returns and discounts treated the same way.
We build for the Monday trading meeting first, because that is where decisions are made.
What the project had to deliver
- 01One version of sales and margin
- 02Stores and online side by side
- 03Stock cover alerts
- 04Promotion results while they run
Key challenges & considerations
What made it harder, and what we did
-
Returns recorded differently by channel
What we did
One definition of a return was agreed with finance and applied to every channel.
-
Cost prices that change often
What we did
Cost prices were held with effective dates, so margin used the cost that applied on the day of sale.
-
Large transaction volumes
What we did
Transactions were aggregated in the data model, and the reports were tested at full volume.
-
Store hierarchies that change
What we did
Store hierarchies were kept with history, so past periods still reported correctly.
What we watched for
- Agree how returns are counted
- Reconcile to finance before launch
- Build for the trading meeting
- Keep store views simple
Development process
Trading meeting first
The first release answers the questions asked every Monday.
-
Measures
Sales, margin, returns and cover defined with buyers and finance.
-
Pipelines
Till, online and ERP data loaded and reconciled.
-
Trading dashboard
Built and used in real trading meetings.
-
Stock and promotions
Cover and promotion views added.
-
Store rollout
Store manager views and training.
Technology stack
Power BI on Microsoft Fabric
Microsoft's data platform, sized to the retailer's data volumes.
Reporting
- Power BI
- Power BI mobile
- Paginated reports
Data platform
- Microsoft Fabric
- Azure SQL
- Dataflows
Governance
- Row-level security
- Deployment pipelines
- Measure documentation
Delivery
- Git integration
- Automated refresh monitoring
Quality assurance & deployment
Every figure reconciled before anyone relied on it
A dashboard is only useful if its numbers are believed. Each measure was reconciled to its source, and to finance where money was involved, before the report was released.
- Each measure reconciled to its source system
- Financial figures signed off by finance
- Data quality checks on every refresh: completeness and freshness
- Row-level security tested with real user accounts
- Report performance tested at full data volume
Deployment
- 01Stock and promotionsCover and promotion views added.
- 02Store rolloutStore manager views and training.
- 03Deployment pipelinesReports moved from development to test to production through controlled pipelines.
- 04Adoption and warrantyTraining for report users and builders, usage reviewed, and six months of free bug fixing.
Final deliverables
Trading decisions from yesterday's numbers
Buyers and store managers start the day with yesterday's sales, margin and stock by store and channel, and trading meetings focus on actions.
Promotion results are visible while promotions are still running.
- Daily trading dashboard
- Comparable channels
- Stock cover alerts
- Live promotion analysis
Handed over to the client
- 01Data model and pipelinesDocumented and scheduled
- 02Reports and dashboardsPublished with security roles
- 03Measure definitionsEvery figure documented
- 04TrainingFor report users and report builders
- 05Six-month warrantyFree bug fixing after delivery
Success metrics
How success is measured
| Measure | Outcome | How it is measured |
|---|---|---|
| Sales and margin visibility | Daily | Refresh schedule set per source |
| Less time building reports | 60–80% | Target range; depends on how many reports move to the model |
| Stock and promotion decisions | Earlier | Where buyers use the dashboards in trading meetings |
Verified reviews
What clients say about our custom software work
Verified reviews from our clients on the same kind of work, published on Clutch.
-
Custom Software Development
Web-Based Coupon App Development
The work produced by the team was of exceptional quality.
Jon Thies
CTO, Model Rocket Feb 2022 – Mar 2023
-
Custom Software Development
Mobile & Web Platform Development & Design
Silver Scintilla was responsive, respectful, and enjoyable to work with, quickly addressing any questions or requests.
DeShawn Brown
CEO & Founder, Lithios Jun 2021 – Nov 2022
-
Custom Software Development
Web-Based HRM System Development
Silver Scintilla provided the best solution with scalable functionality, personalized features, and robust security.
Suheb Khan
Director, Collaborative Insight Technologies Jul 2020 – Jun 2022
Free discovery call
Have an idea? Let's turn it into AI-powered software.
Book a free discovery call with our experts. Share your idea and we will help you shape the scope, timeline and budget, under NDA.
- Free consultation
- NDA before we talk
- Transparent estimate