Visit our new documentation site! This documentation page is no longer updated.

Writing Job Results to Salesforce Marketing Cloud (ExactTarget)

Treasure Data can publish user segments into Salesforce Marketing Cloud (ExactTarget), and enable you to send personalized emails to your customers. You can run data-driven email campaigns, by using your first party data from Web, Mobile, CRM, and other data sources.

Treasure Data provides two methods to write job results to Salesforce Marketing Cloud (SFMC), our SFTP and SFMC plugins. We recommend that the SFTP plugin be used for large data sets and the SFMC plugin for small data sets.

Table of Contents

Use SFTP Plugin for Large Data Sets

Prerequisites

  • Basic knowledge of Treasure Data
  • Basic knowledge of Salesforce Marketing Cloud
  • TD account

Set up a secure, automated account

You access your Salesforce account to begin set up.

Step 1: Create an FTP account

On the SFMC dashboard, in your account, click Administration.


From the Account drop-down menu, click FTP Accounts. You are to establish an SFTP account.


In the FTP Accounts pane, click Add FTP User.


Provide an FTP account password.


Review your SFTP account information.


Step 2: Configure for automatic mail delivery

Go back to the SFMC dashboard, and click Email Studio > Email.


Click Content > Create > Template > From Existing Template to create an email template.


After creating the template, click Save > Save and Exit and then provide a template name and location, and save that information as well.


Remaining on the Email page, click Create > Email to create email content (for example, for a campaign) from a template.


Select the template, define the email properties, including name and location, and click Next to provide content. Continue creating the email and save when you are finished.


Define specification for import into Salesforce

Step 1: Create an Import Interaction in Salesforce

While still in the Email view, from the Interactions drop-down menu, click Import.


Click Create to make a new import interaction definition.


Provide the import interaction information, including SFTP information and data import location. Then, save the information.


Step 2: Specify the import trigger

While still in the Email view, click on the SFDC blue cloud icon to view menu options.

Click Journey Builder > Automation Studio.


Click New Automation.

Drag the File Drop icon to Starting Source.


Click Configure> Trigger Automation.

Specify use a Filename Pattern and then click Done.


Drag the Send Email icon to the canvas and click Create New.


  1. Select an email object, for example, the one you created in this section. Then click Next.
  2. Select an email target list. Then click Next.
  3. Verify the email configuration information, and then click Finish.


Provide a name for the import trigger and an external key that is referred to by the Treasure Data, and click Save.

Click Active to enable the import trigger.


Click Save & Close.

Run a Treasure Data job to complete an initial import to Salesforce

Create a job that selects data from within Treasure Data. The specified column name for the mapping must match the column name in the SFMC Exact Target mail. “Email Address” and “Subscriber Key” columns are required. If needed, you can change the mapped column name that is in TD database. You can change the column name from within the console.


Create the connection. In the connection configuration, specify the SFTP account that you configured in SFMC.


Run the job. After the job finishes, you can check the output file on SFTP server by using the general SFTP command, as shown in the following example:


Check the SFMC dashboard to verify a successful import. If the import and mail delivery is successful, you see Complete on the Automation Studio Overview page.

Use SFMC Plugin (Beta) for Small Data Sets

Prequesites

  • Basic knowledge of Treasure Data
  • Basic knowledge of Salesforce Marketing Cloud
  • TD account
  • Installed application client key and secret

SFMC plugin

This plugin allows writing query results directly to any Data Extension in Salesforce Marketing Cloud. It requires that the Data Extension have a primary key that uniquely identifies each record.

Plugin configuration and options

Options

client_id: Installed package client key
client_secret: Installed package client secret
de_name: Data Extension name
continue_on_failure: Continue running if there is an insertion error

Example configuration

out:
 type: salesforce_marketing_cloud
 client_id: i9a1Bhxckdv3lmoAfw24f41f
 client_secret: cG6pGs1rNBhLdxr9nuzUkNez
 de_name: data_extension_name
 continue_on_failure: false

Acquire client id and secret

In order to use the Salesforce Marketing Cloud plugin, you must have a client ID and secret for an installed package.

Create installed application

  1. Navigate to Administrators menu in Salesforce Marketing Cloud dashboard.
  2. Navigate to Installed Packages menu.


  3. Create new package by clicking the New button.


  4. Add the new component to the installed package.


  5. Choose the API Integration component type.


  6. Choose Perform server-to-server.


  7. Select the Read and Write permissions for Data Extensions to install the package.


Get the client ID and secret

The client ID and secret can be found in the package detail page:


Use Salesforce Marketing Cloud Output Plugin

Using Treasure Data console

Complete the following steps to export TD query results to a Salesforce Marketing Cloud Data Extension:

  1. Create a new data connection

    A new data connection can be used to export results to a SFMC Data Extension.

    The Salesforce Marketing Cloud connector can be found in the Source Catalog


    Fill out the form using the client id and client secret acquired previously.


    Name your connection for later use.


  2. Configure the query results output connector

    Click Output results.


    You can choose the connection created in the previous step.


    Fill out the configuration form.


    You can decide to output to the configured result by selecting Output results.

    You can also create a data connection in the Choose Saved Connection dialog.


    Complete the data connection information in the Create Connection dialog.


  3. Run the query:

    After configuring the output connector, and running a query, the query results are written to the configured data extension.

Using Treasure Data CLI (Command-line interface)

You can use Treasure Data CLI to run query and write output result to Salesforce Marketing Cloud.

Example

$ td query -d database_name -r '{"type": "salesforce_marketing_cloud","client_id":"i9a19hxckdv3lmo6fw24f41f","client_secret":"cGNpGs1rNBhLdxr9nuzUkNef","de_name":"data_extension_name","continue_on_failure":true}'  "select primary_key, mytext, myinteger, mydecimal, myphone, myemail, mydate, mylocale, myboolean=1 as myboolean from my_1_million" -T presto

How to map between query result and Data Extension column name

You can use the as keyword in query to map between query result and Data Extension column

Example:

Given the following Data Extension that contains subscriber information:

name: de_1 Columns: primary_key, cust_email, cust_first_name, cust_last_name, cust_phone_number

Given the following table that contains user account information:

name: account_table

Columns: id, login_id, first_name, last_name, contact_number

You can use the following query with as keyword to map between the table and Data Extension.

select id as primary_key, login_id as cust_email, first_name as cust_first_name, last_name as cust_last_name, contact_number as cust_phone_number from account_table;

Last modified: Apr 02 2018 18:39:47 UTC

If this article is incorrect or outdated, or omits critical information, let us know. For all other issues, access our support channels.