Imports - Loyalty subscriptions

This article explains the automatic import of loyalty program subscriptions. Subscriptions are similar to loyalty cards: each one represents a program member and can connect the program membership with a person.

The name of this scope is "cardcode", and importing a subscriptions file adds new members to a loyalty program.

Table of Contents

• Prerequisites
• Preparing a subscriptions import file
  • Subscription file example
• Name the import file
• Explanation: Dates
• Explanation: NULL and erasing values
  • Difference between "NULL" and empty string

Prerequisites

• Knowledge of the CSV format and the import procedure.
• A UTF-8 capable text editor or spreadsheet software.
• Some understanding of loyalty data imported to Splio.
• The "cardcode" scope needs to be configured by your Project Manager from Splio.

In this example, reports and alerts will be sent to the given addresses. All lines with errors will be reported, and NULL values found in the import will overwrite information in the database.

Compare the following file name which conforms to the settings above:

mycompany_cardcode_bronze_20190211.csv

(back to the top)

Preparing a subscriptions import file

A subscriptions import file is a CSV file containing columns from the list below. Columns marked with an asterisk "*" are mandatory. Splio will not import a file which does not contain all mandatory columns. Also, all rows in which the values for mandatory columns are missing will be skipped as errors.

• customer_key* -- a special column which identifies the cardholder. At the minimum, it usually contains the holder's email address. You can learn more about using this column in the Imports - Contacts file.
• card_code* -- the identifier of the loyalty card. The codes are unique: if you specify an existing code, the record will be updated. This column is mandatory, but a Splio universe can be configured to auto generate cardcodes. If it is so, Splio will supply missing "card_code" values.
• id_program* -- the ID of the loyalty program to which the subscription belongs. It is a number you should receive from your Project Manager at Splio. You can also find it in the URL. This column should only be used to import new subscriptions. Make sure to remove it when importing updates: all attempts to update "id_program" values will fail, causing lines to be skipped.
• join_date* -- the date when the cardholder joined the program. See below to find a more thorough explanation of dates.
• expiration_date -- the time when the subscription loses validity.
• birthday -- a date (without hour) which reveals the cardholder's birthday. Used in many sub-operations.

Subscription file example

The example below shows an import file. Columns that are empty, like ";;", are imported as NULL values.

card_code;customer_key;id_program;join_date;expiration_date;birthday;c0;c1
mon_card_code;mostly@email.fr;1;2019-01-01 00:00:01;2020-01-01 00:00:01;1955-02-01;;
;mostly@email.fr;3;;;;;
mon_card_code1;mostly@email.fr;1;;;;;

There are no obvious errors in this file. The first line contains much more information than the other two. Note, however, that in the middle line there is no card code: if the auto cardcode generation is enabled, Splio will provide the number. If not, this line will cause an error and will not be imported.

Name the import file

Splio requires that you name your import files in a specific way. Each file name must contain the name of the universe, scope ("cardcode"), subsection (you should have obtained it from your Project Manager), and date. The order in which the files are processed depends on the scopes and dates.

The naming schema is universe_scope_subsection_YYYYMMDD.csv. This means that the proper name for an import file in a universe "myuniverse", scope "cardcode", and subsection "customers", dated on February 14, 2019, is:

myuniverse_cardcode_customers_20190214.csv

Explanation: 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

You should always consider all loyalty data to be financial information. It means that you and your company are accountable for these data and cannot afford to leave details to chance. Therefore, it is crucial that you use complete date and time wherever possible (with the exception of birthdates).

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).

(back to the top)

Explanation: NULL and erasing values

"NULL" is a special value that tells the database that the field holding it is empty.

When the option "overwrite with empty values" is set to "1", Splio will interpret "NULL" values as instructions to empty fields. If the option is set to "0", Splio will retain the values for the fields where the imported value is "NULL".

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.

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.

(back to the top)