A reconciliation design that’s strong against differences in payment processing data formats: A migration checklist from manual processing

Payment details for incoming/outgoing funds connected from a payment processor may vary in matching accuracy even for the same “transaction” due to differences in the data format. In particular, when the item definitions (ID system, currency, how fees are handled, and the level of detail in statuses) differ slightly, not only do discrepancy alerts increase, but the reproducibility of operational decisions also decreases.

“Form Differences” You Should Always Get Right When Migrating

First, we identify the assumptions that were implicitly in place through the current operations (or through past CSV imports). The same assumptions may not hold for the payment-processing data at the destination. If we run automatic matching while this mismatch remains unresolved, rule additions will only keep increasing over time.

1) Transaction ID system and matching key

The top priority is deciding which combination to use as the matching key. Payment processors may provide multiple fields such as a payment number, transaction ID, the merchant’s order number, references, and more. Here, if you rely too heavily on a single key, reconciliation can break down in situations involving partial refunds or retries.

• ・注文番号のみで突合している場合、決済番号側の有無も確認する
• ・部分返金/相殺（チャージバック等）で「同一キーが複数件になる」ケースを棚卸しする
• ・タイムスタンプの粒度（秒/ミリ秒）やタイムゾーン差も検証する

2) Amount breakdown and sign (deposits/withdrawals, fees, adjustments)

The amount is an area that’s easy to get wrong if you only match and verify by looking at the total. The payment service provider may break out items such as fees, tax, foreign exchange adjustments (when applicable), and offset amounts in separate fields. Meanwhile, the merchant may interpret which breakdowns correspond to which accounts separately, based on the amount received as the deposit statement.

• ・「手数料込/抜き」の前提がどこで掛かっているかを明文化する
• ・マイナス符号の扱い（返金が負、相殺が正など）を実データで確認する
• ・端数処理（四捨五入、切り捨て）のタイミングを確認する

3) Status and the Definition of “Confirmed”

As automation increases, “confirmed judgment” becomes more important. The status of a payment processing service does not always mean the same as a merchant’s payment being confirmed (accounting recognition/shipping confirmation). Immediately after migration, it’s usually easier to reduce total effort by setting confirmation criteria conservatively to reduce the risk of incorrect cancellations.

• ・入金確定のトリガーが「ステータス変更」なのか「明細反映」なのかを整理する
• ・差異アラートの分類（未確定/要確認/確定不一致）を設計する
• ・再計算（過去分の訂正）があるかを確認する

Migration Checklist for Clearing Design

Here is a checklist arranged in order of what’s easiest to get stuck on when migrating from manual operations. Validate the results using real data (including at least past peak days), and record the outcomes in the operations documentation.

1. Prepare multiple match key candidates
   Separate the primary key and fallback keys, such as order number + payment number, and order number + payment timing.
2. Design to use both “Total” and “Breakdown” for the amount
   Not only ensure they match, but also make it possible to track the factors behind differences in fees/taxes, reducing research time.
3. Standardize interpretation of difference alerts
   We align the intended meaning of “needs confirmation,” including acceptable tolerance (rounding), how to handle uncertain cases, and the possibility of corrections.
4. Be sure to include a test for partial refunds and set-off
   We will scenario-test situations where multiple line items apply to the same order, the timing of refunds is out of sync, and order replacements occur.
5. Assume operations that can withstand re-execution (idempotency)
   Ensure that processing the same data produces the same result, and prevent duplicate checks during reprocessing.
6. Tighten conditions step by step
   Immediately after migration, keep anything uncertain as conservatively as possible, then adjust the conditions based on statistics from incorrect write-offs.

Finally: run “difference learning” in the operations logs

Automation doesn’t end with the initial setup. For cases where a discrepancy alert is triggered, we categorize it by cause category (key differences, amount differences, status/finalization differences, rounding differences, etc.) and use that information to drive improvements to the next rules or redesign tolerance thresholds. As this accumulates, discrepancy-alert operations shift from relying on individual judgment in the field to a repeatable, consistent process.

The design considerations related to this are also informed by the following summary.