Define the smallest useful record
Use shop, currency, order ID, SKU where relevant, transaction or event identity, statement ID and event date. One order can contain several items and later adjustments, so order ID alone is not always a unique row key.
Keep original amounts and field names from the export. Add working columns for the expected base, applicable rate, expected fee, actual fee, reversal and variance. This lets another operator reproduce the calculation without relying on hidden spreadsheet edits.
| Column group | Examples | Purpose |
|---|---|---|
| Identity | Shop, order, SKU, event, statement | Avoid duplicate or ambiguous rows |
| Timing | Order, delivery, statement, refund dates | Apply the appropriate policy period |
| Rate evidence | Category, incentive, rate reference | Explain why this percentage applies |
| Base evidence | Sales, shipping, coupons, refunds | Rebuild the chargeable amount |
| Control | Expected, actual, reversal, variance | Expose differences without overwriting facts |
Illustrative mismatch: buyer shipping was omitted
Assume €50 item sales, €4 customer-paid shipping, no coupons or refunds and a 9% platform fee. A model using only the item price expects €4.50; the actual fee on €54 is €4.86. The €0.36 difference is exactly 9% of the omitted shipping amount.
That establishes the cause only for this invented example. In a real record, inspect the fee base and actual charge before naming the cause. A similar-sized difference could arise from another field or an eligible rate.
Illustrative variance = €4.86 actual − €4.50 expected = €0.36 = €4 omitted shipping × 9%Attach refunds and adjustments without losing the original sale
A later €10 item refund in the same simple example creates a €0.90 platform commission reversal. Keep the original €4.86 charge and the €0.90 reversal; the cumulative platform fee becomes €3.96. Do not erase the original row or create another sale when the refund arrives.
For mixed categories, use the item's actual rate. For platform coupons, retain the funding and relevant base fields because the public fee documents describe discount terms differently. Signed adjustment rows should remain signed; not every adjustment is a cost.
Classify exceptions before escalating them
Separate a missing row from a rate mismatch, a base mismatch, timing, currency conversion, duplicate import and rounding. Start with material amounts and recurring patterns. Rounding should be a demonstrated residual, not the first explanation for every discrepancy.
An escalation packet should contain the affected IDs, raw rows, expected equation, policy basis, actual result and exact difference. Avoid including unrelated customer data when order and fee identifiers are sufficient for the issue.
- Check completeness and duplicate keys.
- Confirm the rate and its effective period.
- Rebuild the chargeable base.
- Match original charges and later reversals.
- Classify the remaining variance.
- Attach only the relevant evidence and resolve or retain the exception.
Which controls deserve to run every period?
Repeated missing events and duplicated imports can matter more than a one-cent rate residual. Track completeness, unmatched order events, changed effective rates and unexplained variance totals. Preserve enough history to see whether an exception is new or recurring.
Automate checks whose definitions are stable, while leaving account-policy ambiguities visible for review. A rules engine should not force an uncertain order into a known category merely to produce a clean dashboard.