Skip to main content
The four reports form a chain. Each stage is joined to the next by an identifier that both files carry.

​
Join keys

The final two joins are on event_id in both files. event_id is globally unique and never reused, so no composite key is needed and no type or period qualifier is required.

​
Reconciling settlement

Every row in a settlement report matches exactly one row in a transactions or disputes report. The relationship is one-to-one in both directions: no settlement row exists without a corresponding processed event, and within a settled period no processed event is missing from settlement.
1

Collect the processed events for the period

Take the transactions and disputes reports whose periods overlap the settlement period, and union their event_id values.
2

Join on event_id

Left join the settlement file’s event_id against that set. Expect a complete match on both sides.
3

Check the amounts

Sum event_amount for SALE rows in the settlement file and compare with the transactions report. They tie exactly. Remember that refunds are signed negative in settlement and unsigned in transactions.
4

Foot each row

Confirm the five component columns sum to payout_amount. Any row failing this indicates a corrupted file — re-download before investigating further.

​
Period alignment

A settlement report covers events whose event_effective_at falls within the settlement period. event_effective_at equals the date of the underlying event, so a transaction processed on 14 August settles in the period containing 14 August.
Settlement periods follow your payout schedule, which may not be calendar months. Where your schedule is more frequent than monthly, one calendar month of transactions spans multiple settlement reports.

​
Reconciling creation against processing

Unlike settlement, this relationship is not one-to-one and is not expected to be.
  • A created payment may never be processed, so it appears in the creation report and nowhere else.
  • A created payment may be processed in a later period, so the two rows sit in reports covering different periods.
  • One payment may produce several transaction rows (e.g., a sale and one or more refunds).

​
Reconciling disputes against payments

Dispute events are frequently raised months after the payment was processed. The disputes report for August will contain events against payments created in June, March, or earlier. Never expect the disputes report for a period to join only to payments from the same period. Join on payment_id across your full payment history, and where a dispute references a payment you do not hold, extend the range of creation reports you have ingested rather than treating it as an orphan.
The reverse direction also fails by design: a payment disputed in September will not appear in any August dispute report, even though the payment itself was processed in August. Dispute coverage is bounded by when the event was recorded, not when the payment was processed.

​
Worked example

Tracing payment PAYM_123 across all four reports.
The payment was created and captured on 1 August, flagged by an Ethoca alert on 3 August, and refunded on 4 August in response to that alert. All three events settle in the same period.

​
Common pitfalls

A refund’s payment_id is the original payment, not the refund. Two refunds against one payment share a payment_id. Join on event_id to keep rows distinct.
One payment can attract multiple fraud events. Grouping by payment_id collapses them and understates dispute fees. Aggregate on event_id.
The date in a settlement filename is the payout date. The events inside it fall in the preceding settlement period. Use event_effective_at but note that in the case of settlement being delayed (e.g. while under Risk review), the pay-out may include events from a previous period.
Fees are already negative. Subtracting them inverts the sign and doubles the error. Add all five component columns.
Created payments may never be processed, or may process in a later period. Age unmatched rows across several periods before treating them as a break.