How to match Amazon FBA fees to your orders
By the Analistable team · Updated · 6 min read
Amazon's settlement report lists each order as several rows: the item price, the referral commission, the FBA fulfilment fee and any refunds. Pivot it to one row per order ID with a column per fee type, then join it to your order report on the order ID. You can then check fees as a percentage of price, find orders with no settlement yet, and spot unusual fees.
Part of our guide: How to reconcile data in Excel
Reshape the settlement report
Download the settlement report (flat file) for the period. Rows have an order ID, a transaction type and an amount type or description, for example item price, commission and the per-unit FBA fulfilment fee. Turn it into one row per order:
PivotTable: Rows = order-id, Columns = amount-description, Values = Sum of amountOr in Power Query: select the description column and use Transform → Pivot Column with Sum of amount.
Join to your orders
| Order ID | SKU | Price | Commission | FBA fee | Net | Fees % |
|---|---|---|---|---|---|---|
| 202-1111111-1111111 | MUG-01 | 14.99 | -2.25 | -2.85 | 9.89 | 34% |
| 202-2222222-2222222 | MUG-01 | 14.99 | -2.25 | -2.85 | 9.89 | 34% |
| 202-3333333-3333333 | TEA-02 | 8.5 | -1.28 | -4.1 | 3.12 | 63% |
| 202-4444444-4444444 | MUG-01 | 14.99 | — | — | — | Not settled yet |
Fees % = −(commission + FBA fee) ÷ price. TEA-02's fees are far higher than the mug's: its size tier or weight may be wrong in Amazon's catalogue, which is worth checking against the product's real dimensions.
Checks worth running
- Same SKU, different FBA fee across orders in the same period: a sign the item was re-measured.
- Orders with no settlement rows: usually just timing — they'll appear in the next settlement.
- Refund rows without a matching reimbursement for returned stock that wasn't put back into inventory.
- Commission rate different from your category's expected rate.
Matching marketplace sales to stock levels is a separate task — see reconciling inventory records with a stock count.
Combining several settlement periods
Stack each settlement report with a Settlement ID column before pivoting. An order can appear in two settlements (the sale in one, a refund in the next), so pivot by order across all of them to see its full net value.
Power Query step by step
- Data → Get Data → From File → From Text/CSV and choose the settlement flat file. Settlement files are usually tab-separated; check the delimiter in the preview.
- Remove the summary row at the top if your file has one, then use Use First Row as Headers.
- Filter out rows with a blank order ID; these are account-level items such as subscription fees, storage fees and reserves. Keep them in a second query.
- Set the amount column to Decimal Number and check the locale if amounts use commas as decimal separators.
- Select the amount-description column, choose Transform → Pivot Column, values = amount, aggregation = Sum.
- Merge the result with your order report on order ID (Left Outer from the orders side), and expand SKU and quantity.
Column names in settlement files vary by marketplace and over time, so match on what your file actually contains. If you want to compare join kinds, see Power Query merge join kinds.
Fees that aren't per order
Some charges never appear against an order ID: monthly storage, long-term storage, subscription, advertising and inbound placement or removal fees, depending on your programmes. They still reduce the settlement total, so the reconciliation has two parts.
| Line | Amount |
|---|---|
| Order-level net (sales − commission − FBA fees − refunds) | 4812.4 |
| Storage fees | -96.3 |
| Advertising | -350 |
| Subscription | -25 |
| Reserve held / released | -120 |
| Settlement total paid to bank | 4221.1 |
4,812.40 − 96.30 − 350.00 − 25.00 − 120.00 = 4,221.10, which should equal the deposit on the bank statement for that settlement.
The same reshape in SQL
Conditional sums do the pivot in one query, which is handy when you have a year of settlements stacked together:
SELECT order_id,
SUM(CASE WHEN amount_description = 'Principal' THEN amount ELSE 0 END) AS item_price,
SUM(CASE WHEN amount_description = 'Commission' THEN amount ELSE 0 END) AS commission,
SUM(CASE WHEN amount_description = 'FBAPerUnitFulfillmentFee' THEN amount ELSE 0 END) AS fba_fee,
SUM(amount) AS net
FROM settlement
WHERE order_id IS NOT NULL
GROUP BY order_id;Replace the description values with the ones in your own file. The net column includes every row for the order, so it also catches shipping credits, promotions and refunds you haven't given their own column.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Order net doesn't match the item price minus fees | Promotion, shipping or gift-wrap rows not pivoted into their own column | Add a column for every description that appears |
| Totals off after import | Amounts read as text because of a decimal comma | Change type using the correct locale |
| Order appears in two settlements | Sale and refund in different periods | Stack settlements before pivoting |
| Fee per unit looks doubled | Multi-unit order | Divide the fee by quantity before comparing |
| Settlement total doesn't reach the bank figure | Account-level fees or reserve excluded | Add the non-order query to the bridge |
Checking the result
Three totals should agree for every settlement: the sum of all rows in the flat file, the order-level net plus the account-level lines, and the deposit on your bank statement. Then sort SKUs by fees as a percentage of price; an outlier is worth a size-tier check. To see the same SKUs across several marketplaces or shops, see finding top products across stores.
Second example: a refund across two settlements
Order 204-555 sold one tea set for £40.00. In the first settlement Amazon recorded the sale; in the next one the customer returned it. The figures are illustrative.
| Settlement | Item price | Commission | FBA fee | Net |
|---|---|---|---|---|
| 1–14 Sep (sale) | 40 | -6 | -3.5 | 30.5 |
| 15–28 Sep (refund) | -40 | 4.8 | 0 | -35.2 |
| Order total | 0 | -1.2 | -3.5 | -4.7 |
Looking only at the second settlement, the order seems to cost £35.20. Across both it cost £4.70: the fulfilment fee was not refunded and only part of the commission came back (in this example £4.80 of £6.00). Whether a refund administration fee applies, and how much, depends on Amazon's current fee rules for your marketplace, so check the rows in your own file. If the returned item was not put back into sellable stock, look for a reimbursement row in a later settlement.
Doing it in Google Sheets
Import the flat file with File → Import and choose tab as the separator. A pivot table with order ID in rows, amount description in columns and SUM of amount as values gives the same one-row-per-order layout. Large settlement files can run to many thousands of rows, which makes Sheets slow, so filter to the columns you need before importing.
Frequently asked questions
- How do I see Amazon fees per order?
- Pivot the settlement report by order ID, with the amount description (item price, commission, FBA fee) as columns.
- Why are some orders missing from the settlement report?
- Settlements are issued every couple of weeks; recent orders appear in the next one.
- How do I check if Amazon charged the wrong FBA fee?
- Compare the FBA fee for the same SKU across orders and against the fee expected for its size and weight tier.
- Where do I download the Amazon settlement report?
- In Seller Central, under the payments or reports area for settlements. Download the flat-file version for each settlement period you want to analyse.
- Can I see fees per SKU instead of per order?
- Yes. After pivoting by order, add the SKU from your order report and pivot again by SKU, averaging the fee columns.