Experiments >

Delivery estimates: alarms

Experiment #8728th March, 2021by Joshua Nussbaum

When a package is an expedited parcel (ie overnight, 2-day, etc), the warehouse cannot miss the carrier pickup or it will result in an unhappy customer. It would be nice to raise an alarm via e-mail when the system detects a package is about to miss the pickup.

It turns out the database schema I built contains much of the data needed to make that work. It already knows the operating & holiday schedule for both the warehouse and the carrier.

Finding unfulfilled orders

If an orders table is added to the mix:

create table orders (
  id serial primary key,
  account_id bigint references accounts not null,
  number varchar not null,
  source varchar not null,
  status varchar not null,
  payment_status varchar not null,
  fulfillment_status varchar not null,
  fulfill_by timestamp with time zone not null

The order data could be pulled from the cart system and then queried to find which orders are still unfulfilled:

select * from orders
  where fulfillment_status = 'unfulfilled' and fulfill_by < now()

Computing the threshold

The tricky part is computing the fulfill_by timestamp.

It’s a combination of:

  • Business date: the business date of the order depends on the warehouse cutoff time. All orders past the cutoff moves to the next business date.
  • Operating schedule: when an order is placed past the cutoff time, or on a date when the warehouse is closed, the date slides to next open calendar day.
  • Holiday schedule: similar to operating schedule rules, the holiday schedule of the carrier and warehouse need to be verified.
  • Packing time: the system need to factor in how long it takes to pack an item (as a global setting). eg, if the packing time is 1 hour, and the carrier pickup time is 3PM, then the alarm should sound at 2PM (1 hour before pickup)
view all experiments

Stay tuned in

Learn how to add more experimentation to your workflow