If your Amazon settlement does not match your own export, the missing money is usually not missing. It is either an export that truncated without telling you, or a join that dropped your fee lines. This page gives you the check that tells those apart, the exact SQL that reconciles a settlement to the cent, the rules that make it tie, and the validation that proves it tied for the right reasons. It also covers how Amazon’s DD+7 reserve policy affects the dates on every line, and why it pushed posting lag from about two days to about ten.
When two systems disagree, find out which one is wrong
Two sources of truth for the same settlement, and they do not match. Before you can fix anything, you have to establish which side is actually wrong, and the intuitive answer is usually the wrong one.
Here is a case worth walking through, because all three of the common failure modes showed up in it at once. An advisor auditing a seller’s account found roughly $150,000 of orders apparently missing from the seller’s transaction data, measured against an export the seller had pulled from Amazon by hand. That is the kind of gap that stops a month-end close.
It turned out the export was the unreliable side, and the comparison query was broken on top of it. Three separate things were going on:
1. The transaction data was complete. The settlement totaled $163,844.49, matching Amazon’s own closed-settlement control total to the cent. Every line was accounted for.
2. The hand-pulled export was truncating. Across a single settlement window it carried 1,126 rows on day 1 and 71 rows by day 15, decaying steadily, while the underlying data for the same window ran a flat 600 to 1,000 rows per day. That decay curve is not a sales pattern. It is the shape of an export quietly hitting a limit.
3. The query comparing them used an INNER JOIN, which silently dropped fee lines and made the gap look worse than it was. On a different account we reproduced that exact pattern and it overstated the payout by $5,950.09, because the dropped lines were net negative fees.
Any one of those alone is enough to make a reconciliation fail. The rest of this page is how you tell them apart on your own account: first check whether your export is trustworthy, then run a query that provably ties, then confirm it tied for the right reasons.
First, check whether your export is the problem
Do this before you write any SQL. If your export is truncating, every downstream comparison is measuring the wrong thing, and no amount of query correctness will save it.
Hand-pulled reports have row limits, and they do not always announce them. A truncated file looks exactly like a complete file except that rows are missing from it, which is precisely what a reconciliation is designed to notice and precisely what you will misattribute to your data source.
The check is row density over time. Count rows per day in your export across the settlement window, and compare that shape against the same count from your source data:
SELECT DATE(transaction_ts) AS posted_day,
COUNT(*) AS lines
FROM sl_transactions_pivot
WHERE venue_id = ?
AND venue_settlement_id = ?
GROUP BY DATE(transaction_ts)
ORDER BY posted_day;Run the equivalent count on your export, day by day, and put the two side by side.
What you are looking for is the shape, not the totals. Real trading volume moves with weekends, promotions, and seasonality. It does not decay monotonically across an arbitrary two-week window. If your export starts strong and thins out steadily toward the end of the period while your source data stays flat, the export is the truncated side, and the “missing” money was never missing.
In the case above the export fell from 1,126 rows to 71 across fifteen days against flat source data, which is about as clear as that signature gets. Your own numbers will differ; the decay shape is the tell, not the specific figures. Those come from one support case rather than a broad measurement, so treat them as an illustration of the pattern.
If your export holds a plausible shape, it is probably fine, and the problem is in the join. That is the rest of this page.
Then check your join: why INNER silently breaks a reconciliation
With the export ruled out, the next suspect is the query. The instinct when you want order dates next to your fees is to join the transaction lines to your order table. That instinct is right. The default join is wrong.
Not every line in a settlement has a matching row in your order table. These event types are the usual ones that do not:
| Event type | What it is |
| ServiceFee | Subscription, storage, and other account-level fees |
| Adjustment | Corrections Amazon applies to your account |
| FBAInventoryReimbursement | Reimbursements for lost or damaged inventory |
| SellerPoweredCoupon | Coupon program charges |
An INNER JOIN requires a match on both sides, so every one of those lines disappears from your result. Your row count drops, your total moves, and nothing errors. On the account we tested, the unmatched lines broke down like this:
| Event type | Unmatched lines | Payout on those lines |
| ServiceFee | 1,149 | -$12,551.70 |
| FBAInventoryReimbursement | 293 | -$441.59 |
| Adjustment | 59 | -$65.55 |
| Retrocharge | 28 | $0.00 |
Those fees are real money leaving your account. Drop them and your reconciliation is not merely incomplete, it is biased in the direction that flatters you.
Worth being precise about the mechanism, because the obvious explanation is not the main one. It is tempting to assume these lines drop because they carry no order id. Some do not, but that is the smaller effect. On one account we measured 1,543 lines that failed to match against only 296 that were missing an order id outright. The dominant cause is simply that no corresponding row exists in the order table, whether because the order predates your history, was never synced, or uses a different SKU convention. Either way an INNER JOIN discards it.
We ran the INNER JOIN version against six accounts to see how far off it lands. It never once returned the right answer:
| Account | Lines dropped | Payout error |
| Account B | 232 | +$5,950.09 |
| Account C | 578 | +$6,547.68 |
| Account E | 9,991 | +$148,790.77 |
| Account D | 183,080 | -$1,068,839.27 |
| Account F | 5,188 | -$84,434.92 |
The direction is not predictable, which is what makes this dangerous. On three of these accounts the INNER JOIN reported a payout higher than the money that actually arrived, because the lines it dropped were net negative fees. A seller reconciling that way concludes they were paid more than they were, and the error scales with the account: on one, the overstatement was $148,790.77 on a single settlement.
The query that ties
This is the full reconciliation query, verified to tie exactly on six independent Seller Labs accounts, across settlements ranging from 5,219 to 197,414 lines. It returns every line in a settlement, with the true order date attached where one exists.
SELECT
p.transaction_ts AS posted_date,
d.order_date AS order_date,
p.event_type,
p.venue_order_id,
p.listing_sku,
p.product_revenue,
p.selling_fees,
p.fba_fees,
p.other_transaction_fees,
p.total_payout
FROM sl_transactions_pivot p
LEFT JOIN (
SELECT order_id, listing_sku, MIN(created_ts) AS order_date
FROM order_items
WHERE venue_id = ?
AND created_ts >= ?
GROUP BY order_id, listing_sku
) d ON d.order_id = CONCAT(p.venue_id, '-', p.venue_order_id)
AND d.listing_sku = p.listing_sku
WHERE p.venue_id = ?
AND p.venue_settlement_id = ?;Three rules make it tie, and a fourth tells you whether it tied for the right reasons. Each one corresponds to a failure we measured in production, not a theoretical concern.
Rule 1: LEFT JOIN, never INNER
Covered above. The LEFT JOIN keeps every settlement line and leaves order_date NULL wherever no matching order row was found.
A NULL order date is usually correct output rather than missing data. A ServiceFee for storage has no order date because it did not come from an order. A query that hides those lines to avoid the NULLs is a query that no longer ties to your deposit.
But do not assume every NULL is a fee line. On one account we found genuine Order-event lines with a NULL order date, because the matching order row was simply absent from the order table. That is a data-coverage gap rather than a fee, and it is worth investigating instead of explaining away. If you see NULLs on Order event types, treat that as a signal, which is exactly what the match-rate check below is for.
Rule 2: Pre-aggregate the order table
Notice that the join target is a subquery, not the raw order_items table. That is deliberate.
A single (order_id, listing_sku) pair can have more than one row in order_items. Join the raw table and those pairs fan out: one transaction line matches two order rows and gets counted twice. Your row count goes up, your payout total inflates, and again nothing errors.
Measured across the accounts we tested:
| Account | Correct rows | Rows after fan-out | Payout inflation |
| Account A | 10,941 | 10,952 | +$187.59 |
| Account B | 15,656 | 15,674 | +$887.31 |
| Account C | 20,171 | 20,239 | +$997.35 |
| Account D | 197,414 | 197,446 | +$282.08 |
| Account E | 26,292 | 27,830 | +$34,535.19 |
Note the last row. A tenant with heavy multi-row order data fanned out by 1,538 lines and inflated the payout by $34,535.19 on a single settlement. Eleven duplicated lines out of eleven thousand cost $187.59 on another. The size of the error is a property of your data, not something you can eyeball. Collapsing the order table to one row per (order_id, listing_sku) with MIN(created_ts) removes the problem entirely.
On one account the fan-out was zero, because that tenant had no duplicate (order_id, listing_sku) pairs when we measured it. Do not take that as license to skip the pre-aggregation: it is a fact about one account’s data at one moment, not a guarantee about yours.
Rule 3: Bound the subquery by date
The created_ts >= ? filter inside the subquery is not optional. Without it, the subquery aggregates your entire order history, and on a large account that scan runs past a 30 second query timeout and returns nothing at all.
Set the bound comfortably before the start of the settlement window you are reconciling. A settlement can carry refunds and adjustments against orders placed months earlier, so do not bound it to the settlement window itself.
There is a related performance trap in the join condition. Join on CONCAT(p.venue_id, ‘-‘, p.venue_order_id) against order_items.order_id, which is the indexed composite key. Joining on venue_order_id directly is unindexed and will also time out.
Prove it tied: the validation query
Do not take the result on faith. Every one of the failures above is silent, so you need an independent check that catches all of them.
Run this against the same settlement, with no join at all:
SELECT COUNT(*) AS line_count,
ROUND(SUM(total_payout), 2) AS settlement_payout
FROM sl_transactions_pivot
WHERE venue_id = ?
AND venue_settlement_id = ?;Both numbers must match your joined result exactly. Same row count, same payout to the cent. This compares your joined query against the settlement lines as stored, so it isolates whether the join changed anything.
Row count came out lower? You used an INNER JOIN and dropped fee lines. Rule 1.
Row count came out higher? Your order table fanned out. Rule 2.
Query never returned? Bound the subquery. Rule 3.
Rule 4: the tie is necessary, not sufficient, so check your match rate
Here is the part most reconciliation guides leave out, and it matters more than the tie itself.
A LEFT JOIN preserves every row on the left side by construction. That means the row count and payout total come entirely from the settlement table and cannot change based on whether the join found anything. If the order side matches zero rows, your query still ties perfectly. The tie proves you did not lose or duplicate money. It does not prove a single order date is correct.
So run this fourth check, which is the one that actually tests the join:
SELECT COUNT(*) AS total_lines,
SUM(CASE WHEN d.order_id IS NOT NULL THEN 1 ELSE 0 END) AS matched_lines
FROM sl_transactions_pivot p
LEFT JOIN (
SELECT order_id, listing_sku, MIN(created_ts) AS order_date
FROM order_items
WHERE venue_id = ? AND created_ts >= ?
GROUP BY order_id, listing_sku
) d ON d.order_id = CONCAT(p.venue_id, '-', p.venue_order_id)
AND d.listing_sku = p.listing_sku
WHERE p.venue_id = ? AND p.venue_settlement_id = ?;We ran this across several accounts and the match rate varies enormously:
| Account | Settlement lines | Matched to an order | Match rate |
| Account C | 20,171 | 19,593 | 97% |
| Account E | 26,292 | 16,301 | 62% |
| Account D | 197,414 | 27,486 | 14% |
| Account F | 5,219 | 31 | 0.6% |
All four tied to the cent. Only the first is giving you trustworthy order dates.
What to do with a low match rate. Some shortfall is expected and correct, because fee lines have no order behind them. Beyond that, work through these in order:
1. Widen your date bound. A settlement can pay out orders placed months earlier. If the bound is tighter than your oldest order in the window, those matches are excluded.
2. Check that your SKU identifiers agree. The join matches on both order id and SKU. If sl_transactions_pivot and order_items carry different SKU conventions for the same product, the SKU condition fails even when the order id matches. Test by rerunning matched against order id alone and comparing.
3. Check your order history coverage. On one account we tested, the orders behind an entire settlement were absent from order_items, so nothing matched on order id either, at any date bound.
If your match rate is low and none of those explain it, the payout reconciliation above is still valid. The order-date attribution is not, and you should not build month-end accrual reporting on it until you know why.
Here is what all four variants look like side by side on one settlement, so you can see exactly what each failure mode does to the same data:
| Account B, one settlement | Rows | Payout | Result |
| Settlement lines, no join (the truth) | 15,656 | $244,617.10 | baseline |
| Correct query | 15,656 | $244,617.10 | ties exactly |
| INNER JOIN | 15,424 | $250,567.19 | drops 232 lines, overstates by $5,950.09 |
| LEFT JOIN without pre-aggregation | 15,674 | $245,504.41 | fans out 18 rows, inflates by $887.31 |
Both wrong variants return a clean-looking number. Neither errors. That is the whole problem.
We ran the same four variants across six accounts in total, spanning a 5,219-line settlement up to a 197,414-line one, including an agency account and a seller whose primary marketplace is outside the US:
| Account | Settlement lines | Payout | Correct query ties? |
| Account A | 10,941 | $163,844.49 | yes |
| Account B | 15,656 | $244,617.10 | yes |
| Account C | 20,171 | $317,022.87 | yes |
| Account D | 197,414 | $1,167,189.67 | yes |
| Account E | 26,292 | $19,739.27 | yes |
| Account F | 5,219 | $84,973.26 | yes |
Six for six on the payout tie. The failure modes reproduced everywhere we could measure them, which is the real argument for the rules: they are not tuned to one seller’s data.
A necessary caveat on that six for six, because it would be easy to over-read. Those six settlements confirm that a correctly written query does not itself introduce a discrepancy. They do not mean every settlement everywhere reconciles perfectly. A wider census of 2,397 closed settlements across 26 venues found about 96% tying exactly, with at least one mismatch appearing on 16 of those 26 venues. So if your query follows all four rules and a particular settlement still does not tie, the query is probably not your problem. Record the difference, reconcile the rest, and raise that settlement rather than reverse-engineering a query change to force it to balance.
One honest limit on that claim. On a very large agency venue, the correct query did not finish inside the platform’s 30 second query timeout even with a tightened date bound, while the simpler unbounded join did complete. We could not verify the tie there. If you are on an account of that size, expect to need a narrower window or a direct database connection rather than a conversational query.
Posted date is not order date
The reason you need this join at all is that a settlement is timestamped by when money moved, not by when your customer bought something.
Each line carries the moment the financial event posted to Amazon, typically at shipment or charge. Amazon defines its own Payment Date Range reports the same way: they cover transactions “based on the date when transactions were posted to your account. This may not correspond to the order date or the shipment date.”
This is a design decision, not a defect. A settlement-basis report closes a month perfectly cleanly on a settlement basis, which is exactly what you want when the question is “which orders produced this deposit.” It becomes a problem only when you need order-date attribution, which is what the join above gives you.
One naming point worth getting right, because a finance reader will catch it: posted date and release date are two different things, and Amazon publishes them as two separate columns. Do not treat them as synonyms when you are comparing reports.
How DD+7 affects your posting lag
If your posting lag jumped in early 2026 and never came back down, this is why.
Amazon’s Delivery Date Based Reserve, commonly called DD+7, holds funds until 7 calendar days after confirmed delivery before they become disbursable. Amazon rolled it out in stages, reaching the remaining US and Canada seller accounts on March 12, 2026. If you sell in those marketplaces, it applies to you.
We measured one account straight through its cutover, which is the cleanest way to see the size of the effect:
| Settlement window | Average lag, order to posted |
| Mar 11 to Mar 25 (last full pre-cutover window) | 1.32 days |
| Mar 26 to Apr 8 (first window after cutover) | 9.85 days |
| Apr 8 to Apr 22 | 10.33 days |
| Apr 22 to Apr 30 | 10.14 days |
That account had run between roughly 1.2 and 2.4 days for months before the cutover. A second, unrelated account sat in the same band, holding 1.41 to 2.25 days across five consecutive settlements, with an all-time average of 2.49 days across 262,614 order lines. Then the first window after March 12 jumps to nearly 10 days and stays there. A roughly 7 day increase, matching a 7 day hold.
So for any US or Canada account operating under DD+7, a reasonable expectation is around 10 days, against roughly 1 to 2.5 days for the same accounts before the policy applied. How far out you actually sit depends on how fast your orders deliver, since DD+7 keys off confirmed delivery rather than shipment. A slower-delivering catalog will sit further out.
Industry reporting puts the resulting holdback at roughly 20 to 25% of a period’s revenue. Which brings up the limit of this method, stated plainly below.
The cross-month number nobody should quote you
Once posting runs behind ordering, some lines land in a different calendar month than the sale. Everyone wants a percentage for this. We are deliberately not giving you one, and you should be suspicious of anyone who does.
The share of lines crossing a month boundary is not a property of your business. It is geometry: where a two-week settlement window happens to fall relative to the first of the month.
Here is the proof, from consecutive settlements on a single account at near-identical posting lag:
| Settlement window | Cross-month lines | Average lag |
| Mar 11 to Mar 25 (sits inside a month) | 0.69% | 1.32 days |
| Mar 26 to Apr 8 (straddles April 1) | 98.69% | 9.85 days |
| Apr 22 to Apr 30 (ends on the boundary) | 0.37% | 10.14 days |
Look at the last row. A 10 day lag produced a 0.37% cross-month rate, lower than the window with a 1.3 day lag, purely because the window ended exactly at the month boundary. Lag and cross-month rate are not the same variable.
The mechanism is straightforward once you see it: on one settlement that straddled a boundary, 73% of the cross-month lines came from orders placed in the final two days before the first of the month, decaying exponentially with distance from the boundary. Orders placed near a boundary post on the other side of it. Orders placed mid-window do not.
Across one account’s individual windows the figure ranged from 0.44% to 14.54%, with an all-time average of 8.64%. That is a 33x swing within a single seller.
The practical read: expect settlements straddling the first of the month to carry nearly all of your cross-month lines, and settlements sitting inside a month to carry almost none. Any single percentage, ours included, is a statement about one window rather than about your business. Average posting lag is the metric that behaves consistently enough to track. Measure your own with the query on this page.
What this method cannot tell you
One limit, stated up front rather than left for you to hit mid-close.
This is a settled-transactions report. It shows what posted and settled. You can reconcile what landed in your bank account to the penny, which is what everything above is about. It does not show the funds Amazon is holding behind it under DD+7.
Under a policy that defers roughly 20 to 25% of a period’s revenue, that is a real gap in the accrual picture, and it is one place where Amazon’s own reporting goes further than sl_transactions_pivot. In April 2026 Amazon added a deferred-or-released status column and a Transaction Release Date column to its Date Range Transaction Report for the US, Canada, Mexico, and Brazil, and its Finances API exposes the same per transaction, including the deferral reason. At the time of writing, sl_transactions_pivot carries neither field.
If tracking the held balance is what you need, use Amazon’s report or its Finances API for that specific question, and use this method for reconciling what actually settled. We would rather tell you that here than have you discover it during a close.
Two smaller notes while you are here. Timestamps are stored in UTC, while Seller Central’s manual download comes in marketplace-local time, so convert the boundaries of your date range rather than the column itself. And every figure on this page comes from USD marketplaces, including the US venue of a seller whose other marketplaces are European; we have not tested reconciliation accuracy on a non-USD venue and are not claiming it.
Where the data lives, and how to run this
Everything above runs against sl_transactions_pivot and order_items in your Seller Labs Data Hub, a centralized Amazon data warehouse holding your own Seller Central data in one place.
The way to query it conversationally is the Amazon MCP Server, which connects your Data Hub to an AI assistant like Claude or ChatGPT. You describe what you want in plain language, the assistant writes and runs the SQL, and you get rows back that you can export. If you have not connected it yet, start with Connecting the Amazon MCP Server to Claude.
You can also point your own SQL client at Data Hub directly, since it gives you standard MySQL credentials. The queries on this page are plain MySQL and will run either way.
Two related pages worth having open: Unified Transactions Report (sl_transactions_pivot) explains what the underlying report is and how it relates to Amazon’s version, and How to Reconcile Amazon Orders From Creation to Settlement walks the reconciliation end to end. If your broader question is why a Seller Central figure and a Seller Labs figure disagree at all, Why Seller Central and Seller Labs Numbers Can Differ covers the general case.
For agencies and outside accountants, this is the workflow that resolved the dispute described at the top of this page. A seller can add their agency or accountant as a user on their Seller Labs account, and from there the advisor can query the client’s Data Hub through the MCP Server directly, running the same reconciliation above without asking the client to export anything or forward a spreadsheet.