Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Table of Contents
maxLevel3

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

...

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. 

...

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. 

...

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.

Numbered Headings
start-numbering-with1
start-numbering-ath5
Open TD Console.
Navigate to Integrations Hub Catalog.
Search for and select Salesforce Pardot.

Select Create Authentication.

 Enter values for the following parameters:


Expand
titleAuthenticate with a new OAuth connection for the Treasure Data Connected App
  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.




Expand
titleAuthenticate using a Custom Connected app

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


Numbered Headings
start-numbering-with1
start-numbering-ath5
 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


Numbered Headings
start-numbering-with1
start-numbering-ath5
 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.

...

Numbered Headings

Excerpt Include
PD:Exporting Your Query Data to Your Destination
PD:Exporting Your Query Data to Your Destination
nopaneltrue

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

...

Code Block
languagesql
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

...

Code Block
languagesql
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.

Numbered Headings
start-numbering-with1
start-numbering-ath5

Excerpt Include
PD:Scheduling Jobs Using TD Console
PD:Scheduling Jobs Using TD Console
nopaneltrue


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 


Code Block
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.

...

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

Numbered Headings
start-numbering-ath5
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).

Image Modified


Authorize Your App to Access your Salesforce Pardot Account

Open a web browser and type in the following:

...

Numbered Headings
start-numbering-with1
start-numbering-ath5
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 =
Code Block
http://localhost:8080/?code=xxxx%3D%3D

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



Obtain a Refresh Token


Expand
titleUsing Postman
  1. Open the Postman tool.


  2. Create a request. For example:

    With:




  3. Select Send.

...