Multi-Store, Multi-Platform Reconciliation: Designing a Unified Statement Model

Why a Unified Statement Model Is Necessary

Fifteen platforms means fifteen statement formats: different column names, different fee terminology, different settlement granularity (per order, per line, per cycle). If each platform gets its own reconciliation logic, rule count explodes with platforms × stores, and cross-platform analysis becomes impossible. The goal of a unified statement model: statements from any platform converge into one structure, and reconciliation logic is written once for all.

The Three Layers

Raw layer. Preserve platform statements exactly as delivered — every row, every column, including headers, totals and blank lines. This layer is legal evidence: any dispute or audit must be able to reproduce what the platform actually sent. In practice, each raw row is attached to its fact row as a JSON snapshot, with the source file archived.

Standard fact layer. One fact table per platform × statement type, with unified fields: platform and store codes referencing master data; the business order number, where split sub-orders still point to the parent; an accounting-item code; a direction flag (income +1 / expense −1); amounts at Decimal(20,4) and unit prices at Decimal(20,6); business timestamp and settlement timestamp stored separately; and the raw JSON snapshot.

Reconciliation layer. Results never mutate the fact layer. Plans, match details (status, variance amount, variance reason) and allocation details live independently. A minimal bridge table — about five fields: both primary keys, match method, match timestamp — connects the layers, so any reconciliation result traces back to the original statement row.

The Accounting-Item Dictionary

Every platform fee item maps into a unified dictionary: goods payment; platform service fee (Douyin's technical service fee, Pinduoduo's base technical service fee); commission (JD's operations support fee, Amazon's referral fee, Douyin influencer commissions); advertising (Jingzhuntong, Zhitongche, Amazon Ads); logistics and warehousing (including FBA fulfillment); shipping insurance; refund deductions; and other adjustments. Reserve extension slots: when a new platform brings a new fee, first try to fold it into an existing item, and only add a new one with a documented rationale when truly necessary.

Implementation Essentials

Store master data comes first — platform store codes must map to ERP organizations or departments before statements have somewhere to land. Parsing rules are scripted: the raw-to-fact transformation for each platform is a testable, versioned script, so a platform layout change edits a script, not the model. Amounts are Decimal end to end — never float — with (20,4) for amounts and (20,6) for unit prices as the floor. The reconciliation layer is append-only: discrepancy handling adds states and records instead of updating history, keeping audits replayable. Once the unified model stands, multi-platform reconciliation becomes one ruleset plus N parsing scripts — the only path to scale.