Optimizing STEM Store's Delivery Operations with Data-Driven Insights�
MOD Group: 4�Vineeth , Kavyasri , Srujana, Harthik, Srinija, Prashanth
10/03/2023
STEM SHOP's Challenge
2
The STEM Store, is an e-commerce company specializing in educational toys and tools that promote science, technology, engineering, and math (STEM) learning. STEM Shop has recently expanded into new cities resulting in a surge in order volumes. However, they are struggling with delayed deliveries and losing valuable customers to competitors.
Objective: To efficiently allocate delivery partners across the cities facing huge customer churn and contribute to the success of STEM Store and support their business growth.With access to their dataset including sales orders, product details, shipping records, and vendor information, we aim to devise effective strategies for on-time deliveries and improve the CDSI (Customer Deliver Satisfaction Index) while optimizing delivery costs - CPO (Cost per Order).
CDSI
ERD
3
Relational Schema
4
Our Approach
STEP-1:
Delivery Delays Calculation: Performing calculations (Out for delivery date – Promised delivery date to customer) grouped by city, it is noted that the company is facing abject issue of delayed deliveries in specific cities.
Customer Delivery Satisfaction Index : is inversely proportional to delays in deliveries
CDSI α 1/ Delays
Query:
select A.city_id, A.city_name, avg(A.Delay) as average_delay from � ( select *, datediff(out_for_delivery_date, Promised_Delivery_date) as Delay from ofd_sheet) as A� group by city_id,city_name� order by avg(A.delay) DESC
5
Our Approach
STEP-2:
Targeting Top 10 cities with lowest CDSI :
After recognizing that cities with longer delays have a lower CDSI, we coordinated with the client to prioritize top 10 cities experiencing lower CDSI
(SELECT D.City_name, D.city_id, D.AVG_CDSI, B.average_delay from (�# D-table --Calculating Average CDSI by joining ofd_sheet with order table to get CDSI at Shipment level and grouping by city_id’s�(select C.city_name, C.city_ID, AVG(C.CDSI) as AVG_CDSI FROM �(SELECT O.city_name,O.city_ID,O.awb_shipment_number,T.CDSI �FROM ofd_sheet O inner join order_table T ON O.awb_shipment_number = T.awb_shipment_number) as C�group by C.city_name, C.city_ID�order by avg(C.CDSI)) as D�inner join�# B-table --Calculating Average delay by city_id’s�(select A.city_id,A.city_name,avg(A.Delay) as average_delay from �(select *, datediff(out_for_delivery_date,Promised_Delivery_date) as Delay �from ofd_sheet) as A�group by city_id,city_name�order by avg(A.delay) DESC�) B on D.city_ID = B.city_ID)�ORDER BY AVG_CDSI ASC�limit 10)
6
Our Approach
STEP-3:
Now evaluating potential alternative delivery agents for each city using the vendor rate card, which ranks vendors by delivery time and cost. By joining the rate card data with the shipment data, we can pivot vendor information into columns for comparison.
SELECT s.awb_shipment_number, s.city_id, s.city_name, s.Driver_ID, s.SKU_CODE,left(p.volumetric_weight,1) as weight_bucket, p.weight, v.Rate_per_shipment, v.Delivery_Lead_Time, v.Vendor_Id, v.Vendor_Name � from ofd_sheet s left join offline_products_table p� on s.SKU_CODE = p.SKU_CODE left join fleet_table f on f.Driver_ID = s.Driver_ID left join vendor_rate_card v on � left(p.volumetric_weight,1)= v.weight_buckets
7
Our Approach
STEP-4:
Now we bring together the shipment performance data and vendor ranking data. By inner joining the tables, we can compare the current vendor's delivery time and cost to the best available vendor's estimated time and cost.
Criteria for Vendor ranking: For each weight bucket we have ranked the vendors from lowest to highest—lowest for vendors having least lead_time and least rate_per_shipment.
Re-mapping vendors for these cities which have the minimum rank
This results in columns for current vs. revised lead times and costs with preferred vendors (based on ranks).
8
Our Approach
STEP-5:
The top 10 cities by lowest average CDSI are summarized (sample attached), including current performance metrics and potential improvements from switching vendors.
FINAL OUTPUT
9
REVISED STRATEGY
10
Stem Store is launching more products in the above cities and the orders expected to surge. With the above recommended strategy we reduce the delivery lead time from average of 2 days to 2.5 days per shipment thus we predict the CDSI to improve drastically
And Average cost per shipment is reduced by minimum 1$ to maximum 1.5$ which would bring STEM store cost savings by following the suggested dynamic vendor allocation
Result trends
11
The chart shows The trends of Avg. current lead time and Avg. revised lead time for City Id. We can see that there is a significant reduction in lead times with our proposed solution
12
The chart shows the trends of Avg. current cost and Avg. revised cost for City Id. We can see that there is a significant reduction in lead times with our proposed solution
RECOMMENDATIONS TO STEMSTORE
13
Thank You