Modelling

The four traps in modelling commerce data for reporting — the grain of a row, refunds and cancellations, currency, and tax — and how to check what the platform actually gives you.

Most wrong numbers are not pipeline bugs. They are modelling decisions somebody made implicitly and nobody wrote down. Four decisions are worth making explicitly.

Which fields exist, what they are called and what they mean is defined per endpoint and per tenant. Check the schema in the API Explorer before you rely on any field named on this page. What follows is where to look and what to decide, not a field list.

Trap 1: the grain

The grain of a table is what one row means. Get it wrong and every aggregate is wrong in a way that looks plausible.

The commerce version of this is order versus line item:

Grain	One row is	Good for
Order	One order	Order counts, average order value, orders per customer
Line item	One product on one order	Revenue by product, units sold, category mix

You need both, and they must be separate tables. The failure everyone hits once: joining orders to line items and then summing the order total, which multiplies each order's total by its number of lines. Sum order-grain measures from the order table, line-grain measures from the line table, and never mix the two in one SUM.

State the grain in the table's name or its documentation. orders and order_lines beats sales twice.

Trap 2: gross, net, and what a refund does

Three questions, and the platform's schema answers the first two:

Is a total gross or net? Check the field's definition rather than its name. "Total" means different things in different systems, and a report that quietly mixes both is off by the tax rate.
What does a discount do to it? Whether a discount is a separate line, a reduction on the line, or both, decides whether summing lines equals the order total.
How is a refund or a cancellation represented? This is the one that is a modelling decision as much as a data one.

For refunds, decide and document:

Does revenue for a refunded order disappear, or does a negative amount appear later? If refunds are separate records, your revenue series has to subtract them, and the subtraction lands in the period of the refund — not the period of the order. Both are defensible; a report that does neither consistently is not.
Do you report gross revenue and refunds as two measures, or net revenue as one? Two measures is almost always better: somebody will ask what the refund rate is.
Does a cancelled order count as an order? For "orders per day" it usually should not; for "conversion" it usually should.

And check status: a status in an event payload is the status at that moment. An order paid after the creation event does not rewrite that event. For anything status-dependent, the API is the answer and the event is the trigger — see Pull versus push.

Trap 3: currency

Two properties to internalise:

Amounts are integers in the currency's minor unit. A total of 14990 with currency EUR is €149.90. Store the integer and the currency code together, in the same row, and divide only at the point of display. A float column for money is a rounding error waiting for a quarterly report.
A multi-currency tenant cannot be summed. SUM(total) across EUR and CHF rows is a meaningless number that no error message will warn you about. Either report per currency, or convert into a reporting currency and store the rate you used and the date you used it alongside the converted value.

If you convert, keep the original amount and currency too. Somebody will ask what the order was actually worth, and "we converted it in March at a rate we no longer have" is not an answer.

Trap 4: tax

Tax is where a reporting store most often diverges from what finance believes.

Find out from the schema, per entity: whether the amounts you are storing include tax, whether tax is broken out as its own amount, and whether a rate is recorded on the row or has to be derived. Then pick one convention for your store and apply it everywhere, with the field names saying which — total_gross and total_net are worth the extra characters over total.

Two things not to do:

Do not derive tax by multiplying by a rate you know. Rounding is per line in most systems, and per-line rounding does not reconstruct from a total.
Do not assume one rate per order. Mixed baskets and cross-border orders both break that.

If a figure will be reconciled against an accounting system, agree the convention with whoever owns that system before you build the table.

Two dimensions people forget

Market. A tenant can be scoped by market, and a total that silently mixes markets is as wrong as one that mixes currencies. Carry the market on the row if the tenant uses them — see Commerce.
Tenant. If your store serves several tenants, the tenant is part of every key and every filter, not a column you add later. A row that lost its tenant cannot be fixed.

Write it down

Whatever you decide, put it next to the table:

Data contract

orders             one row per order.  total_gross in minor units + currency.
                   cancelled orders excluded. timestamp = envelope time (UTC).
order_lines        one row per product per order. sum(line_net) may differ from
                   order net by rounding — do not use it as a check.
refunds            one row per refund. subtracted in the refund's own period.

Four lines like that prevent more wrong reports than any amount of pipeline engineering.

Where to go next

Backfill — getting the rows in the first place.
Incremental sync — restatement, and what a late arrival does to a closed period.
Declaring a view — the same grain question, when you are exposing your app's own data to the tenant.
API Explorer — the authoritative schema for every field this page told you to check.

Was this page helpful?