The Hidden €131K Loss: A European Logistics Company’s Data Analysis Story

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:

1. Data detective work: Find what’s really happening
2. Strategic modeling: Build architecture for insights
3. Business translation: Turn numbers into actions
4. Ongoing optimization: Continuous improvement based on results

How it was built

Cleaning happened before modelling, in Python

The source was 15+ Excel files with carrier costs mangled by decimal formatting — errors large enough to push figures into the millions. Those were corrected and validated in Python before anything reached the model, so the Power BI layer never had to carry defensive logic to compensate for bad inputs.

Surrogate keys because the routes had no identifiers

Routes existed only as sender/receiver combinations with no unique identifier, which makes per-route aggregation impossible. The location dimension introduces surrogate keys, and that is precisely what turns ‘route #318’ into something that can be ranked, compared and costed — the step that later exposed the loss-making routes.

A customer dimension that spans countries

The same customers operated across multiple countries with no consistent tracking, so country-level and customer-level profitability could not both be true at once. Mapping multi-country operations in the customer dimension is what allowed the €131K French loss to be seen separately from the +€541K German result, rather than netting off into a single healthy-looking margin.

Why weekly, not just monthly

Month-over-month showed steady growth of 8.93%. The same data at weekly grain showed a +23% spike followed by a −20% drop. Both are correct; only one of them is useful for planning. The date dimension supports both, and the difference between them was itself a finding.