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
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.
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.
1. Open TD Console.
2. Navigate to Integrations Hub > Catalog.
3. Search for and select Salesforce Pardot.
4. Select Create Authentication.
5. Enter values for the following parameters:
- Pardot Host - if you are using a Production account it would be https://pi.pardot.com. For a Pardot Developer Org or a Sanbox account, it would be https://pi.demo.pardot.com.
- Pardot Business ID
6. Select Continue.
7. Type a name for your connection.
8. 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
1. Open the TD Console.
2. Navigate to Data Workbench > Queries.
3. Select the query that you plan to use to export data.
4. Select Export Results.
5. Choose Use Existing Integration
6. Select your saved connection name in previous steps
7. Select Prospects.
8. Input the List ID you want for your Prospects to be added to in Pardot (can be a single List ID or comma-separated string of List ID).
9. Select Add or Remove in the Prospect List Operation to add or remove Prospects in or out of the List ID you have specified.
10. Select Upserting prospects by emails if you want to export only email as the primary key when upserting prospects.
11. Configure the number of Concurrent API Calls within the limit of your Pardot plan for parallel processing.
12. 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 the job failed by 404 error "Request URI is too long".
13. Click Done
Configure Output Data To List Membership Data Object
1. Open the TD Console.
2. Navigate to Data Workbench > Queries.
3. Select the query that you plan to use to export data.
4. Select Export Results.
5. Select Use Existing Integration.
6. Select your saved connection.
7. Select List Memberships to export your data from Treasure Data audience segments to Pardot.
8. Select Upsert or Delete. The respective operation will be used as your selection.
9. 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 the job failed by 404 error "Request URI is too long".
10. 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.
Depending on your data object, you will need different column mapping.
- Complete the instructions in Creating a Destination Integration.
Navigate to Data Workbench > Queries.
Select a query for which you would like to export data.
Run the query to validate the result set.
Select Export Results.
- Select an existing integration authentication.
- Define any additional Export Results details. In your export integration content review the integration parameters.
For example, your Export Results screen might be different, or you might not have additional details to fill out: Select Done.
Run your query.
Validate that your data moved to the destination you specified.
Salesforce Paradot Column Mapping
Column Metadata for Prospects Data Object
Column Name | Data Type | Required? | Description | Note |
---|---|---|---|---|
id | Integer | ✅ | Pardot ID for this prospect | should be left null in case of inserting new prospects |
fid | String | ✅ | CRM FID if syncing with Salesforce Leads | should be left null in case of inserting new prospects |
String | ✅ | Prospect's email address | always required in case of inserting new prospects | |
campaign_id | Integer | Pardot ID of the campaign associated with this prospect | ||
salutation | String | Prospect's formal prefix | ||
first_name | String | Prospect's first name | ||
last_name | String | Prospect's last name | ||
password | String | Prospect's password | ||
company | String | Prospect's company | ||
prospect_account_id | Integer | Prospect's account ID | ||
website | String | Prospect's website URL | ||
job_title | String | Prospect's job title | ||
department | String | Prospect's department | ||
country | String | Prospect's country | ||
address_one | String | Prospect's address, line 1 | ||
address_two | String | Prospect's address, line 2 | ||
city | String | Prospect's city | ||
state | String | Prospect's US state | ||
territory | String | Prospect's territory | ||
zip | String | Prospect's postal code | ||
phone | String | Prospect's phone number | ||
fax | String | Prospect's fax number | ||
source | String | Prospect's source | ||
annual_revenue | String | Prospect's annual revenue | ||
employees | String | Prospect's number of employees | ||
industry | String | Prospect's industry | ||
years_in_business | String | Prospect's number of years in business | ||
comments | String | Comments about this prospect | ||
notes | String | Notes about this prospect | ||
score | Integer | Prospect's score | ||
is_do_not_email | Boolean | If the value is 1, the prospect prefers not to be emailed | ||
is_do_not_call | Boolean | If the value is 1, the prospect prefers not to be called | ||
is_reviewed | Boolean | If the value is 1, the prospect has been reviewed | ||
is_starred | Boolean | If the value is 1, the prospect has been starred | ||
is_archived | Boolean | If the value is 1, the 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 Name | Data Type | Description | Required? |
---|---|---|---|
prospect_id | Integer | Prospect's ID | ✅ |
list_id | Integer | List's ID | ✅ |
Sample query
SELECT list_id, prospect_id FROM (VALUES (xxx, yyyy)) FROM table1 (list_id, prospect_id)
Integration Parameters for Salesforce Pardot
Parameter | Values | Description |
---|---|---|
client_id | xxxx | Your connected app's consumer key |
client_secret | xxxx | Your connected app's secret key |
refresh_token | xxxx | Your refresh token obtained after authorizing your connected app |
login_url | https://login.salesforce.com/ | Your Salesforce's login URL |
pardot_domain | https://pi.pardot.com | Your Pardot's domain |
business_unit | 0Uv4xxxxx | Your Business Unit Id |
data_object | prospects | Your target data object must be either prospects or list_memberships |
skip_invalid_records | true | Set this value to true if you would like to skip failed record |
prospect_operation | sync | The operation to send prospects data to Pardot, sync is supported |
prospect_lists | xxxx | Comma separate of prospect list ids in case `data_object = prospects` (string) |
prospect_list_operation | add | Operation to add/remove prospects if `prospect_lists` is set. Applicable in case `data_object = prospects` (enum(`add`, `remove`), default: `add`) |
upsert_by_email | false | Upsert propsect using email as key |
parallel_tasks | 1 | Number of tasks to run in parallel when sending prospects data to Pardot |
max_payload | 5000 | 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_operation | upsert | The operation to send list memberships data to Pardot. Must be upsert or delete |
maximum_retries | 5 | Maximum number of retries when request failed |
initial_retry_wait | 1 | The initial amount of time before the first retry in seconds |
maximum_retry_wait | 120 | The maximum amount of time to wait for a retry in seconds |
maximum_connection_timeout | 300 | Maximum 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.
1. Navigate to Data Workbench > Queries.
2. Create a new query or select an existing query.
3. Next to Schedule, select None.
4. In the drop-down, select one of the following schedule options:
Drop-down Value | Description |
---|---|
Custom cron... | Review Custom cron... details. |
@daily (midnight) | Run once a day at midnight (00:00 am) in the specified time zone. |
@hourly (:00) | Run every hour at 00 minutes. |
None | No schedule. |
Custom cron... Details
Cron Value | Description |
---|---|
| Run once an hour. |
| Run once a day at midnight. |
| Run once a month at midnight on the morning of the first day of the month. |
"" | Create a job that has no scheduled run time. |
* * * * * - - - - - | | | | | | | | | +----- day of week (0 - 6) (Sunday=0) | | | +---------- month (1 - 12) | | +--------------- day of month (1 - 31) | +-------------------- hour (0 - 23) +------------------------- min (0 - 59)
The following named entries can be used:
Day of Week: sun, mon, tue, wed, thu, fri, sat.
Month: jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec.
A single space is required between each field. The values for each field can be composed of:
Field Value | Example | Example Description |
---|---|---|
A single value, within the limits displayed above for each field. | ||
A wildcard | ‘0 0 1 * *’ | Configures the schedule to run at midnight (00:00) on the first day of each month. |
A range ‘2-5’ , indicating the range of accepted values for the field. | ‘0 0 1-10 * *’ | Configures the schedule to run at midnight (00:00) on the first 10 days of each month. |
A list of comma-separated values ‘2,3,4,5’ , indicating the list of accepted values for the field. |
| Configures the schedule to run at midnight (00:00) every 1st, 11th, and 21st day of each month. |
A periodicity indicator ‘*/5’ to express how often based on the field’s valid range of values a schedule is allowed to run. |
| Configures the schedule to run on the 1st of every month, every 2 hours starting at 00:30. ‘0 0 */5 * *’ configures the schedule to run at midnight (00:00) every 5 days starting on the 5th of each month. |
A comma-separated list of any of the above except the ‘*’ wildcard is also supported ‘2,*/5,8-10’ . | ‘0 0 5,*/10,25 * *’ | Configures the schedule to run at midnight (00:00) every 5th, 10th, 20th, and 25th day of each month. |
5. (Optional) You can delay the start time of a query by enabling the Delay execution.
Execute the Query
Save the query with a name and run, or just run the query. Upon successful completion of the query, the query result is automatically imported to the specified container destination.
Scheduled jobs that continuously fail due to configuration errors may be disabled on the system side after several notifications.
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
1. Login to your Salesforce Lightning Experience.
2. Select the gear icon.
3. Select Setup.
4. Navigate to Platform Tools > Apps.
5. Select App Manager.
6. Select on New Connected App.
7. Type the required information in Basic information.
8. Select Enable OAuth Settings.
9. Navigate to API (Enable OAuth Settings) > Callback URL.
10. Type http://localhost:8080.
11. Navigate to API (Enable OAuth Settings) > Selected OAuth Scopes.
12. You MUST select Access Pardot services (pardot_api) and Perform requests on your behalf at any time (refresh_token, offline_access).
13. Scroll to the end.
14. Select Save.
15. Select Continue.
16. Write down the Consumer Key (this will be your Client ID).
17. Select Click to reveal to get your Consumer Secret.
18. 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
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