Also available in these formats:
This manual provides information on how to export a list of orders from an XLS spreadsheet.
There are 2 ways of exporting the orders placed in a store.
- Using the Manage Orders admin page
- Using the Export Orders admin page
By the end of this guide, you will learn how to export the orders spreadsheet and the details of each tab and column of the spreadsheet.
Manage Orders admin page #
The Export Order section is available on the Manage Orders admin page.
- Access to the system admin pages using a Sys admin role
- Access to a store on the server
- The export orders spreadsheet that is used here is .xls format. Ensure you have spreadsheet software that can view and edit these files, such as Microsoft Excel or Open Office.
To access the Manage Orders admin page, follow these steps:
1. Login to the sys admin panel and go to Orders > Manage Orders
3. Click on the down arrow to expand the section.
The following elements are available in the Export Orders section
- Specify Date: This is the first method to export the order data into a .xlsx file. The following duration of data is available:
- last 10 days
- last 35 days
- last 65 days
- all orders
- Status: This button will redirect you to the IMPORT/EXPORT STATUS admin page, where the list of tasks to import and export is displayed. When a task is completed, the file will be available to download.
- Export: Use this button to create a new task to export the order data into a .xlsx file according to the duration selected on the Specify Date dropdown menu. After this button is clicked, you will be redirected to the IMPORT/EXPORT STATUS admin page, when the task is completed, the export orders file will be available to download to your computer.
- Import: Use this button to import the order data from a .xlsx file on your computer.
Export Orders #
4. From the ‘Export Orders’ section of the Manage Orders admin page, select a specific date from the dropdown menu and click on ‘Export’
5. The IMPORT/EXPORT STATUS admin page will open in the same window.
- A new Export task will be displayed on the list of tasks as a new record.
- The new task will have a Waiting Status and Auction.
6. Wait a few minutes, when the export orders file is completed, a download link will be displayed on the Auction columns.
7. Click on the ‘download’ link and save the file to your computer.
8. Open the file and Enable the Edition of the file
Instructions of the Export Orders spreadsheet #
The Export Orders spreadsheet contains the orders’ data that was downloaded from a particular store in the server and in a specific period of time.
It consists of 3 worksheets (Do not forget to fill in the corresponding information on the worksheets):
- Order: This is the main tab of this spreadsheet and contains all the important information about the orders that were placed in the store. Here you will find:
- Order information, such as the Order ID, as well as the date and time of the order.
- Customer information, such as the customer’s name and billing and shipping addresses of the customer for those specific orders.
- Product details, such as the product code, title, price, taxes, and shipping per product, as well as product status.
- Order totals, such as the total pay, net pay
- Order Property: This tab will have information about the order properties of each order and their values.
- Order Item Property: This tab will have information about the properties of the product per order and their values.
Data Definition #
Order worksheet #
Most of the columns are self-explanatory but refer to the following table for more information and some examples of the values that are expected on each column:
|Column||Column Header||Definition and Use||Accepted Values||Example(s)|
|A||Order ID||The ID uniquely identifies an order. The Order ID consists of the order prefix and the date when the order was placed, which could be in these 2 formats YYYYMMDDSSSSS or YYMMDDSSSSS. This can be specified on the Order Preferences admin page.||Alphanumeric||ABC22121200002|
|B||Date||In this column the date and time when the order was placed will be displayed in the following format: |
|C||Customer Code||The customer code column will display the value of the customer number order property for each product in the order||String||[email protected]|
|D||Customer Email Address||The email of the customer who placed the order||String||[email protected]|
|E-N||Bill To Columns||The Billing Address information of the customer who placed the order or the Billing Address information of the customer specified in the billing form when the order was placed:|
Bill To First Name
Bill To Last Name
Bill To Company
Bill To Address1
Bill To Address2
Bill To City
Bill To State
Bill To Country
Bill To Phone
Bill To Postal/Zip Code
|String per column||Billing address|
|O-Y||Shipping To columns||The Shipping Address information of the customer who placed the order or the Shipping Address information of the customer specified in the billing form when the order was placed:|
Ship To First Name
Ship To Last Name
Ship To Company
Ship To Address1
Ship To Address2
Ship To City
Ship To State
Ship To Country
Ship To Phone
Ship To Postal/Zip Code
|String per column||Shipping address|
|Z||Product Code||Unique identifier of the products on an order. When the supplier admin exports the spreadsheet from the supplier store, the product code will be shown in this column. That means the product code the supplier used to create the product will be the one displayed in this column. |
If the spreadsheet is exported from the Marketplace store using a marketplace or sys admin account, the AIN number will be displayed in this column instead. The supplier product code will be displayed in the next column called “SKU code”. The AIN number is automatically created when the products from different suppliers are copied to the MCW. If your project does not require a Master Catalog Warehouse store (MCW) the products of your store won’t have an AIN number.
This field can not be empty.
|String||GOAT-CREAM-CHEESE / |
|AA||SKU code||This column will be empty when exporting the spreadsheet from the supplier store. |
The supplier product code will be displayed here when ONLY when the spreadsheet is exported from the MCW and Marketplace stores.
|AB||Qty||The specific amount of the product on the order. This field can not be empty.||Numeric||10|
|AC||Title||The title of the product on the order. The title of the product can not be greater than 255 characters. This field can not be empty.||String||Ironwood Coffee Medium|
|AD||Shipping & Services||Shipping value that was charged when the customer placed the order.||Numeric||1.56|
|AE||Shipping Method||The name of the Shipping Method that was used to ship the order. This field can be empty.||String||Default Shipping|
|AF||Product Ship Tax||Tax charges on delivery or shipping services that were paid when placing the order. |
These charges are calculated according to the tax rates set up in the store.
|AG||Product Total Tax||The total tax including shipping tax and products sale tax||Numeric||5.65|
|AH||Product Commission||Commissions are set up in the marketplace store by marketplace admins. |
Marketplace admin can set commission rules on products and categories by applying different commission percentages or fixed amounts to the products or products in a category.
The value in this column is the fee the marketplace will get per line item.
This column will display 0 if commissions haven’t been configured in the marketplace or if the commission rules haven’t been specified for the specific product.
|AI||Product Refund||A value will be displayed here when the order is shipped and then returned and refunded. The refund could be a partial refund or a total refund of what was paid per product when the order was placed.||Numeric||8.5|
|AJ||List Price||The listed price or the manufacturer’s suggested retail price that the product had when the order was placed.||Numeric||200|
|AK||Item Price||The actual price of the product when the order was placed. This price will always be lower than the List Price.||Numeric||198.85|
|AL||Final Item Price||The Final Item price is the item price multiplied by the qty of products per line on the order.||Numeric||994.25|
|AM||Total Tax||The total tax value the customer paid per order. Note: All the products in one order will have the same Total Tax per line.||Numeric||15.55|
|AN||Total Commission||The Total Commission value the marketplace will get per order. Note: All the products in one order will have the same Total Commission per line.||Numeric||2.50|
|AO||Total Refund||The Total Refund value the customer got when one of the products or the complete order was returned and refunded. Note: All the products in one order will have the same Total Refund value per line.||Numeric||55.26|
|AP||Total Pay||The Total Pay value the customer paid per order. Note: All the products in one order will have the same Total Pay per line.||Numeric||1250.66|
|AQ||Net Pay To Supplier||The Net Pay value the customer paid per order. Note: All the products in one order will have the same Net Pay per line.||Numeric||168.84|
|AR||Supplier Id||The Supplier ID will be displayed in this column. This is a unique identifier specially used by the marketplace admin to know which products belong to which suppliers. The Supplier admin won’t be using this information.||Numeric||244|
|AS||All Applied Discounts||This column will be empty if there were no discounts or promotions applied to the products on the order||Numeric||568.56|
|AT||Product Status||Current Order Item State per product on an order. Each product could have a different order item state on an order, and that will determine the overall order state. The following are the available Order Item State:|
· Being Prepared
· To Be Shipped
· Shipped On
· On Hold
|AU||Currency||This column will display the currency of the order as a 3-digit code. The store currency needs to be previously set up in the store before placing an order.||String||USD / CAD / AUD / EUR|
|AV||Payment Type||This column will show the code of the payment method used to place each order. The payment gateway code is automatically provided to the supplier when the payment gateway is configured in the store. |
The payment gateways are set up directly in the marketplace store so the supplier does not need to set them up.
Some examples of payment gateway codes are:
-PP: Pay with Paypal
-MO: Pay with Moneris
-NCC-COD: Cash On Delivery
|AW||PO Number||PO Number will display the information the customer enters on the checkout page when placing the orders only if the order was paid by Purchase Order or by Cheque.||Alphanumeric||00001|
|AX||Identity||The Identity column is storing the credit card number (with only last 4 digits displayed) OR the gift certificate code. This column could be empty.||String||***********1111|
|AY||Tracking Code||If the product has already been shipped, the tracking code will be displayed on this column. The tracking code is a unique number assigned to each package of the order, if the complete order was shipped with the same tracking code, all the products in that order will have the same tracking code.||Alphanumeric||RH469238277CN|
Order Property worksheet #
Order Properties are additional specifications of an order that provide details about the order to help an admin make an informed decision about the order.
The Order Properties are predefined and automatically assigned to the orders after they are placed.
The Order Property tab consists of 3 columns:
|Column Name||Definition and Use||Accepted Values||Example(s)|
|Order ID||The ID uniquely identifies an order. The Order ID consists of the order prefix and the date when the order was placed, which could be in these 2 formats YYYYMMDDSSSSS or YYMMDDSSSSS. This can be specified on the Order Preferences admin page.||Alphanumeric||ABC22121200002|
|Prop Name||The name of the order property that was automatically attached to the order after it was placed.||String||Browser User Agent|
|Prop Value||The value of the order property that was automatically attached to the order after it was placed.||String||Chrome 10|
At this point, hopefully, you were successful in exporting the orders of a store. In short, the export process is as follows:
- Navigate to the Import/Export page
- Select the data you wish to export
- Download the files to your computer
- Open the file and review the information or the orders