Marketplace settlement reconciliation — count what matches, show only what doesn't

Order, fee, and refund data arrived per channel, and someone reconciled it by hand every night. One number out of place meant starting over. Closing day meant working past midnight.

What was actually wrong

The data all existed. The problem was that it was scattered across channels in different shapes, and matching it up was entirely a person's job.

How it was solved

• Order and settlement data collected automatically per sales channel, daily
• Fees, refunds, and shipping checked against an explicit rule
• Only the rows that disagree get flagged
• Results written to a ledger, summary posted to Slack

The shift is from "a person checks everything" to "a person checks exceptions." The workflow handles what reconciles; people see only what's flagged.

The rule — this one line is the whole case

Exactly what "checked against a rule" means is the heart of this. The shape is the same whatever your channel.

sale − fees − shipping − refunds  =  the payout WE calculate

diff = the payout the channel REPORTS − the payout we calculate

  diff = 0        → matches. do nothing
  diff > 0        → channel says it owes more. usually fee discounts or promotions
  diff < 0        → channel says it owes less. a person must look at this

Rendered as SQL, that becomes the following — it lives in the workflow's Reconcile Yesterday node.

SELECT channel, order_id, settled_on,
       sale_amount, fee_amount, shipping_amount, refund_amount,
       payout_reported,
       (sale_amount - fee_amount - shipping_amount - refund_amount) AS payout_expected,
       payout_reported
         - (sale_amount - fee_amount - shipping_amount - refund_amount) AS diff
FROM settlements
WHERE settled_on = (CURRENT_DATE - INTERVAL '1 day')::date
ORDER BY abs(payout_reported
         - (sale_amount - fee_amount - shipping_amount - refund_amount)) DESC;

Choosing the tolerance

You have to decide whether a one-unit difference counts. That's TOLERANCE_KRW at the top of the Flag Mismatches Only node.

• `0` — flags any difference at all. Precise, but channels that round will generate dozens of rows daily.
• `1`–`2` (recommended starting point) — absorbs rounding error and catches everything else. Right for most sellers.
• `100` or more — only when alerts are overwhelming. Raising this is identical to increasing the amount of money you never look at. Find out why the discrepancies are large before raising it. A higher threshold hides the symptom rather than fixing the cause.

What you need

• n8n — self-hosted or Cloud. Core nodes only.
• One Postgres database — a single settlement ledger table. Adding channels later just adds rows.
• A Postgres credential — n8n → Credentials → Postgres. Docker n8n with a host database needs host.docker.internal.
• A Slack Bot User OAuth Token — scopes chat:write and channels:read.
• Some way to reach your channel's settlement data — its API where one exists, otherwise a downloaded settlement spreadsheet. Only the first node in this workflow knows which channel you're on.

Step 1 — the settlement ledger

CREATE TABLE IF NOT EXISTS settlements (
  channel         text    NOT NULL,   -- sales channel name
  order_id        text    NOT NULL,
  settled_on      date    NOT NULL,   -- settlement date
  sale_amount     numeric NOT NULL DEFAULT 0,
  fee_amount      numeric NOT NULL DEFAULT 0,
  shipping_amount numeric NOT NULL DEFAULT 0,
  refund_amount   numeric NOT NULL DEFAULT 0,
  payout_reported numeric NOT NULL DEFAULT 0,  -- what the channel says it will pay
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (channel, order_id, settled_on)
);

Changing channel — only the first node changes

This workflow is deliberately channel-agnostic. Replace the Fetch Channel Settlement node with your channel's API and map its response to these eight fields; everything downstream runs unchanged.

channel · order_id · settled_on
sale_amount · fee_amount · shipping_amount · refund_amount · payout_reported

• Channels with a settlement API — wire it straight in. Authentication varies enormously between channels, from a plain API key to request signing and caller-IP registration, so confirm the integration difficulty before you commit to a timeline.
• Channels with no API, or no access — download the settlement spreadsheet, paste it into a sheet, and swap the first node for a sheet read. Collection stays manual while reconciliation becomes automatic, which already removes most of the hours.
• Multiple channels — duplicate the workflow, change only the first node, and set a different channel value. They accumulate in one table, so reconciliation and reporting merge automatically.

Setup (25 minutes)

1. Create the table — run the Step 1 SQL.
2. Register the Postgres credential — n8n → Credentials → Postgres → Test.
3. Register the Slack credential — create an app at api.slack.com/apps → add chat:write and channels:read → Install → paste the xoxb- token. Then /invite @your-bot in the channel.
4. Import the workflow — n8n → Workflows → ... → Import from File.
5. Fill in the collection node — put your channel's settlement API URL and auth into Fetch Channel Settlement, mapping its response to the eight fields above. No API? Replace it with a sheet-read node.
6. Set the tolerance — pick TOLERANCE_KRW in Flag Mismatches Only. Start at 1.
7. Set channel and credentials — change the channel name in Post to #settlement and attach credentials to all three nodes.
8. Verify against hand calculation — do not skip this. Take three of yesterday's settlement rows, compute sale − fees − shipping − refunds on a calculator, and compare against payout_expected and diff in the query output. A mismatch here means your field mapping is wrong. Mapping errors are the most dangerous failure because they produce quietly incorrect numbers.
9. Activate — it reconciles the previous day every morning at 07:00.

What happens in edge cases

• A day where everything matches — no per-row listing, just "N rows all matched" and the payout total. Confirmation that it ran, with nothing to read.
• A day with no settlements at all — nothing is sent. Channels commonly don't settle on weekends and holidays, so this is normal. But silence on a day that should have settlements is itself a signal — that's the "never ran" problem our workflow-watchdog case handles.
• More than 20 mismatches — Slack gets the 20 largest by discrepancy plus an "…and N more" line, so the message doesn't get truncated into uselessness. Query the ledger for the full set.
• A channel revising settlement data later — the same key is overwritten on re-fetch. If you need the pre-revision values, add a history table; the base structure doesn't retain them.
• Partial refunds — put the refunded amount in refund_amount and the rule applies as-is. Some channels push refunds to the following settlement date, which produces a large same-day diff. The same order appearing several days running suggests a carried-over settlement.
• A channel changing its API schema — the collection node either fails or returns empty fields. Amounts arriving as 0 produce a large diff and get flagged, so it's visible. Failing loudly beats failing quietly — that's the intent.

I don't reconcile at 2am any more. The workflow does.— Store owner