Imports - Orders

This article will show you how to prepare import files for the "orders" scope. Records in this scope represent orders placed in stores, or receipts, which are then filled with "ordersitems" data.

Data in the "orders" scope may be updated with a later upload. Also, Splio will create a default (blank) "orders" record for each order_id found when importing "ordersitems" which does not exist in the database yet.

Please be aware that Splio begins processing imports in the "orders" scope by first removing all "ordersitems" for each "order_id" found in the import file.

Prerequisites

• Basic knowledge of the CSV format and UTF-8 encoding.
• The sub-sequence must be defined in the config file under the "orders" scope.
• A UTF-8-enabled text editor.
• A spreadsheet software.
• A file to re-import all "ordersitems" linked to each "order_id" you are updating.

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.

Header and Columns

The first line of the file is called the header. It should only contain the names of the columns used in the remaining lines. If Splio encounters a column name it cannot recognize, it will not import the file.

The following columns are available in the "orders" scope:

• order_id -- the external identifier of the imported order, e.g., the unique number of a receipt; this column is mandatory and must be unique for each order.
• customer_key -- the special column used by Splio to identify contacts in the database; this column is mandatory. It will be discussed shortly in the examples below. Please refer to the Imports - Contacts guide for more details.
• store_id -- the external id of the store where the order has been placed, creating a relationship between "orders" and "stores". This column is mandatory and must contain references to previously imported stores.
• order_date -- the date when the order was placed. See "dates" below for more details on formatting.
• card_code -- the number of a loyalty card linked to the order. It must refer to an existing loyalty membership card. See "Example 3: Loyalty orders" below for more details.
• shipping_amount -- represents the amount on the receipt owed as payment for shipping the order.
• discount_amount -- the total discount applied to the order.
• tax_amount -- the total amount of tax (VAT, sales tax) applied to the order.
• total_amount -- the total amount paid.
• currency -- 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 -- the salesperson related to the order.
• A custom column, if defined for orders in your Splio universe.

Note that column names are always lowercase.

Please remember that Splio checks import files for mandatory columns. You need to include "order_id", "store_id" and "customer_key" 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 

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, only the kind of values in the "customer_key" is different. You must always use the kind of customer key which is defined in your Splio universe.

Example 3: 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 in this example show "card_code" as an extra column. It is enough to use such a file to import "loyalty orders".

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_20180627.csv

This filename belongs to the universe "myuniverse", sub-sequence "zoo" defined for orders, and is dated June 27, 2018.

If you wish to know more, consult the "File Naming and Grouping" section in the [Overview] document.

You can now upload the file to SFTP/FTPS.

Dates

Each date consists of 4 digits for a year, 2 for a month, and 2 for a day, followed by hours, minutes, and seconds, 2 digits each. A correct date for March 15th, 2018, 1:17 pm will take the following form:

2018-03-15 13:37:00

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-03-07

is treated exactly as if you entered

2018-03-07 00:00:00

Advanced Information

This part of the document contains additional information which supplements the guide above and provides more detail.

File format

If a date field in the file does not comply with the above format, the import will not be processed.

Columns

The sections below describes columns (fields) available for use when constructing or editing orders files.

/!\ Remember that all column names are case sensitive. For this reason, default names are all lowercase.

The import feature in Splio allows using custom fields which have been configured in your universe. To include a custom field in the header, use the technical name of the field (lowercase letter "c" followed by its numerical id).

For example, if you have a field called "promo" in your "orders" table, and its id is 5, you should use "c5" as the name of the column. You can include as many custom fields (columns) in your import files as defined for the given scope in your Splio universe. Each column name should be used only once.

Also, bear in mind that if Splio cannot recognize the name of a column, it will not import the file.

Monitoring

The following information is made available by Splio after import.

• The number of OK and KO lines
• The number of created and updated lines (the sum will be the OK lines)

But the data is not available if:

• The import is aborted or not processed
• The reason why a line was skipped

When present in the imports/ folder, where you upload the .csv to import, you need to go to the logs/ folder to find the imports logs. Similarly, the imported files are located in /imports/archives/.