How Do I Tie My Amazon Reports to Blue Onion?
Blue Onion and Amazon reporting contain the same data but report using different timing. Amazon's Monthly Summary report only includes transactions in the date window that have been made available to be paid by Amazon. This does not correlate to the date of order or the date of payment. This is the date on which Amazon has verified a transaction has met all criteria to be paid to a store. This generally means the order has been placed, the payment collected from the customer, and the fulfillment of the order has been completed. Below is an example image of the report from Amazon being discussed. This is also true of the Monthly Transaction report containing transaction-level details for the same period.
It's easiest to go step-by-step through an example transaction and discuss where each system will have a transaction reported. We will follow the order below through each of it's points of its lifecycle (order, fulfillment, and payment).
At Order
For the reports we are discussing here, Amazon would not include any transactions that had only been placed and not fulfilled. In other words, this transaction would be missing from these reports. Blue Onion represents all orders placed within a period in the Deferred Revenue of the Orders section of the Journal Entries page while also recording the expected payment in the Accounts Receivable section to be recorded in your clearing account or undeposited funds. See below:
Note: For the month of January, only the deferred revenue and accounts receivable are being recorded because while the order took place in January, it was not fulfilled or paid until February.
At Fulfillment
When an order is fulfilled, it will generally be made available to be paid by Amazon and be included in reporting. At this point, Amazon will include it in the Monthly Summary and Monthly Transactions report, along with any of the fees incurred in completing and fulfilling this order. Blue Onion will back out the Deferred Revenue recorded at the time of the order and move it to Revenue on the Income Statement. Below are the entries that Blue Onion includes at this time:
At Payment
While Amazon will show the amounts transferred to your bank in their reports, you are not able to find which transactions were included in each payout. Upon the actual payment date (date received at the bank), Blue Onion includes the cash received, the fees incurred, as well as the reversal of the clearing account entry that was included at the time of the order.
Summary
Amazon only includes transactions in the Monthly Summary or the Monthly Transactions reports when the transaction is made available to be paid. Blue Onion tracks when Amazon (or any payment processor) has made a payment available to be received and can provide full transparency to not only when it was made available, but also when it was ordered, fulfilled (and recognizable as revenue), and paid.
To tie the values on your Amazon Monthly Summary, export the payment records for Amazon Seller from Blue Onion for a period of about a month before through the end of the period you'd like to tie out. You'll need to make sure you go back far enough to include all orders that were made available in the period. Once exported, the easiest way to summarize the data is to create a pivot table in Excel with data filtered for available dates within the range of review, the rows will be payment type and you can sum the values in the Gross Amount, Fees, and Net Amount columns - but you will be comparing the Net Amount to Amazon's report. Below is an example result of this and the fields used to create the pivot table:
In the version of the report below, we have mapped the line items from the Amazon report to the payment types within Blue Onion:
By comparing the two reports in this way you'll be able to confirm both are including the same data. While Amazon is showing you this data only once available to be paid, Blue Onion allows you to trace the order from order to fulfillment and ultimately to payout, including all revenue fees.