Source: platform, store, account
Identity: platform order, business order, line
State: paid, canceled, refunded, reshipped
Amount: payment, discount, refund
Reconcile: quantity, revenue, settlement
Deduplication is not deleting equal rows
One purchase can appear in platform, ERP, warehouse, and payment systems. Splits, merges, reshipments, and after-sales records add more identities.
Define whether the metric counts customer purchases, platform transactions, or item lines, then model stable identity at each level.
Build source and cross-source keys
Within a source, combine platform, store, and order ID; lines need a suborder or line ID. Use mapping tables across platform, ERP, payment, and logistics.
Phone, amount, and timestamp can flag possible duplicates but are unsafe unique keys because they change and collide.
Preserve state change
Model payment, shipment, cancellation, partial refund, full refund, and reshipment as events or versioned states. Final-state overwrite loses timing and makes history hard to explain.
Define included states per metric and distinguish order, payment, refund, and settlement time.
Reconcile count, money, and inventory
Compare order and line counts, then payment, discount, refund, fee, and shipped quantity. Put unresolved differences in an owned queue.
Deduplication also affects stock, customers, and repeat purchase, not only revenue.
Accept with boundary samples
Test split, merge, partial refund, duplicate sync, reshipment, same ID across stores, and missing mappings.
BI0.AI can be evaluated as an integrated collection-to-analysis path. Connector, incremental sync, and mapping behavior require source-specific confirmation.
Public references
Take the next step with BI0.AI
Talk through a real business scenario and see how governed AI BI can fit your team.
Explore BI0.AI