For businesses selling on Amazon and looking to export order data from Amazon Seller Central and importing that into QuickBooks Online using Zed Axis then we have outlined the steps in this article along with some of the key considerations you need to look at to make the most of this data in your QuickBooks Online company file. Taking the time to plan and test this can make a huge difference.
Step One: Exporting the order and payment data (Settlement Report) from Amazon Seller Central
The first step is to export the fortnightly payment and sales data (Settlement Report) from Amazon Seller Central into a text or Excel file format. Follow these steps:
- Login into your Amazon Seller Central Dashboard
- Select the Reports menu option, select Payments and then click Date Range Reports.
- Select the report type as Transaction and the Date range you require
- Download the report and save the Settlements report to your desktop.
- The report can be saved as a text tab delimited file, and can be opened in Excel
The Settlement report from Amazon includes a detailed breakdown of your account activity for a given period, including Sales, Shipping, Tax, Refunds, Fees, Returns, Storage Fees, Reimbursements. In this article we will go through how to import the SKU and Fees for the transaction into QuickBooks Online.
Step Two: Importing the Amazon Settlement Report into QuickBooks Online using Zed Axis
Before starting to import the Amazon Settlements report into QuickBooks Online you need to consider how the data is brought in. Things to consider:
- Linking Amazon SKUs to QuickBooks product names - It is important that the name of the Amazon SKU matches the Product Fully Qualified Name in QuickBooks. If there is a mismatch then Axis will create a new product in QuickBooks, this may lead to items having duplicate names in QuickBooks leading to problems with having an accurate picture of inventory
- Selling in multiple countries - Assuming you have multi-currency enabled in your QuickBooks company file you will need to do separate imports, have fees expense accounts for each currency
- Recording fees and deductions - How to record the fees and charges levied by Amazon make sure you have created items as Other Charge types with the same name as the Amazon fees and linked them to corresponding Expense Accounts in QuickBooks. You will need to have items for;
- How to record returns, lost items and storage fees
- Reconciliation with Settlement report
- Settlement report is generated by Amazon every 14 days or so
Importing the SKU details
To import the Settlements report into QuickBooks Online follow these steps:
- Open Zed Axis and connect to your QuickBooks Online file
- Click on the import tab and browse to the Settlement Report file that you downloaded from Amazon Seller Central in step one above.
- Select the option to Add New Mapping, select the transaction type as Sales Receipt and map the fields shown below, finally save the map with the name AMZN - SR SKU. (on subsequent imports you just need to select this saved mapping)
- This first phase of the import is going to import the SKU details so we need to exclude any rows that do not have SKU details (In the preview grid filter the Amount column to exclude Blanks) and click Import.
Importing the Fees
1. Now to import the Fees, select the option to Add New Mapping and select Sales Receipt and create a mapping as shown below and save the map with the name AMZN - SR Fees
2. Select the option to Append (as we going to be appending the Fees to the Sales Receipts we create previously).
3. Filter out the rows that do not have Fees by setting the Filter for the ItemRefFullName column to is not NULL
4. Click Import and the various fees will be added to the sales receipts.
Once imported Axis will create Sales Receipts in batch in QuickBooks that will look similar to the screen shot below.
We have provided this support article as a guide to how to import Amazon data into QuickBooks we welcome any suggestions or improvements you might have in the feedback section below. We are always looking for ways to add features and improvements to make this process simpler.