A fully automated reconciliation workflow in n8n that captures every Amazon order, breaks out gross revenue and fees into the correct QuickBooks Chart of Accounts, and ensures your books match your actual Amazon settlements to the penny.
The Problem
Manual entry of Amazon sales is labor-intensive and error-prone, especially when accounting for Amazon's complex fee structures (referral fees, FBA storage, etc.). This leads to discrepancies between bank deposits and QuickBooks records, making tax season and financial audits difficult.
The Outcome
A fully automated reconciliation workflow in n8n that captures every Amazon order, breaks out gross revenue and fees into the correct QuickBooks Chart of Accounts, and ensures your books match your actual Amazon settlements to the penny.
Step-by-Step Guide
1. **Establish Credentials**: In n8n, go to 'Credentials' and create connections for 'Amazon Seller Central' (OAuth2) and 'QuickBooks Online' (OAuth2). Ensure you have 'Accounting' scopes enabled for QBO.
2. **Configure Amazon Trigger**: Add the 'Amazon Seller Central' node. Select the 'Order' resource and the 'On New Order' event. For settlements, choose 'On New Report' filtered by `GET_V2_SETTLEMENT_REPORT_DATA_FLAT_FILE`.
3. **Handle Data Arrays with the Item Lists Node**: Amazon often returns orders in a list. Use the 'Item Lists' node to 'Split Out' items so each line item on an order is processed individually.
4. **Map Data via n8n Expressions**: Add a 'Set' node (now called 'Edit Fields') to transform Amazon's date format and currency strings into QBO-compatible numbers using expressions like `{{ $json.order_total.amount.toNumber() }}`.
5. **Deduplicate with Filter Node**: To prevent duplicate sales entries, use a 'QuickBooks Online' node to 'Get All' Sales Receipts filtered by `DocNumber` (Amazon Order ID). Follow this with an 'IF' node: if 'Check exists' is true, stop the workflow.
6. **Synchronize Customers**: Use the QBO node to 'Search' for a customer by email. If not found, use a 'Customer: Create' node. Reference the resulting `ID` for the next step.
7. **Create Sales Receipt**: Add the QBO node with the action 'Sales Receipt: Create'. Map the Customer ID from step 6 and line items from step 3. Crucially, map the Amazon Order ID to the 'Private Note' or 'DocNumber' for tracking.
8. **Process Fees via Journal Entry**: For settlements, Amazon reports fees as negative values. Use n8n logic to sum these and create a 'Journal Entry' in QBO, debiting your 'Amazon Fees' expense account and crediting 'Amazon Clearing Account'.
9. **Implement Error Handling**: Create a 'Error Trigger' workflow. When any node fails (e.g., QBO API rate limit), this node will catch the error and send a notification to Slack or Discord with the `executionId`.
10. **Enable Self-Hosting Perks**: If self-hosting n8n, ensure your `N8N_PAYLOAD_SIZE_MAX` environment variable is increased if you process large monthly settlement reports (5,000+ rows).