You can use the Salesforce Pardot to send job results (in the form of prospects or list memberships) from Treasure Data directly to Salesforce Pardot to sync up your CRM data.

Pardot offers marketing automation to help marketing and sales teams find and nurture leads, close more deals, and maximize ROI.

Prerequisites

• Basic Knowledge of Treasure Data.

• Basic knowledge of Salesforce Pardot models

• A working Salesforce Pardot account

• Your Business Unit ID. 

Supported

We support sending data to Prospect or List Memberships in Salesforce Pardot.

We support parallel prospects upsert. Use this feature whenever you would like to speed up the execution of your jobs by increasing the number of parallel tasks. But please be aware of your account's concurrent calls limit.

Requirements and Limitations

Customers have an adequate Salesforce Pardot plan (Pro or Ultimate) in order to use the connector.

For Prospects, due to the API, Treasure Data only supports a URI length to a certain length, then we add a restriction if a row contains data that is greater than 7500 characters in length (after serialized as JSON) will be considered an invalid record.

For List Membership, opting out prospects is not supported because it is a one-way action and can not be reverted.

The connector adheres to all Pardot API limits below:

• The daily Pardot API call limit and the concurrent Pardot API call limit apply to Import API calls just as they would any other Pardot API calls.

Column names in your export results must be in a lower letter case and strictly follow Pardot field names of each data object (case sensitivity).

Obtain the Pardot Business Unit ID

To find the Pardot Business Unit ID, use Setup in Salesforce. 

Your Pardot Business Unit ID begins with "0Uv" and is 18 characters long.

If you cannot access the Pardot Account Setup information, ask your Salesforce Administrator to provide you with the Pardot Business Unit ID. https://developer.pardot.com/kb/authentication/.

Authenticating with Pardot requires entering your Business Unit ID. 

1. Log into Salesforce using the same account you use to log into Pardot.

2. Navigate to Setup > Pardot > Pardot Account Setup.  OR, from Setup, enter "Pardot Account Setup" in the Quick Find box.

3. You may have multiple Pardot business units. 
Your Pardot Business Unit ID begins with "0Uv" and is 18 characters long.

Define your Custom Connected App

A custom connected app, the correct authorization, and the refresh token are all required to define your Pardot authorization within Treasure Data. 

See the Using your Custom Connected App instructions at the end of this HTML page. 

• Using your Custom Connected App
  • Define Your Connected App
  • Authorize Your App to Access your Salesforce Pardot Account
  • Obtain a Refresh Token

Use the TD Console to Create Your Connection

Create a New Connection

In Treasure Data, you must create and configure the data connection prior to running your query. As part of the data connection, you provide authentication to access the integration.

Configure Output Target Data Object

We support sending data to Prospect or List Memberships in Salesforce Pardot.

Configure Output Data To Prospects Data Object

Configure Output Data To List Membership Data Object

Define your Query

You need to define the column mapping in the query. The columns in the query represent Prospects or List Memberships fields to be imported into Salesforce Pardot.

Depending on your data object, you will need different column mapping.

Salesforce Paradot Column Mapping 

Column Metadata for Prospects Data Object

Sample query

Upsert a prospect to a list_xxx, the query will be: 
SELECT 'xxx' as id FROM prospect_table
Or
SELECT email from prospect_table

Column Metadata for List Membership Data Object

Sample query

SELECT list_id, prospect_id FROM (VALUES (xxx, yyyy)) FROM table1 (list_id, prospect_id)

Integration Parameters for Salesforce Pardot

Optionally Schedule the Query Export Jobs

You can use Scheduled Jobs with Result Export to periodically write the output result to a target destination that you specify.

 *    *    *    *    *
 -    -    -    -    -
 |    |    |    |    |
 |    |    |    |    +----- day of week (0 - 6) (Sunday=0)
 |    |    |    +---------- month (1 - 12)
 |    |    +--------------- day of month (1 - 31)
 |    +-------------------- hour (0 - 23)
 +------------------------- min (0 - 59)

Optionally Configure Export Results in Workflow

Within Treasure Workflow, you can specify the use of this data connector to export data.

Learn more at Using Workflows to Export Data with the TD Toolbelt.

Example Workflow for Salesforce Pardot 

timezone: UTC

_export:
  td:
    database: sample_datasets

+td-result-into-target:
  td>: queries/sample.sql
  result_connection: salesforce_pardot
  result_settings:
    data_object: prospects
    prospect_operation: sync
    prospect_lists: xxxx
    prospect_list_operation: add
    upsert_by_email: true
    parallel_tasks: x
    skip_invalid_records: true
    max_payload: 5000
    maximum_retries: 5
    initial_retry_wait: 1
    maximum_retry_wait: 120
    maximum_connection_timeout: 300

Using your Custom Connected App

To integrate with your Pardot account, you will need a Connected App created by Treasure Data or yourself.

The connected app created by you is called a custom connected app.

Optionally, you could use our default connected app.

If you use a custom domain name for your Salesforce instances or your login URL is not https://login.salesforce.com then a custom connected app is required.

Define Your Connected App

Authorize Your App to Access your Salesforce Pardot Account

Open a web browser and type in the following:

https://{YOUR_SALESFORCE_LOGIN_URL}/services/oauth2/authorize?client_id={YOUR_CONSUMER_KEY}&redirect_uri=http://localhost:8080&response_type=code

YOUR_SALESFORCE_LOGIN_URL: {Your salesforce login url - i.e https://login.salesforce.com}
YOUR_CONSUMER_KEY: {The consumer key you got in create connected app step}

An example of full URL: https://login.salesforce.com/services/oauth2/authorize?client_id=xxxxxx&redirect_uri=http://localhost:8080&response_type=code

1. Log in to your account.

2. Select Allow.

3. You are redirected to a non-existing page.

4. Copy the URL and select the text after code= and replace %3D with =

http://localhost:8080/?code=xxxx%3D%3D

For example xxxx== this is the code to get refresh token.

Obtain a Refresh Token

Alternative you could use cUrl

curl --location --request POST 'https://{your_salesforce_login_url}/services/oauth2/token' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'grant_type=authorization_code' \
--data-urlencode 'code=your_code' \
--data-urlencode 'client_id=client_id' \
--data-urlencode 'client_secret=client_secret' \
--data-urlencode 'redirect_uri=http://localhost:8080'

{
    "access_token": "xxxxx",
    "refresh_token": "xxxx",
    "signature": "xxxx",
    "scope": "refresh_token pardot_api",
    "instance_url": "xxxx",
    "id": "xxx",
    "token_type": "Bearer",
    "issued_at": "1616662486135"
}