- Settings
- Import/Export
- Order History Importer
Order History Importer
PC users: Excel files must be saved in .xls format (excel workbook).
Mac users: Excel files must be saved in .xlsx format (excel workbook)
Mac users: Excel files must be saved in .xlsx format (excel workbook)
Always download a fresh copy of the order history sample file. When working with the Order History Importer Vin65 recommends reviewing how the importer imports order data before importing all of your order history. While the documentation found on this page will review how to work with the importer, many find it helpful to see exactly how their data translates into Vin65 before moving forward.
Remember once an order has been created or imported it cannot be deleted. For this reason it is best to import one or two test orders using the Order History Importer to confirm that your order data is importing correctly. Two test orders are included in the Order History Sample File. To help you understand exactly where each field is imported to in the order we suggest reviewing a live or test order from the Store > Orders section of your admin panel. This will help you to visualize what the data will look like after it has been imported.
Remember once an order has been created or imported it cannot be deleted. For this reason it is best to import one or two test orders using the Order History Importer to confirm that your order data is importing correctly. Two test orders are included in the Order History Sample File. To help you understand exactly where each field is imported to in the order we suggest reviewing a live or test order from the Store > Orders section of your admin panel. This will help you to visualize what the data will look like after it has been imported.
Make sure you highlight the sample data cells ( leaving the titles ), right click and select DELETE - Entire Row (do not clear them). If you select clear and do not select DELETE then when you import your data blank product SKU records will appear in your orders/reports.
The Order History Importer allows you to import historical order data from your existing systems into the Vin65 platform. Historical data are orders that have already taken payment in a system other than Vin65 and you are simply looking to import a record of the order to maintain a customer's order history and life time value (LTV). It will not affect inventory. If you are looking for a solution to import new orders that have yet to been created or charged you will want to review the Order Importer documentation.
Before you will be able to import your historical order data you will have to fill out the Order History Importer Sample File so your data matches the Vin65 system and are in the correct format for the import.
Download Sample File: Learn about using our sample files for your order history import. Learn More >
Filling out the Sample File: Learn how to correctly fill out the sample file. Learn More >
Importing your Order History: Learn how to import your order history into your admin panel. Learn More >
Set A Specific Order Number: Learn how to set a specific order number to start from. Learn More >
Download Sample File
1. Navigate to the Settings > Import/Export > Order History Importer.
2. Click Download Sample File to download the spreadsheet we will use.
Filling out the Sample File
For import speed and stability, please keep your excel file to 5000 lines or less. If you are importing a large amount of order history you will need to run multiple imports.
Each line in the Order History Importer is used for a different wine/product SKU that was purchased in an order. So an order for one product will take one line in your Excel spreadsheet; an order with x products will take up x lines in your Excel spreadsheet.
The green fields at the end of the spreadsheet (4 columns) will be different for each line in the order as these fields are used for each of the individual wines/products within the order.
Below is a list of all of the fields within the Order History Importer spreadsheet and their definitions:
Australian clients would need to adjust the Date format for any Date related columns to DD/MM/YYYY
| *OrderNumber | The order number for this historical order. Order numbers in Vin65 are numeric only. |
|---|---|
| OrderDate | The date of the order. Format is in: MM-DD-YYYY or DD-MM-YYYY.. |
| *OrderType |
This field allows one to indicate where the order originated from. Some possible order types may include:
Other types may exist (if they have been set up in your Admin Panel > Settings > Misc > Order Types). |
| BillCustomerNumber | Billing contact's customer number. This is the primary method the order history will be matched to contact records in Vin65. If no customer number is included, orders will be matched with a customer record using the billing email address. If no customer record is found that matches, a new contact record will be created. |
| BillBirthDate |
Billing contact's birth date. Format is in: MM/DD/-YYYY or DD-MM-YYYY. |
| BillFirstName | Billing contact's first name. |
| BillLastName | Billing contact's last name. |
| BillCompany | Billing contact's Company. |
| BillAddress | Billing contact's address. |
| BillAddress2 | Additional information regarding the billing contact's address. |
| BillCity | Billing contact's city. |
| BillStateCode |
Billing address State/Provice code. Two character abreviated state or province code. Example:
|
| BillZipCode |
Billing contact's address zip code. |
| BillPhone | Billing contact's phone number. Format is in: 000-000-0000. |
| BillEmail | Billing contact's email address. Used as a secondary means of matching order history to customer records. If no customer number is included orders will be matched with a customer record using the billing email address. If no customer record is found that matches a new contact record will be created. |
| isPickup | This field indicates whether this order is a pickup order or not. Format is in: Yes or No. |
| PickupDate | The date in which the pickup order was picked up. Format is in: MM/DD/YYYY or DD/MM/YYYY. |
| PickupLocationCode | If you selected Yes for isPickupAtWinery, you must enter the code for the pickup location where the club member will be picking up thier shipment. The codes for your PickUpLocations may be found in Settings > Misc > Pickup Locations. |
| ShipBirthDate | Shipping recipient's birth date. Format is in: MM-DD-YYYY or DD-MM-YYYY. |
| ShipFirstName | Shipping recipient's first name. |
| ShipLastName | Shipping recipient's last name. |
| ShipCompany | Shipping recipient's company name. |
| ShipAddress | Shipping recipient's address. |
| ShipAddress2 | Additional information for the shipping recipient's address. |
| ShipCity | Shipping recipient's city. |
| ShipStateCode |
Billing address State/Provice code. Two character abreviated state or province code. Example:
|
| ShipZipCode | Shipping recipient's zip code. |
| ShipPhone | Shipping recipient's phone number. Format is in: 000-000-0000. |
| ShipEmail | Shipping recipient's email address. |
| ShippingStatus |
The shipping status for the order. This allows you to mark the order as being shipping, or indicate another shipping status where applicable. If left blank, no shipping status will be imported. Must be one of the following:
|
| ShipDate | The date the order shipped. Format is in: MM/DD/YYYY or DD/MM/YYYY. |
| Carrier |
The carrier that was used to ship the order. If the carrier used is not available in the provided list, please use 'Other'. Must be one of the following:
|
| TrackingNumber | The tracking number associated with a shipped order. |
| SourceCode | Source Codes are only visible in Vin65 sales reports and are used for internal winery tracking. This will not display directly on the order and is not visible to customers. |
| PaymentType |
The payment type used for the order. If left blank, no payment type will be imported. Must be one of the following:
|
| CreditCardType |
The credit card type must be one of the following, if the payment type was selected to be credit card:
|
| CreditCardNumber | The credit card number used for the order. |
| NameOnCard | The name of the credit card holder. |
| ExpiryMonth | A one or two digit month. Format is in: 1 or 12. |
| ExpiryYear | A four digit year. |
| OrderNotes | Notes field for special shipping instructions for the order. |
| GiftMessage | Gift message included on the order. |
| OrderSubtotal | The order subtotal should be equal to the total value of all wines/products for the order, excluding shipping and taxes. Orders containing more than one product will take up multiple lines in your excel sheet. This amount should be duplicated in all rows pertaining to one order number. All white (neutral) columns will contain the same data, so make sure that you place the same subtotal in each line for a single order. Green columns will be different totals as they pertain to each individual product in the order. |
| OrderShipping | The order shipping should be the total value of any shipping fees associated with the order. Orders containing more than one product will take up multiple lines in your excel sheet. This amount should be duplicated in all rows pertaining to one order number. All white (neutral) columns will contain the same data, so you can place the same shipping total in each line for a single order. Green columns will be different totals as they pertain to each individual product in the order. |
| OrderHandling | The order handling should be the total value of any handling fees associated with the order. Orders containing more than one product will take up multiple lines in your excel sheet. This amount should be duplicated in all rows pertaining to one order number. All white (neutral) columns will contain the same data, so make sure that you place the same handling total in each line for a single order. Green columns will be different totals as they pertain to each individual product in the order. |
| TaxIncluded | Whether or not tax (GST) is included in the Order. Must be: Yes or No. Available for Australian clients only. |
| OrderTax | This should equal the taxes charged on the order. This amount should be duplicated in all rows pertaining to one order number. |
| OrderTotal | This should be the total charge for the order. This amount should also be duplicated in all rows pertaining to one order number. |
| ItemSKU | The SKU number for the wine/product that was ordered. SKUs can be numeric or alphanumeric. Orders containing more than one product will take up multiple lines in your excel sheet. All white (neutral) columns will contain the same data. Green columns such as this one will be different as they pertain to each individual product in the order. |
| ItemName | The name of the wine/product ordered. Orders containing more than one product will take up multiple lines in your excel sheet. All white (neutral) columns will contain the same data. Green columns such as this one will be different as they pertain to each individual product in the order. |
| ItemQuantity | The quantity of the wine/product ordered. Orders containing more than one product will take up multiple lines in your excel sheet. All white (neutral) columns will contain the same data. Green columns such as this one will be different as they pertain to each individual product in the order. |
| ItemUnitPrice | The price per unit for the wine/product ordered. Orders containing more than one product will take up multiple lines in your excel sheet. All white (neutral) columns will contain the same data. Green columns such as this one will be different as they pertain to each individual product in the order. |
*Mandatory Fields: OrderNumber, OrderDate and OrderType.
Unlike the order importer, order history doesn't have a lot of required fields. Fields like product SKU, product type, etc. don't need to be setup in the admin for a history import (but they would for a order import). If you're importing historical orders and intending to send them over to compliance, you will need to have the SKUs, etc. setup in the admin panel and compliance (it will let you import it without it setup and a compliance will fail - compliance checks are optional).
Australian clients would need to adjust the Date format for any Date related columns to DD/MM/YYYY
Importing your Order History
1. Navigate to Settings > Import/Export > OrderHistory Importer in your Admin Panel.
2. Click on Browse to load the file from your computer.
3. Click on Import to upload the order history to your Vin65 database.
Importing a Refund
A refund will be filled out similar to a purchase order, with certain fields being inputted with negative values. The following fields will need negative values: OrderSubtotal, OrderShipping, OrderHandling, OrderTax, OrderTotal, ItemQuantity.
Items will not be put back into inventory. A manual adjustment will need to be done for returned products.
Set a Specific Order Number to Start From
When setting up your website with Vin65 or when moving data over from an existing order management system, you will want your Vin65 orders to start at a specific order number. The Vin65 platform, by default, starts your first order at number 1001. You will need to import a 'dummy' order to set the order numbers to where you want them to start. Below is an quick guide:
Let's say we want to set the order number to start at 5001.
1. Navigate to Settings > Import/Export > Order History Importer in your Admin Panel.
2. Download the Sample File.
3. In the sample file spreadsheet, populate it with a fake order and indicate that it's OrderNumber = 5000 and OrderType = AdminPanel. Save your spreadsheet afterwards.
4. Import this spreadsheet with the fake order. After doing so, the next live order that comes in will have an order number 5001.
Once your start order number has been set you cannot enter an order through the Vin65 admin panel with a number that preceeds your start order number (i.e. start order number is 5000, you cannot enter an order with 4999 as it's order number).