Why an amazon sage integration should follow the settlement, not the order

The usual starting point for an amazon sage integration is the order: pull each sale as it happens, raise a sales invoice in Sage 200, and hope the ledger ends up in the right place. It rarely does. Amazon does not pay you per order. It pays you per settlement period, net of commission, fulfilment fees, storage, advertising, refunds, chargebacks, promotional rebates, reserves and any number of adjustments raised weeks after the original transaction. An order-level feed produces a debtor balance that never agrees to cash, and a finance team that spends the first week of every month working out why.

The alternative is to treat the Amazon disbursement as the unit of accounting. Every fourteen days or so, Amazon closes a settlement period and produces a report that balances: the sum of every line in that report equals the amount transferred to your bank account. If your feed reproduces that report faithfully in Sage 200, bank reconciliation becomes a single matched line rather than an investigation. This is the design decision that determines whether the project succeeds, and it belongs in discovery, not in build.

That does not mean order detail is worthless. Many businesses still want per-SKU revenue analysis, and a settlement feed can carry SKU and quantity through to nominal analysis codes or to a separate reporting table. But the ledger postings themselves should be driven by the settlement, because the settlement is the only artefact Amazon publishes that is guaranteed to tie to money received.

What is actually inside an Amazon settlement report

Amazon Seller exposes settlement data through the Selling Partner API. The flat-file settlement report is generated by Amazon on its own schedule rather than requested on demand, so the integration lists available reports of that type, identifies any it has not yet processed, downloads the document and parses it. There is also a Finances API that returns financial events in near real time, grouped by financial event group. It is useful for early visibility, but the groups only close when the disbursement completes, and the totals move until they do. For posting to the ledger, take the settlement report; for management information between settlements, the Finances API is reasonable.

The flat file is one row per amount, not one row per order. A single order can produce a principal row, a shipping row, a tax row, a commission row and one or more fulfilment fee rows, each with the same order identifier but different amount-type and amount-description values. Refunds appear as their own transaction type with negative principal and, usually, a partial commission reversal. Service fees, subscription charges, storage and advertising arrive as lines with no order identifier at all. The first row of the file is a header carrying the settlement identifier, the period start and end, the deposit date, the total amount and the currency, with the transaction type left blank.

Two categories catch people out. The first is reserve movement: Amazon holds back funds against expected refunds and releases them in a later period, and these appear as adjustment lines that have nothing to do with trading. The second is the settlement that closes with a zero or negative total and no deposit, where the balance carries forward. Both are normal, and both must be modelled explicitly, or your feed will post a journal that does not balance to anything visible in the bank.

Before writing a line of code, extract three or four historical settlements and produce a distinct list of every transaction type, amount type and amount description combination the account has actually generated. That list is the specification for the nominal mapping. It is also the point at which the scope becomes fixed and estimable, rather than open-ended.

  • Header row — Settlement identifier, period dates, deposit date, total amount and currency. The settlement identifier is the natural idempotency key for the whole posting.
  • Order and refund rows — Principal, shipping, gift wrap, promotional rebates and the tax elements of each, plus the associated commission and fulfilment fees as negative amounts.
  • Service fee and other-transaction rows — Storage, advertising, subscription, removal orders, reimbursements, disputes and reserve movements. No order identifier, so they need mapping by description alone.
  • Reserve adjustments — Current and previous reserve balances, which explain the gap between trading activity and the amount actually paid out.

Mapping settlement lines to Sage 200 nominal codes

The structure that works in Sage 200 is a clearing account. Create an Amazon clearing account in the Cash Book, post everything from the settlement into it, then record the disbursement as a bank transfer from clearing to the trading bank account. After a correctly processed settlement, the clearing balance returns to zero, or to the carried-forward reserve if one exists. Anything left over is an unmapped line, and that is exactly the signal you want finance to see.

For the postings themselves, resist the temptation to do everything by nominal journal. Taxable sales normally need to reach the VAT return through the sales ledger, so summarise the order rows into one sales invoice per settlement per marketplace, against a dedicated marketplace customer account, with one line per revenue nominal and tax code. Refund rows become a corresponding credit note. Amazon fees are a cost, and are cleanest as a purchase invoice against an Amazon supplier account so that the VAT treatment is explicit and the supplier statement can be checked. Reserve movements and rounding differences go through a nominal journal.

The Sage 200 API surface is not uniform. Sage 200 Standard and Sage 200 Professional expose different endpoints, and some on-premise Professional sites still post nominal journals through the SDK or a supported import route rather than the REST API. Establish during discovery precisely which deployment, version and authentication model you are working with, which endpoints are available, and whether the customer, supplier and nominal accounts you intend to use already exist. This single question has more effect on the estimate than anything on the Amazon side.

Keep the mapping in configuration, not in code. A simple table keyed on transaction type, amount type and amount description, returning a nominal code, cost centre, department and tax code, allows finance to add a mapping when Amazon introduces a new fee description without a change request. Unmapped combinations should never post to a suspense code silently. They should stop the settlement, raise an alert and wait.

VAT, currency and cut-off: the three things that go wrong

VAT on marketplace sales is not a mapping exercise. Where the marketplace is the deemed supplier — imported consignments below the low-value threshold sold to UK consumers, and goods already in the UK sold by overseas sellers — the seller's supply is not a standard-rated UK sale, and posting it as one will overstate output tax. Sales into the EU, whether under the Import One Stop Shop or through local registrations, need separating from domestic sales at the point of posting, which means the marketplace identifier and the ship-to country in the settlement data have to drive the tax code, not a single default. Have the business's accountant sign off the tax code matrix in writing before build, and treat it as part of the fixed scope.

The same caution applies to fees. Which Amazon entity invoices your seller fees, and whether those fees carry UK VAT or fall under the reverse charge, has changed more than once. Do not infer it from an old integration or from a forum post. Take a current Amazon VAT invoice, confirm the treatment, and encode that, with a note in the handover documentation recording the date the position was confirmed.

Currency is the second trap. A settlement is denominated in the marketplace currency, but if disbursement runs through Amazon's own conversion service the amount landing in your bank is in sterling at a rate Amazon chose. The settlement will balance in its own currency and the bank line will not match it. The workable pattern is to post the settlement in its native currency against a currency-denominated clearing account, then post the conversion as a transfer with the difference going to an exchange differences nominal. Sage 200 handles this properly provided the clearing account is set up for the right currency from the outset.

Cut-off is the third. Settlement periods cross month ends with complete indifference to your reporting calendar. There are two defensible policies: post the whole settlement on its deposit date and accept the timing difference, or split the settlement by posted date into two journals and accrue the unpaid portion. The first is simpler and suits businesses where marketplace revenue is a modest share of turnover. The second is more accurate and more work. Pick one, document it, and apply it consistently — the failure mode is a feed that quietly does one thing while the management accounts assume the other.

Building the feed so it survives contact with production

Idempotency is straightforward here because Amazon gives you a natural key. Record every settlement identifier that has been processed, together with the Sage 200 document numbers it produced, and refuse to process the same identifier twice. If a run fails halfway, the recovery path should be to roll back or void the partial documents and reprocess the whole settlement, not to guess which lines made it through. Partial postings are the most expensive thing to unpick in a ledger, and the cheapest thing to design out.

Build in a hard balance check before anything reaches Sage 200. Sum every line in the parsed report and compare it to the total amount in the header. If the difference is not zero, stop. Then, after posting, compare the movement on the clearing account to the same figure. Two checks, both automatic, both blocking. A feed that posts a settlement it could not reconcile is worse than no feed at all, because it produces plausible numbers that are wrong.

Acceptance testing should replay at least three historical settlements into a Sage 200 test company, including one that straddles a month end and one containing refunds and a reserve movement. The acceptance criterion is simple to state and hard to argue with: the clearing account returns to the expected balance, the VAT return figures agree to a manually prepared schedule, and the bank deposit matches a single transfer line. If the business also sells through eBay, note that its settlement model differs and will need its own mapping — the clearing account pattern transfers, the file format and fee vocabulary do not.

Finally, plan for the feed to be maintained by someone else. Hand over the mapping table, the tax code matrix with its sign-off date, the list of settlement identifiers processed, the alerting configuration and a short runbook covering the two most likely incidents: an unmapped fee description, and a settlement that fails its balance check. A settlement feed that runs unattended for a year is the goal, but the year in which nobody has to open the code is only achieved by writing down how it works.

Key points

  • Drive ledger postings from the Amazon settlement report rather than individual orders, because the settlement is the only artefact that is guaranteed to reconcile to the bank deposit.
  • Use a currency-denominated clearing account in Sage 200 so that every settlement nets to zero, and treat any residual balance as a blocking error rather than a rounding curiosity.
  • Agree the VAT treatment for deemed-supplier sales, overseas marketplaces and Amazon's own fees in writing before build, and record the date that position was confirmed in the handover documentation.