Enhancing Operational Efficiency using Data Analytics Solution in a last-mile delivery company

Case Study | Data Analytics | Logistics

Problem Statement

A last-mile delivery company was facing operational inefficiencies, with delivery delays averaging 20% and fuel costs rising by 15% annually. The operations team relied on basic metrics like delivery volume and route distance but overlooked critical data related to driver behavior, traffic patterns, and customer feedback, missing opportunities to optimize routes and improve service quality.

Solution

The company implemented a data analytics solution with a hero dashboard that integrated previously unconsidered data sources, enabling better decision-making and operational optimization.

Technical Approach

• Data Collection and ETL Process
  • Extracted data from previously siloed sources, including:
    • Telematics data (e.g., speed, idle time, harsh braking) from GPS devices installed in delivery vehicles.
    • Real-time traffic data from Google Maps API.
    • Customer feedback and delivery success rates from the company’s CRM.
    • Weather data from a public API.
    • Delivery schedules and route plans from the company’s ERP system.
  • Built an ETL pipeline to ingest, clean, and transform data, including handling JSON data from APIs and normalizing telematics data into a consistent format.
  • Addressed missing data (e.g., GPS signal loss) using interpolation techniques and deduplicated overlapping traffic data using timestamp-based filtering.
• Data Integration
  • Stored processed data in a data warehouse, optimized for analytical queries and geospatial analysis.
  • Created a unified schema to join telematics, traffic, weather, and customer data, enabling cross-functional analysis.
• Uncovering Hidden Data
  • Identified previously unconsidered data points, such as:
    • Driver Behavior Impact: Drivers with high idle times (>10 minutes per trip) were 35% more likely to cause delays and consumed 20% more fuel.
    • Traffic and Weather Correlation: Routes with high traffic congestion during rainy days had a 50% higher delay rate, indicating a need for dynamic rerouting.
    • Customer Feedback Patterns: Negative feedback (e.g., “late delivery”) correlated with specific drivers and routes, highlighting training and optimization needs.
  • Used statistical analysis to validate the significance of these new data points and segment routes by risk level.
• Dashboard Development
  • Built an interactive dashboard using PowerBI, to visualize insights and enable decision-making. Key features included:
    • Route Optimization Map: A geospatial visualization (using PowerBI’s mapping capabilities) showing real-time traffic, weather, and delivery delays, overlaid with recommended route adjustments.
    • Driver Performance Scorecard: A heatmap showing driver metrics (e.g., idle time, fuel efficiency, delivery success rate), with color-coded alerts for underperforming drivers.
    • Customer Impact Analysis: A stacked bar chart showing delay rates and customer feedback scores by route, driver, and time of day, highlighting high-risk areas.
    • Actionable Recommendations: Embedded rules to recommend actions, such as rerouting deliveries in high-traffic areas or scheduling driver training for those with high idle times.
  • Enabled real-time updates, live data connection with a latency of <1 minute.
• Deployment and Automation
  • Deployed the dashboard as a web application, integrated with SSO for secure access.
  • Automated data refreshes to ensure real-time insights, with a latency of <1 minute.
  • Integrated the dashboard with the ERP system using REST APIs to push route recommendations directly to delivery planning tools.

Tech Stack

1. Data Storage: Google BigQuery
2. Data Processing: ETL
3. Programming: Python
4. Visualization: PowerBI (dashboard)
5. Deployment: PowerBI Server, Google Cloud Scheduler
6. Integration: Google Maps API, OpenWeatherMap API, CRM API, ERP API

Implementation

• Conducted discovery session to design and build the dashboard.
• Conducted analysis sessions for operations, fleet management, and customer service teams on interpreting dashboard insights and acting on recommendations.
• Rolled out the solution across five major delivery hubs over 6 months, starting with a pilot in the highest-delay hub.

Impact

1. Efficiency: Reduced time spent on manual route planning by 65%, as the dashboard provided real-time recommendations based on traffic, weather, and driver data.
2. Productivity: Delivery operations productivity increased by 30%, as drivers completed 20% more deliveries per day due to optimized routes and reduced idle times.
3. Decision-Making:
   • Uncovered Insights: The dashboard revealed that driver behavior (e.g., idle time) was a major driver of fuel costs, prompting targeted training programs that reduced fuel consumption by 15%.
   • Data-Driven Actions: The operations team used traffic and weather data to implement dynamic rerouting, reducing delays by 40%.
   • Proactive Service Improvement: Customer feedback analysis enabled proactive outreach to dissatisfied customers, improving satisfaction scores by 25%.
4. Overall Growth: Operational costs decreased by 12% due to fuel savings and efficiency gains, while on-time delivery rates improved to 95%, contributing to a 15% increase in customer retention and an 8% revenue growth within a year.