Imports - Orders items

This article will show you how to prepare files for import in the "ordersitems" scope. Representing individual items in receipts or shopping carts, orders items are used to fill the contents of Orders and Abandoned carts.

Records in the "ordersitems" scope are not updated. Instead, you need to be prepared to reimport all orders items for each "order_id" you update in the "orders" or "abandonedcarts" scope. Updates to these two scopes always result in Splio purging all associated orders items (it is one of the initial steps of import).

Table of Contents

• Prerequisites
• Preparation of an Ordersitems file
  • Header and Columns
  • Example of an Ordersitems file
  • Name your file
• Advanced information
  • File format
  • Columns
  • Monitoring

Prerequisites

• Basic knowledge of the CSV format and UTF-8 encoding.
• The sub-sequence must be defined in the config file under the "ordersitems" scope.
• A UTF-8-enabled text editor.
• A spreadsheet software.

Preparation of an Ordersitems file

Use your favorite spreadsheet software or UTF-8 enabled text editor to prepare and edit the file. Spreadsheet is the recommended tool whenever you need to trim or move columns.

Remember to save your file in the CSV format using UTF-8 encoding.

Header and Columns

The first line of the file, called the header, is used to determine the contents of the following lines. Therefore, it should be constructed only from the names of columns.

Remember that if Splio encounters a column name it cannot recognize, it will skip the file.

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

• order_id -- The external identifier of an order, this column binds "ordersitems" to specific "orders" (or "abandonedcarts"); this column is mandatory and should reference an already existing order, otherwise a blank "orders" entry will be created.
• product_id -- the external id of the product on the receipt or in the shopping cart. This column is mandatory. If the product does not yet exist in the data base, Splio will create a default (empty) record. Updating products does not erase associated orders items.
• unit_price -- The price charged for a single unit of the product.
• quantity -- The quantity of the product.
• discount_amount -- The amount of discount applied to the product.
• tax_amount -- The amount of tax applicable to the product.
• total_line_amount -- The total amount of the line (i.e., "ordersitems" record); normally equals unitprice times quantity minus discountamount plus tax_amount.
• currency -- Three-letter ISO code of the currency in which all prices and amounts are given. If absent the universe's default is used.
• A custom column, if defined for the "ordersitems" scope in your Splio universe. You can include as many custom columns as you need. The names of custom columns are composed of the lowercase letter "c" and the number of the corresponding custom field, e.g., "c15".

Note that all column names are always lowercase.

Please remember that Splio checks import files for mandatory columns. You need to include both "orderid" and "productid" for the import to succeed.

Example of an Ordersitems file

Examine the following example of a file containing 3 items which belong to the same order:

order_id;product_id;unit_price;quantity;tax_amount;total_line_amount;currency;c3
70x1bMhtt-1531745300;00131300020004;182.49;1;36.5;218.99;EUR;Accessories
70x1bMhtt-1531745300;0027011018374;9.16;5;9.16;54.96;EUR;Food
70x1bMhtt-1531745300;0077890206324;154.5;2;61.8;370.80;Accessories

The fields represent the unique identifier of the receipt, identifier of the product, its net price, quantity, applied tax, sum total of each line, and a custom category field.

Name your file

When saving your file, use a name composed of the universe name, scope ("ordersitems"), sub-sequence, and current date. For example:

myuniverse_ordersitems_pets_20180712.csv

This filename belongs to the universe "myuniverse", sub-sequence "pets" defined for orders, and is dated July 12, 2018. Note that this date marks the file, it does not have to correspond to the items.

If you wish to know more, consult the File Naming and Grouping guide.

You can now upload the file to SFTP/FTPS.

(back to the top)

Advanced Information

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

File format

Columns

The sections below describes columns (fields) available for use when constructing or editing ordersitems 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.

Important: please note that when uploading your product prices, you must enter two decimals after the dot (i.e 10.99).

Monitoring

The following information is made available by Splio after import.

• Whether the import was aborted or not and, if aborted, the reason (unknown column, missing external id column, undeclared custom field)
• The number of lines treated (imported+skipped)/imported/skipped
• The number of new records
• The reason each line was skipped (productid NULL, too many/not enough columns, orderid or product_id not found in the database)

Normally, a log file will be available in the same repository where the import file was uploaded.