Page tree
Skip to end of metadata
Go to start of metadata
  • Help Content has moved. Help documentation for Marketing Cloud Personalization is now published in Salesforce Help. Starting February 1, 2023, the doc.evergage.com site will no longer be available.
  • New Name: Interaction Studio (formerly Evergage) is now known as Marketing Cloud Personalization. The new name reflects our mission and vision for innovation in Salesforce Marketing Cloud. We wish we could snap our fingers to update the name everywhere, but you can expect to see the previous name in various places until we replace it.  

Interaction Studio can import data from external data sources using extract, transform, and load data feeds (ETLs).

Want to import data from Marketing Cloud Data Extensions? You can configure an automatic process in Marketing Cloud Automation Studio to automatically export data from data extensions and load them directly into Interaction Studio.


This Article Explains

This article details how to connect your Interaction Studio inbound SFTP to Marketing Cloud, create an automation to export data feeds from Marketing Cloud data extensions using Automation Studio, and load these exported files to Interaction Studio.

Sections in this Article

Process Overview

  1. Create an SFTP user in Interaction Studio
  2. Connect the Interaction Studio SFTP to Marketing Cloud
  3. Create a source Data Extension in Marketing Cloud
  4. Define a scheduled Automation in Automation Studio
    1. Populate a source data extension with data such as that from SQL queries
    2. Export the source data extension to a CSV file
    3. Transfer the CSV file to the Interaction Studio SFTP


Create an SFTP User in Interaction Studio

  1. Log into Interaction Studio as an administrator
  2. Follow the process detailed in Manage SFTP Users to create a new SFTP user in Interaction Studio. If you already have an Interaction Studio SFTP account, you can skip this process and continue to the next section.


Connect the Interaction Studio SFTP to Marketing Cloud

  1. While logged into Interaction Studio or any Marketing Cloud product, hover over your name in the upper right corner and click Setup.
  2. Expand Data Management in the Administration section of the left rail.
  3. Click File Locations.
  4. Click Create.
  5. Enter the properties for the file location:
    1. Name - Enter a name that includes the dataset and folder. For example, Interaction Studio Production Inbound.
    2. External Key - leave this field blank.
    3. Description - enter a description of the file location that is related to the dataset and folder.
  6. Select the Location Type as External SFTP Site and complete the following fields:
    1. Url - enter the SFTP URL in the following format: FTP_DOMAIN_NAME/DATSET_NAME/inbound. For example, sftp.germany-2.evergage.com/engage/inbound
      1. FTP_DOMAIN_NAME - this is the host name of your Interaction Studio SFTP server. It can be found in the last column of the SFTP user credentials CSV file. For example, sftp.germany-2.evergage.com.
      2. DATASET_NAME - this is the ID of the Interaction Studio dataset that contains the segment you are exporting. For example, engage
    2. Port - enter 22
    3. Username - copy and paste the SFTP username from the CSV file you downloaded when you created the SFTP user
    4. Auth Type - select Password
    5. Password - copy and paste the SFTP password from the CSV file you downloaded when you created the SFTP user
    6. Retype Password - copy and paste the SFTP password from the CSV file you downloaded when you created the SFTP user
  7. Click Save
  8. Repeat this for each dataset you have. For example:
    1. Test Inbound
    2. Production Inbound


Create a Source Data Extension in Marketing Cloud

These instructions follow the process outlined in Create a Data Extension in Contact Builder. The steps listed below detail how to configure an ETL feed for user or subscriber data. For other ETL feed specifications, refer to the feed requirements for each data type.

  1. Select Audience Builder > Contact Builder from Marketing Cloud's main navigation.
  2. Click Data Extensions at the top .
  3. Click Create in the upper right corner.
  4. Complete the information in the Properties section:
    • Creation Method - The method used to create the data extension. Select Create from New.
    • Name - Enter a name that matches the segment so you can identify the data extension in Contact Builder.
    • Description - Use this optional description to further help identify the data extension within Contact Builder.
    • Is Sendable - Indicates whether you intend this data extension to have messages sent to it. This checkbox determines whether the data extension is available to use as part of sends. For user or subscriber related feeds, create a sendable data extension so standard Marketing Cloud deletion processes will automatically remove data from the data extension.
  5. Click   Next .
  6. Optional: Click  On  under Data Retention to set a data retention policy for your data extension. 
    1. To apply the data retention policy, click the button under the  Apply To  heading for the entity or entities.
    2. Click the appropriate button under the Retention Period heading:
      1. For a revolving time period, enter the number in the text field. Select the unit of measure in the dropdown menu next to the first button. For example, you could enter the number 6 and select Months in the dropdown menu to enforce a 6-month data retention policy.
      2. For a set date, select the date next to the second button.
  7. Click  Next.
  8. Configure all mandatory and optional columns according to the feed specification. Column names must match field names in the feed specification.
  9. Select Text (50 character length) for the Date and Boolean fields data types in Interaction Studio. For example: 
    1. attribute:sfmcContactKey
      1. Name: attribute:sfmcContactKey
      2. Data Type: Text
      3. Length: 50
      4. Primary Key: selected
      5. Nullable: not selected
    2. attribute:name
      1. Name: attribute:name
      2. Data Type: Text
      3. Length: 50
      4. Primary Key: selected
      5. Nullable: selected
    3. attribute:isLoggedIn
      1. Name: attribute:isLoggedIn
      2. Data Type: Text
      3. Length: 50
      4. Primary Key: not selected
      5. Nullable: selected
    4. attribute:firstVisitDate
      1. Name: attribute:firstVisitDate
      2. Data Type: Text
      3. Length: 50
      4. Primary Key: selected
      5. Nullable: selected
  10. Click Create .
  11. Click OK.
  12. Open the data extension and save the External Key. You will need it when you set up data extracts in Automation Studio.


Load Data to a Source Data Extension

A SQL query receives data from other data extensions and standard views of Marketing Cloud, transforms it to the required format, and saves it to the target data extension. The example below describes the process using a SQL query, but you can use another method.

  1. Open Automation Studio.
  2. On the Activities tab, create a SQL query activity.
  3. Prepare your SQL Query Activity similar to SQL Query Example.
    1. Extract date fields: format dates in the ISO 8601 format, such as the format function: FORMAT(YOUR_DATE_COLUMN, 'yyyy-MM-ddTHH:mm:ssZ')
    2. Extract boolean fields: use “TRUE” or “FALSE” for boolean values. By default, Marketing Cloud exports boolean values as “1” or “0”. To convert a boolean value to the right format use: IIF(YOUR_BOOLEAN_COLUMN = 1, 'TRUE', 'FALSE')
  4. Continue configuring the SQL Query Activity. Select your source data extension and use the Overwrite method for faster processing
  5. Manually run your SQL query to make sure that everything works as expected and the query populates your source data extension.

SQL Query Example

select
Lower(SubscriberKey) as "attribute:sfmcContactKey",
FirstName as "attribute:name",
Email_Address as "attribute:emailAddress",
Number_Of_Visits as "attribute:visitNumber",

IIF(Known_Subscriber = 1, 'TRUE', 'FALSE') as "attribute:isLoggedIn",
FORMAT(Registration_Date, 'yyyy-MM-ddTHH:mm:ssZ') as "attribute:firstVisitDate"
from
ContactMaster


Define an Automation in Automation Studio

These instructions follow the process outlined in  Define a Scheduled Automation , but include customizations specific to automating a segment import into Marketing Cloud from Interaction Studio.

IMPORTANT

Interaction Studio requires all CSV feeds to be encoded with UTF-8. Marketing Cloud data extension extracts are using a different encoding by default. Therefore you have to make sure that a special “File Convert” data extract type is available on your Marketing Cloud Account. If it is not available create a support request to enable it on your account. 

To check availability of the “File Convert” data extract on your MC Account, create a new Data extract activity and make sure that there is “File Convert” in the list of Extract types


Create the Starting Source Schedule

  1. Select Journey Builder > Automation Studio f rom Marketing Cloud's main navigation. 

  2. Click New Automation.

  3. Click and drag Schedule from Starting Sources to Start with a Starting Source.

  4. Click Configure.

  5. Enter a Start Date and Time. Do not set a start date and time that is before the date that you will complete end-to-end testing of this process.

  6. Confirm that the Time Zone is correct or make changes as needed.

  7. Select the Repeat as Daily every 1 day(s).

  8. Set the End as a number of occurrences, a specific date, or never.

  9. Click Done.


Add Required Activities

  1. SQL Query (or any other method) - to load data to the source data extension
  2. Data extract (Data Extension Extract) - to extract data from a data extension to a CSV file
  3. Data extract (File Convert) - to convert a CSV file to UTF-8
  4. File Transfer - to transfer a CSV file to the Interaction Studio FTP


Create a SQL Query

  1. Drag SQL Query onto the workflow canvas.

  2. Click Choose to select an object for that activity.

  3. Select the SQL Query Activity you created 


Convert the Data Extract from a data extension to a CSV file

  1. Drag Data Extract onto the workflow canvas.

  2. Click Choose to select an object for that activity.

  3. Click Create New Data Extract Activity.
  4. Configure the following settings:
    1. Extract Type: select Data Extension Extract
    2. File Name Pattern: this depends on the feed type - see filing naming pattern example shown for User ETL
    3. Column Delimiter: comma (,)
    4. DECustomer Key: enter the customer key for your source data extension
    5. Has Column Headers: select Yes
    6. Text Qualified: select Yes

File Naming Pattern Example

The Filing Naming Pattern for the User ETL would be:

user-%%Year%%-%%Month%%-%%Day%%_00-00-00.csv


Convert the Data Extract from CSV to UTF-8

  1. Drag Data Extract onto the workflow canvas.
  2. Click Choose to select an object for that activity.
  3. Click Create New Data Extract Activity.
  4. Configure the following settings:
    1. Extract Type:  select File Convert
    2. File Name Pattern: this depends on the feed type - see filing naming pattern example shown for User ETL
    3. Convert To: select UTF8
    4. Is File In Safe House: select Yes

File Naming Pattern Example

The Filing Naming Pattern for the User ETL would be:

user-%%Year%%-%%Month%%-%%Day%%_00-00-00.csv


Transfer the CSV file to the Interaction Studio SFTP

  1. Drag File Transfer onto the workflow canvas.
  2. Click Choose to select an object for that activity.
  3. Click Create New File Transfer Activity
  4. Configure the following settings:
    1. Move a File From Safehouse: Yes
    2. File Name Pattern: this depends on the feed type - see filing naming pattern example shown for User ETL
    3. Destination: Interaction Studio SFTP

File Naming Pattern Example

The Filing Naming Pattern for the User ETL would be:

user-%%Year%%-%%Month%%-%%Day%%_00-00-00.csv


Save, Test, and Run the Automation

  1. Add and configure more activities as needed. Click a configured activity to review, edit, or delete.
  2. Click Save to save the Automation.
  3. Click Run Once to test the automation once there is a file in the inbound folder in the Interaction Studio FTP. New records should appear on the target data extension.
  4. Once you complete testing, activate the automation.


Enable and Run the ETL Process

For detailed instructions on how to run the ETL process, see Setup and Deploy ETL Feeds in this knowledge base.