Calculate True SKU Net Profit from Amazon Settlement Reports
Amazon's Settlement Report (report type: _GET_V2_SETTLEMENT_REPORT_DATA_FLAT_FILE_) is a TSV where each row represents a single event (Order, Refund, FBA Fee, Commission) rather than order aggregates. The accounting trap: when pivoting this TSV to calculate net profit per SKU, direct summation of the 'amount' column double-counts refunds—the original sale AND the refund reversal both appear as separate rows. Worse, fee columns store values as negative numbers for charges, but refund rows store fee reversals as positive values (credits back to you). The transaction_type column contains values like 'Order', 'Refund', 'Service Fee', 'FBA Inventory Fee'. This workflow parses the TSV, groups transactions by amazon-order-id and sku, nets each transaction type correctly, and produces a clean per-SKU profit summary.
Why This Matters
A 7-figure Amazon seller built a monthly P&L by pivoting their Settlement Report. The Pivot showed SKU B08X7K9PL2 with -$4,200 net profit. After manual audit, they discovered the Pivot had summed refund fee reversals (positive values) as additional charges instead of credits. Corrected, the SKU was actually +$11,300 profitable. Across 340 SKUs, this double-counting error understated total monthly net profit by $38,700—silently compounding for months, leading to incorrect restock decisions and unnecessary PPC budget cuts.
Why Excel & Python Fail Here
The TSV structure is event-based, not order-based. One order with 2 items and 1 refund generates 5+ rows: 2 'Order' rows, 2 'FBA Fee' rows, 1 'Refund' row, plus commission adjustments. Excel Pivot Tables cannot distinguish 'fee charge' from 'fee reversal' without explicit transaction_type filtering. The result: SUM(amount) mixes debits and credits incorrectly. Python's groupby works but requires understanding Amazon's 15+ transaction_type values and their sign conventions.
| Criterion | Excel / Sheets | Python / Cloud | DataPrep (This Page) |
|---|---|---|---|
| Max Rows | ~150K (crashes) | RAM-limited | 1M+ via Wasm |
| Setup Time | Manual formulas | 30–60 min env setup | 0 seconds |
| Data Privacy | Local but fragile | Uploads to cloud | 100% in-browser |
| Cost | License fees | EC2 / SaaS $49+/mo | Free forever |
Step-by-Step Solution
We will use the Amazon Settlement Cleaner to execute this entirely in your browser.
- 1
Upload Amazon Settlement Report TSV
Drag and drop the file from Seller Central → Reports → Payments → Settlement Reports → Download. The tool auto-detects TSV format, identifies transaction_type, sku, amazon-order-id, and amount columns.
- 2
Configure netting rules by transaction type
The tool maps each transaction_type to its accounting treatment: 'Order' → revenue (positive), 'Refund' → revenue reduction (negative), 'FBA Fee' → cost (negative), 'Refund' fee reversal → cost credit (positive). Review the mapping.
- 3
Export per-SKU profit summary
Download a CSV: SKU, Total Revenue, Total Fees, Total Refunds, Net Profit, Units Sold, Avg Margin %. Flag any SKU with negative margin for pricing review.
Privacy Guarantee: Zero Bytes Uploaded
All processing happens in your browser via WebAssembly. Your data never touches a server. Try it: disconnect your Wi-Fi right now—the tool will still work.
Common Errors & Fixes
| Error / Symptom | Root Cause | Fix |
|---|---|---|
SKU shows negative profit but is actually profitable | Refund fee reversals (positive values) summed as additional charges instead of credits | Filter by transaction_type. Fee reversals on Refund rows are CREDITS (subtract from costs, don't add). |
Revenue double-counted for multi-item orders | Each item in a multi-item order generates a separate 'Order' row—correct behavior, but Pivot shows inflated 'order count' | Count unique amazon-order-id values for order count. Sum amount for revenue. Don't conflate the two. |
Frequently Asked Questions
Where do I download the Settlement Report?
How does this handle Amazon's 'Transfer' rows?
Ready to clean your data?
100% local processing · Zero uploads · Blazing fast
Trusted by 2,400+ data teams · 18M+ rows processed monthly