How I uncovered profit leaks, optimized routes, and transformed chaos into clarity using Power BI and strategic analysis
The Challenge: When Success Hides Serious Problems
A growing European logistics company contacted me with a concerning situation. Despite healthy revenue growth, their operations team suspected something was wrong with their financial figures for May and June 2024.
The symptoms:
- Revenue numbers that didn’t match operational performance
- Inconsistent cost reporting across departments
- Route profitability that seemed “off” to experienced managers
- Manual reporting taking days to compile
The real problem: Their data was scattered across multiple systems, making it impossible to see the complete financial picture.
The Investigation: What the Data Revealed
Step 1: Data Archaeology
Before building any dashboards, I needed to understand what we were really working with.
What I found:
- Shipment data spread across 15+ Excel files
- Cost carrier figures with decimal formatting errors creating numbers in the millions
- No unique route identifiers making analysis nearly impossible
- Customer operations spanning multiple countries without proper tracking
The fix: I rebuilt the data foundation using Python for cleaning and Power BI for modeling, creating a star schema that could actually tell the story hidden in their numbers.
Step 2: Building the Financial Picture
Once the data was clean, the real insights started emerging:
Overall Health Check:
- Profit margin: 10.84% (healthier than expected)
- International focus: 84% of revenue from cross-border shipments
- Cost structure: 91% external carriers vs. 9% internal fleet
But then came the surprises…
Step 3: The €131K Discovery
The breakthrough came when I analyzed performance by operational country:
- Germany operations: +€541K profit (stellar performance)
- France operations: -€131K loss (completely hidden before)
This single insight immediately explained why overall margins felt “wrong” despite decent headline numbers.
The Route Analysis: Where Money Disappears
Route #318: The Revenue Champion
- 76 shipments (highest volume)
- 18.6% of total company revenue
- Strong profitability requiring optimization focus
The Problem Routes
Several routes showing consistent losses, with specific sender-receiver combinations bleeding money monthly without anyone realizing.
Why this mattered: Before this analysis, route pricing was based on distance and weight. Now they could see actual profitability per route and adjust accordingly.
Customer Profitability: The Real Eye-Opener
Customer #042: The Profit Engine
- €125K in profits (33% of total company profit)
- Consistent performer across multiple routes
- Clear candidate for relationship deepening
Customer #224: The Hidden Drain
- €70K annual loss (larger than many companies’ entire profit)
- High service requirements without proportional revenue
- Contract restructuring or relationship evaluation needed
The strategic shift: Instead of treating all customers equally, they now had data to focus resources on profit-generating relationships.
Weekly Volatility: The Planning Problem
The month-over-month view looked stable (+8.93% growth from May to June), but weekly analysis revealed dramatic swings:
- Week 21-22: +23% profit spike
- Week 22-23: -20% sudden drop
- Week 23-24: +28% recovery
- Week 24-25: +0.6% plateau
- Week 25-26: +1.4% modest growth
What this meant: Financial planning was nearly impossible without understanding these fluctuation drivers.
The Transformation Results
Immediate Wins:
- €131K loss identified and addressed within 30 days
- Route optimization based on actual profitability data
- Customer contract reviews prioritized by profit impact
- Weekly reporting reduced from 2 days to 30 minutes
Strategic Changes:
- Pricing strategy shifted from cost-plus to value-based
- Resource allocation focused on German market expansion
- Carrier negotiations leveraged with 91% cost visibility
- Decision speed improved from days to hours
Long-term Impact:
With real-time visibility into route and customer profitability, they could make strategic decisions based on data rather than intuition.
The Technical Foundation
For those interested in the methodology:
Data Architecture:
- Star schema with fact table for shipments
- Customer dimension with multi-country operations mapping
- Location dimension using surrogate keys for routes
- Date dimension enabling trend analysis
Key Measures:
- Profit margins by customer, route, and time period
- Cost per shipment benchmarking
- Revenue leakage identification
- Week-over-week and month-over-month trending
Tools Used:
- Python for data cleaning and validation
- Power BI for modeling and visualization
- DAX for advanced calculations
- Automated refresh for real-time insights
Why This Approach Works
This wasn’t just about building dashboards—it was about uncovering the story hidden in their data.
The difference:
- Traditional BI: Shows what happened
- Strategic analysis: Reveals why it happened and what to do next
The process:
- Data detective work: Find what’s really happening
- Strategic modeling: Build architecture for insights
- Business translation: Turn numbers into actions
- Ongoing optimization: Continuous improvement based on results
Your Data Has Stories Too
Every European/UK company I work with has similar hidden insights waiting to be discovered. The question isn’t whether the insights exist—it’s whether you have the right approach to find them.
If your operation team has:
- Multiple Excel files for the same data
- Specific costs profitability questions
- Customer performance mysteries
- Weekly planning difficulties
Then your data probably has similar stories to tell.
Ready to Uncover Your Hidden Insights?
I help European companies transform their data chaos into strategic clarity. This case study represents the typical transformation possible when you combine proper data architecture with business-focused analysis.
