Skip to article frontmatterSkip to article content
Site not loading correctly?

This may be due to an incorrect BASE_URL configuration. See the MyST Documentation for reference.

Order to Delivery (OTD) Time Forecasting

Order to Delivery (OTD) Time Forecasting

  • Ease of e-commerce over the last decade, along with the recent COVID-19 pandemic, has seen a marked shift in consumer behavior and expectations when it comes to purchase - and more importantly, the delivery of goods. This has caused a paradigm shift in functioning of the supply chain. Along with delivery speed, consumers feel that the transparency around delivery time and shipment status are an equally important aspect of the fulfilment process. This has direct implications in customer churn/retention. More than half of consumers are less likely to shop with a retailer if the item is not delivered within 2 days of date promised. (source)

Order to Delivery Time Forecasting as a Supply Chain Optimization Usecase:

OTD forecasting is a key aspect of supply chain optimization as it helps to ensure that customers receive their products in a timely manner, it enables companies to make informed decisions about inventory, logistics and production which in turn helps to improve the overall efficiency of the supply chain.

AI Project Cycle AI_Project_Cycle.png

Context: Understanding the Problem Statement --------Problem Scoping (AI Project Cycle - Step 1)

An ML based predictive solution for providing delivery time forecasting

  • An ML based predictive solution for providing delivery time forecasting can provide great insights to e-commerce platforms. From a customer-facing point of view these insights would help e-commerce platforms to give more accurate delivery forecasts to customers and decrease customer churn. In addition, the ML based predictive solution will also allow e-commerce platforms to take pre-emptive actions to mitigate delays, costs, and loss of revenue.

  • For the individual components of the ensemble model, we will use XGB, RF and SVM. These components will then be fed to a Voting Model, which is an ensemble model that combines the individual predictions to provide a final, consensus prediction. The final consensus prediction can be (1) a prediction for a wait time for a package and (2) if a delay will occur.

e2e-flow_stock

Import the useful Packages & Libraries

Requirement already satisfied: ipython-autotime in c:\Program Files\Python314\Lib\site-packages (0.3.2)
Requirement already satisfied: ipython in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from ipython-autotime) (9.17.1)
Requirement already satisfied: colorama>=0.4.4 in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from ipython->ipython-autotime) (0.4.6)
Requirement already satisfied: ipython-pygments-lexers>=1.0.0 in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from ipython->ipython-autotime) (1.1.1)
Requirement already satisfied: jedi>=0.18.2 in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from ipython->ipython-autotime) (0.20.0)
Requirement already satisfied: matplotlib-inline>=0.1.6 in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from ipython->ipython-autotime) (0.2.2)
Requirement already satisfied: prompt_toolkit<3.1.0,>=3.0.41 in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from ipython->ipython-autotime) (3.0.53)
Requirement already satisfied: psutil>=7 in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from ipython->ipython-autotime) (7.2.2)
Requirement already satisfied: pygments>=2.14.0 in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from ipython->ipython-autotime) (2.21.0)
Requirement already satisfied: stack_data>=0.6.0 in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from ipython->ipython-autotime) (0.6.3)
Requirement already satisfied: traitlets>=5.13.0 in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from ipython->ipython-autotime) (5.16.1)
Requirement already satisfied: wcwidth>=0.1.4 in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from prompt_toolkit<3.1.0,>=3.0.41->ipython->ipython-autotime) (0.9.2)
Requirement already satisfied: parso<0.9.0,>=0.8.6 in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from jedi>=0.18.2->ipython->ipython-autotime) (0.8.7)
Requirement already satisfied: executing>=1.2.0 in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from stack_data>=0.6.0->ipython->ipython-autotime) (2.2.1)
Requirement already satisfied: asttokens>=2.1.0 in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from stack_data>=0.6.0->ipython->ipython-autotime) (3.0.2)
Requirement already satisfied: pure-eval in C:\Users\joann\AppData\Roaming\Python\Python314\site-packages (from stack_data>=0.6.0->ipython->ipython-autotime) (0.2.4)
Note: you may need to restart the kernel to use updated packages.
time: 100 μs (started: 2026-10-06 13:24:52 -07:00)
time: 3.04 s (started: 2026-10-06 13:24:52 -07:00)

Dataset: Data Acquisition (AI Project Cycle - Step 2)

Dataset downloading steps

DataSet: https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce

“brazilian-ecommerce.zip” will get downloaded in the current data folder folderd
In the data folder execute the following command to unzip (Note: You may need to install unzip using: sudo apt-get install unzip) unzip brazilian-ecommerce.zip

Source - Before we delve into the proposed architecture of the solution pipeline, it is critical to understand the dataset and its schema. The dataset consists of real-world delivery details sourced from a Brazilian E-commerce Company which was scrubbed and anonymized. It can be found here.

It consists of multiple tables which include relevant information about the customer, seller, order, location etc. The individual tables are interconnected as shown in the following schema. Relevant features will be extracted/designed from this data and then be used to train our supervised ML model.

dataset-schema

Load/Read the Dataset

time: 3.42 s (started: 2026-10-06 13:24:55 -07:00)

View the data

Loading...
time: 35.3 ms (started: 2026-10-06 13:24:59 -07:00)
Loading...
time: 34.7 ms (started: 2026-10-06 13:24:59 -07:00)
Loading...
time: 20 ms (started: 2026-10-06 13:24:59 -07:00)
Loading...
time: 16.9 ms (started: 2026-10-06 13:24:59 -07:00)

From a business perspective, we need to tackle two main challenges to ensure transparency for the customer:

  1. Extracting insights/estimates into forecasted delivery

  2. Increasing the accuracy of those insights and estimates

We can achieve the first objective by implementing multiple pipelines with separate objectives and the second one by utilizing techniques such as ensemble modeling to increase the accuracy of stand-alone ML components.

**A schematic of the proposed reference architecture is shown in the following figure. We start off with Data Ingestion from the multiple tables of the dataset, followed by merging and preprocessing for feature extraction. We can use the features to extract delivery insights in the form of predicted wait time as well as likelihood of delivery delay.

The former is a regression problem and the latter is a classification problem.

Data Preprocessing ------ Data Exploration(AI Project Cycle - Step 3)

View the information of data

<class 'pandas.DataFrame'>
RangeIndex: 32951 entries, 0 to 32950
Data columns (total 9 columns):
 #   Column                      Non-Null Count  Dtype  
---  ------                      --------------  -----  
 0   product_id                  32951 non-null  str    
 1   product_category_name       32341 non-null  str    
 2   product_name_lenght         32341 non-null  float64
 3   product_description_lenght  32341 non-null  float64
 4   product_photos_qty          32341 non-null  float64
 5   product_weight_g            32949 non-null  float64
 6   product_length_cm           32949 non-null  float64
 7   product_height_cm           32949 non-null  float64
 8   product_width_cm            32949 non-null  float64
dtypes: float64(7), str(2)
memory usage: 2.3 MB
time: 92.2 ms (started: 2026-10-06 13:24:59 -07:00)
<class 'pandas.DataFrame'>
RangeIndex: 99441 entries, 0 to 99440
Data columns (total 5 columns):
 #   Column                    Non-Null Count  Dtype
---  ------                    --------------  -----
 0   customer_id               99441 non-null  str  
 1   customer_unique_id        99441 non-null  str  
 2   customer_zip_code_prefix  99441 non-null  int64
 3   customer_city             99441 non-null  str  
 4   customer_state            99441 non-null  str  
dtypes: int64(1), str(4)
memory usage: 3.8 MB
time: 59.4 ms (started: 2026-10-06 13:24:59 -07:00)
<class 'pandas.DataFrame'>
RangeIndex: 3095 entries, 0 to 3094
Data columns (total 4 columns):
 #   Column                  Non-Null Count  Dtype
---  ------                  --------------  -----
 0   seller_id               3095 non-null   str  
 1   seller_zip_code_prefix  3095 non-null   int64
 2   seller_city             3095 non-null   str  
 3   seller_state            3095 non-null   str  
dtypes: int64(1), str(3)
memory usage: 96.8 KB
time: 15.6 ms (started: 2026-10-06 13:24:59 -07:00)
<class 'pandas.DataFrame'>
RangeIndex: 99441 entries, 0 to 99440
Data columns (total 8 columns):
 #   Column                         Non-Null Count  Dtype
---  ------                         --------------  -----
 0   order_id                       99441 non-null  str  
 1   customer_id                    99441 non-null  str  
 2   order_status                   99441 non-null  str  
 3   order_purchase_timestamp       99441 non-null  str  
 4   order_approved_at              99281 non-null  str  
 5   order_delivered_carrier_date   97658 non-null  str  
 6   order_delivered_customer_date  96476 non-null  str  
 7   order_estimated_delivery_date  99441 non-null  str  
dtypes: str(8)
memory usage: 6.1 MB
time: 241 ms (started: 2026-10-06 13:24:59 -07:00)

Merge/Clean Data

Loading...
time: 331 ms (started: 2026-10-06 13:24:59 -07:00)
Loading...
time: 273 ms (started: 2026-10-06 13:25:00 -07:00)
Loading...
time: 82.7 ms (started: 2026-10-06 13:25:00 -07:00)
Loading...
time: 257 ms (started: 2026-10-06 13:25:00 -07:00)
time: 56.1 ms (started: 2026-10-06 13:25:00 -07:00)
time: 1.41 s (started: 2026-10-06 13:25:01 -07:00)
Loading...
time: 261 ms (started: 2026-10-06 13:25:02 -07:00)
time: 20.1 ms (started: 2026-10-06 13:25:02 -07:00)
time: 160 ms (started: 2026-10-06 13:25:02 -07:00)
time: 8.9 ms (started: 2026-10-06 13:25:03 -07:00)
Loading...
time: 252 ms (started: 2026-10-06 13:25:03 -07:00)

Find the total number of missing values feature wise

Loading...
time: 239 ms (started: 2026-10-06 13:25:03 -07:00)

Scaling the data

time: 2.16 ms (started: 2026-10-06 13:25:03 -07:00)

Splitting dataset into separate training and test set

time: 2.86 ms (started: 2026-10-06 13:25:03 -07:00)
time: 2.82 s (started: 2026-10-06 13:25:03 -07:00)
time: 35.3 ms (started: 2026-10-06 13:25:06 -07:00)
79933
time: 4.86 ms (started: 2026-10-06 13:25:06 -07:00)
26645
time: 173 ms (started: 2026-10-06 13:25:06 -07:00)
Loading...
time: 36.5 ms (started: 2026-10-06 13:25:06 -07:00)
Loading...
time: 391 ms (started: 2026-10-06 13:25:06 -07:00)
Loading...
time: 106 ms (started: 2026-10-06 13:25:07 -07:00)

Building a Model ------ Modeling ( AI Project Cycle - Step 4)

We will now use the sklearn library to build the model. We will begin by defining the hyperparameters and determining the best fit model. We will be comparing the accuracy of three models, namely Support Vector Machine (SVM), Random Forest, XGBoost.

time: 31 ms (started: 2026-10-06 13:25:07 -07:00)

What are Hyperparameters? • A hyperparameter is a parameter whose value is set before the learning process begins

• Algorithm hyperparameters affect the speed and quality of the learning process

• Hyperparameters are important because they can have a direct impact on the behaviour of the training algorithm and have a
significant impact on the performance of the model being trained

Examples - Number of epochs, number of clusters, batch size etc

Performing hyperparameter tuning

As shown below, we are trying to find which algorithm should be used to build the model and which hyperparameters would contribute to better performance:

In this case, C(adds penalty to each misclassified point) and kernel are the hyperparameters of SVM

n_estimators(this specifies the number of trees you want to build before taking the maximum voting or averages of predictions. Higher number of trees give you better performance but makes your code slower) - hyperparameter for random forest

C(logistic regression) large values of C give more freedom to the model. Conversely, smaller values of C constrain the model more

ensemble
time: 15.3 ms (started: 2026-10-06 13:25:07 -07:00)
time: 3.28 ms (started: 2026-10-06 13:25:07 -07:00)
Running Hyperparameter Tuning for XGB
time: 8.96 ms (started: 2026-10-06 13:25:07 -07:00)
Fitting 4 folds for each of 4 candidates, totalling 16 fits
time: 38.9 s (started: 2026-10-06 13:25:07 -07:00)
time: 1.94 ms (started: 2026-10-06 13:25:46 -07:00)
Hyperparameter tuning time for for: XGB:  38.92837381362915


time: 251 ms (started: 2026-10-06 13:25:46 -07:00)
Running Hyperparameter Tuning for RF
time: 7.46 ms (started: 2026-10-06 13:25:46 -07:00)
Fitting 4 folds for each of 4 candidates, totalling 16 fits
time: 1min 30s (started: 2026-10-06 13:25:46 -07:00)
time: 1.22 ms (started: 2026-10-06 13:27:17 -07:00)
Hyperparameter tuning time for for: RF:  90.3900499343872


time: 3.68 s (started: 2026-10-06 13:27:17 -07:00)
Running Hyperparameter Tuning for SV
time: 4.67 ms (started: 2026-10-06 13:27:21 -07:00)
Fitting 4 folds for each of 4 candidates, totalling 16 fits
time: 40.4 s (started: 2026-10-06 13:27:21 -07:00)
time: 71.8 ms (started: 2026-10-06 13:28:01 -07:00)
Hyperparameter tuning time for for: SV:  40.38777542114258


time: 4.46 s (started: 2026-10-06 13:28:01 -07:00)
Running Hyperparameter Tuning for ensemble
time: 13.5 ms (started: 2026-10-06 13:28:06 -07:00)
Fitting 4 folds for each of 4 candidates, totalling 16 fits
time: 1min 44s (started: 2026-10-06 13:28:06 -07:00)
time: 1.34 ms (started: 2026-10-06 13:29:50 -07:00)
Hyperparameter tuning time for for: ensemble model:  104.85030245780945


time: 41.6 ms (started: 2026-10-06 13:29:50 -07:00)

Training the Ensemble Model

Running Training for ensemble model with data length  8192
Training time for for: ensemble model:  16.383808374404907


Running Training for ensemble model with data length  32768
Training time for for: ensemble model:  385.5708031654358


time: 6min 42s (started: 2026-10-06 13:29:50 -07:00)
['purchase_dow', 'purchase_month', 'year', 'product_size_cm3', 'product_weight_g', 'geolocation_state_customer', 'geolocation_state_seller', 'distance', 'wait_time']
[2.0, 4.0, 2018.0, 4500.0, 1550.0, 18.0, 20.0, 590.8619422803324, 26.0]
time: 9.72 ms (started: 2026-10-06 13:36:33 -07:00)

Saving the machine learning models

The code below trains and tests a voting regressor model using a dataset called ti_test_section, saves the trained model to a file using the pickle module, and evaluates the average inference time for the model.

Average Streaming Inference Time for Voting Regressor model:  0.11344070792198181
time: 1min 56s (started: 2026-10-06 13:36:33 -07:00)

Evaluating the Model ------- Evaluation (AI Project Cycle - Step 5)

Testing the Ensemble Model

Running Inference with data length  10000
Inference time and MSE for for XGB:  0.08887147903442383 70.40806025142967
Inference time and MSE for for RF:  0.7224164009094238 62.61134961699608
Inference time and MSE for for SV:  9.705978393554688 87.77734877500572
Inference time and MSE for for Voting model:  38.271326303482056 59.96530031259762


Running Inference with data length  30000
Inference time and MSE for for XGB:  0.1336226463317871 71.31120645152184
Inference time and MSE for for RF:  1.6259596347808838 62.19745110063734
Inference time and MSE for for SV:  30.64043617248535 85.88216885918328
Inference time and MSE for for Voting model:  120.47177004814148 57.963955295744874


Running Inference with data length  50000
Inference time and MSE for for XGB:  0.5704658031463623 71.29848722819017
Inference time and MSE for for RF:  4.127070903778076 61.656879172854964
Inference time and MSE for for SV:  55.103110790252686 85.64032957257525
Inference time and MSE for for Voting model:  162.6711859703064 57.19466770572212


Running Inference with data length  70000
Inference time and MSE for for XGB:  0.3344845771789551 71.48840305248123
Inference time and MSE for for RF:  3.1643364429473877 62.187941486414765
Inference time and MSE for for SV:  53.120020627975464 86.34629841040132
Inference time and MSE for for Voting model:  266.75788497924805 57.83300521101771


time: 12min 27s (started: 2026-10-06 13:38:29 -07:00)

Few questions on Interpreting MSE of an Machine Learning Model

Q1. What is a good MSE value for a machine learning model?

A: The MSE value depends on the problem being solved and the nature of the data. In general, a lower MSE indicates better performance. However, there is no fixed threshold for a good or bad MSE value, and it is often relative to the baseline or other models being compared.

Q2. Can MSE be negative?

A: No, MSE is always a non-negative value. It is the sum of the squared errors divided by the number of data points, so it cannot be negative.

Q3. Is MSE affected by outliers in the data?

A: Yes, MSE is sensitive to outliers in the data. Outliers, which are data points that are significantly different from the other points, can have a large influence on the squared errors and increase the MSE. Therefore, it is important to handle outliers appropriately, such as by removing them or transforming the data before modeling.