Imports - Contacts
This article explains how to prepare a "contacts" file for import with Splio.
/!\ All examples used in this file are either anonymized or fictitious.
Table of Contents
Prerequisites
- Basic knowledge of the CSV format and UTF-8 encoding.
- The sub-sequence must be defined in the config file under the "stores" scope.
- A UTF-8-enabled text editor.
- A spreadsheet software.
Preparation of a contacts file
If necessary, use your spreadsheet software to save the stores file in the CSV format or to remove columns you cannot (or do not want to) import. Always remember to save with UTF-8 encoding without BOM and use semicolons (";") to separate columns.
Header and Columns
The first row of the file is the header. Make sure that it contains only column names, or the import will fail.
The following columns are available in "contacts" files:
- email -- the contact's email. This column is the default customer key (see below for more explanation) and is mandatory when used to distinguish unique contacts.
- customer_key -- in non-standard cases, your SCP universe may use a custom field to distinguish contacts instead of "email". This case is shown in Example 2.
- cellphone -- the contact's mobile number.
- firstname -- the contact's first name.
- lastname -- the contact's first name.
- dateOfCreation -- fills the "Created On" field in Splio with the provided date see below.
- language -- the code of the language assigned to the contact (the codes are lowercase).
- subscriptions -- a list of Splio groups to change membership status (subscribe or unsubscribe contacts from lists, see Subscriptions below).
- A custom column, if defined for customers in your Splio universe.
By default, column names are lowercase (with the exception of "dateOfCreation"). This is because all column names are case sensitive, which means that, e.g. "cellphone" and "CellPhone" are two different columns.
Each column name should be used only once.
Remember that the import will fail if you do not include the mandatory column (email or custom) or if you use a column which does not exist.
Customer Key
Splio uses a single column to recognize unique contacts in the database. It is often called "customer key" because each value in this column is always unique, and you can identify your customer (contact) records by this value. For this reason, the customer key column is always mandatory.
By default, the "email" field is the customer key. This case is presented in Example 1.
Your SCP universe can be configured to use a custom column instead of "email" to identify contacts. If it is so, this colum replaces "email" as the customer key and becomes mandatory. Use the alias of this custom column in the header of your import file.
Remember that Splio does not normally distinguish between contacts on the basis of phone numbers or check for duplicated phone numbers. It is up to you to deduplicate the cellphone data when necessary.
Example 1: Default Customer Key
This example shows the default case when the contact's email address is the customer key. Use "email" in the header to identify it:
email;cellphone;firstname;lastname;language;subscriptions "misterspots@examplemail.org";2235334599;Spotty;Spots;fr;"+2" "lady@examplemail.org";+4499884472525;Monica;Jameson;en;"+2" "8sk5k7g87@examplemail.org";+48502553311;Lukas;Syndyk;pl;"+2"
There are 6 columns in the example. The email addresses will be matched against the database to see if each record already exists. New records will be added and existing ones updated, depending on the settings in your Config file.
Note that the phone number in the first line below the header does not have the country code. In such a case, the default code defined in the config file will be used.
The last column tells Splio to subscribe all contacts to list No 2. See below for more about list subscriptions.
Example 2: Custom column as Customer Key
This example presents an import file for a Splio universe which uses a custom field to distinguish between unique contacts.
In the example, we will use the custom column "c0". This column also has an alias, "externalId". We suggest you use the alias when possible for the sake of clarity.
externalId;email;cellphone;firstname;lastname;language "PPL000000045732";"jinx@examplemail.org";+332235334599;Mister;Jinx;fr "PPL000000107463";"lady@examplemail.org";+4499884472525;Monica;Jameson;en "PPL000000084232";"ijon.tichy@examplemail.org";+420257537371;Ijon;Tichy;cz
Six columns can be seen, the first is the custom "externalId" column containing external identifiers. Splio will use these values to check which records are new, and which already exist.
The "email" column is still used to import email addresses for the contacts.
Name your file
Save your file under a name composed of the universe name, scope ("contacts"), sub-sequence, and current date. For example:
myuniverse_contacts_business_20180410.csv
This filename belongs to the universe "myuniverse", sub-sequence "business" defined for contacts, and is dated April 10, 2018.
If you wish to know more, consult the File Naming and Grouping guide.
You can now upload the file to SFTP/FTPS.
Explanations
This section contains additional explanations which may come useful when working with contacts files.
List subscriptions
Contacts in Splio can belong to numbered lists, and imports allow you to perform batch subscribing and unsubscribing using the subscriptions column.
The column contains a list of signed numbers separated by commas (","). Each number must be preceded by the plus sign ("+") to subscribe (e.g., "+3") or by the minus sign ("-") to unsubscribe (e.g., "-6"). The whole list must be enclosed within quotation marks, like "+3,-6".
In the following example, the contact is subscribed to lists 1 and 7, and unsubscribes from lists 2 and 4:
email;subscriptions subscriber33@examplemail.org;"+1,-2,-4,+7"
Be careful when editing files containing the subscription column with spreadsheet software. It may misinterpret the list and break it, especially if it wants to use the comma (",") as decimal separator.
Dates
The dates in the "dateOfCreation" field consist of 4 digits for a year, 2 for a month, and 2 for a day, followed by hours, minutes, and seconds, 2 digits each. A correct date for March 15th, 2018, 1:17 pm will take the following form:
2018-03-15 13:37:00
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-03-07
is treated exactly as if you entered
2018-03-07 00:00:00
Remember that if you do not specify the dateOfCreation, Splio will use the import date and time.
Advanced Information
This part of the document contains the technical information which supplements the guide above and provides more detail.
File format
| Filename | <universe>_contacts_<name>_<date>(_<id>).csv |
|---|---|
| Encoding | UTF-8 without BOM |
| Format | CSV (no multiline) |
| File delimiter | ; (semicolon) |
| Text qualifier | " (double quote, optional) |
| Escape character | \ (backslash) |
| End of line | \n or \r\n |
| Decimal separator | . (dot) |
| Date field format | yyyy-mm-dd HH:MM:SS or yyyy-mm-dd |
If a date field in the file does not comply with the above format, the import will not be affected. However, data modules from the Target module will not operate successfully on that field.
Columns
The table below defines all columns which can be used in the header of a contacts file.
/!\ All column names are presented respecting the case of characters (they are case sensitive).
| Column | Mandatory | Data Type / Maximum length | Description |
|---|---|---|---|
| Yes (by default) | Text (max. 100 characters) | A single email address. All email addresses will be checked for correctness on import, and the lines with incorrect addresses will be skipped. This field is the default key for the contacts table. When used so, it is mandatory and its values must be unique. | |
| customer_key | Yes (if used) | Text (max. 100 characters) | A custom customer key replacing email (it is likely to be an external identifier of the contact). If your Splio universe is configure to use the customer_key column, it becomes the unicity key and is used to distinguish between contacts. The email field can still be used as a normal field. |
| cellphone | No | Text (max. 30 characters) | A cellphone number. All values in this field will be checked for correctness on import, and the lines with incorrect cellphone numbers will be skipped. The default cellphone number prefix, as defined in the configuration file, will be added to all numbers which do not begin with one. This field is part of the default table key. |
| firstname | No | Text (max. 255 characters) | The contact's first name. |
| lastname | No | Text (max. 255 characters) | The contact's last name. |
| dateOfCreation | No | Date | This column corresponds to the "Created On" field on the contact's overview page in Splio (see above for details). |
| language | No | Text (exactly 2 characters) | A 2-character language code, as defined by ISO 639-1. |
| subscriptions | No | List | A comma separated list of Splio lists to which the imported contact should be subscribed (see above for details) |
| <custom> | No (unless key) | Text (max. 255 characters) | A custom field. The SCP universe can be configured to use one custom field as the key of the contacts table. |
Custom columns
Splio allows you to add custom columns to your universe during its creation. Custom columns are numbered from 0 upwards, and each column can be given a name (alias). We suggest you use the alias when referring to custom columns in the import files, as shown in the Example 2 above.
Alternatively, you can refer to your custom columns by the letter "c" (lowercase) and its number, like "c0" or "c2".
Monitoring
The following information is made available by Splio after import:
- Whether the import was aborted or not and, if aborted, the reason (unknown column, missing customer_key column, undeclared custom field)
- The number of lines treated (imported+skipped)/imported/skipped
- The number of new contacts/updated contacts
- The reason each line was skipped (customer_key NULL, too many/not enough columns, subscribe/unsubscribe from an undeclared list, contact already in the database and update forbidden by config file, contact not in the db and creation forbidden by config file)