Pardot integration summary

Stitch’s Pardot integration replicates data using the Pardot API. Refer to the Schema section for a list of objects available for replication.

Pardot feature snapshot

A high-level look at Stitch's Pardot (v1) integration, including release status, useful links, and the features supported in Stitch.

STITCH
Release status

Released on February 12, 2020

Supported by

Stitch

Stitch plan

Standard

API availability

Available

Singer GitHub repository

singer-io/tap-pardot

REPLICATION SETTINGS
Anchor Scheduling

Supported

Advanced Scheduling

Supported

Table-level reset

Unsupported

Configurable Replication Methods

Unsupported

DATA SELECTION
Table selection

Supported

Column selection

Supported

Select all

Supported

TRANSPARENCY
Extraction Logs

Supported

Loading Reports

Supported

Connecting Pardot

Pardot setup requirements

To set up Pardot in Stitch, you need:

  • A user with a preferred timezone of UTC. This is required to ensure you don’t encounter Extraction errors during Daylight Savings Time, as some Replication Key fields used by Stitch are reported in Pardot using the user’s preferred timezone. By using UTC, this ensures that time data is accurately reported during extraction. Otherwise, you might encounter Extraction errors during Daylight Savings Time.


Step 1: Retrieve your Pardot business unit ID

  1. Sign into your Salesforce account.
  2. Navigate to the Setup page.
  3. Enter Pardot Account Setup in the Quick Find.
  4. The Pardot setup page will display. Copy your 18-charater Pardot Business Unit ID and keep it readily available for the next step.

Step 2: Add Pardot as a Stitch data source

  1. Sign into your Stitch account.
  2. On the Stitch Dashboard page, click the Add Integration button.

  3. Click the Pardot icon.

  4. Enter a name for the integration. This is the name that will display on the Stitch Dashboard for the integration; it’ll also be used to create the schema in your destination.

    For example, the name “Stitch Pardot” would create a schema called stitch_pardot in the destination. Note: Schema names cannot be changed after you save the integration.

  5. In the Pardot Business Unit Id field, paste your Business Unit ID that you copied in Step 1.

Step 3: Define the historical replication start date

The Sync Historical Data setting defines the starting date for your Pardot integration. This means that data equal to or newer than this date will be replicated to your data warehouse.

Change this setting if you want to replicate data beyond Pardot’s default setting of 1 year. For a detailed look at historical replication jobs, check out the Syncing Historical SaaS Data guide.

Step 4: Create a replication schedule

In the Replication Frequency section, you’ll create the integration’s replication schedule. An integration’s replication schedule determines how often Stitch runs a replication job, and the time that job begins.

Pardot integrations support the following replication scheduling methods:

To keep your row usage low, consider setting the integration to replicate less frequently. See the Understanding and Reducing Your Row Usage guide for tips on reducing your usage.

Step 5: Authorize Stitch to access Pardot

  1. When finished in the Integration Settings page, click the Authorize button. You’ll be prompted to sign into your Pardot account.
  2. Sign into your Pardot account.
  3. After the authorization process is successfully completed, you’ll be directed back to Stitch.
  4. Click All Done.

Step 6: Set objects to replicate

The last step is to select the tables and columns you want to replicate. Learn about the available tables for this integration.

Note: If a replication job is currently in progress, new selections won’t be used until the next job starts.

For Pardot integrations, you can select:

  1. Individual tables and columns

  2. All tables and columns

Click the tabs to view instructions for each selection method.

  1. In the integration’s Tables to Replicate tab, locate a table you want to replicate.
  2. To track a table, click the checkbox next to the table’s name. A blue checkmark means the table is set to replicate.

  3. To track a column, click the checkbox next to the column’s name. A blue checkmark means the column is set to replicate.

  4. Repeat this process for all the tables and columns you want to replicate.
  5. When finished, click the Finalize Your Selections button at the bottom of the screen to save your selections.
  1. Click into the integration from the Stitch Dashboard page.
  2. Click the Tables to Replicate tab.

  3. In the list of tables, click the box next to the Table Names column.
  4. In the menu that displays, click Track all Tables and Fields:

    The Track all Tables and Fields menu in the Tables to Replicate tab

  5. Click the Finalize Your Selections button at the bottom of the page to save your data selections.

Initial and historical replication jobs

After you finish setting up Pardot, its Sync Status may show as Pending on either the Stitch Dashboard or in the Integration Details page.

For a new integration, a Pending status indicates that Stitch is in the process of scheduling the initial replication job for the integration. This may take some time to complete.

Free historical data loads

The first seven days of replication, beginning when data is first replicated, are free. Rows replicated from the new integration during this time won’t count towards your quota. Stitch offers this as a way of testing new integrations, measuring usage, and ensuring historical data volumes don’t quickly consume your quota.


Pardot table reference

Replication Method :

Key-based Incremental

Replication Key :

id

Primary Key :

id

API endpoint :

Query campaigns

The campaigns table contains info about the campaigns in your Pardot account.

id
INTEGER

The ID of the campaign.

Reference:

name
STRING

The name of the campaign.

cost
INTEGER

The cost associated to the campaign.


Replication Method :

Key-based Incremental

Replication Key :

created_at

Primary Key :

id

API endpoint :

Query email clicks

The email_clicks table contains info about email click events.

id
INTEGER

The email click ID.

created_at
DATE-TIME

The time that the email click occurred.

drip_program_action_id
INTEGER

The ID for the drip program action associated with the email click.

email_template_id
INTEGER

The ID for the email template associated with the email click.

Reference:

list_email_id
INTEGER

The ID for the list email associated with the email click.

Reference:

prospect_id
INTEGER

The ID of the prospect associated with the email click.

Reference:

tracker_redirect_id
INTEGER

The ID of the tracker redirect associated with the email click.

url
STRING

The URL of the email click.


Replication Method :

Key-based Incremental

Replication Key :

updated_at

Primary Key :

id

API endpoint :

Query lists

The lists table contains info about the lists in your Pardot account.

id
INTEGER

The list ID.

Reference:

updated_at
DATE-TIME

The last time the list was updated.

created_at
DATE-TIME

The time the list was created.

description
STRING

The description of the list.

is_crm_visible
BOOLEAN

If true, the list will be visible in a CRM.

is_dynamic
BOOLEAN

If true, the list has prospects dynamically added to it via a set of chosen rules.

is_public
BOOLEAN

If true, the list will show on EPC pages to prospects.

name
STRING

The name of the list.

title
STRING

The title of the list, as it displays to subscribers.


Replication Method :

Key-based Incremental

Replication Key :

id : list_id : updated_at

Primary Key :

id

API endpoint :

Query list memberships

The list_memberships table contains info about list memberships.

Note: To replicate this table, the lists table must also be set to replicate.

id
INTEGER

The list membership ID.

Reference:

list_id
INTEGER

The ID of the list associated with this membership.

Reference:

updated_at
DATE-TIME

The time the membership was last updated.

created_at
DATE-TIME

The time the membership was created in Pardot.

opted_out
INTEGER

If 1, the prospect has unsubscribed from receiving emails from this list.

prospect_id
INTEGER

The ID of the prospect associated with the membership.

Reference:


Replication Method :

Key-based Incremental

Replication Key :

id : updated_at

Primary Key :

id

API endpoint :

Query opportunities

The opportunities table contains info about the opportunities in your Pardot account.

id
INTEGER

The ID of the opportunity.

Reference:

updated_at
DATE-TIME

The time the opportunity was last updated.

campaign_id
INTEGER

The ID of the campaign associated with the opportunity.

Reference:

closed_at
DATE-TIME

The time the opportunity was closed.

created_at
DATE-TIME

The time the opportunity was created.

name
STRING

The name of the opportunity.

probability
INTEGER

The probability of the opportunity. This will be a value between 0 and 100.

stage
STRING

The stage of the opportunity.

status
STRING

The status of the opportunity. Possible values are:

  • won
  • lost
  • open

type
STRING

The type of the opportunity.

value
NUMBER

The value of the opportunity.


Replication Method :

Key-based Incremental

Replication Key :

updated_at

Primary Key :

id

API endpoint :

Query prospects

The prospects table contains info about the prospects in your Pardot account.

id
INTEGER

The prospect ID.

Reference:

updated_at
DATE-TIME

The last time the prospect was updated.

address_one
STRING

The first line of the prospect’s address.

address_two
STRING

The second line of the prospect’s address.

annual_revenue
STRING

The prospect’s annual revenue.

campaign_id
INTEGER

The ID of the campaign associated with the prospect.

Reference:

city
STRING

The city of the prospect’s address.

comments
STRING

Comments about the prospect.

company
STRING

The prospect’s company.

country
STRING

The country of the prospect’s address.

created_at
DATE-TIME

The time the prospect was created.

crm_account_fid
STRING

The prospect’s account ID in a supported CRM system.

crm_contact_fid
STRING

The prospect’s contact ID in a supported CRM system.

crm_last_sync
DATE-TIME

The last time the prospect was synced with a supported CRM system.

crm_lead_fid
STRING

The prospect’s lead ID in a supported CRM system.

crm_owner_fid
STRING

The prospect’s owner ID in a supported CRM system.

crm_url
STRING

The URL to view the prospect within the CRM system.

department
STRING

The department of the prospect.

email
STRING

The prospect’s email address.

employees
STRING

The prospect’s number of employees.

fax
STRING

The prospect’s fax number.

first_name
STRING

The first name of the prospect.

grade
STRING

The prospect’s letter grade.

industry
STRING

The prospect’s industry.

is_do_not_call
BOOLEAN

If 1, the prospect prefers to not be called.

is_do_not_email
BOOLEAN

If 1, the prospect prefers to not be emailed.

is_reviewed
BOOLEAN

If 1, the prospect has been reviewed.

is_starred
BOOLEAN

If 1, the prospect has been starred.

job_title
STRING

The prospect’s job title.

last_activity_at
DATE-TIME

The time of the prospect’s last activity.

last_name
STRING

The prospect’s last name.

notes
STRING

Notes about the prospect.

opted_out
BOOLEAN

If 1, the prospect has opted out of marketing communications.

password
STRING

The prospect’s password.

phone
STRING

The prospect’s phone number.

prospect_account_id
INTEGER

The prospect’s account ID.

recent_interaction
STRING

The prospect’s most recent interaction with Pardot.

salutation
STRING

The prospect’s salutation.

score
INTEGER

The prospect’s score.

source
STRING

The source of the prospect.

state
STRING

The US state of the prospect’s address.

territory
STRING

The territory of the prospect’s address.

website
STRING

The prospect’s website.

years_in_business
STRING

The prospect’s number of years in business.

zip
STRING

The prospect’s postal code.


prospect_accounts

Replication Method :

Key-based Incremental

Replication Key :

updated_at

Primary Key :

id

API endpoint :

Query prospect accounts

The prospect_accounts table contains info about prospect accounts.

id
INTEGER

The ID of the prospect account.

updated_at
DATE-TIME

The time the prospect account was last updated.

assigned_to
OBJECT

Details about who the prospect account is assigned to.

user
OBJECT

Details about the user assigned to the prospect account.

id
INTEGER

The ID of the user.

Reference:

account
INTEGER

created_at
DATE-TIME

The time the user was created.

email
STRING

The email of the user.

first_name
STRING

The first name of the user.

job_tile
STRING

The job title of the user.

last_name
STRING

The last name of the user.

role
STRING

The role of the user.

updated_at
DATE-TIME

The time the user was last updated.

prospect_accounts (table), user (attribute)
prospect_accounts (table), assigned_to (attribute)

created_at
DATE-TIME

The time the prospect account was created.

name
STRING

The name of the prospect account.


Replication Method :

Key-based Incremental

Replication Key :

id : updated_at

Primary Key :

id

API endpoint :

Query users

The users table contains info about the users in your Pardot account.

id
INTEGER

The user ID.

Reference:

updated_at
DATE-TIME

The time the user was last updated.

created_at
DATE-TIME

The time the user was created.

email
STRING

The user’s email address.

first_name
STRING

The user’s first name.

job_title
STRING

The user’s job title.

last_name
STRING

The user’s last name.

role
STRING

The user’s role.


Replication Method :

Key-based Incremental

Replication Key :

updated_at

Primary Key :

id

API endpoint :

Query visitors

The visitors table contains info about visitors.

id
INTEGER

The visitor ID.

Reference:

updated_at
DATE-TIME

The time the visitor was last updated.

campaign_parameter
STRING

The campaign parameter (utm_campaign) for the visitor from Google Analytics.

content_parameter
STRING

The content parameter (utm_content) for the visitor from Google Analytics.

created_at
DATE-TIME

The time the visitor was created.

hostname
STRING

The visitor’s hostname.

ip_address
STRING

The visitor’s IP address.

medium_parameter
STRING

The medium parameter (utm_medium) for the visitor from Google Analytics.

page_view_count
INTEGER

The number of page views by this visitor.

source_parameter
STRING

The source parameter (utm_source) for the visitor from Google Analytics.

term_parameter
STRING

The term parameter (utm_term) for the visitor from Google Analytics.


visitor_activities

Replication Method :

Key-based Incremental

Replication Key :

id

Primary Key :

id

API endpoint :

Query visitor activities

The visitor_activities table contains info about visitor activities.

id
INTEGER

The visitor activity ID.

campaign
OBJECT

TODO

created_at
DATE-TIME

The time the visitor activity was created.

details
STRING

Details about the visitor activity.

email_id
INTEGER

The ID of the email associated with the visitor activity.

email_template_id
INTEGER

The ID of the email template associated with the visitor activity.

Reference:

file_id
INTEGER

The ID of the file associated with the visitor activity.

form_handler_id
INTEGER

The ID of the form handler associated with the visitor activity.

form_id
INTEGER

The ID of the form associated with the visitor activity.

landing_page_id
INTEGER

The ID of the landing page associated with the visitor activity.

list_email_id
INTEGER

The ID of the list email associated with the visitor activity.

Reference:

multivariate_test_variation_id
INTEGER

The ID of the multivariate test variation associated with the visitor activity.

paid_search_id_id
INTEGER

The ID of the paid search ad associated with the visitor activity.

prospect_id
INTEGER

The ID of the prospect associated with the visitor activity.

Reference:

site_search_query_id
INTEGER

The ID of the site search query associated with the visitor activity.

type
INTEGER

The type of the visitor activity. Refer to Pardot’s documentation for a full list of possible values.

type_name
STRING

The type name of the visitor activity. Refer to Pardot’s documentation for a full list of possible values.

visitor_id
INTEGER

The ID of the visitor.

Reference:

visitor_page_view_id
INTEGER

The ID of the visitor page view associated with the visitor activity.


Replication Method :

Key-based Incremental

Replication Key :

id : updated_at

Primary Key :

id

API endpoint :

Query visits

The visits table contains info about visits.

Note: To replicate this table, you must also set the visitors table to replicate.

id
INTEGER

The ID of the visit.

Reference:

updated_at
DATE-TIME

The time the visit was last updated.

campaign_parameter
STRING

The campaign parameter (utm_campaign) for the visit from Google Analytics.

content_parameter
STRING

The content parameter (utm_content) for the visit from Google Analytics.

created_at
DATE-TIME

The time the visit was created.

duration_in_seconds
INTEGER

The length of the visit, in seconds.

first_visitor_page_view_at
DATE-TIME

The time of the first page view for this visit.

last_visitor_page_view_at
DATE-TIME

The time of the last page view for this visit.

medium_parameter
STRING

The medium parameter (utm_medium) for the visit from Google Analytics.

prospect_id
INTEGER

The ID of the prospect associated with the visit.

Reference:

source_parameter
STRING

The source parameter (utm_source) for the visit from Google Analytics.

term_parameter
STRING

The term parameter (utm_term) for the visit from Google Analytics.

visitor_id
INTEGER

The ID of the visitor associated with the visit.

Reference:

visitor_page_view_count
INTEGER

The number of page views for this visit.

visitor_page_views
OBJECT

Details about page views during the visit.

visitor_page_view
ARRAY

A list of page view events during the visit.

created_at
DATE-TIME

The time the page view was created during the visit.

id
INTEGER

The ID of the page view.

title
STRING

The title of the page that was viewed.

url
STRING

The URl of the page that was viewed.

visits (table), visitor_page_view (attribute)
visits (table), visitor_page_views (attribute)


Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.