Embedded Files
A sophisticated Power BI-based Business Intelligence solution designed to enhance financial performance and operational efficiency by analyzing revenue leakage, driver-fleet metrics, and dynamic customer loyalty behavior
The Business Problem & Goal
The Business Problem:
The dynamic ride-hailing platform struggled with fragmented data across sales, fleet vehicles, drivers, and regional territories. This made it difficult to track real-time profitability, isolate significant revenue leakages from uncompleted/cancelled bookings, and monitor driver performance constraints that were hurting the overall trip success rate.
Goal:
To build a unified, full-scale analytical tool that provides a 360-degree view of passenger operations, plugs financial leakages, optimizes fleet utilization, and deploys an automated customer win-back marketing engine through dynamic voucher allocation.
Analytical Process & Data Modeling
Analytical Process & Data Modeling
To achieve precise profitability and operational analysis, a robust data model was engineered by integrating disparate datasets (Bookings, Fleet Vehicles, Drivers, Customers, and Cities) into a unified, high-performance architecture.
🛠️ Data Cleaning & ETL Process (Power Query):
Utilized Power BI’s Power Query engine to perform critical ETL (Extract, Transform, Load) operations, ensuring data integrity across the transaction logs:
+ Multi-Source Integration: Seamlessly merged disconnected tables using unique identifiers (Booking_ID, Customer_ID, Driver_ID) to create a comprehensive relational view of the ride-hailing business.
+ Data Standardization: Performed advanced cleaning to resolve inconsistent timestamp formats, standardize categorical trip statuses (Completed, Cancelled, Incomplete), and handle missing spatial location strings, ensuring 100% accuracy in the final dashboard metrics.
📐 Advanced Computational Logic & Data Modeling:
Built a sophisticated Star Schema within the Power BI Data Model to drive complex business calculations:
+ Relational Architecture: Established "One-to-Many" relationships between dimension tables (Customers, Drivers, Vehicles, Cities) and the Fact table (Booking), enabling seamless cross-filtering and deep-dive situational analysis.
+ Profitability Engineering: Developed advanced DAX calculated measures to track Net Revenue ($145.1K) and isolate Revenue Leakage ($63.27K), providing immediate visibility into realized corporate income versus lost operational capacity.
+ Operational Ratios: Engineered custom logic to calculate the Trip Success Rate (69%) and tracking time-based inefficiencies through Average Turnaround Time (Avg_TAT: 15.01) and vehicle operational cycles (Avg_VTAT: 6.62).
The Interactive Solution (Full Dashboard Demo)
The Interactive Solution (Full Dashboard Demo)
Key Insights & Findings
Key Insights & Findings
💳 Premium Payment Dominance & Cash Flow Analytics:
The Card and Debit Card payment segments serve as the primary financial anchors of the business, collectively contributing over $95.7K of the total realized Net Revenue. This proves that digital payment infrastructure drives the vast majority of consumer spending on the platform, allowing for highly predictable and trackable cash-flow streams.
📈 Fulfillment vs. Revenue Leakage Disparity:
A significant operational disparity was identified through the Revenue Status breakdown. While the platform successfully fulfilled 69% of all trips (generating $145.13K in Net Revenue), it also suffered a massive $63.27K in Revenue Leakage driven strictly by Cancelled and Incomplete bookings. This exposes an explicit gap between gross demand capacity and actual supply fulfillment.
🌙 Operational Demand Outliers (Night Shift Peak):
The temporal booking analysis pinpointed a critical supply-demand spike during Night cycles, which captured the absolute highest volume with 4.24K total bookings. Conversely, the "Afternoon" segment recorded the lowest traction with only 1.65K bookings. This highlights a clear opportunity to optimize dynamic driver surging strategies and driver-partner distribution during nocturnal peak hours.
🚗 Asset Efficiency & Fleet Revenue Leaders:
Strategic asset mapping across vehicle tiers identified the SUV classification as the primary fleet revenue engine, single-handedly driving $39K in revenue and holding the largest market ride share of 18.28%. This proves that premium, larger-capacity vehicle options achieve higher relative spending efficiency compared to baseline models like the Mini ($23K revenue / 11.6% share).
🗺️ Regional Market Hubs & Performance Outliers:
The geographical breakdown across major US operations identifies Los Angeles as the absolute core revenue engine, generating $16,384.1 across 995 bookings. From an efficiency standpoint, Chicago leads the country with a peak 72% Trip Success Rate, while Denver and Seattle emerge as underperforming outliers with the lowest success rates (67%), signaling the need for urgent localized operational interventions
Business Impact & Conclusion
This comprehensive Power BI-based Business Intelligence solution transformed fragmented booking, vehicle fleet, and regional performance data into a high-value strategic asset. By providing granular visibility into operational flows, the leadership team was empowered to pinpoint a critical $63.27K in Revenue Leakage driven by cancellation and incompletion patterns, enabling immediate operational strategies and dynamic surge calibration to protect baseline margins.
Business Impact & Conclusion
This comprehensive Power BI-based Business Intelligence solution transformed fragmented booking, vehicle fleet, and regional performance data into a high-value strategic asset. By providing granular visibility into operational flows, the leadership team was empowered to pinpoint a critical $63.27K in Revenue Leakage driven by cancellation and incompletion patterns, enabling immediate operational strategies and dynamic surge calibration to protect baseline margins.
The absolute clarity provided on Net Revenue ($145.13K) alongside the tracking of high-performance drivers and regional hubs (e.g., Chicago’s market-leading 72% Trip Success Rate and Los Angeles as the premium revenue anchor generating $16.38K) allowed the business to shift its strategy from pure volume-chasing to high-efficiency route and fleet optimization.
Furthermore, by embedding the automated, data-driven customer loyalty engine (Voucher Allocation Status), the business is now strategically positioned to maximize marketing ROI, eliminate promotional budget waste on low-retention accounts, and sustain scalable profitability while ensuring that every vehicle asset and driver-partner path contributes to long-term operational success.
Page updated
Google Sites
Report abuse