How to automate a daily fulfillment report for health commerce

Join order lines, warehouse activity, and carrier events into one exception report. Assign each open order line a current state, promised date, and owner so the fulfillment and customer teams can resolve delays before the next cutoff.

Decide what counts as late

Write down whether the promise is a ship date or delivery date and whether it applies to an order, line, or shipment. A single order can split across warehouses or contain held and canceled lines. Use the most detailed grain required to avoid marking an entire order as late when only one item is delayed. Shopify's fulfillment-order model describes items expected to fulfill from the same location and exposes fulfillment statuses. Warehouse and carrier statuses still need a separate mapping for your operation. Shopify FulfillmentOrder documentation · Shopify fulfillment status reference

Build an owned queue instead of a static export

What to automate

Collect source events

Run a scheduled intake from the order system, warehouse and carrier feeds. Save source event ID, event time, received time, source and revision, then checkpoint the last complete window. Reconcile expected source arrivals and backfill a gap before marking it current. Alert the source owner and carry a stale-source flag into the report while a feed is late.

Link orders and shipments

Join by order line and shipment allocation keys, allowing a line to have multiple allocations. Roll up dispatched quantities after cancellations, and keep picks, dispatch and delivery as separate milestones. Validate that assigned quantities fit the line quantity. Route unlinked shipment IDs and mismatched quantities to warehouse operations with the source references.

Compare with the promise

Configure the approved ship or delivery promise by line, warehouse time zone and hold policy. Compare the promised milestone with confirmed events at the report cutoff. Classify approaching deadlines, late lines and missing evidence separately. Include the source freshness, elapsed time and rule used so an owner can distinguish a late order from an unavailable feed.

Route and resolve

Assign each exception to warehouse, inventory or carrier follow-up according to the documented cause. Use a stable order-line and cause key to update the same ticket on replay. Let AI suggest a category from redacted notes and known events. Have the operator confirm the category, and preserve evidence for any approved change to a promise or status.

Report and close

Publish the dated report after the operations owner checks source freshness, open counts and escalations. Close an issue when the agreed evidence arrives, for example a confirmed dispatch that clears a ship-promise exception. Record the closing event and time. Compare yesterday's and today's open, resolved and reopened records to detect work that vanished from the queue.

Test a split shipment and a stale carrier feed

Use a synthetic two-line order where line 1 ships before the promise and line 2 has no warehouse pick after the promise. Expect one late line owned by warehouse operations. If the warehouse confirms dispatch for line 2 but the carrier feed is stale, report the stale source separately instead of treating it as proof the parcel is missing. Replay the same dispatch event and confirm the report and ticket counts are unchanged. Measure exception age, cutoff misses, report timeliness, and avoidable customer follow-ups.

Implement an order-line reconciliation

Keep order_line(order_id, line_id, ordered_qty, cancelled_qty, sku) apart from fulfillment_event(provider, event_id, order_id, line_id, shipment_id, event_type, qty, source_revision). One order can have several shipments. Normalize provider status to explicit dispatch, delivered, returned, cancelled and unknown states and retain the raw status.

1. Join commerce records, fulfillment partner events and approved inventory counts using stable scoped IDs. Map SKU substitutions and bundles at the line level. Put unknown SKU, address-related delivery issue or unrecognized provider event in an access-controlled exception.
2. Deduplicate event IDs and resolve correction versions before aggregating quantity. Cap accepted cancellations at the ordered quantity and flag dispatched quantity above the remaining obligation. Report return-to-stock separately from shipment completion.
3. Recompute open and late lines from the approved date and SLA policy. A late carrier scan can update a later report, while a previous publication retains the exact snapshot. Missing status is visible in its own bucket.
4. Deliver an aggregate dashboard and a role-scoped exception queue. Test a split shipment, duplicate scan, partial cancellation and replacement. A webhook retry should never inflate the quantity or notify the owner twice.

ordered, cancelled = 5, 1
shipments = [("s1", 2), ("s1", 2), ("s2", 1)]
accepted = {}
for shipment_id, qty in shipments:
    if shipment_id in accepted and accepted[shipment_id] != qty:
        raise ValueError("conflicting shipment quantity")
    accepted[shipment_id] = qty
assert sum(accepted.values()) == 3
assert ordered - cancelled - sum(accepted.values()) == 1

The synthetic fixture treats each shipment ID as one accepted quantity for one order line. A real feed may need event subtype and revision in the deduplication key.

Implement the work step by step

The fulfillment operations manager decides whether warehouse, inventory and carrier owners receive the right late-line exceptions before cutoff. The workflow begins at the daily operating cutoff after source event pulls. Ends with an owned exception queue and resolved-case proof. Excludes physical picking, shipping and returns fulfillment.

Capture order, warehouse and carrier events

• Load order promises and line detail. Stage events by ID with order timezone and read-only access. Read Commerce API, promise configuration, order line snapshots. Record Current line population to reconciliation. Owner Operations analyst. Validate definition of promise and eligible statuses. Check An order-level promise applies only according to the approved line rule.
• Load warehouse and carrier movements. Capture allocation and tracking events, distinguish event from arrival timestamps. Read WMS allocation events and carrier tracking. Record Warehouse and carrier cutoff manifest. Owner Warehouse coordinator. Request corrected missing feed when stale. Check Do not silently treat stale source as negative scan.

Resolve status and late work

• Match line allocations and partial fulfillment. Map allocation keys and aggregate quantity by status with reversal handling. Read Order line keys, allocation IDs and tracking. Record One quantity and state ledger per line. Owner Operations analyst. Resolve orphan shipment. Check Line quantity cannot exceed authorized shipped quantity absent approved correction.
• Identify promise breaches and holds. Run date and time-zone comparisons and approved hold policy. AI can suggest category from redacted reason notes. Read Promise policy, line state, cutoff calendar. Record Prioritized exception candidates to coordinator. Owner Operations analyst. Confirm unresolved or conflicting hold reason. Check No duplicate alerts for same open issue.
• Handle ambiguous carrier or WMS event. Create linked task and retain the event IDs and stale-source status. AI can draft an internal summary from redacted notes. Read Exception detail, WMS shipment evidence, carrier portal. Record Verified state or source issue to operations. Owner Warehouse coordinator. Verify physical shipment or contact carrier. Check Wait time measured apart from active work.

Route and close

• Assign exceptions to responsible owner. Upsert one issue by line and cause and enforce owner mapping. AI can suggest reason and summarize before handoff. Read Ticketing, warehouse routing rules, live exceptions. Record Owned queue with due date and severity. Owner Fulfillment coordinator. Handle physical correction or authorized communication. Check No orphan or duplicate task after replay.
• Publish report and verify resolved issues. Publish date-stamped snapshot, close only on source proof, preserve resolved times. AI can draft summary of major trend from report data. Read Exception snapshots, dispatch receipts, ticket status. Record Approved report and close ledger to operations. Owner Operations manager. Approve SLA and send externally only under policy. Check Opened, resolved and outstanding counts tie to lifecycle events.

Parallel run. Shadow one warehouse across normal and surge days. Test split shipment, cancellation, stale carrier, replay and late ingest. Verify each open ticket has one owner.

Build it yourself with AI

Create fictional orders.csv, warehouse_events.csv and carrier_events.csv for two lines and one warehouse. Set the operating time zone and promise type in fulfillment_policy.md. Ask a coding assistant to build a dry run that writes one state per line and an owned exception file.

1. Design the state rules

   Read the fixture and policy. Build a table mapping order-line and allocation events to ordered, held, picked, dispatched, delivered and canceled states. Show which quantities are partial and which timestamps support the promised milestone. List cases where an operator must review missing evidence.

2. Build the exception queue

   Write a local Python command that deduplicates source events and uses a stable exception ID. Create a two-line order with line 1 shipped before its deadline and line 2 missing a pick after the ship promise. Expect one late warehouse-owned line. Then add confirmed dispatch for line 2 and mark the carrier feed stale. Keep stale-source status separate and check that replaying dispatch creates no duplicate ticket.

3. Draft a morning handoff

   Given exception rows and redacted notes, draft an owner-grouped handoff with line reference, evidence, age, next action and due time. Put uncertain causes in an operator review group. Use the approved closing rules.

Run alongside the daily report across both on-time and missed cutoffs. Have the warehouse and customer operations leads check that a late line never produces the wrong order-level message. Start by sharing the report with internal reviewers and keep promise changes in the established approval flow.

Need help automating this?

Explore the related retail workflows for bringing order, warehouse and stock signals into checked alerts and operator queues. Explore Mage's inventory and order exception workflows.