This article explains the automatic import of loyalty program subscriptions. Subscriptions are loyalty card codes, each one represents a program membership that can be connected to a person. Importing a subscriptions file adds new members to a loyalty program. You will also see here how to change the tier of a batch of Loyalty members.
You will find an example of a file for this scope at the end of the article.
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 “cardcode” scope
- Knowledge of the unique key of your universe
- Some understanding of loyalty data imported to Splio
Preparing a subscriptions import file
⚠️ | Please limit the size of your file to 200K objects.
Prepare a CSV file containing columns from the table below.
|
Column |
Mandatory |
Format |
Description |
|
card_code |
Yes |
Text |
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 in EAN13 format. In this case, the column can have an empty value (but you still need to include it in the file). |
|
customer_key |
Yes |
Text |
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. If empty, the cardcode will not be linked to a contact. |
|
id_program |
Yes |
Positive integer |
ID of the loyalty program to which the subscription belongs. It is a number you should receive from your contact 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 |
No |
YYYY-MM-DD HH:MM:SS |
The date when the cardholder joined the program. If empty, will be filled with the time of the import. See below to find a more thorough explanation of dates. |
|
expiration_date |
No |
YYYY-MM-DD HH:MM:SS |
The date when the subscription loses validity. |
|
birthday |
No |
YYYY-MM-DD |
Date (without hour) which reveals the cardholder's birthday. Used in many sub-operations. |
|
id_tier |
No |
Positive integer |
Tier ID to put the card code in a given tier (including VIP tiers). If empty, it will give the lowest tier of your program. It can work only for the creation of the cardcode, and not for an update. |
⚠️ | The order of the fields in the cardcode import is essential and must be: "card_code";"customer_key";"id_program";"join_date";"custom_fields"
📗 | 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. See also notes for individual columns above.
Subscription file example
The example below shows an import file. The ;NULL; values are explained below.
card_code;customer_key;id_program;join_date;expiration_date;birthday;id_tier
"my_card_code";"mostly@email.fr";"1";"2019-01-01 00:00:01";"2020-01-01 00:00:01;1955-02-01";"2";
"mostly@email.fr";"3";;;;"2"
"my_card_code1";"mostly@email.fr";"1";;;;"4"
📗 | There are no obvious errors in this file, though the first line contains much more information than the other two. Note 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.
Tier change
Importing card codes can also serve the purpose of changing the tier of a batch of members of your Loyalty program.
To do so, please prepare a file with these fields:
|
Column |
Mandatory |
Format |
Description |
|
card_code |
Yes |
Text |
see above |
| tier_name | Yes | Text | the name of the tier |
| context | No | Text | the reason why you changed the tier |
|
value_date |
Yes |
YYYY-MM-DD HH:MM:SS |
The date when the member changes from a tier to another. |
| last_downgrade_check | No | YYYY-MM-DD HH:MM:SS | Specify the last downgrade check date - will be taken into account for periodic downgrade checks (daily, monthly, yearly) |
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" or "tierchange"), subsection (you should have obtained it from your contact at Splio), 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" or "tierchange", and subsection "customers", dated on February 14, 2019, is:
myuniverse_cardcode_customers_20190214.csv
⚠️ | If there is more than one cardcode file per day, the subsequence in the file name is mandatory.
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
⚠️ | 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.
💡 | "birthday" is the only field where importing without hours is safe and recommended.
📗 | 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.
Explanation: erasing values
For the Loyalty subscriptions scope only, the NULL values don't work to delete data. Please use "" or an empty value for this purpose.