🌐

Other languages available for this guide

🇫🇷 La version française de ce guide
🇪🇸 La versión española de esta guía
🇮🇹 La versión italiana de esta guía

This article explains how to perform automatic import to credit loyalty points (both Q and NQ). Can be used to credit your program members with points for activities performed outside of Splio.

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 “creditpoints” scope
• Knowledge of the unique key of your universe
• Some understanding of loyalty data imported to Splio

Preparing a creditpoints import file

Prepare a CSV file containing columns from the table below. Columns marked with an asterisk "*" are mandatory. Splio will not import a file in which any mandatory columns are missing. Moreover, it will skip all rows in which the values for mandatory columns are not provided.

🚧

Please limit the size of your file to 200K objects.

📘

Important! Even though neither nq_points nor q_points is a mandatory column, at least one of these numbers must be present (and non-zero) in each line of the import file.

Example: a creditpoints file

Below you can find an example of a creditpoints import file. The first line is a header containing the names of columns, the following 3 lines are data.

card_code;credit_date;nq_points;q_points;context
"my_card_code";"2019-01-01 00:02:01";"-10";"0";"my_context"
"my_card_code";"2019-01-01 12:03:06";"0";"-10";"my_context"
"my_card_code";"2019-01-01 13:00:55";"0";"0";"my_context"

Because the first two lines contain negative numbers, importing them will decrease the number of points on the card. The third line will not be imported because both point values are zero (this line does not represent any change).

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 ("creditpoints"), 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 "creditpoints", and subsection "daily", dated on February 14, 2019, is:

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

🚧

• This should never be an option for dates and times and may result in errors and some triggers associated with dates will fail. Moreover, all loyalty data is always considered 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 CEST timezone.
• "birthday" is the only field where importing without hours is safe and recommended.

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 empty values.

Most importantly, the empty string "" is never considered a NULL value, so it will not cause an existing value to be erased.