Orders
Other languages available for this guide
🇫🇷 La version française de ce guide
🇪🇸 La versión española de esta guía
🇮🇹 La versione italiana di questa guida
This article describes how to prepare import files for the “orders” scope. Records in this scope represent orders placed in stores, which are then filled with “ordersitems” data.
Data in the “orders” scope may be updated with a later upload. If you import an order that has the same ID as an existing one, the new order will replace the existing one, and all orderitems linked to the existing order will be deleted. Splio will reject any "orderitem" record if the linked order has not been created yet.
Prerequisites
- Basic knowledge of the CSV format and UTF-8 encoding
- A UTF-8-enabled text editor
- A spreadsheet software
- Creation of the sub-sequence in the config file under the “orders” scope
Preparation of an Orders file
Edit the import file with your favorite UTF-8 capable text editor. If necessary, control the number and position of columns with a spreadsheet software you like. It is the best tool to remove any columns you do not want to import.
- Always remember to save using the UTF-8 encoding without BOM.
- Please limit the size of your file to 200K objects.
Header and Columns
The first line of the file is called the header. It should only contain the names of the columns found in the table below.
Remember that if Splio encounters a column name it cannot recognize, it will skip the file.
The following columns are available in the “orders” scope:
| Column | Mandatory | Data Type / Maximum length | Description |
|---|---|---|---|
| order_id | Yes | Text (max. 50 characters) | The external identifier of the imported order (the unique number of an order). This value must be unique for each order. |
| customer_key | Yes | Text | Special column used by Splio to identify contacts in the database. If this is the email address, it must be in lowercase. |
| card_code | No | Text | Code of a loyalty card, creates a link between the order and the loyalty card. See “Loyalty orders” below for more details. |
| store_id | Yes | Text (max. 50 characters) | external id of the store where the order has been placed, creating a relationship between “orders” and “stores”. Values in this column must refer to already imported stores. |
| order_date | No | Date | Date when the order was placed. See “Dates” below for more details on formating. To avoid errors, always fill hours, minutes, and seconds. |
| shipping_amount | No | Decimal | Represents the amount due as payment for the shipment of the order. |
| discount_amount | No | Decimal | The total discount amount applied to the order. |
| tax_amount | No | Decimal | The total amount of tax (VAT, sales tax) applied to the order. |
| total_amount | No | Decimal | The total amount paid for items in the order (and shipping), minus the discount. |
| currency | No | Text (max. 3 characters) | The 3-letter code for the currency of the order; applies to all amount values. If no currency is given, Splio will assume the default currency of the universe. |
| salesperson | No | Text (max. 120 characters) | The salesperson related to the order. |
| c0 | No | A custom column defined in your universe for orders. You can include up to 32 columns referred to as “c0” to “c31”. |
- Note that all column names are always lowercase.
- The customer_key column identifies contacts in the database of your Splio universe.
- Please remember that Splio checks import files for mandatory columns. You need to include “order_id”, “customer_key”, and “store_id” for the import to succeed.
Example 1: Default Customer Key
The first lines of an “orders” import file using the default customer key (contact’s email) may look like this:
order_id;customer_key;store_id;order_date;total_amount;currency
"70x1bMhtt-1531745300";"[email protected]";"Internet";"2018-06-22 11:30:00;173.00";"EUR"
"70x1byTRJ-1531778200";"[email protected]";"Internet";"2018-06-21 12:10:00";"244.99";"EUR"
"70xb1KLio-1531723300";"[email protected]";"Internet";"2018-06-20 17:33:00;25.50";"EUR"
Each line consists of exactly 6 columns. The “order_id” is used to distinguish between orders, the “customer_key” links the order with the contact who purchased it.
Example 2: Custom column as Customer Key
Consider an import file using a custom column as the customer key:
order_id;customer_key;store_id;order_date;total_amount;currency
"70x1bKkUt-1531738300";"PPL000000045732";"MGZOO;2018-06-27 12:17:27;225.00";"EUR"
"70x1bKooY-1531738300";"PPL000000007633";"MGZOO;2018-06-27 12:19:23;17.00";"EUR"
"70x1bu9Gt-1531756600";"PPL000000045661";"MGZOO;2018-06-27 12:22:07;112.50";"EUR"
This import is very similar to the one in Example 1, the main difference is the type of values in the “customer_key” column. You must always use the same type of customer key as defined in your Splio universe.
Name your file
To save your file, use a name composed of the universe name, scope (“orders”), sub-sequence, and current date. For example:
myuniverse_orders_zoo_20210225.csv
This filename belongs to the universe “myuniverse”, sub-sequence “zoo” defined for orders, and is dated February 25, 2021.
Dates
All dates used in subscription import files need to be formatted in the following way: 4 digits for year, 2 for month, and 2 for day, followed by hours, minutes, and seconds, 2 digits each. A correct date for August 9th, 2018, 10:00:05 takes the following form:
2018-08-09 10:00:05
The day and hour are separated by a blank space. You can omit the time part and only use the date. If you do, Splio will assume the earliest possible hour for the day (midnight). Therefore,
2018-09-09 equals 2018-09-09 00:00:00
- Using dates without time should never be an option for dates and times: 00:00:00 is midnight and Splio will try to process the date as such. This may result in errors and some triggers associated with dates will fail. Moreover, all loyalty data is always considered as financial information. It means that you and your company are accountable for these data and cannot afford to leave details to chance.
An additional benefit of using complete dates is that you will be able to search and filter by date with much greater accuracy.- When importing dates with time, make sure to always use the CEST timezone.
Explanation: NULL and erasing values
NULL is a special value that tells the database that the field holding it is empty.
Your Splio universe can be configured to interpret NULL values as instructions to empty fields. You can use this to erase values stored in the database. To do so, make sure that the imported value is exactly NULL. You need to avoid leading or trailing spaces: NULL or NULL will be recognized as string values.
If this option is not set, Splio will retain the values for the fields where the imported value is NULL.
Difference between NULL and empty string
"" is an empty string. In most cases, both NULL and "" will be imported as an empty value.
Most importantly, the empty string "" is never considered a NULL value, so it will not cause an existing value to be erased.
Updated 4 months ago