Project Overview
Shipping costs can add up quickly in ecommerce, but a lower quoted rate does not automatically mean a programme will produce meaningful savings.
For this project, I analysed historical orders and shipping costs to evaluate a proposed lightweight shipping program. The goal was simple: determine how many past shipments would have qualified, compare what the business actually paid against the proposed rate, and estimate the potential savings.
The analysis identified approximately $22,185 in potential shipping savings, representing a 29.2% reduction across qualifying shipments.
The Challenge
The carrier proposed a flat shipping rate of $8.63 for eligible lightweight packages.
On paper, the rate looked competitive. But before evaluating the program, the business needed to understand how it would have performed against actual historical shipments.
The challenge was that the information needed for the analysis did not exist in a single dataset.
Order information came from the ecommerce platform, shipping costs came from separate shipping label records, and product weights had to be referenced from another product-level dataset.
The question was not simply whether $8.63 was a good shipping rate, it was – how much would the business actually have saved if this pricing had been applied to historical orders?
The Approach
01
Connect orders with shipping records
I matched ecommerce orders with their corresponding shipping label records using the available order identifiers. This allowed the shipping cost paid for each shipment to be connected back to the products contained in the order.
02
Reconstruct shipment weights
The order export did not provide all the weight information needed for the analysis. I used a separate SKU-level weight reference to estimate the total product weight of each order and identify shipments that met the programme’s eligibility requirements.
03
Isolate qualifying shipments
The analysis focused on shipments weighing 9 lbs or less, based on the proposed program requirements. Historical orders outside this threshold were excluded from the comparison.
04
Compare actual and proposed costs
For each qualifying shipment, I compared the historical shipping cost against the proposed $8.63 flat rate. The differences were then aggregated to estimate the program’s potential impact across the full analysis period.
What I Built
I created an order-level analysis that combined data from multiple sources into a single comparison dataset.
For every qualifying shipment, the analysis could identify:
-the corresponding ecommerce order
-estimated shipment weight
-historical shipping cost
-proposed shipping cost
-estimated savings or additional cost
This made it possible to evaluate the carrier proposal using the company’s own shipping history rather than relying only on the quoted rate.
From Shipping Data to Savings
A total of 6,226 orders met the program’s lightweight criteria. Those shipments had generated approximately $75,915.01 in actual shipping costs.
Applying the proposed $8.63 rate to the same qualifying shipments produced an estimated cost of $53,730.38. That represented potential savings of approximately $22,184.63 or 29.2%.
The Outcome
Rather than evaluating the program based only on the carrier’s advertised rate, the business could see its estimated impact against thousands of actual historical shipments.
The analysis showed that the proposed program had the potential to materially reduce shipping costs for qualifying orders.
It also provided a practical benchmark for discussions with the carrier and for evaluating future shipping pricing proposals.
More importantly, it turned what initially looked like a simple rate comparison into a decision supported by the company’s own data.