Import - Automated imports

Overview

Automated imports are processed via a secure and encrypted SFTP connection. For this, a custom automated SFTP import flow must be established.

In order to establish the automated SFTP import flow, please open a support ticket with the following information:

1. What is the preferred SFTP folders' structure?
2. Who is the person responsible for the SFTP integration on your side?
3. What IP addresses should be whitelisted for connecting to our SFTP server?
4. What structure will the files you will send us have and what is the preferrable import strategy?
5. By what schedule the files should be imported?

Please see the detailed information on each question can be found below.

The process of creating an automated SFTP import flow is the following:

1. We create a folder on our SFTP server with the following folder structure (folder names can be configured, this is the default suggested structure):

/foldername
|----InputFiles
|----Processing
|----Processed

The default structure works as follows:

1. Files are put to InputFiles folder in the expected format by a customer.
2. Then at the agreed time, each file from InputFiles is put to Processing folder while it's being processed
3. When processing of the file is done it is put to Processed folder

2. Additionally, we create an SFTP user and send you a password for it to a person responsible so that the folder can be accessed via SFTP.

3. The access to the SFTP folder should be trusted, so we would need the list of IP addresses the SFTP server will be accessed from so that we can whitelist them on our side.

4. You place the file for import to the InputFiles folder with the agreed filename mask and structure. Please find IDS Example.xlsx file attached for the Input Data Specification.

The requirements for import files are the following:

• Filename: case-sensitive, can contain ASCII symbols (abc...), digits (123...), underscore symbol (_), dash symbol (-)
• Filename mask: single generic mask with a mandatory timestamp (e.g. "acquisition_YYMMDD.csv"). In case if several files are provided for the same date period the name of each file must be unique (e.g. acquisition_161006_01.csv, acquisition_161006_02.csv, etc). The second solution is to adjust the filename mask to include hours/minutes/seconds.
• File format: plain-text .csv or .txt
• Encoding: UTF-8.

Additionally, the information on what columns in the file will be used for CUSTOMER_ID, ADDRESS, LANGUAGE, SURVEY_ID mandatory columns must be provided.
Please find examples of import strategy scenarios:

1. Separate file for each survey channel:
   • Filename mask of the files provided must contain Surve ID in it (e.g. acquisition_YYMMDD_ID_XX.csv). Survey ID can be found in VoC Feedback UI.
   • Any number of files can be provided for the same survey on the same date until filenames are unique
2. A single file containing multiple surveys:
   • Survey ID column parameter should be specified. Survey ID can be found in VoC Feedback UI. This column name must be the same in all the files
   • Any number of files can be provided, containing any survey ids, on the same date until filenames are unique
3. Single file with same Survey ID but for WEB and SMS channels:
   • Any active Channel IDs of any survey can be picked
   • Survey Channel column parameter must be provided. If Survey ID refers to WEB channel of the survey and you invite the customer to the SMS channel then Survey Channel column value = SMS, otherwise leave empty and customer will be invited to WEB. Same behavior with other configurations, it is possible to invite a customer through any channel of the survey by specifying an alternate channel in the Survey Channel column.
4. Single file with different columns for phone and email and same survey id for all channels:
   • Any of survey's channel IDs can be picked
   • Names of the columns which contain a phone number and email address should be specified
   • Rules on how to determine the channel should be specified (e.g. "If EMAIL_ADDRESS column is empty then channel = SMS and address column = PHONE_NUMBER else channel = WEB")Single file with different columns for phone and email and same survey id for all channels:

5. Then, an import schedule should be defined and agreed, according to which the files you upload to the /InputFiles folder, will be processed, e.g., each Monday at 10 o'clock or every day from 10:00 CET to 20:00 CET every 30 minutes. The aforementioned schedule means that automatic import script will go to the /InputFiles folder for the new file to process every 30 minutes every day from 10:00 CET to 20:00 CET.

According to the defined schedule, import script goes to the /InputFiles folder, takes a file(s) with the defined filename masks, and starts processing them. As a result, a new import (separate import for each file) is created and can be seen in the VoC Feedback UI at the Import History page.

Automated imports from the SFTP folder are also shown in the list of imports. Some attributes may not be shown for automated imports.

Please feel free to open a support ticket to set up your SFTP account to have SANDSIV+ enable the automated import process. 

Note: Be sure to request the right survey ID.