Historical data can be imported with a CSV file into Spotler Activate. Two types of data can be imported: Profile data and Events.
The import of CSV files cannot be undone
Profile fields can be overwritten with a new value, or merged incorrectly. Test a CSV import with only a few profiles and view the result in Spotler Activate.
When importing files manually, there’s a limit to the file size of 200MB. This limit is not present when uploading files to our FTPS.
CSV file format
The CSV file has to be constructed as follows:
- Separation with comma or semicolon: the columns should be separated with a comma (,) or semicolon (;)
- Name of data fields in first row: In the first row, the column names are processed data fields are processed as column names. Give the columns exactly the same name as the data fields in Spotler Activate (for example email, first name, last name).
- Rows of data fields in correct datatype: the Spotler Activate data fields should be processed in the correct datatype (for example name is text, birthdate is number). Check the datatype of the data fields in this item.
- Identifier field always present: the data field email or UserID should always be part of the CSV, where each row contains an identifier. Based on the identifier, the data is added to the identifier's existing profile or a new profile is created.present: the data field email or UserID should always be part of the CSV, with each row containing an identifier. Based on the identifier, the data is added to the identifier's existing profile or a new profile is created.
Do not confuse UserID with CustomerID / ContactIDs
CustomerID / ContactID is an internal ID in Spotler Activate that is automatically created for a new profile when a visitor on the website is not recognized. The UserID is optional and is immediately highest in the hierarchy of all identifiers. If you accidentally map CustomerID / ContactID to UserID, there are nasty consequences. All CustomerID / ContactID's in the import will then be seen as a UserID which will create new profiles.
Example CSV fields for profile data
| Firstname | Lastname | |
|---|---|---|
| pim@spotler.com | Pim | Reijnen |
| maarten@spotler.com | Maarten | Moerland |
When importing events, the following things are important:
- Extra column for event: in the first row the column 'event' is added. In the rows below the name of the event is given (for example purchase). Spotler Activate considers each line in the CSV file as an event and links this event to the email address which is entered in the column 'email'.
- Extra column for event date: in the first row the column 'event date' is added. The date of the event is entered in the rows below.
Don't forget the event date
When the event date is missing, the date of import will be used as the event date.
Example CSV fields for events
| Event | Orderid | Event_data | First name | Last name | Quantity | Products | Price | |
|---|---|---|---|---|---|---|---|---|
| Purchase | 12345 | 02/02/2024 | pim@spotler.com | Pim | Reijnen | 1 | 123-ab-XL | 100 |
| Purchase | 12345 | 02/02/2024 | pim@spotler.com | Pim | Reijnen | 1 | 123-ac-XXL | 120 |
| Purchase | 12555 | 08/02/2024 | maarten@spotler.com | Maarten | Moerland | 3 | 123-ab-xl | 100 |
When importing Purchase events, the following things are important:
- Extra column for orderid: Add an orderid to the purchase events. Use the same orderid on multiple lines to link order lines (products) within an order to the same purchase event.
- Additional column for order lines: For each order line within the same order, add the product id under 'products', specify the quantity under 'quantity' and specify the price of the product under 'price'.
Manual import
Go to Data, click on the CRM import tab and the button New CSV import.
Select and upload the CSV file. Connect the CSV columns to the Spotler Activate data fields. When connecting, the following columns are visible:
- CSV Field: the first row of the CSV
- Spotler Activate Field: the name of the data field in Spotler Activate
Make sure that each CSV field is connected to the desired Spotler Activate data field!
Import via FTPS
CSV file can be periodically imported into Spotler Activate via the FTPS Server. First, you need to submit a support request called 'Activate FTP Server' with the account name. The Support Team will send an update once the FTP Server is activated.
When the FTP server is activated for your account, go to Settings and use the FTP settings in the overview to periodically import the CSV file.
Pay attention to the column names
The column names need to exactly match the data fields in Spotler Activate!
When a CSV has been imported, this will be visible under CRM Import. Tip: First import a test CSV with the data of one profile and see the effects of the import in the 360° profile.
Import via API
Historical data can be received in Spotler Activate via the backend API.
- Sending events: in our API documentation you will find more information about sending events through the backend API. When sending historical events it is important to send the timestamp of the event.
Email address
It is important to always send an email address!