Abandoned carts
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 explains how to prepare a file for the “abandonedcarts” scope.
Files in the “abandonedcarts” scope are very similar to “orders” file, as abandoned carts represent purchase orders which have never been completed. Also, like in the case of orders, if there are any items in the carts, you will have to import them as “ordersitems”.
Abandoned carts data are usually collected by online stores, which need to store customers’ selections in virtual shopping carts and can easily identify the carts that have never been checked out.
Data in the “abandonedcarts” scope may be updated with later uploads. Splio will reject any "orderitem" record if the linked order is not created yet.
Please be aware that Splio begins processing imports in the “abandonedcarts” 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.
- A UTF-8-enabled text editor.
- A spreadsheet software.
- The sub-sequence must be defined in the config file under the “abandonedcarts” scope.
Preparation of an Abandoned carts file
Edit the import file with your favorite UTF-8-capable text editor. If necessary, use a spreadsheet software you are comfortable with to control the number and position of columns.
- 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, called the header, is used to determine the content 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 “abandonedcarts” scope:
| Column | Mandatory | Data Type / Maximum length | Description |
|---|---|---|---|
| order_id | Yes | Text (max. 50 characters) | The external identifier of the imported abandoned cart. This value must be unique for each cart. |
| customer_key | Yes | Text | Special column used by Splio to identify contacts in the database. |
| card_code | No | Text | Code of a loyalty card, creates a link between the abandoned cart and the loyalty card. |
| store_id | Yes | Text (max. 50 characters) | external id of the store where the cart was created and abandoned, relates “abandonedcarts” and “stores”. Values in this column must refer to already imported stores. |
| order_date | No | Date | Date associated with the abandoned cart. See “Dates” below for more details on formating. To avoid errors, always fill hours, minutes, and seconds. |
| shipping_amount | No | Decimal | Represents the amount on the order owed as payment for shipping an order with the same content as the cart. |
| discount_amount | No | Decimal | The total discount amount applied to the cart. |
| tax_amount | No | Decimal | The total amount of tax (VAT, sales tax) applied to the cart. |
| total_amount | No | Decimal | The total amount paid for items in the cart (and shipping), minus the discount. |
| currency | No | Text (max. 3 characters) | The 3-letter code for the currency of the abandoned cart; 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 cart. |
| 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 “abandonedcarts” 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 shopping carts, the “customer_key” links the order with the contact who created and abandoned the cart.
Example 2: Custom column as Customer Key
Compare an import file using a custom column as the customer key with Example 1 above:
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"
Both imports are very similar, only the kind of values in the “customer_key” is different. You must always use the customer key that is required by your Splio universe.
Name your file
To save your file, use a name composed of the universe name, scope (“abandonedcarts”), sub-sequence, and current date. For example:
myuniverse_abandonedcarts_zoo_20210226.csv
This filename belongs to the universe “myuniverse”, sub-sequence “zoo” defined for abandoned carts, and is dated February 26, 2021.
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 March 15th, 2021, 1:37 PM takes the following form:
2021-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-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 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.
Updated 4 months ago