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.


This Data Connector is in Beta. For more information, contact support@treasuredata.com.


Prerequisites

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 your jobs execution 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 only support URI length to a certain length then we add 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:

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 > PardotPardot 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. 

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.

Open TD Console.
Navigate to Integrations Hub Catalog.
Search for and select Salesforce Pardot.

Select Create Authentication.

 Enter values for the following parameters:


  1. Select Click here.
  2. Login to your Salesforce app.
  3. Select Allow.


    You are redirected to TD Console.

  4. Navigate to Integrations Hub Catalog.

  5. Search for and select Salesforce Pardot.

  6. Confirm that your connection is displayed in the OAuth connection field.




You could use your Custom Connected App if your Salesforce login site is different than https://login.salesforce.com or if you have trouble authenticating using the Treasure Data Connected App option.  

  1. Select Custom Connected App.

  2. Enter your connected app's Client ID.
  3. Enter your connected app's Client Secret.
  4. Enter your connected app's Login URL.
  5. Enter your Refresh Token.


Select Continue.
Type a name for your connection.
Select Done.



Configure Output Target Data Object

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

Configure Output Data To Prospects Data Object


 Open the TD Console.
 Navigate to Data Workbench > Queries.
 Select the query that you plan to use to export data.
Select Export Results.

 Choose Use Existing Integration
 Select your saved connection name in previous steps

Select Prospects. 

Input the List ID you wish for your Prospects to be added to in Pardot (can be a single List ID or comma separated string of List ID).
Select Add or Remove in the Prospect List Operation to add or remove Prospects in or out of the List ID you have specified.
 Select Upserting prospects by emails if you want to export only email as primary key when upserting prospects. 
Configure the number of Concurrent API Calls within the limit of your Pardot plan for parallel processing.
The rest of the parameters have default recommended values for the connector. However, when there are exceptions, adjust the values accordingly to avoid failed jobs. For example: Depends on the Pardot server to change Maximum Payload from default 5000 characters to 4000 characters if job failed by 404 error "Request URI are too long".

Click Done

Configure Output Data To List Membership Data Object


 Open the TD Console.
 Navigate to Data Workbench > Queries.
 Select the query that you plan to use to export data.
Select Export Results.

 Select Use Existing Integration.
 Select your saved connection.

Select List Memberships to export your data from Treasure Data audience segments to Pardot. 

Select Upsert or Delete. The respective operation will be used as your selection.
The rest of the parameters have default recommended values for the connector.
However, when there are exceptions, adjust the values accordingly to avoid failed jobs. For example: Depends on the Pardot server to change Maximum Payload from default 5000 characters to 4000 characters if job failed by 404 error "Request URI are too long".
 Select Done.


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.

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


Salesforce Paradot Column Mapping 

Column Metadata for Prospects Data Object

Column NameData TypeRequired?DescriptionNote
idIntegerPardot ID for this prospectshould be left null in case of inserting new prospects
fidStringCRM FID if syncing with Salesforce Leadsshould be left null in case of inserting new prospects
emailStringProspect's email addressalways required in case of inserting new prospects
campaign_idInteger
Pardot ID of the campaign associated with this prospect
salutationString
Prospect's formal prefix
first_nameString
Prospect's first name
last_nameString
Prospect's last name
passwordString
Prospect's password
companyString
Prospect's company
prospect_account_idInteger
Prospect's account ID
websiteString
Prospect's website URL
job_titleString
Prospect's job title
departmentString
Prospect's department
countryString
Prospect's country
address_oneString
Prospect's address, line 1
address_twoString
Prospect's address, line 2
cityString
Prospect's city
stateString
Prospect's US state
territoryString
Prospect's territory
zipString
Prospect's postal code
phoneString
Prospect's phone number
faxString
Prospect's fax number
sourceString
Prospect's source
annual_revenueString
Prospect's annual revenue
employeesString
Prospect's number of employees
industryString
Prospect's industry
years_in_businessString
Prospect's number of years in business
commentsString
Comments about this prospect
notesString
Notes about this prospect
scoreInteger
Prospect's score
is_do_not_emailBoolean
If value is 1, prospect prefers not to be emailed
is_do_not_callBoolean
If value is 1, prospect prefers not to be called
is_reviewedBoolean
If value is 1, prospect has been reviewed
is_starredBoolean
If value is 1, prospect has been starred
is_archivedBoolean
If value is 1, prospect has been archived

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

Column NameData TypeDescriptionRequired?
prospect_idIntegerProspect's ID
list_idIntegerList's ID

Sample query

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

Integration Parameters for Salesforce Pardot

ParameterValuesDescription
client_idxxxxYour connected app's consumer key
client_secretxxxxYour connected app's secret key
refresh_tokenxxxxYour refresh token obtained after authorized your connected app
login_urlhttps://login.salesforce.com/Your Salesforce's login URL
pardot_domain

https://pi.pardot.com

Your Pardot's domain
business_unit0Uv4xxxxxYour Business Unit Id
data_objectprospectsYour target data object must be either prospects or list_memberships
skip_invalid_recordstrueSet this value to true if you would like to skip failed record
prospect_operationsyncThe operation to send prospects data to Pardot, sync is supported
prospect_listsxxxxComma separate of prospect list ids in case `data_object = prospects` (string)
prospect_list_operationaddOperation to add/remove prospects if `prospect_lists` is set. Applicable in case `data_object = prospects` (enum(`add`, `remove`), default: `add`)
upsert_by_emailfalseUpsert propsect using email as key
parallel_tasks1Number of tasks to run in parallel when sending prospects data to Pardot
max_payload5000

Payload size when sending prospects data to Pardot.

This is a parameter for fine-tuning for each Pardot instance, they will have a different max URI configured.

Whenever you reach the error 414 Request-URI Too Long when sending prospects then please lower this value.

Otherwise, the default value is enough.

list_membership_operationupsertThe operation to send list memberships data to Pardot. Must be upsert or delete
maximum_retries5Maximum number of retry when request failed
initial_retry_wait1The initial amount of time before the first retry in seconds
maximum_retry_wait120The maximum amount of time to wait for a retry in seconds
maximum_connection_timeout300Maximum connection time for a request to 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.


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

Login to your Salesforce Lightning Experience.
Select the gear icon. 
Select Setup.

Navigate to Platform Tools > Apps.
Select App Manager.


Select on New Connected App.


Type the required information in Basic information.


Select Enable OAuth Settings.


Navigate to API (Enable OAuth Settings) > Callback URL.
Type http://localhost:8080.
Navigate to API (Enable OAuth Settings) > Selected OAuth Scopes.
You MUST select Access Pardot services (pardot_api) and Perform requests on your behalf at any times (refresh_token, offline_access).

Scroll to the end.

Select Save.
Select Continue.

Write down the Consumer Key (this will be your Client ID).

Select Click to reveal to get your Consumer Secret. 
Write down (this will be your Client Secret).


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


Log in to your account.
Select Allow.



You are redirected to a non-existing page.


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


  1. Open the Postman tool.


  2. Create a request. For example:

    With:




  3. Select Send.



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'

After sending the request you will receive a response like below

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

The value in refresh_token is your Refresh Token used in the Authentication screen.