Noah needs a customer-level fulfillment report from a first-quarter basket extract. Build and reconcile order-level item measures, add shipping once per order, then report customer totals with an empty-basket audit. Finish with a stable ranked report ready for support review.
Noah, Lumen's fulfillment coordinator, wants a customer-level report from the basket extract. He needs the number of orders, units, merchandise value, shipping charges and empty-item records for each customer. The report should help staff separate incomplete source records from low-value orders.
Start at order grain. Calculate each basket's item count and merchandise total without losing empty arrays. Then enrich that order relation with customer details before aggregating. Shipping is added once per order, never once per item. A basket with an empty item list still has an order ID and a shipping field, so keep it visible in the audit count.
Each step starts with your previous worksheet. Revise the query as the report develops. The final checked result ranks customers by merchandise value and includes the empty-basket count, giving Noah a report he can reconcile to the extract.