Creating workflow templates to import data import-workflow-template

Create a new workflow template from Resources > Templates > Workflow templates.

Add the following activities:

• Load file: Define the expected structure of the file containing the data to import.

  note note
  NOTE
  You can only import data from a single file. If the workflow has multiple Load file activities, the same file will be used each time.

• Reconciliation: Reconcile the imported data with database data.

• Segmentation: Create filters to process records differently depending on whether they could be reconciled or not.

• Deduplication: Deduplicate the data from the incoming file before it is inserted in the database.

• Update data: Update the database with the imported profiles.

Configure the Load file activity:

• Define the expected structure by uploading a sample file. The sample file should contain only a few lines but all the columns necessary for the import. Check and edit the file format to make sure that the type of each column is set correctly: text, date, integer, etc. For example:

  code language-none
  lastname;firstname;birthdate;email;crmID Smith;Hayden;23/05/1989;hayden.smith@mailtest.com;123456

• In the File to load section, select Upload a new file from the local machine and leave the field blank. Each time a new workflow is created from this template, you can specify here the file you want, as long at it corresponds to the defined structure.

  You can use any of the options but you have to modify the template accordingly. For example, if you select Use the file specified in the inbound transition, you can add a Transfer file activity before to retrieve the file to import from a FTP/SFTP server.

  If you want users to be able to download a file containing errors that occurred during an import, check the Keep the rejects in a file option and specify the File name.

  

Configure the Reconciliation activity. The purpose of this activity in this context is to identify the incoming data.

• In the Relations tab, select Create element and define a link between the imported data and the recipients targeting dimension (see Targeting dimensions and resources). In this example, the CRM ID custom field is used to create the join condition. Use the field or combination of fields you need as long it allows to identify unique records.
• In the Identification tab, leave the Identify the document from the working data option unchecked.

Configure the Segmentation activity to retrieve reconciled recipients in one transition and recipients that could not be reconciled but who have enough data in a second transition.

The transition with reconciled recipients can then be used to update the database. The transition with unknown recipients can then be used to create new recipient entries in the database if a minimum set of information is available in the file.

Recipients that cannot be reconciled and do not have enough data are selected in a complement outbound transition and can be exported in a separate file or simply ignored.

• In the General tab of the activity, set the Resource type to Temporary resource and select Reconciliation as the targeted set.

• In the Advanced options tab, check the Generate complement option to be able to see if any record cannot be inserted in the database. If you need, you can then apply further processing to the complementary data: file export, list update, etc.

• In the first segment of the Segments tab, add a filtering condition on the inbound population to select only records for which the profile’s CRM ID is not equal to 0. This way, data from the file that are reconciled with profiles from the database are selected in that subset.

  

• Add a second segment that selects unreconciled records that have enough data to be inserted in the database. For example: email address, first name and last name. Records that are not reconciled have their profile’s CRM ID value equal to 0.

  

• All records that are not selected in the first two subsets are selected in the Complement.

Configure the Update data activity located after the first outbound transition of the Segmentation activity configured previously.

• Select Update as Operation type since the inbound transition only contains recipients already present in the database.

• In the Identification tab, select Using reconciliation criteria and define a key between the Dimension to update - Profiles in this case - and the link created in the Reconciliation activity. In this example, the CRM ID custom field is used.

  

• In the Fields to update tab, indicate the fields from the Profiles dimension to update with the value of the corresponding column from the file. If the names of the file columns are identical or almost identical to the names of the recipients dimension fields, you can use the magic wand button to automatically match the different fields.

  

  note note
  NOTE
  If you plan on sending direct mails to these profiles, make sure to include a postal address as this information is essential to the direct mail provider. Also make sure that the Address specified box in your profiles’ information is checked. To update this option from a workflow, simply add an element to the fields to update, and specify 1 as Source and select the postalAddress/@addrDefined field as Destination. For more on direct mail and the use of the Address specified option, see this document.

Configure the Deduplication activity located after the transition containing unreconciled profiles:

• In the Properties tab, set the Resource type to the temporary resource generated from the Reconciliation activity of the workflow.

  

• In this is example, the email field is used to find unique profiles. You can use any field you are sure is filled and part of a unique combination.

• Choose a Deduplication method. In this case, the application decides automatically which records are kept in case of duplicates.

Configure the Update data activity located after the Deduplication activity configured previously.

• Select Insert only as Operation type since the inbound transition only contains profiles not present in the database.

• In the Identification tab, select Using reconciliation criteria and define a key between the Dimension to update - Profiles in this case - and the link created in the Reconciliation activity. In this example, the CRM ID custom field is used.

  

• In the Fields to update tab, indicate the fields from the Profiles dimension to update with the value of the corresponding column from the file. If the names of the file columns are identical or almost identical to the names of the recipients dimension fields, you can use the magic wand button to automatically match the different fields.

  

  note note
  NOTE
  If you plan on sending direct mails to these profiles, make sure to include a postal address as this information is essential to the direct mail provider. Also make sure that the Address specified box in your profiles’ information is checked. To update this option from a workflow, simply add an element to the fields to update, and specify 1 as Source and select the [postalAddress/@addrDefined] field as Destination. For more on direct mail and the use of the Address specified option, see this document.

After the third transition of the Segmentation activity, add a Extract file activity and a Transfer file activity if you want to keep track of data not inserted in the database. Configure those activities to export the column you need and to transfer the file on a FTP or SFTP server where you can retrieve it.

Add an End activity and save the workflow template.