📋 Overview
Amazon does not deposit your revenue. It deposits what is left after fees, refunds, reserves, and withheld tax — and if your books record only that deposit, your revenue is understated, your expenses are invisible, and your margins look better than they are. This article covers how to use AI to turn Amazon’s settlement data into a categorized, reconciled file your bookkeeper or accountant can post, and what you have to check before anyone touches it.
🧾 What a Settlement Report Actually Contains
Settlement data is the system of record for money. It is produced per settlement period, and each period closes with a single disbursement to your bank. You can download statements from the Payments section of Seller Central, including All Statements for individual settlement periods and Date Range Reports when you need a calendar-month cut.
The line-level structure
A settlement flat file is not a list of orders. It is a list of money movements, usually identified by a transaction type (order, refund, adjustment, service fee, transfer) and an amount description that says what the specific line is (principal, shipping, commission, a fulfillment fee, a promotional rebate, withheld tax). One order can produce six or more rows. Column naming differs between report versions and marketplaces, so open your own export before you assume the field names.
This structure is why manual categorization falls apart at volume. You are not sorting 400 orders; you are sorting several thousand lines that collapse into maybe 40 to 60 distinct code combinations. Those combinations are the thing worth categorizing — and they are stable month to month.
Gross versus net
Recording the bank deposit as revenue is the single most common Amazon bookkeeping error. The deposit is a net figure: product sales, minus referral and fulfillment fees, minus refunds, minus advertising charged against the account, plus reimbursements, adjusted for reserve. Booking it as one number means you can never answer “what did fees cost me last quarter.”
Timing gaps you have to account for
Settlement periods rarely line up with calendar months, so most periods straddle a month end and need splitting if you report monthly. Separately, Amazon holds funds for deferred transactions and releases them on a delivery-based schedule — under the DD+7 policy, funds become available seven days after confirmed delivery. The practical effect is that sales you made in a month may not appear in that month’s statement at all, which is a cash-versus-accrual decision to settle with your accountant, not something AI should decide for you.
🗂️ Building a Mapping File With AI
The goal is a reusable mapping file: every Amazon transaction code paired with one account in your chart of accounts. Build it once with AI’s help, then reuse it every month and only review what is new.
1. Export a full settlement period and deduplicate the codes
Download a complete settlement statement from Payments. In your spreadsheet, build a pivot or unique-values list of transaction type plus amount description, and count the rows behind each pair. Strip the amounts, order IDs, and anything buyer-identifying out of this list — the mapping exercise needs the codes, not the money or the customer.
A good result here is a list of 30 to 70 unique pairs on one tab. If you see hundreds, you have probably included a column that varies per order.
2. Write down your chart of accounts first
AI will invent plausible account names if you do not give it real ones. Paste your actual accounts — product sales, sales returns, Amazon selling fees, fulfillment fees, storage fees, advertising expense, sales tax payable, other income, owner draws, and so on — each with a one-line description of what belongs in it. If you do not have a chart of accounts yet, get it from your accountant before you start, not after.
3. Prompt for a mapping table with reasons and flags
Ask for structure and evidence, not a verdict. A pattern that works:
- “Input A is my chart of accounts with descriptions. Input B is a deduplicated list of Amazon settlement transaction-type and amount-description pairs, with no amounts attached.”
- “For each pair in Input B, return one row: the pair, the single best-matching account from Input A, its classification (revenue, contra-revenue, expense, tax liability, or balance-sheet transfer), a one-sentence reason, and a confidence of high, medium, or low.”
- “If a pair does not clearly match any account in Input A, mark it UNMAPPED and describe what you think it represents. Do not create new accounts. Do not return any pair that was not in Input B.”
The confidence column is what makes this usable. It turns a 60-row guess into a 10-row review queue.
4. Review every medium, low, and UNMAPPED row yourself
AI is good at recognizing that a line labeled as a commission is a selling fee. It is unreliable on the ambiguous ones: FBA inventory reimbursements, liquidation proceeds, promotional rebates, shipping chargebacks, currency conversion lines if you use Amazon Currency Converter for Sellers, and anything tax-related. Those decisions have real reporting consequences, and they belong to your accountant.
Two worth pre-deciding: reimbursements for lost or damaged inventory are not product sales, and withheld marketplace facilitator tax that Amazon collects and remits is not your revenue. Where each lands in your books depends on your accounting method — ask once, then write the answer into the mapping file so nobody re-litigates it monthly.
5. Apply the mapping in the spreadsheet and reconcile to the deposit
Use a lookup formula to attach the account to every settlement line, then total by account. Do the arithmetic in the spreadsheet — never ask a chat tool to sum a column of figures.
The reconciliation test is absolute: the sum of all categorized lines must equal the deposit amount shown on the statement, to the cent. If it does not, you have a mapping gap, a dropped row, or a reserve movement you have not accounted for. Do not post until it ties.
💡 Pro Tip: Keep an explicit “Unmapped” account in your spreadsheet rather than letting unrecognized codes fall into a catch-all like miscellaneous expense. Amazon introduces new fee and adjustment codes over time, and a visible Unmapped balance of zero is the only reliable signal that your mapping file is still complete.
6. Freeze the mapping and only review the diff
Save the approved mapping as its own file with a date. Next month, run the deduplication again and compare the code list against the frozen file. Only genuinely new codes go to AI for a suggestion. This takes the monthly job from an hour of re-categorizing to a few minutes of exception handling, and it stops the categorization from quietly drifting as the model or the prompt changes.
⚠️ Where AI Bookkeeping Prep Goes Wrong
Assuming one fee rate across the catalog
Sellers ask AI to “estimate my Amazon fees” and get a single percentage applied to everything. Referral fees alone range from 5% to 45% depending on category, and fulfillment fees vary by size and weight tier. Your settlement file already contains the exact charged amounts — use those. An estimated fee line in your books is a number you will have to unwind at year end.
Letting the AI retype the numbers
Pasting a settlement file into a chat window and asking for totals by category feels efficient and produces figures that are close enough to look right. Language tools transcribe and add unreliably at volume. Keep the amounts in the spreadsheet and give AI only the codes.
Treating refunds as negative sales in one lump
A refund line usually carries several components — the returned principal, returned shipping, a refunded tax amount, and a fee adjustment that may not fully offset the original fee. Collapsing all of it into a single reduction of revenue hides what returns actually cost you and makes your fee accounts wrong. Map each refund component separately, the same way you map sale components.
Re-prompting from scratch every month
If you rebuild the categorization each period by pasting everything into a fresh conversation, the same code can land in two different accounts in consecutive months, and nothing in your books will flag it. The frozen mapping file exists to prevent exactly this.
❓ FAQs
Can AI replace an Amazon bookkeeping or accounting integration?
No. Dedicated accounting integrations post summarized journal entries into your ledger on a schedule and handle the mechanics of multi-currency and tax lines. AI helps you understand and categorize the underlying data, build the mapping, and investigate the months that will not tie. The two solve different problems, and capabilities in this space change often enough that you should check a vendor’s own documentation rather than rely on a comparison you read somewhere.
Is it safe to upload settlement reports to an AI tool?
Treat it as financial data leaving your control. The safer habit is to upload only the deduplicated code list with no amounts, no order IDs, and nothing buyer-identifying, which is all the categorization step actually requires. If you do need to share fuller data, confirm the tool’s current data-retention and training settings in its own documentation first.
How do I handle a settlement period that crosses a month end?
Split the lines by posting date into the two months and categorize each half with the same mapping. Date Range Reports can give you a calendar-month view as a cross-check, but reconcile each individual settlement to its own disbursement first — otherwise you lose the one test that proves nothing was dropped.
What should I hand my accountant at year end?
The raw settlement exports for every period, your frozen mapping file with its date, the categorized totals by account per period, and a short note listing any codes you were unsure about and how you decided to treat them. That last note saves the most time, because it is usually what the accountant would have had to ask you about anyway.