How to track On-Time Delivery using SQL
How to implement on time delivery metrics in your business, and why they are key to customer satisfaction.
Why are On-Time Delivery metrics so important?
When customers' orders are not delivered on time, they may miss their delivery, or it may arrive too late to be useful.
Ensuring a high on-time delivery rate protects customer satisfaction, so it's important to measure if customers are receiving their items on time.
 |
|---|
| A flawless handover |
Definition
On-Time Delivery (OTD), sometimes called On-Time Performance, or On-Time Percentage (OTP) measures the percentage of orders that are delivered on time:
Calculating On-Time Delivery
In order to calculate the OTD %, we need to know when the deliveries were meant to be delivered, and when they were actually delivered.
Assuming we are starting with the below data from 100,000 deliveries, which is stored in a database. For each order delivery_slot_start and delivery_slot_end mark the time period when the delivery was expected, and delivery_time is when the delivery was actually made.
| Order ID | Delivery Slot Start | Delivery Slot End | Delivery Time |
|---|---|---|---|
| 1 | 2021-08-10 | 2021-08-10 | 2021-08-10 |
| 2 | 2020-04-04 | 2020-04-04 | 2020-04-04 |
| 3 | 2021-12-17 | 2021-12-17 | 2021-12-17 |
| 4 | 2021-11-03 | 2021-11-03 | 2021-11-03 |
| 5 | 2021-01-23 | 2021-01-23 | 2021-01-23 |
| 6 | 2021-07-01 | 2021-07-01 | 2021-07-01 |
| 7 | 2019-01-08 | 2019-01-08 | 2019-01-08 |
| 8 | 2020-06-06 | 2020-06-06 | 2020-06-06 |
| 9 | 2020-12-15 | 2020-12-15 | 2020-12-15 |
| 10 | 2019-11-03 | 2019-11-03 | 2019-11-03 |
We first compare the delivery time to the start and end of the delivery slot to determine if the order was delivered on time, early, or late.
| Order ID | Delivery Slot Start | Delivery Slot End | Delivery Time | Delivery Status |
|---|---|---|---|---|
| 1 | 2021-08-10 | 2021-08-10 | 2021-08-10 | On-Time |
| 2 | 2020-04-04 | 2020-04-04 | 2020-04-04 | Early |
| 3 | 2021-12-17 | 2021-12-17 | 2021-12-17 | On-Time |
| 4 | 2021-11-03 | 2021-11-03 | 2021-11-03 | On-Time |
| 5 | 2021-01-23 | 2021-01-23 | 2021-01-23 | On-Time |
| 6 | 2021-07-01 | 2021-07-01 | 2021-07-01 | On-Time |
| 7 | 2019-01-08 | 2019-01-08 | 2019-01-08 | On-Time |
| 8 | 2020-06-06 | 2020-06-06 | 2020-06-06 | Late |
| 9 | 2020-12-15 | 2020-12-15 | 2020-12-15 | On-Time |
| 10 | 2019-11-03 | 2019-11-03 | 2019-11-03 | On-Time |
From here, we can calculate the OTD % for each month in the dataset.
| Delivery Mon | Total Deliveries | on Time | Early | Late |
|---|---|---|---|---|
| 2019-01-01 | 2,235 | 62% | 32% | 7% |
| 2019-02-01 | 1,761 | 64% | 29% | 7% |
| 2019-03-01 | 2,275 | 64% | 30% | 6% |
| 2019-04-01 | 1,838 | 62% | 31% | 7% |
| 2019-05-01 | 2,398 | 61% | 32% | 7% |
| 2019-06-01 | 2,204 | 63% | 30% | 7% |
| 2019-07-01 | 2,576 | 61% | 32% | 7% |
| 2019-08-01 | 1,971 | 62% | 31% | 6% |
| 2019-09-01 | 2,044 | 64% | 30% | 6% |
| 2019-10-01 | 2,665 | 61% | 32% | 7% |
Presenting On-Time Delivery Metrics
On-time delivery metrics are typically presented as a time series, allowing the viewer to see the OTD % for each month.
We can use Evidence to plot a line chart to see how the OTD % changes over time:
We can also use an area chart to help show the proportion of deliveries that were late or early: