Datahub - Orders

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 which 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 is not created yet.

You will find an example of a file for this scope at the end of the article.

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:

📗 | Note that all column names are always lowercase.

💡 | The customer_key column identifies contacts in the database of your Splio universe. See Imports - Contacts to learn more about how it works.

⚠️ | 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";"misterspots@examplemail.org";"Internet";"2018-06-22 11:30:00;173.00";"EUR"
"70x1byTRJ-1531778200";"lady@examplemail.org";"Internet";"2018-06-21 12:10:00";"244.99";"EUR"
"70xb1KLio-1531723300";"8sk5k7g87@examplemail.org";"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.

Loyalty orders

You can create connections between orders and loyalty memberships by adding the “card_code” colum. The example below shows an import file with “card_code” values.

💡 | It is enough to use such a file to import “loyalty orders”.

Imported orders can be tied to loyalty memberships simply by including the card number (“card_code”). Orders imported this way will then appear in the “Loyalty” section of contact data and, among others, affect the number of points earned by the contact as a member.

order_id;customer_key;store_id;card_code;order_date;total_amount;currency
72cvSYhmJ-1537635000;72RqRjczN-1537005545@nomail.xl.cx;Internet;72RqRjeki;2018-09-22 13:37:48;262.19;EUR
1562029508;72TSdyzYr-1537097372@yopmail.com;Internet;72TSdz0iy;2019-07-01 10:03:45;371.95;EUR
73XAxEvFX-1540830000;73F3Q9dzx-1539800401@mailnesia.com;Internet;73F3Q9nHM;2018-10-29 17:33:00;14.92;EUR

📗 | Orders imported with a “card_code” are “loyalty orders” (orders bound to a loyalty card). They can be found in the “loyalty” section of contact data affect the number of points earned by the contact as a member, and can be processed by loyalty rules.

💡 | You can import “card_code” values with either “orders” or “ordersitems” for loyalty events. However, you must import the card codes with “ordersitems” if you want to use loyalty rules based on products.

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.

If you wish to know more, consult the “File Naming guidelines” section in the Overview document.

You can now upload the file to SFTP/FTPS.

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.

💡 | “birthday” is the only field where importing without hours is safe and recommended.

📗 | 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 same timezone (it is GMT+1 for clients outside of China, and GMT+8 for Chinese clients).

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.