tsb — Fulfillment SLA Control Tower

Skills you'll use: chained merge, SLA variance metrics, boolean filters, nlargestDataFrame, groupby().agg(), backlog bucketing, and pivot-table heatmaps.

1 · Join order, shipment, and carrier facts

Calculate days late, carrier variance, and an exception score for customer-impact triage.

TypeScript

import { DataFrame, merge, nlargestDataFrame } from "tsb";

const joined = merge(
  merge(orders, shipments, { on: "order_id", how: "left" }),
  carriers,
  { on: "carrier", how: "left" },
).assign({
  days_late: (df) => df.col("ship_days").sub(df.col("promised_days")),
  carrier_variance: (df) => df.col("ship_days").sub(df.col("sla_days")),
  exception_score: (df) => df.col("ship_days").sub(df.col("sla_days")).add(df.col("scan_exceptions").mul(2)),
});

console.log(joined.select(["order_id", "warehouse", "carrier", "customer_tier", "order_value", "days_late", "exception_score"]).toString());

const late = joined.filter(joined.col("days_late").gt(0));
console.log("\nHigh-value late orders:");
console.log(nlargestDataFrame(late, 4, { columns: "order_value" }).select(["order_id", "warehouse", "carrier", "order_value", "days_late"]).toString());

import pandas as pd

orders = pd.DataFrame([
    {"order_id": "o1001", "customer_tier": "gold", "warehouse": "WH-East", "order_value": 840, "promised_days": 2},
    {"order_id": "o1002", "customer_tier": "standard", "warehouse": "WH-West", "order_value": 215, "promised_days": 4},
    {"order_id": "o1003", "customer_tier": "gold", "warehouse": "WH-East", "order_value": 1240, "promised_days": 2},
    {"order_id": "o1004", "customer_tier": "standard", "warehouse": "WH-South", "order_value": 180, "promised_days": 5},
    {"order_id": "o1005", "customer_tier": "silver", "warehouse": "WH-West", "order_value": 520, "promised_days": 3},
    {"order_id": "o1006", "customer_tier": "gold", "warehouse": "WH-South", "order_value": 760, "promised_days": 2},
    {"order_id": "o1007", "customer_tier": "standard", "warehouse": "WH-East", "order_value": 130, "promised_days": 4},
    {"order_id": "o1008", "customer_tier": "silver", "warehouse": "WH-West", "order_value": 680, "promised_days": 3},
])
shipments = pd.DataFrame([
    {"order_id": "o1001", "carrier": "ParcelPro", "ship_days": 3, "scan_exceptions": 1},
    {"order_id": "o1002", "carrier": "BudgetFreight", "ship_days": 4, "scan_exceptions": 0},
    {"order_id": "o1003", "carrier": "SwiftShip", "ship_days": 2, "scan_exceptions": 0},
    {"order_id": "o1004", "carrier": "BudgetFreight", "ship_days": 7, "scan_exceptions": 2},
    {"order_id": "o1005", "carrier": "ParcelPro", "ship_days": 5, "scan_exceptions": 1},
    {"order_id": "o1006", "carrier": "SwiftShip", "ship_days": 4, "scan_exceptions": 1},
    {"order_id": "o1007", "carrier": "ParcelPro", "ship_days": 3, "scan_exceptions": 0},
    {"order_id": "o1008", "carrier": "BudgetFreight", "ship_days": 6, "scan_exceptions": 2},
])
carriers = pd.DataFrame([
    {"carrier": "ParcelPro", "sla_days": 3, "cost_per_pkg": 11.5},
    {"carrier": "SwiftShip", "sla_days": 2, "cost_per_pkg": 14.75},
    {"carrier": "BudgetFreight", "sla_days": 5, "cost_per_pkg": 8.25},
])
joined = orders.merge(shipments, on="order_id", how="left").merge(carriers, on="carrier", how="left")
joined["days_late"] = joined["ship_days"] - joined["promised_days"]
joined["carrier_variance"] = joined["ship_days"] - joined["sla_days"]
joined["exception_score"] = joined["carrier_variance"] + joined["scan_exceptions"] * 2
print(joined[["order_id", "warehouse", "carrier", "customer_tier", "order_value", "days_late", "exception_score"]])
print(joined[joined["days_late"] > 0].nlargest(4, "order_value")[["order_id", "warehouse", "carrier", "order_value", "days_late"]])

Click ▶ Run to execute

Ctrl+Enter to run · Tab to indent

2 · Score carrier and warehouse SLA misses

Summarize late volume by carrier and build a warehouse × carrier heatmap.

TypeScript

import { DataFrame, nlargestDataFrame, pivotTableFull } from "tsb";

const scored = DataFrame.fromRecords([
  { order_id: "o1001", warehouse: "WH-East", carrier: "ParcelPro", late_flag: 1, days_late: 1, order_value: 840 },
  { order_id: "o1002", warehouse: "WH-West", carrier: "BudgetFreight", late_flag: 0, days_late: 0, order_value: 215 },
  { order_id: "o1003", warehouse: "WH-East", carrier: "SwiftShip", late_flag: 0, days_late: 0, order_value: 1240 },
  { order_id: "o1004", warehouse: "WH-South", carrier: "BudgetFreight", late_flag: 1, days_late: 2, order_value: 180 },
  { order_id: "o1005", warehouse: "WH-West", carrier: "ParcelPro", late_flag: 1, days_late: 2, order_value: 520 },
  { order_id: "o1006", warehouse: "WH-South", carrier: "SwiftShip", late_flag: 1, days_late: 2, order_value: 760 },
  { order_id: "o1007", warehouse: "WH-East", carrier: "ParcelPro", late_flag: 0, days_late: -1, order_value: 130 },
  { order_id: "o1008", warehouse: "WH-West", carrier: "BudgetFreight", late_flag: 1, days_late: 3, order_value: 680 },
]);

const carrierSummary = scored
  .groupby("carrier")
  .agg({ late_flag: "sum", days_late: "mean", order_value: "sum" }, false)
  .sortValues("late_flag", false);
console.log("Carrier SLA summary:");
console.log(carrierSummary.toString());

const lateHeatmap = pivotTableFull(scored, {
  index: "warehouse",
  columns: "carrier",
  values: "late_flag",
  aggfunc: "sum",
  fill_value: 0,
  margins: true,
});
console.log("\nLate shipments by warehouse/carrier:");
console.log(lateHeatmap.toString());

const lateOrders = scored.filter(scored.col("late_flag").eq(1));
console.log("\nLargest late orders:");
console.log(nlargestDataFrame(lateOrders, 3, { columns: "order_value" }).select(["order_id", "warehouse", "carrier", "order_value", "days_late"]).toString());

import pandas as pd

scored = pd.DataFrame([
    {"order_id": "o1001", "warehouse": "WH-East", "carrier": "ParcelPro", "late_flag": 1, "days_late": 1, "order_value": 840},
    {"order_id": "o1002", "warehouse": "WH-West", "carrier": "BudgetFreight", "late_flag": 0, "days_late": 0, "order_value": 215},
    {"order_id": "o1003", "warehouse": "WH-East", "carrier": "SwiftShip", "late_flag": 0, "days_late": 0, "order_value": 1240},
    {"order_id": "o1004", "warehouse": "WH-South", "carrier": "BudgetFreight", "late_flag": 1, "days_late": 2, "order_value": 180},
    {"order_id": "o1005", "warehouse": "WH-West", "carrier": "ParcelPro", "late_flag": 1, "days_late": 2, "order_value": 520},
    {"order_id": "o1006", "warehouse": "WH-South", "carrier": "SwiftShip", "late_flag": 1, "days_late": 2, "order_value": 760},
    {"order_id": "o1007", "warehouse": "WH-East", "carrier": "ParcelPro", "late_flag": 0, "days_late": -1, "order_value": 130},
    {"order_id": "o1008", "warehouse": "WH-West", "carrier": "BudgetFreight", "late_flag": 1, "days_late": 3, "order_value": 680},
])
print(scored.groupby("carrier", as_index=False).agg({"late_flag": "sum", "days_late": "mean", "order_value": "sum"}).sort_values("late_flag", ascending=False))
print(pd.pivot_table(scored, index="warehouse", columns="carrier", values="late_flag", aggfunc="sum", fill_value=0, margins=True))
print(scored[scored["late_flag"] == 1].nlargest(3, "order_value")[["order_id", "warehouse", "carrier", "order_value", "days_late"]])

Click ▶ Run to execute

Ctrl+Enter to run · Tab to indent

3 · Age the open backlog against staffing capacity

Bucket open work by age, merge staffing capacity, and identify overloaded warehouses.

TypeScript

import { DataFrame, merge, pivotTableFull } from "tsb";

const agingHeatmap = pivotTableFull(backlog, {
  index: "warehouse",
  columns: "age_bucket",
  values: "pick_units",
  aggfunc: "sum",
  fill_value: 0,
  margins: true,
});
console.log("\nOpen pick units by age bucket:");
console.log(agingHeatmap.toString());

import numpy as np
import pandas as pd

backlog_rows = [
    {"order_id": "b201", "warehouse": "WH-East", "status": "pick", "age_hours": 5, "pick_units": 12, "pack_units": 0},
    {"order_id": "b202", "warehouse": "WH-East", "status": "pack", "age_hours": 14, "pick_units": 0, "pack_units": 9},
    {"order_id": "b203", "warehouse": "WH-West", "status": "pick", "age_hours": 27, "pick_units": 18, "pack_units": 0},
    {"order_id": "b204", "warehouse": "WH-West", "status": "pack", "age_hours": 36, "pick_units": 0, "pack_units": 16},
    {"order_id": "b205", "warehouse": "WH-South", "status": "pick", "age_hours": 9, "pick_units": 10, "pack_units": 0},
    {"order_id": "b206", "warehouse": "WH-South", "status": "pack", "age_hours": 52, "pick_units": 0, "pack_units": 22},
    {"order_id": "b207", "warehouse": "WH-East", "status": "pick", "age_hours": 31, "pick_units": 14, "pack_units": 0},
    {"order_id": "b208", "warehouse": "WH-West", "status": "pick", "age_hours": 6, "pick_units": 8, "pack_units": 0},
]
backlog = pd.DataFrame(backlog_rows)
backlog["age_bucket"] = np.select(
    [backlog["age_hours"] >= 48, backlog["age_hours"] >= 24, backlog["age_hours"] >= 8],
    ["48h+", "24-48h", "8-24h"],
    default="<8h",
)
capacity = pd.DataFrame([
    {"warehouse": "WH-East", "pickers": 5, "packers": 4},
    {"warehouse": "WH-West", "pickers": 3, "packers": 3},
    {"warehouse": "WH-South", "pickers": 2, "packers": 2},
])
workload = backlog.groupby("warehouse", as_index=False).agg({"pick_units": "sum", "pack_units": "sum", "age_hours": "mean"})
staffing_plan = workload.merge(capacity, on="warehouse", how="left")
staffing_plan["pick_units_per_picker"] = staffing_plan["pick_units"] / staffing_plan["pickers"]
staffing_plan["pack_units_per_packer"] = staffing_plan["pack_units"] / staffing_plan["packers"]
print(staffing_plan.sort_values("pick_units_per_picker", ascending=False))
print(pd.pivot_table(backlog, index="warehouse", columns="age_bucket", values="pick_units", aggfunc="sum", fill_value=0, margins=True))

Click ▶ Run to execute

Ctrl+Enter to run · Tab to indent

🚚 Fulfillment SLA Control Tower

1 · Join order, shipment, and carrier facts

2 · Score carrier and warehouse SLA misses

3 · Age the open backlog against staffing capacity