This article introduces three scopes which allow you to import rewards data:
- masterreward – allows creating master rewards, which are then used for rewards attribution (like templates),
- earnreward – with which rewards are earned by loyalty cards, and
- burnreward – through which rewards can be spent (burned).
You will find 3 examples of files for these scopes at the end of the article.
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 import scopes listed above need to be configured by your Project Manager from Splio.
Preparing a file to import rewards
Files in the "masterreward" scope allow you to add master rewards to the catalog. When master rewards are used to attribute rewards, cardholders receive rewards that are exact copies of the master reward.
⚠️ | Please limit the size of your file to 200K objects.
The following columns are available in the "masterreward" scope:
|
Column |
Mandatory |
Format |
Description |
|
external_id |
Yes |
Text (max.128 characters) |
Unique key of master reward: lines with external_id values already in the base will be seen as attempts at update. |
|
name |
Yes |
Text (max.128 characters) |
Name of the reward. |
|
monetary |
Yes |
0 or 1 |
Takes "1" if the reward has a monetary value, "0" if not. If "1", you need to add "monetary_type" and "monetary_value". |
|
monetary_type |
No |
value from the list |
One of: "value" or "percentage". |
|
monetary_value |
No |
double |
Value of the reward in EUR. |
|
nqp_value |
Yes |
Positive integer |
Number of non-qualifying (NQ) points which have to be spent to acquire the reward. |
|
description |
No |
Text |
Text description of the reward. |
|
rich_description |
No |
Text |
Reward description using rich formatting. |
|
holding_days |
Yes |
Positive integer |
Number of days which need to pass between selecting a reward and having it at one's disposal. |
|
validity_interval_count |
Yes |
Positive integer |
Determines for how many days, months or years the reward remains valid. |
|
validity_interval_type |
Yes |
value from the list |
One of: "days", "weeks", "months", or "years". |
|
forced_validity |
Yes |
0 or 1 |
If "1", the reward has an absolute validity period (overriding the relative validity period of "validity_interval_count"). You must provide the "forced_validity_start" and "forced_validity_end" columns when using absolute validity. |
|
forced_validity_start |
No |
YYYY-MM-DD HH:MM:SS |
Date when the reward becomes valid; the time part is optional but will equal 00:00:00 if omitted. |
|
forced_validity_end |
No |
YYYY-MM-DD HH:MM:SS |
Date when the reward validity ends; the time part is optional but will equal 00:00:00 if omitted. |
|
is_limited |
No |
0 or 1 |
If "0", the autogeneration of rewards is unlimited. |
|
max_attribution_count |
No |
Positive integer |
Number of available units of the reward. Must be 0 or greater if "is_limited" is set to 1, or greater than 0 when "is_limited" equals 0. |
|
is_auto_generated |
No |
0 or 1 |
If "1", it allows Splio to generate coupon codes (the "max_attribution_count" should equal 0). |
|
image_url |
No |
Text |
Path to the file with the picture of the reward. |
|
c0 |
No |
|
A custom column defined in your universe for rewards. You can include up to 32 columns referred to as "c0" to "c31". |
💡 | The columns not in boldface represent database subfields. They contain data that may be required depending on the value in another field.
⚠️ | Remember that Splio will not import files with missing mandatory columns or columns it cannot recognize. Lines with empty values in mandatory columns will be skipped.
📗 | Also note that the values of "validity_period" and "validity_interval_type" fields can be overridden (forced) at the time of attribution.
Example 1: a "masterreward" file
The example below shows an import file using all standard columns. Columns that are empty, like ";;", are imported as NULL values.
external_id;name;monetary;monetary_type;monetary_value;nqp_value;description;rich_description;holding_days;validity_interval_count;validity_interval_type;forced_validity;is_limited;max_attribution_count
"mastertest_missing_price";"mastertest";;;"2";;;;"0";"1";"day";"0";;
"master_missing_auto_generation";"mastertest";;;"9";"10";;;"0";"1";"day";"0";;"0"
"master_bad_stock";"mastertest";;;"9";"10";;;"0";"1";"week";"0";"1";"0"
"masterlight";"salad";;;"9";"10";;;"0";"1";"month";"0";"1";"100"
"Masteritem_0_stock";"mastertest_item";;;;"10";;;"0";"1";"week";"0";;"0"
"masteritem_bad_stock";"mastertest_item";;;;"10";;;"0";"1";"week";"0";;
📗 | Note that most rows in this example will not import successfully. The "external_id" value indicates the issue, such as a missing price, missing or incorrect stock value. For example, the second line will not work, because the price is missing.
Preparing a file with rewards being earned
You can use "earnreward" files to represent the assignment (earning) of rewards to loyalty card holders. Technically speaking, earning a reward creates a link between the definition of the reward and a loyalty card.
The columns below are available in "earnreward" files:
|
Column |
Mandatory |
Format |
Description |
|
reward_id |
Yes |
Text |
Indicates the internal ID* of the existing master reward to earn, available in the interface of Splio. (* this ID can be found in the url of the reward page) |
|
card_code |
Yes |
Text |
Identifies the cardholder who earns the reward. |
|
attribution_id |
Yes |
Text (max.128 characters) |
Unique identifier of the connection between the reward and the card number (it is called attribution because the reward is attributed to the card). If autogenerated, this will be filled automatically, so it must be empty. |
|
quantity |
No |
Positive integer |
Number of reward items being earned. If not present, the default of 1 is assumed. If autogenerated, this will be filled automatically. |
|
earn_date |
Yes |
YYYY-MM-DD HH:MM:SS |
Date when the reward will be earned. This date needs to be the import day or after. If absent, the current datetime of the server is assumed (at GMT +1 for non-Chinese clients and GMT +8 for Chinese clients). |
|
context |
No |
Text |
Describes the situation where the attribution (earning) takes place. |
|
validity_start_date |
Yes |
YYYY-MM-DD HH:MM:SS |
Date when the reward becomes valid. |
|
validity_end_date |
Yes |
YYYY-MM-DD HH:MM:SS |
Date when the validity of the earned reward expires. |
|
burn_date |
No |
YYYY-MM-DD HH:MM:SS |
Date when the reward has been burned, e.g., 2021-02-12 08:30:00. If absent, the current datetime of the server is assumed. |
|
store_id |
No |
Text |
Identifier of a store related to the reward. |
⚠️ | Always use dates like 2021-02-12 08:30:22 (with hours, minutes, and seconds) for "earn_date", "validity_end_date", "validity_start_date", and "burn_date". When the datetime of the server is assumed, it is at GMT +1 for non-Chinese clients an GMT +8 for Chinese clients. See the "Explanation: Dates" section below to learn more.
📗 | Earn rewards imports do not use custom columns.
Example 2: an "earnreward" file
"reward_id";"card_code";"attribution_id";"quantity";"context";"earn_date";"validity_start_date";"validity_end_date"
"1066";"CC0001";"oc_0002750875";"1";"web";"2012-06-19 23:25:00";"2012-06-20 00:00:00";"2012-06-26 00:00:00"
"1086";"CC0003";"oc_0002750876";"1";"web";"2012-06-19 06:55:00";"2012-06-20 00:00:00";"2012-06-26 00:00:00"
"1455";"CC0002";"oc_0043302300";"1";"web";"2014-12-23 12:04:00";"2014-12-24 00:00:00";"2015-01-06 00:00:00"
"1455";"CC0004";"oc_0043243103";"1";"web";"2014-12-20 21:54:00";"2014-12-21 00:00:00";"2015-01-06 00:00:00"
"1455";"DD0001";"oc_0043244548";"1";"web";"2014-12-20 11:32:00";"2014-12-21 00:00:00";"2015-01-06 00:00:00"
"1455";"EE0001";"oc_0039303830";"1";"web";"2014-12-13 14:28:00";"2014-12-14 00:00:00";"2015-01-06 00:00:00"
"1455";"XXX00000";"oc_0043243488";"1";"web";"2014-12-20 09:10:00";"2014-12-21 00:00:00";"2015-01-06 00:00:00"
"1455";"abcde";"oc_0043302697";"1";"web";"2014-12-23 08:42:00";"2014-12-24 00:00:00";"2015-01-06 00:00:00"
"1455";"CC111";"oc_0043303913";"1";"web";"2014-12-23 12:54:00";"2014-12-24 00:00:00";"2015-01-06 00:00:00"
"1455";"SM123";"oc_0043286685";"1";"web";"2014-12-22 15:33:00";"2014-12-23 00:00:00";"2015-01-06 00:00:00"
Preparing a file with rewards being burned
A "burnreward" file contains information about rewards being "burned" by cardholders. In lay terms, burning a reward means it has been used or claimed by the cardholder. Speaking more technically, it changes not the status of "masterreward" data but of the information about earned rewards.
The following columns are available in "burnreward" files:
|
Column |
Mandatory |
Format |
Description |
|
card_code |
Yes |
Text |
Identifies the loyalty card to which the reward is assigned. |
|
context |
No |
Text |
Describes the situation (context) where the reward (its attribution) is being burned (used or canceled). |
|
attribution_id |
Yes |
Text |
Unique identifier created during the reward attribution. See "earnrewards" scope above. |
|
burn_date |
No |
YYYY-MM-DD HH:MM:SS |
Date when the reward has been burned, e.g., 2021-02-19 18:10:03. If absent, the current datetime of the server is assumed (at GMT +1 for non-Chinese clients and GMT +8 for Chinese clients). |
|
store_id |
No |
Text |
Identifier of a store related to the reward. |
⚠️ | Always use dates like 2021-02-12 08:30:22 (with hours, minutes, and seconds) for "burn_date".
📗 | Burn rewards imports do not use custom columns.
Example 3: a "burnreward" file
card_code;context;burn_date;attribution_id
CC0001;An incredible corgi designed bowl (empty unique_key);2018-08-09 11:03:05;NULL
invalidcardcode;An incredible corgi designed bowl;2018-08-09 11:03:05;test
CC0001;Welcome reward (valid burn);2018-08-09 10:00:05;EG2563
CC0003;Chocolate bone;2018-09-09 12:43:05;invalidattrid
CC0003;Chocolate bone(invalid date: future);2018-09-09 08:12:10;DE-0009-3981
CC0003;Chocolate bone(invalid date bis);2018-09-09;DE-0009-3981
CC0002;already_burned;2018-08-09 11:03:05;DE-0009-3981
CC0004;An incredible corgi designed bowl(valid burn);2018-08-09 08:12:10;BB-0001
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 ("masterreward", "earnreward", or "burnreward"), 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 always universe_scope_subsection_YYYYMMDD.csv. This means that import files including all three scopes from this article, in a universe "mycompany" and subsection "silver", dated on February 14, 2019, could be named like this:
mycompany_masterreward_silver_20190214.csv
mycompany_earnreward_silver_20190214.csv
mycompany_burnreward_silver_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 March 9th, 2021, 10:00:05 takes the following form:
2021-03-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.
⚠️ | It is crucial that you use complete date and time wherever possible (with the possible exception of birthdates).
If the time part is omitted, Splio will assume the earliest possible hour for the day (00:00:00). Therefore,
2018-09-09
equals
2018-09-09 00:00:00
⚠️ | This 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.