Practical framework / 5 MIN READ
Choose whether the question is about baskets or items
Separate order outcomes from product outcomes before allocating basket-level amounts.
AI-assisted research and analysis for the SNS Creative Fieldbook
Sources checked 2026-11-06
A single order can contain several products, quantities and adjustments. A product report needs rules for those components, while a basket report needs one record per order. Mixing them can distort both product value and order size.
What you will leave with: Build an order-to-item bridge with explicit treatment of quantities, discounts, delivery and returns.
Use this when
- A product report claims more revenue than the order ledger.
- A team wants average order value by product category.
This lesson concerns commercial unit and allocation choices, rather than matching campaigns to leads.
Choose the commercial question
Ask whether the decision concerns basket economics, units sold or product contribution. Orders containing a product, units of that product and revenue assigned to that product answer different questions. An order containing two categories can legitimately appear in both category-presence counts, which therefore cannot be added into unique orders.
Write the chosen unit beside every measure. Average order value for baskets containing an item describes those baskets, not the item’s own price or incremental contribution. Keep that distinction visible when buyers use the report to decide assortment or promotion.
Map how the export represents items
Inspect whether items are separate rows, nested arrays or a mixture of order headers and line records. BigQuery documentation explains that UNNEST turns array elements into rows. Expanding items therefore changes the table’s grain even when order-level fields remain visible on each resulting row.
Keep order identifiers and line identifiers distinct. Quantity two on one line represents two units but still one line. Returns may refer to a line, a quantity or an order adjustment. Test those cases explicitly before summarizing product performance. Do not assume every negative record is an independent new order.
Agree the allocation policy
Separate amounts already attached to an item from basket-level amounts. Item discounts may be direct; shipping, an order coupon or an order-wide refund may need a policy. Possible allocations include proportional item value or a separate unallocated bucket, depending on the decision and finance definitions.
Do not present an allocation as an observed causal contribution. Splitting a delivery charge across products is an accounting convenience for this report. Document rounding and residual handling so item amounts reconcile to the chosen order amount. Retain original amounts to support a different legitimate view later.
Reconcile both directions
Start with a small set of baskets: a single item, several categories, multiple quantities and a partial return. Confirm that the sum of allocated components matches the agreed order basis, while unique order counts remain correct. Check whether taxes and delivery belong in the comparison.
Publish separate basket and item views when both are needed. Label category-presence counts as overlapping and show product net revenue under its stated allocation policy. A clear report can support two valid perspectives without forcing every measure to be additive across every dimension.
Make the reasoning concrete.
A hypothetical basket contains a $60 jacket and $40 bag, with a $10 order coupon. A proportional policy assigns $6 of discount to the jacket and $4 to the bag, leaving $54 and $36 of item revenue. Delivery and taxes are excluded here.
Copy basket revenue
Opening: Assign the $90 net basket total to both items.
Hypothesis: Each item is connected to the purchase.
Risk: The product totals falsely sum to $180.
Allocate with a named policy
Opening: Use the $54 and $36 item amounts while counting one basket.
Hypothesis: Product amounts reconcile to $90.
Risk: The split does not prove either item caused the sale.
Use the explicit proportional policy for this report and retain the original basket and line values. Review a different policy if the business question changes.
Take the questions into your next brief.
Use these prompts in your own notes. Nothing is submitted from this page.
Unit
Is the decision about orders, lines, units or product amounts?
Components
Which values are directly attached to items?
Policy
How are basket-level adjustments handled?
Overlap
Which category counts can overlap?
Reconciliation
Which basket fixtures prove the totals?
Watch for the wrong conclusion.
- Calling line count unit count.
- Adding overlapping category order counts.
- Copying order revenue onto each product.
- Treating an allocated margin as causal contribution.
Your next action
Build a five-basket fixture that reconciles item amounts and unique orders under a written allocation policy.
Sources & scope
Frameworks, worksheets and hypothetical examples are original editorial synthesis. Linked primary sources support only the stated concepts. Source checking is not an independent audit of effectiveness. No named human author or reviewer is claimed.
- Google Cloud: Work with arrays →Primary technical documentation · Supports: Documents that UNNEST expands array elements into rows. Supports the technical grain change, not any particular commercial allocation rule.