A fully automated reconciliation scenario in Make.com that extracts Amazon Settlement reports, iterates through every transaction line, and generates a corresponding Journal Entry or Sales Receipt in QuickBooks, ensuring books balance to the penny.
The Problem
Amazon payouts don't match bank deposits because they are net of referral fees, FBA storage, and refunds. Manually calculating gross sales vs. expenses from settlement reports in QuickBooks is time-consuming and prone to accounting errors.
The Outcome
A fully automated reconciliation scenario in Make.com that extracts Amazon Settlement reports, iterates through every transaction line, and generates a corresponding Journal Entry or Sales Receipt in QuickBooks, ensuring books balance to the penny.
Step-by-Step Guide
1. **Establish Connections**: In Make.com, create a new scenario. Add the 'Amazon Seller Central' module and follow the OAuth flow to authorize your Seller ID. Repeat for the 'QuickBooks Online' module.
2. **Configure Amazon Trigger**: Select 'Watch Settlement Reports'. Set the 'Limit' to 1 to process one report at a time during testing to avoid operation spikes.
3. **Parse Report Data**: Add an 'Amazon Seller Central: Get Settlement Report' module. Use the Report ID from the trigger. Use a 'CSV Parser' or 'JSON Parser' module if the report content is raw text.
4. **Initialize Iterator**: Add a 'Flow Control: Iterator'. Map the array of transactions from the settlement report. This allows Make.com to process each fee and sale line individually.
5. **Apply Router Logic**: Add a 'Flow Control: Router' after the Iterator. Create filters for each path: 'Order' (TransactionType = Order), 'Refund' (TransactionType = Refund), and 'Fee' (TransactionType = ServiceFee or ReferralFee).
6. **Search for Existing Customers/Items**: Before creating a Sales Receipt, add a 'QuickBooks: Search for Customer' module. Use the Amazon `BuyerEmail`. If not found, use a 'QuickBooks: Create a Customer' module.
7. **Handle QuickBooks SyncToken**: For updates, always use a 'Search' module first to retrieve the current `SyncToken` and `Id` to prevent 400 Bad Request errors.
8. **Map Line Items**: In the 'QuickBooks: Create a Sales Receipt' module, use the `map()` function to transform Amazon product arrays into QuickBooks line items. Use `lower()` or `trim()` functions to clean SKU strings.
9. **Implement Error Handling**: Right-click the QuickBooks module and select 'Add error handler'. Choose the 'Rollback' or 'Resume' directive to ensure one failed line item doesn't stop the entire settlement processing.
10. **Finalize Aggregator**: Add an 'Array Aggregator' at the end of the paths to group processed IDs for a final summary log.