Imports - Credit loyalty points
This article explains how to perform automatic import to credit loyalty points (both Q and NQ). For instance, this allows you to credit your program members with points for activities performed outside of Splio.
The name of this import scope is "creditpoints".
Table of Contents
- Prerequisites
- Preparing a creditpoints import file
- Name the import file
- Explanation: Dates
- Explanation: NULL and erasing values
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 "creditpoints" import scope needs to be configured by your Project Manager from Splio.
Preparing a creditpoints import file
An import file is a CSV file composed of the following columns. 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.
- credit_date -- (mandatory) the date at which the points are being credited. See below to find a more thorough explanation of dates. If absent, the current datetime of the server is assumed (at GMT +1 for non-Chinese clients and GMT +8 for Chinese clients)
- context -- (mandatory) provides information where and why the points are credited
- card_code* -- (mandatory) the identifier of the loyalty card to which the points are being credited.
- nq_points -- the number of credited non-qualifying points.
- q_points -- the number of credited qualifying points.
/!\_ *Important! Even though neither nqpoints 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. If there is negative values, the points will be debited from the balance of the member.*
Example: a creditpoints file
Below you can find an example of a creditpoints import file. The first line is a header containing names of columns, the following 3 lines are data.
card_code;credit_date;nq_points;q_points;context mon_card_code;2019-01-01 00:00:01;-10;0;mon_context mon_card_code;2019-01-01 00:00:01;0;-10;mon_context mon_card_code;2019-01-01 00:00:01;0;0;mon_context
The third line will not be imported, since both points values are zero. Importing it would be pointless, as 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 reward 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. Both the date and the time parts must be complete – lines which, for instance, contain only hours and minutes will not be imported.
You have the option to use just the date (though you shouldn't). If you do so, Splio will assume the earliest possible hour for the day (00:00:00). Therefore,
2018-09-09
equals
2018-09-09 00:00:00
However, 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).
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.