Overview

Skill Level: Any

Prerequisites

This interface, along with SFTP allows importing a batch file that contains new, modified, or opted out contacts. Contacts can also be added to an existing Contact List.

Step-by-step

  1. ImportList – Complete an import through the API

    1. Upload the source file to the Watson Campaign Automation SFTP server.
    2. Upload the Definition and Column Mapping file to the Watson Campaign Automation SFTP server.
    3. Authenticate the user with a Login API call.
    4. Initiate the database import using an ImportList call.
    5. Determine status of the background import job by making a GetJobStatus API call (optional).
    6. Log off from the Watson Campaign Automation API using a Logout API call.

    You must place all files in the upload directory on the FTP server or the database import service will not “find” them.

    Note: To learn more about setting up an SFTP account, click here.

    Below is the syntax of the request and response:

    Operation <ImportList>     
    Elements  MAP_FILE  The name of the Mapping file in the upload directory of the FTP server to use for the import.   
      SOURCE_FILE The name of the file containing the contact information to use in the import. This file must reside in the upload directory of the FTP Server.   
      FILE_ENCODING  Optional 

    Defines the encoding of the source file. Supported values are:

    UTF‐8

    ISO‐8859‐1

    If not specified, Watson Campaign Automation will use the Organization default encoding. 

    Example 
    <soapenv:Envelope xmlns:
    soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:ses=”SilverpopApi:EngageService.SessionHeader” xmlns:user=”SilverpopApi:EngageService.ListMgmt.UserActions”> <soapenv:Header> <ses:sessionHeader> <ses:sessionid>50F4F411A048A4DDA159ABE2954E4610</ses:sessionid> </ses:sessionHeader> </soapenv:Header> <soapenv:Body> <user:ImportList> <user:MAP_FILE>List_Import_Test.xml</user:MAP_FILE> <user:SOURCE_FILE>List_Import_Test.csv</user:SOURCE_FILE> </user:ImportList> </soapenv:Body> </soapenv:Envelope>
    Response  <RESULT>     
    Elements  SUCCESS TRUE if successful.   
      JOB_ID  Identifies the Watson Campaign Automation Background Job created and scheduled for this import.   
    Example
    <envelope:Envelope 
    xmlns=”SilverpopApi:EngageService.ListMgmt.UserActions” xmlns:envelope=”http://schemas.xmlsoap.org/soap/envelope/”> <envelope:Header/> <envelope:Body> <RESULT> <SUCCESS>TRUE</SUCCESS> <JOB_ID>502732</JOB_ID> </RESULT> </envelope:Body> </envelope:Envelope>

      

    Database Definition and Column Mapping Format

    Mapping is the process of associating columns in the import file with the corresponding fields in the database. For example, Column 1 in the mapping file corresponds to Name in the database. You must describe the details
    of the database including its columns as well as the format of the source file (which contains the actual contact records). You will place this information in an XML mapping file and upload it to a specified directory on an FTP server.

    Add the <KEY_COLUMN> element (set to True) in a field declaration in the mapping file to set a field as a unique key for the database. You can also define more than one unique key field for a database. You must define the EMAIL field in the mapping file and cannot leave it out for an NEK database. The column TYPE should always be set to 9 and the IS_REQUIRED element should always be set to True. However, you must specify KEY_COLUMN as True in order to list it as a unique key. If not specified, it will be a regular field in the database.

    Watson Campaign Automation returns errors in the submission as part of the response. However, Watson Campaign Automation does not detect errors in the mapping file or the source file until it runs the data job. You can view Data Job errors on the Data Jobs screen of the Watson Campaign Automation user interface.

    The XML file contains four main sections:

    1. LIST_INFO: defines information about the database you are creating (or updating), as well as actions to perform on this database.
    2. COLUMNS: if creating a new database, this section consists of one or more COLUMN elements. Each COLUMN element contains child elements to define the attributes for that column (for example, name,
      type, and key column). If the action is not CREATE (and you are not adding new columns to the database), you can exclude the COLUMNS section from the mapping file.
    3. MAPPING: this section also consists of COLUMN elements. Each COLUMN element contains child elements to define the attributes for mapping that column (for example, name and index).
    4. CONTACT_LISTS: this optional section is used to specify one or more Contact Lists that all contacts will be added to in addition to the database. This section may be used with all actions except OPT_OUT.

    The Import Data Job will handle imports of contacts to a database having No Unique Identifier as follows:

    • If the ACTION is CREATE or ADD_ONLY and SYNC_FIELDS are NOT specified, each row in the source file will result in a new contact.
    • If the ACTION is CREATE and SYNC_FIELDS are specified, rows having identical SYNC_FIELDS within the source file will be rejected as duplicates.
    • If the ACTION is ADD_ONLY and SYNC_FIELDS are specified, rows having identical SYNC_FIELDS within the source file or in the database will be rejected as duplicates.
    • If the ACTION is UPDATE_ONLY, contacts matching the SYNC_FIELDS will be updated; all other contacts will be ignored. If more than one contact is found matching the lookup columns, all contacts will be updated.
    • If the ACTION is ADD_AND_UPDATE, contacts matching the SYNC_FIELDS will be updated; all other contacts will be added. If more than one contact is found matching the lookup columns, all contacts will be updated.
    • If the ACTION is OPT_OUT, contacts matching the SYNC_FIELDS will be opted out; all other contacts will be ignored. If more than one contact is found matching the lookup columns, the all matching contacts will be opted out.

    Note: All column names are case‐sensitive, meaning you must ensure the names in the mapping file and source file exactly match the field names in the Watson Campaign Automation database.

    The table below defines the sections. You should contain each of these sections within the LIST_IMPORT element:

    Section <LIST_INFO> The LIST_INFO section defines the structure of the database you are creating or updating, as well as the action to perform on this database. 
    Elements  ACTION

    Defines the type of import you are performing. The following is a list of valid values and how Watson Campaign Automation interprets them:

    CREATE – create a new database.

    ADD_ONLY – only add new contacts to the database. Ignore existing contacts when found in the source file.

    UPDATE_ONLY – only update the existing contacts in the database. Ignore contacts who exist in the source file but not in the database.

    ADD_AND_UPDATE – process all contacts in the source file. If they already exist in the database, update their values. If they do not exist, create a new record in the database for the contact.

    OPT_OUT – opt out any contact in the source file who is already in the database. Ignore contacts who exist in the source file but not the database. 

      LIST_TYPE

    Defines the type of database. Only specified if the ACTION is CREATE. Supported values are:

    0 – Database

    6 – Seed list

    13 – Suppression list

      LIST_NAME Defines the name of the new database if the ACTION is CREATE (names must be unique). 
      LIST_ID Unique ID of the database in the Watson Campaign Automation system. You must provide this for all ACTION values (except CREATE).
      LIST_VISIBILITY

    Required. Defines the visibility of the newly created database.

    0 – private

    1 – shared

      PARENT_FOLDER_PATH Used with the CREATE ACTION to specify the folder to place the new database in. Use the VISIBILITY element to specify Private/Shared.
      FILE_TYPE

    Defines the formatting of the source file. Supported values are:

    0 – CSV file

    1 – Tab‐separated file

    2 – Pipe‐separated file 

      HASHEADERS The HASHEADERS element is set to true if the first line in the source file contains column definitions. 
      LIST_DATE_FORMAT Optional

    Used to specify the date format and date fields in your file if your date format differs from “mm/dd/yyyy” (the month, day, and year can be in any order you choose).

    Valid values for Month are:

    mm (e.g. 01)
    m (e.g. 1)
    mon (e.g. Jan)
    month (e.g. January)

    Valid values for Day are:

    dd (e.g. 02)
    d (e.g. 2)

    Valid values for Year are:

    yyyy (e.g. 1999)
    yy (e.g. 99)

    Separators may be up to two characters in length and can consist of periods, commas, question marks, spaces, and forward slashes (/). 

    Examples:

    For “Jan 2, 1975” your LIST_DATE_FORMAT would be “mon d, yyyy”

    For “1975/09/02” your LIST_DATE_FORMAT would be “yyyy/mm/dd”

      DOUBLE_OPT_IN Optional

    If this element accompanies the CREATE action, Watson Campaign Automation creates a Double Opt‐In database. If you do not specify the element, the Organization default is used.

    Note: The database security settings that allow you to use the database import operation are, by default, disabled for all Double Opt‐In databases.

      ENCODED_AS_MD5 Optional If this element is set to true, email addresses in the Email column will be MD5 encoded.
      SYNC_FIELDS Optional

    Required if the database has no Unique Identifier and action is UPDATE_ONLY, ADD_AND_UPDATE, or OPT_OUT. Used to specify what columns are used to look up a contact in a database that has no Unique Identifier defined. Include a SYNC_FIELD element for each database column that defines a unique contact.

    Note: If more than one contact is found matching the lookup columns, all matching contacts will be updated. If the ACTION is opt out, all matching contacts will be opted out. 

      Child Element SYNC_FIELD XML nodes defining the column Name of the unique identifier fields. 
        Child Element NAME Defines the field name.
    Section <COLUMNS> This section defines how many columns to write to the database (0 or more). Use aseparate COLUMN element to define each database column. If the ACTION is not CREATE and you are not adding new columns to the database, you can exclude the COLUMNS section from the mapping file. 
    Elements COLUMN
      Child Elements NAME Defines the name of the column.
        TYPE

    Defines what type of column to create. The following is a list of valid values:

    0 – Text column

    1 – YES/No column

    2 – Numeric column

    3 – Date column

    4 – Time column

    5 – Country column

    6 – Select one

    8 – Segmenting

    9 – System (used for defining EMAIL field only)

    17 – Timestamp

    20 – Multi‐Select

    Note: The import process does not validate the data in the source file against these column types.

        IS_REQUIRED

    Defines whether to create the column as “required” when contacts are providing information through a Web form. Valid values are True and False.

    Note: The import process does not use this information to validate the data. 

        KEY_COLUMN Added to field definition and defines a field as a unique key for the database when set to True. You can define more than one unique field for each database. 
        DEFAULT_VALUE Defines the default value of the column used by the Send process if the contact does not contain a value for this column.
        SELECTION_VALUE Defines values in a drop‐down list. You must provide at least one element for each value. Used in conjunction with TYPE 6. 
    Section <MAPPING>

    Use the MAPPING section to define which columns in the source file map to which columns in the database.

    Define each column in its own COLUMN element.

    Elements COLUMN
      Child Elements INDEX The INDEX element defines the order in which the source file defines this column.
        NAME

    The name of the database column to which Watson Campaign Automation maps the source file column. You can use the following system‐defined names in the mapping file:

    EMAIL – the email address.

    EMAIL_TYPE – the type of contact body to send.

    CREATED_FROM – An integer defining where Watson Campaign Automation collected the contact’s information.

    OPTED_IN – the date and time the contact was opted into the database. Watson Campaign Automation assumes all times to be GMT. If there is no optin date specified, the system will insert the current date and time (in GMT).

    OPT_IN_DETAILS – any information (up to 250 characters) that defines where

    The name of the database column to which Watson Campaign Automation maps the source file column. You can use the following system‐defined names in the mapping file:

    EMAIL – the email address.

    EMAIL_TYPE – the type of contact body to send.

    CREATED_FROM – An integer defining where Watson Campaign Automation collected the contact’s information.

    OPTED_IN – the date and time the contact was opted into the
    database. Watson Campaign Automation assumes all times to be GMT. If there is no optin date specified, the system will insert the current date and time (in GMT).

    OPT_IN_DETAILS – any information (up to 250 characters) that defines where Watson Campaign Automation obtained the contact’s information.

    OPTED_OUT – the date and time the contact opted out of the database (for OPT_OUT actions only).

    All system‐type field names must be in uppercase.

        INCLUDE

    Defines whether to include the element in the database import.

    You do not need to include all source file columns in the import.

    This element allows you to specify which columns to skip during the Import process. Valid values are True and False

    Section <CONTACT LISTS> Use the CONTACT_LISTS section if you want to specify one or more Contact Lists that all contacts will be added to in addition to the database. This section may be used with all actions except OPT_OUT.
      Child Elements CONTACT_LIST_ID Zero to many elements defining the Id of an existing Contact List.

     

    Example Mapping Files

    Example 1 

    This example shows a simple mapping file with the unique key set as CustID. Email is not a unique key in this example. 

    <LIST_IMPORT>
      <LIST_INFO>
        <ACTION>CREATE</ACTION>
        <LIST_NAME>Premier Accts</LIST_NAME>
        <LIST_VISIBILITY>0</LIST_VISIBILITY>
        <FILE_TYPE>0</FILE_TYPE>
        <HASHEADERS>true</HASHEADERS>
      </LIST_INFO>
      <COLUMNS>
        <COLUMN>
          <NAME>EMAIL</NAME>
          <TYPE>9</TYPE>
          <IS_REQUIRED>true</IS_REQUIRED>
        </COLUMN>
        <COLUMN>
          <NAME>CustID</NAME>
          <TYPE>0</TYPE>
          <IS_REQUIRED>true</IS_REQUIRED>
          <KEY_COLUMN>true</KEY_COLUMN>
        </COLUMN>
      </COLUMNS>
      <MAPPING>
        <COLUMN>
          <INDEX>1</INDEX>
          <NAME>EMAIL</NAME>
          <INCLUDE>true</INCLUDE>
        </COLUMN>
        <COLUMN>
          <INDEX>2</INDEX>
          <NAME>CustID</NAME>
          <INCLUDE>true</INCLUDE>
        </COLUMN>
      </MAPPING>
    </LIST_IMPORT>
    

     

    Example 2 

    This example shows a more complicated mapping file with custom column definitions. Both EMAIL and CustID are unique keys. Contacts are also being added to existing Contact Lists. 

    <LIST_IMPORT>
      <LIST_INFO>
        <ACTION>ADD_AND_UPDATE</ACTION>
        <LIST_ID>50194</LIST_ID>
        <FILE_TYPE>0</FILE_TYPE>
        <HASHEADERS>true</HASHEADERS>
      </LIST_INFO>
      <COLUMNS>
        <COLUMN>
          <NAME>EMAIL</NAME>
          <TYPE>9</TYPE>
          <IS_REQUIRED>true</IS_REQUIRED>
          <KEY_COLUMN>true</KEY_COLUMN>
        </COLUMN>
        <COLUMN>
          <NAME>CustID</NAME>
          <TYPE>0</TYPE>
          <IS_REQUIRED>true</IS_REQUIRED>
          <KEY_COLUMN>true</KEY_COLUMN>
        </COLUMN>
        <COLUMN>
          <NAME>Att1</NAME>
          <TYPE>0</TYPE>
          <IS_REQUIRED>false</IS_REQUIRED>
          <DEFAULT_VALUE/>
        </COLUMN>
        <COLUMN>
          <NAME>Att2</NAME>
          <TYPE>0</TYPE>
          <IS_REQUIRED>false</IS_REQUIRED>
          <DEFAULT_VALUE/>
        </COLUMN>
        <COLUMN>
          <NAME>CountryField1</NAME>
          <TYPE>0</TYPE>
          <IS_REQUIRED>false</IS_REQUIRED>
          <DEFAULT_VALUE/>
        </COLUMN>
        <COLUMN>
          <NAME>CountryField2</NAME>
          <TYPE>0</TYPE>
          <IS_REQUIRED>false</IS_REQUIRED>
          <DEFAULT_VALUE/>
        </COLUMN>
        <COLUMN>
          <NAME>DateField1</NAME>
          <TYPE>0</TYPE>
          <IS_REQUIRED>false</IS_REQUIRED>
          <DEFAULT_VALUE/>
        </COLUMN>
      </COLUMNS>
      <MAPPING>
        <COLUMN>
          <INDEX>1</INDEX>
          <NAME>EMAIL</NAME>
          <INCLUDE>true</INCLUDE>
        </COLUMN>
        <COLUMN>
          <INDEX>2</INDEX>
          <NAME>EMAIL_TYPE</NAME>
          <INCLUDE>true</INCLUDE>
        </COLUMN>
        <COLUMN>
          <INDEX>3</INDEX>
          <NAME>CustID</NAME>
          <INCLUDE>true</INCLUDE>
        </COLUMN>
        <COLUMN>
          <INDEX>4</INDEX>
          <NAME>DateField1</NAME>
          <INCLUDE>true</INCLUDE>
        </COLUMN>
      </MAPPING>
      <CONTACT_LISTS>
        <CONTACT_LIST_ID>31279</CONTACT_LIST_ID>
        <CONTACT_LIST_ID>54564</CONTACT_LIST_ID>
      </CONTACT_LISTS>
    </LIST_IMPORT>
    

     

    Example 3 

    This example shows a mapping file for a database with no unique key where the columns EMAIL and SSN are being used to update contacts. 

    <LIST_IMPORT>
      <LIST_INFO>
        <ACTION>UPDATE_ONLY</ACTION>
        <LIST_NAME>Premier Accts</LIST_NAME>
        <LIST_VISIBILITY>0</LIST_VISIBILITY>
        <FILE_TYPE>0</FILE_TYPE>
        <HASHEADERS>true</HASHEADERS>
      </LIST_INFO>
      <SYNC_FIELDS>
        <SYNC_FIELD>
          <NAME>EMAIL</NAME>
        </SYNC_FIELD>
        <SYNC_FIELD>
          <NAME>SSN</NAME>
        </SYNC_FIELD>
      </SYNC_FIELDS>
      <MAPPING>
        <COLUMN>
          <INDEX>1</INDEX>
          <NAME>EMAIL</NAME>
          <INCLUDE>true</INCLUDE>
        </COLUMN>
        <COLUMN>
          <INDEX>2</INDEX>
          <NAME>SSN</NAME>
          <INCLUDE>true</INCLUDE>
        </COLUMN>
        <COLUMN>
          <INDEX>3</INDEX>
          <NAME>First_Name</NAME>
          <INCLUDE>true</INCLUDE>
        </COLUMN>
      </MAPPING>
    </LIST_IMPORT>
    

Join The Discussion

Your email address will not be published. Required fields are marked *