Tools : Looker Studio, Google BigQuery, Google Cloud Function, Google Cloud Scheduler
Tags : Operation Performance
Created : 2023

I developed the Logistics Order Journey Scorecards dashboard to provide a comprehensive view of logistics operations, from order initiation to final delivery. This dashboard tracks key performance indicators (KPIs) such as pickup rates, transit volumes, and delivery success. By leveraging both real-time and historical data, the tool helps teams monitor shipment progress, identify bottlenecks, and resolve issues faster, ultimately improving logistics efficiency.

In our logistics operations, we lacked a clear way to monitor the journey of an order through its lifecycle, such as pickup, in-transit, and delivery stages, because the data didn’t include any explicit indicators for these stages. This gap made it difficult to assess order status in real time, limiting our ability to resolve issues proactively.

To solve this, I designed a custom scorecard system that allows logistics teams to :

• Track orders in real-time  across first mile, middle mile, and last mile stages

• Identify exceptions such as unpicked, canceled, or delayed deliveries

• Analyze trends to support data-driven improvements in operational performance

The primary challenge was deriving actionable insights from the available data, particularly because certain key metrics were not explicitly provided. Here’s how I approached it:

Journey Stage Classification

The absence of direct fields indicating order stages in the raw data posed a significant challenge. 

To solve this, I classified orders using patterns in branch_code and scan_type to determine whether an order was in the first mile, middle mile, or last mile.

Sample logic : 

CASE WHEN SUBSTR(sc.operation_branch_name,1,2) IN ('MH','DC','HQ') THEN 'Medium Mile'

     WHEN SUBSTR(sc.operation_branch_name,1,2) IN ('TH','FB','PD')
AND t1.option_name IN ('Pick up scan','Loading scan','Packing scan','Sending scan')
THEN 'First Mile'

     WHEN ...

     ELSE 'Not Defined'

END AS process_level,

Historical Data Complexity

The order journey data was distributed across multiple scan records, with no flag indicating the current status.

I addressed this by joining scan records with transaction history and using window functions to identify the latest scan per order, allowing reliable classification of current status.

After writing the SQL logic for classification and tracking, I built a Looker Studio dashboard that :

• Displays scorecards for each journey stage (pickup, in transit, delivered)

• Flags exceptions such as delayed pickups or undelivered orders

• Supports drill-downs into order-level details for investigation

This solution enabled the logistics team to:

• Gain Real-Time Visibility across all stages of the order journey

• Handle Exceptions Faster, identifying and responding to delays proactively

• Improve Operational Efficiency by optimizing routes and processes using data-driven insights

• Enhance Customer Experience through more reliable and timely deliveries

You can find the SQL code and technical breakdown in the GitHub Repository.