This page provides a guide to Establishing a Connection to Salesforce Marketing Cloud Account Engagement in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Salesforce Marketing Cloud Account Engagement

Establishing a Connection shows how to authenticate to Salesforce Marketing Cloud Account Engagement and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Salesforce Marketing Cloud Account Engagement through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Salesforce Marketing Cloud Account Engagement by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Connecting to Salesforce Marketing Cloud Account Engagement

To connect to Salesforce Marketing Cloud Account Engagement you need to obtain the Pardot Business Unit ID. To do so, use Setup in Salesforce. From Setup, enter "Pardot Account Setup" in the Quick Find field.

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.

Authenticating to Salesforce Marketing Cloud Account Engagement

OAuth

AuthScheme must be set to OAuth in all user account flows.

Desktop Applications

CData provides an embedded OAuth application that simplifies OAuth desktop Authentication. Alternatively, you can create a custom application. See Creating a Custom OAuth Application for information on creating custom applications and reasons for doing so.

For authentication, the only difference between the two methods is that you must set two additional connection properties when using custom OAuth applications.

After setting the following connection properties, you are ready to connect:

• OAuthClientId: (custom applications only) Set to the client ID in your application settings.
• OAuthClientSecret: (custom applications only) Set to the client Secret in your application settings.

Web Applications

When connecting via a web application, you need to register a custom OAuth application with Salesforce Marketing Cloud Account Engagement. See Creating a Custom OAuth Application for more information. You can then use the driver to get and manage the OAuth token values.

First, get an OAuthAccessToken by setting the following connection properties:

• OAuthClientId: Set to the client Id in your application settings.
• OAuthClientSecret: Set to the client secret in your application settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationUrl stored procedure. Set the CallbackURL input to the callback URL you specified in your application settings. If necessary, set the Scope parameter to request custom permissions. The stored procedure returns the URL of the OAuth endpoint.
2. Open the URL, log in, and authorize the application. You are redirected back to the callback URL.
3. Call the GetOAuthAccessToken stored procedure. Set the Authmode input to WEB. Set the Verifier input to the "code" parameter in the query string of the callback URL. If necessary, set the Scope parameter to request custom permissions.

After you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the driver automatically refresh the OAuth access token, set the following on the first data connection:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the client Id in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• RefreshOAuthAccessToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where the Cloud saves the OAuth token values, which persist across connections.

On subsequent data connections, the values for OAuthAccessToken and OAuthRefreshToken are taken from OAuthSettingsLocation.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token.

Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the client Id in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Headless Machines

To configure the driver to use OAuth with a user account on a headless machine, you need to authenticate on another device that has an internet browser.

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the Cloud on a machine with a browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow, as described in "Transfer OAuth Settings" below.

2. Then configure the Cloud to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application click Salesforce Marketing Cloud Account Engagement OAuth endpoint to open the endpoint in your browser.
   • If you are using a Custom OAuthd Application, create the Authorization URL by setting the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client Id assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.

2. Log in and grant permissions to the Cloud. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. Later you will set this in the OAuthVerifier connection property.

Next, you need to exchange the OAuth verifier code for OAuth refresh and access tokens. Set the following properties:

On the headless machine, set the following connection properties to obtain the OAuth authentication values.

• InitiateOAuth: Set this to REFRESH.
• OAuthVerifier: Set this to the verifier code.
• OAuthClientId: (custom applications only) Set this to the client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the client secret in the custom OAuth application settings.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified location.

After the OAuth settings file is generated, you need to re-set the following properties to connect:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the Cloud to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to install and create a connection with the driver on a device that supports an internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

After you have successfully tested the connection, copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location of your OAuth settings file. Make sure this location gives read and write permissions to the Cloud to enable the automatic refreshing of the access token.

OAuthPassword

When authenticating with a username and password, set the following connection properties:

• AuthScheme: Set this to OAuthPassword.
• User: Set this to the username.
• Password: Set this to the user password.
• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.

OAuthJWT

Set the AuthScheme to OAuthJWT.

After creating your OAuth Application (see Creating a Custom OAuth Application), set the following connection properties:

• AuthScheme: OAuthJWT.
• InitiateOAuth: GETANDREFRESH.
• OAuthJWTCert: The JWT Certificate store.
• OAuthJWTPassword: The Password of the JWT Certificate store.
• OAuthJWTIssuer: The OAuth Client ID.
• OAuthJWTCertType: The type of the certificate store specified by OAuthJWTCert.

Note: This flow never issues a refresh token.

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Cloud uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

Overview

This section shows the available API objects and provides more information on executing SQL to Salesforce Marketing Cloud Account Engagement APIs.

Key Features

• The Cloud models Salesforce Marketing Cloud Account Engagement entities like documents, folders, and groups as relational views, allowing you to write SQL to query Salesforce Marketing Cloud Account Engagement data.
• Stored procedures allow you to execute operations to Salesforce Marketing Cloud Account Engagement, including retrieving the access token and keeping it refreshed in OAuth 2.0.
• Live connectivity to these objects means any changes to your Salesforce Marketing Cloud Account Engagement account are immediately reflected when using the Cloud.

The CData Cloud models Salesforce Marketing Cloud Account Engagement entities as relational Tables, Views, and Stored Procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

The Cloud models the data in Salesforce Marketing Cloud Account Engagement as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Salesforce Marketing Cloud Account Engagement Tables

Name	Description
Campaigns	Retrieve and modify campaigns.
CustomFields	Retrieve and modify custom fields.
ListMemberships	Retrieve and modify list memberships.
Lists	Retrieve and modify lists.
Opportunities	Retrieve and modify opportunities.
ProspectAccounts	Retrieve and modify prospect accounts.
Prospects	Retrieve and modify prospects.

Retrieve and modify campaigns.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• Name supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Campaigns WHERE Id = 43
SELECT * FROM Campaigns WHERE Name = 'Website Tracking'

Insert

This table does not have any required attributes when performing an insert.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID for this campaign.
Name	String	False		Campaign's name.
Cost	Integer	False		Cost associated to the campaign.
CrmFId	String	True		The CRM FID associated to the campaign.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
CreatedAt	Datetime	Filter by the creation time.
UpdatedAt	Datetime	Filter by the modification time.

Retrieve and modify custom fields.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM CustomFields WHERE Id = 50
SELECT * FROM CustomFields WHERE CreatedAt > '01/01/2019'

Insert

The following attributes are required when performing an insert: FieldId, Name.

INSERT INTO CustomFields (FieldId, Name) VALUES (100, 'Example')

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID for this custom field.
Name	String	False		Custom field's name.
FieldId	String	True		API ID for custom field.
Type	String	False		type of field.
TypeId	Int64	True		Pardot ID for custom field's type.
CreatedAt	Datetime	True		Time custom field was created in Pardot.
UpdatedAt	Datetime	True		Last time custom field was updated.
IsRecordMultipleResponses	Boolean	False		If true, this custom field will record multiple responses.
CrmId	String	False		The CRM ID of the field you would like to map to this custom field.
IsUseValues	Boolean	False		If true, this custom field will use predefined values.

Retrieve and modify list memberships.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• ListId supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM ListMemberships WHERE Id = 26
SELECT * FROM ListMemberships WHERE ListId = 44
SELECT * FROM ListMemberships WHERE CreatedAt > '01/01/2019'

Insert

The following attributes are required when performing an insert: ListId, ProspectId.

INSERT INTO ListMemberships (ListId, ProspectId) VALUES (20104, 3457990)

Get Deleted

To retrieve deleted records, a GETDELETED statement can be executed. The GETDELETED statement supports the same server side filters that are supported by the SELECT statement (except ID when using the equality(=) operator), but does not perform any client side filtering.

GETDELETED FROM ListMemberships WHERE ID = 231

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID for this list membership.
ListId	Int64	True	Lists.Id	Pardot ID of the list for this membership.
ProspectId	Int64	True	Prospects.Id	Pardot ID of the prospect for this membership.
OptedOut	Integer	False		If value is 1, the prospect is unsubscribed from receiving emails from this list.
CreatedAt	Datetime	True		Time that this membership was created in Pardot.
UpdatedAt	Datetime	True		Last time that this membership was updated.

Retrieve and modify lists.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID of this list.
Name	String	False		List's name (internal to Pardot).
IsPublic	Boolean	False		If true, list will show on EPC pages to prospects.
IsDynamic	Boolean	True		If true, list has prospects dynamically added to it via a set of chosen rules.
Title	String	False		List's title (visible to subscribers).
Description	String	False		List's description.
IsCrmVisible	Boolean	False		If true, list will be visible in CRM to add or remove from.
CreatedAt	Datetime	True		Time list was created in Pardot.
UpdatedAt	Datetime	True		Last time list was updated.

Retrieve and modify opportunities.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• Probability supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• ProspectEmail supports '='.
• ProspectId supports '='.

For example, the following queries are processed server side:

SELECT * FROM Opportunities WHERE Id = 14
SELECT * FROM Opportunities WHERE ProspectId = 34
SELECT * FROM Opportunities WHERE ProspectEmail = '[email protected]'

Insert

The following attributes are required when performing an insert: CampaignId, Name, Value, Probability, ProspectEmail, ProspectId.

INSERT INTO Opportunities (CampaignId, Name, Value, Probability, ProspectEmail, ProspectId) VALUES (1, 'Example', 3, 75, '[email protected]', 2)

Note: This table cannot be updated if a Salesforce connector is present.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID for this opportunity.
CampaignId	Int64	False	Campaigns.Id	Pardot ID of the campaign associated with this opportunity Note: Information about an opportunity's campaign association is returned in a campaign node in the XML response. However, updates to campaign associations are done by providing campaign_id during an UPDATE> request. See XML Response Formats in Using Opportunities for more details.
Name	String	False		Opportunity's name.
Value	Double	False		Opportunity's value Restrictions: value must be a positive numeric value.
Probability	Integer	False		Opportunity's probability Restrictions: value must be a positive numeric value between 0 and 100 inclusive.
Type	String	False		Opportunity's type.
Stage	String	False		Opportunity's stage.
Status	String	False		Opportunity's status Restrictions: status must be either won, lost, or open.
ClosedAt	Datetime	False		Opportunity's closed date Note: if this is left blank, the closed_at datetime (Closed Date within the app) will not be set, even when the Opportunity's stage, status or probability are set to indicate opportunity closure.
CreatedAt	Datetime	True		Time opportunity was created in Pardot.
UpdatedAt	Datetime	True		Last time opportunity was updated in Pardot.
ProspectEmail	String	False		Email of the prospect.
ProspectId	Int64	False		Id of the prospect.

Retrieve and modify prospect accounts.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID of the prospect account.
CreatedAt	Datetime	False		Filter by the creation time.
UpdatedAt	Datetime	False		Filter by the modification time.

Retrieve and modify prospects.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int63	True		Pardot ID for this prospect.
CampaignId	Int63	False	Campaigns.Id	Pardot ID of the campaign associated with this prospect Note: Information about a prospect's campaign association is returned in a campaign node in the XML response. However, updates to campaign associations are done by providing campaign_id during an UPDATE> request. See XML Response Formats in Using Prospects for more details.
Salutation	String	False		Prospect's formal prefix.
FirstName	String	False		Prospect's first name.
LastName	String	False		Prospect's last name.
Email	String	False		Prospect's email address.
Password	String	False		Prospect's password.
Company	String	False		Prospect's company.
ProspectAccountId	Int63	False	ProspectAccounts.Id	Prospect's account ID.
Website	String	False		Prospect's website URL.
JobTitle	String	False		Prospect's job title.
Department	String	False		Prospect's department.
Country	String	False		Prospect's country.
AddressOne	String	False		Prospect's address, line 1.
AddressTwo	String	False		Prospect's address, line 2.
City	String	False		Prospect's city.
State	String	False		Prospect's US state.
Territory	String	False		Prospect's territory.
Zip	String	False		Prospect's postal code.
Phone	String	False		Prospect's phone number.
Fax	String	False		Prospect's fax number.
Source	String	False		Prospect's source.
AnnualRevenue	String	False		Prospect's annual revenue.
Employees	String	False		Prospect's number of employees.
Industry	String	False		Prospect's industry.
YearsInBusiness	String	False		Prospect's number of years in business.
Comments	String	False		Comments about this prospect.
Notes	String	False		Notes about this prospect.
Score	Integer	False		Prospect's score.
Grade	String	True		Prospect's letter grade.
LastActivityAt	Datetime	True		Time stamp of this prospect's latest visitor activity.
RecentInteraction	String	True		Describes the prospect's most recent interaction with Pardot.
CrmLeadFid	String	True		Prospect's lead ID in a supported CRM system.
CrmContactFid	String	True		Prospect's contact ID in a supported CRM system.
CrmOwnerFid	String	True		Prospect's owner ID in a supported CRM system.
CrmAccountFid	String	True		Account ID in a supported CRM system.
CrmLastSync	Datetime	True		Last time this prospect was synced with a supported CRM system.
CrmUrl	String	True		URL to view the prospect within the CRM system.
IsDoNotEmail	Boolean	False		If value is 1, prospect prefers not to be emailed.
IsDoNotCall	Boolean	False		If value is 1, prospect prefers not to be called.
OptedOut	Boolean	True		If value is 1, prospect has opted out of marketing communications.
IsReviewed	Boolean	False		If value is 1, prospect has been reviewed.
IsStarred	Boolean	False		If value is 1, prospect has been starred.
CreatedAt	Datetime	True		Time prospect was created in Pardot.
UpdatedAt	Datetime	True		Last time prospect was updated in Pardot.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Salesforce Marketing Cloud Account Engagement Views

Name	Description
Account	Retrieve account info.
CustomRedirects	Retrieve and modify custom redirects.
DynamicContents	Retrieve dynamic contents.
EmailClicks	Retrieve email clicks.
Emails	Retrieve emails.
EmailStats	Retrieve email stats.
EmailTemplates	Retrieve email templates.
Forms	Retrieve forms.
LifecycleHistories	Retrieve lifecycle histories.
LifecycleStages	Retrieve lifecycle stages.
OneToOneEmails	Retrieve one to one emails.
TagObjects	Retrieve tag objects.
Tags	Retrieve tags.
Users	Retrieve users.
VisitorActivities	Retrieve visitor activities.
Visitors	Retrieve visitors.
VisitorsPageViews	Retrieve visitors page views.
Visits	Retrieve visits.

Retrieve account info.

Table Specific Information

Select

The Cloud will process all filters client side.

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this account.
Level	String		The level of product for the account.
Website	String		Account website.
VanityDomain	String		Custom vanity domain name.
PluginCampaignId	Int64		Plugin ID for account campaign.
TrackingCodeTemplate	String		Markup and code for use in tracking templates.
Address1	String		Account contact address, line 1.
Address2	String		Account contact address, line 2.
City	String		Account contact city.
State	String		Account contact state.
Territory	String		Account contact territory.
Zip	String		Account contact zip code.
Country	String		Account contact country.
Phone	String		Account contact phone number.
Fax	String		Account contact fax number.

Retrieve and modify custom redirects.

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this custom redirect.
Name	String		Custom redirect's name.
Url	String		URL for the custom redirect.
Destination	String		URL the custom redirect leads to.
CampaignId	Int64	Campaigns.Id	The campaign associated with this custom redirect.
CampaignName	String	Campaigns.Name	The campaign associated with this custom redirect.
CreatedAt	Datetime		Time custom redirect was created in Pardot.
UpdatedAt	Datetime		Last time custom redirect was updated.

Retrieve dynamic contents.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM DynamicContents WHERE Id = 32
SELECT * FROM DynamicContents WHERE CreatedAt > '02/03/2019 23:11:33'
SELECT * FROM DynamicContents WHERE UpdatedAt > '01/03/2018 23:11:33' AND UpdatedAt <= '02/03/2019 23:11:33'

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this dynamic content.
Name	String		Dynamic content's name.
EmbedCode	String		Code to embed this dynamic content onto your webpage.
EmbedUrl	String		URL to embed this dynamic content.
BaseContent	String		The default dynamic content.
BasedOn	String		Field that this dynamic content is based on.
Variation	String		The variation of content prospect will see based on the field's value Note: Information about a variation is returned in a variation node in the XML response. It contains the value of the field in the comparison tag and the content of the variation in the content tag.
CreatedAt	Datetime		Time dynamic content was created in Pardot.
UpdatedAt	Datetime		Last time dynamic content was updated.

Retrieve email clicks.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports '>', '>='.
• ListEmailId supports '='.
• DripProgramActionId supports '='.
• EmailTemplateId supports '='.
• TrackerRedirectId supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM EmailClicks WHERE ListEmailId = 20
SELECT * FROM EmailClicks WHERE DripProgramActionId = 42
SELECT * FROM EmailClicks WHERE EmailTemplateId = 26 AND TrackerRedirectId = 31 AND CreatedAt > '02/03/2019 23:11:33'

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this email click.
ProspectId	Int64	Prospects.Id	Pardot ID for the associated prospect.
Url	String		URL of the email click.
ListEmailId	Int64		Pardot ID for the associated list email. Value not present if null.
DripProgramActionId	Integer		Pardot ID for the associated drip program action. Value not present if null.
EmailTemplateId	Int64	EmailTemplates.Id	Pardot ID for the associated email template. Value not present if null.
TrackerRedirectId	Int64		Pardot ID for the associated tracker redirect. Value not present if null.
CreatedAt	Datetime		Time that email click occurred.

Retrieve emails.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports '='.

For example, the following query is processed server side:

SELECT * FROM Emails WHERE Id = 10

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this email.
Name	String		Name of this email.
Subject	String		Email Subject.
Message	String		Contains the text of email.
HTMLContent	String		Contains text and html elements of different formats.
CreatedAt	Datetime		Time the Email Was Created.

Retrieve email stats.

Table Specific Information

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. To query this table, you need to specify the Id. The rest of the filter is executed client side within the Cloud.

• Id supports '='.

For example, the following query is processed server side:

SELECT * FROM EmailStats WHERE Id = 10

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this email.
Sent	Integer		Sent status.
Delivered	Integer		Delivered status.
TotalClicks	Integer		Total clicks.
UniqueClicks	Integer		Unique clicks.
SoftBounced	Integer		Soft bounces.
HardBounced	Integer		Hard bounces.
OptOuts	Integer		Opt count.
SpamComplains	Integer		Spam complains.
Opens	Integer		Opens.
UniqueOpens	Integer		Unique opens.
DeliveryRate	String		Delivery rate.
OpensRate	String		Open rate.
ClickThroughRate	String		Click through rate.
UniqueClickThroughRate	String		Unique click through rate.
ClickOpenRatio	String		Click to open ration.
OptOutrate	String		Opt rate.
SpamComplaintRate	String		Spam complaint rate.

Retrieve email templates.

Table Specific Information

Select

By default only templates connected to OneToOneEmails will be returned.

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports '='.

For example, the following queries are processed server side:

SELECT * FROM EmailTemplates WHERE Id = 22

Columns

Name	Type	References	Description
Id	Int64		Identifier.
EmailType	Integer		Email type.
Error	Integer		Error.
HtmlMessage	String		Html content.
IsAutoResponderEmail	Integer		Whether it is an auto responder email.
IsDripEmail	Integer		Whether it is a drip email.
IsListEmail	Integer		Whether it is a lsit email.
IsOneToOneEmail	Integer		Whether it is a one to one email.
Name	String		Name
SendOptionsReplyToAddressAggregate	String		Reply to addresses.
SendOptionsSendFromDataAggregate	String		Send from information.
Subject	String		Email subject.
TextMessage	String		Email text.
TrackedHtmlMessage	String		Tracked html message.
TrackedTextMessage	String		Tracked text message.
Type	Integer		Email type.

Retrieve forms.

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this form.
Name	String		Form's name.
CampaignId	Int64	Campaigns.Id	Pardot ID of the campaign associated with this form.
EmbedCode	String		The code used to embed the form on your webpage.
CreatedAt	Datetime		Time form was created in Pardot.
UpdatedAt	Datetime		Last time form was updated.

Retrieve lifecycle histories.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM LifecycleHistories WHERE Id = 49
SELECT * FROM LifecycleHistories WHERE Id >= 44
SELECT * FROM LifecycleHistories WHERE CreatedAt <= '02/03/2019 23:11:33'

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID of this lifecycle history.
ProspectId	Int64	Prospects.Id	Pardot's ID for the prospect in this stage.
PreviousStageId	Int64		Pardot ID of the stage this prospect was previously in.
NextStageId	Int64		Pardot ID of the stage this prospect will be in next.
SecondsElapsed	Integer		Number of seconds for prospect to get to current stage.
CreatedAt	Datetime		Time lifecycle history was created in Pardot.

Retrieve lifecycle stages.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM LifecycleStages WHERE Id = 18

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID of this lifecycle stage.
Name	String		Lifecycle stage's name.
Position	Integer		Lifcycle stage's position in lifecycle.
IsLocked	Boolean		If true, lifecycle stage is locked.

Retrieve one to one emails.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports '='.

For example, the following queries are processed server side:

SELECT * FROM OneToOneEmails WHERE Id = 11

Columns

Name	Type	References	Description
Id [KEY]	Int64		Identifier.
EmailType	Integer		Email type.
IsAutoResponderEmail	Integer		Whether it is an auto responder email.
IsDripEmail	Integer		Where it is a drip email.
IsListEmail	Integer		Whether it is a list email.
IsOneToOneEmail	Integer		Whether it is a one to one email.
Name	String		Name.
Subject	String		Email subject.
Type	Integer		Email type.

Retrieve tag objects.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• TagId supports '='.
• Type supports '='.
• ObjectId supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM TagObjects WHERE Id <= 31
SELECT * FROM TagObjects WHERE TagId = 12
SELECT * FROM TagObjects WHERE CreatedAt <= '02/03/2019 23:11:33'

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID of the tag object.
TagId	Int64	Tags.Id	The Pardot ID of the tag.
Type	String		The type of object associated with the tag.
ObjectId	Int64		The Pardot ID of the object.
CreatedAt	Datetime		Time tag was associated with the object in Pardot.

Retrieve tags.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• Name supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Tags WHERE Id >= 42
SELECT * FROM Tags WHERE Name = 'criteria'
SELECT * FROM Tags WHERE CreatedAt >= '02/03/2019 23:11:33'

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this tag.
Name	String		Tag's name.
CreatedAt	Datetime		Time tag was created in Pardot.
UpdatedAt	Datetime		Last time tag was updated.

Retrieve users.

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID of the user.
Email	String		User's email address.
FirstName	String		User's first name.
LastName	String		User's last name.
JobTitle	String		User's job title.
Role	String		User's role.
CreatedAt	Datetime		Time user was created in Pardot; Time is reported in the API user's preferred timezone.
UpdatedAt	Datetime		Last time user was updated in Pardot; Time is reported in the API user's preferred timezone.

Retrieve visitor activities.

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this visitor activity.
ProspectId	Int64	Prospects.Id	Pardot ID for the associated prospect.
VisitorId	Int64	Visitors.Id	Pardot ID for the associated visitor.
Type	Integer		Visitor activity's type number; See listing below.
TypeName	String		Visitor activity's type name; See listing below.
Details	String		Details about this visitor activity such as the name of the object associated with this activity, the search phrase used in a site search query, etc.
EmailId	Int64	Emails.Id	Pardot ID of the email associated with this visitor activity Note: This node will only appear if this visitor activity has an email associated with it.
EmailTemplateId	Int64	EmailTemplates.Id	Pardot ID of the email template associated with this visitor activity Note: This node will only appear if this visitor activity has an email associated with it.
ListEmailId	Int64		Pardot ID of the list email associated with this visitor activity Note: This node will only appear if this visitor activity has an email associated with it.
FormId	Int64	Forms.Id	Pardot ID of the form associated with this visitor activity Note: This node will only appear if this visitor activity has a form associated with it.
FormHandlerId	Int64		Pardot ID of the form handler associated with this visitor activity Note: This node will only appear if this visitor activity has a form handler associated with it.
SiteSearchQueryId	Int64		Pardot ID of the site search query associated with this visitor activity Note: This node will only appear if this visitor activity has a site search query associated with it.
LandingPageId	Int64		Pardot ID of the landing page associated with this visitor activity Note: This node will only appear if this visitor activity has a landing page associated with it.
PaidSearchIdId	Int64		Pardot ID of the paid search ad associated with this visitor activity Note: This node will only appear if this visitor activity has a paid search ad associated with it.
MultivariateTestVariationId	Int64		Pardot ID of the multivariate test variation associated with this visitor activity Note: This node will only appear if this visitor activity has a multivariate test variation associated with it.
VisitorPageViewId	Int64		Pardot ID of the visitor page view associated with this visitor activity Note: This node will only appear if this visitor activity has a visitor page view associated with it.
FileId	Int64		Pardot ID of the file associated with this visitor activity Note: This node will only appear if this visitor activity has a file associated with it.
CampaignId	Int64	Campaigns.Id	Campaign id.
CampaignName	String		Campaign name.
CampaignCost	Integer		Campaign cost.
CreatedAt	Datetime		Time that visitor activity occurred.

Retrieve visitors.

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this visitor.
PageViewCount	Integer		Number of page views by this visitor.
IpAddress	String		Visitor's IP address.
Hostname	String		Visitor's hostname.
CampaignParameter	String		Visitor's campaign parameter utm_campaign from Google Analytics.
MediumParameter	String		Visitor's medium parameter utm_medium from Google Analytics.
SourceParameter	String		Visitor's source parameter utm_source from Google Analytics.
ContentParameter	String		Visitor's content parameter utm_content from Google Analytics.
TermParameter	String		Visitor's term parameter utm_term from Google Analytics.
CreatedAt	Datetime		Time visitor was created in Pardot.
UpdatedAt	Datetime		Last time visitor was updated in Pardot.
ProspectId	Int64		Selects visits with the given Prospect IDs.

Retrieve visitors page views.

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this visitor page view.
VisitId	Int64	Visits.Id	Pardot ID for this visit.
VisitorId	Int64	Visitors.Id	Pardot ID for the associated visitor.
ProspectId	Int64	Prospects.Id	Pardot ID for the associated prospect.
Url	String		The Url of the page.
Title	String		The Title column of the VisitorsPageViews object.
CreatedAt	Datetime		The CreatedAt column of the VisitorsPageViews object.

Retrieve visits.

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this visit.
VisitorId	Int64	Visitors.Id	Pardot ID for the associated visitor.
ProspectId	Int64	Prospects.Id	Pardot ID for the associated prospect.
VisitorPageViewCount	Integer		Number of page views for this visit.
FirstVisitorPageViewAt	Datetime		Time of first page view for this visit.
LastVisitorPageViewAt	Datetime		Time of last page view for this visit.
DurationInSeconds	Integer		Length of this visit.
CampaignParameter	String		Visit's campaign parameter utm_campaign from Google Analytics.
MediumParameter	String		Visit's medium parameter utm_medium from Google Analytics.
SourceParameter	String		Visit's source parameter utm_source from Google Analytics.
ContentParameter	String		Visit's content parameter utm_content from Google Analytics.
TermParameter	String		Visit's term parameter utm_term from Google Analytics.
CreatedAt	Datetime		Time visit was created in Pardot.
UpdatedAt	Datetime		Last time visit was updated in Pardot.

Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with Salesforce Marketing Cloud Account Engagement.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Salesforce Marketing Cloud Account Engagement, along with an indication of whether the procedure succeeded or failed.

CData Cloud - Salesforce Marketing Cloud Account Engagement Stored Procedures

Name	Description
AssignProspect	Update the locations for a shipping zone.
AssignProspectAccount	Update the locations for a shipping zone.
SendEmail	Update the locations for a shipping zone.
UnAssignProspect	Update the locations for a shipping zone.
UnDeleteOpportunity	Update the locations for a shipping zone.

Update the locations for a shipping zone.

Execute

EXEC AssignProspect ProspectId = 432, AssignmentType = 'user', AssignTo = 101
EXEC AssignProspect ProspectId = 432, AssignmentType = 'email', AssignTo = '[email protected]'

Input

Name	Type	Required	Description
ProspectId	String	False	The ID of the prospect.
AssignmentType	String	False	The type of the assignment. Can be one of: email, user, group.
AssignTo	String	False	The value to assign to. If the type is user or group the value should be the respective id.

Result Set Columns

Name	Type	Description
Success	String	This value shows a boolean indication of whether the operation was successful or not.

Update the locations for a shipping zone.

Execute

EXEC AssignProspectAccount UserId = 432, ProspectAccountId = 501

Input

Name	Type	Required	Description
ProspectAccountId	String	False	The ID of the prospect account.
UserId	String	False	The ID of the user.

Result Set Columns

Name	Type	Description
Success	String	This value shows a boolean indication of whether the operation was successful or not.

Update the locations for a shipping zone.

Execute

This procedure requires any of the following input combinations:

One of: ProspectId, ProspectEmail or ListId. One of: TemplateId or (TextContent, Name, Subject and (FromEmail, FromName or FromUserId)) Examples:

EXEC SendEmail CampaignId = 20006, ProspectId = 1807618, TemplateId = 13598
EXEC SendEmail CampaignId = 20006, ProspectEmail = '[email protected]', TextContent = 'content', Name = 'email name', Subject = 'email subject', FromUserId = 24486
EXEC SendEmail CampaignId = 20006, ListId = 20104, TextContent = 'content', Name = 'email name', Subject = 'email subject', FromEmail = '[email protected]', FromName = 'Name Surname'

Input

Name	Type	Required	Description
CampaignId	String	False	The ID of the campaign.
ProspectId	String	False	The ID of the prospect.
ProspectEmail	String	False	The email of the prospect.
ListId	String	False	The ID of the lists to send to.
TemplateId	String	False	The ID of the prospect.
TextContent	String	False	The ID of the prospect.
Name	String	False	The ID of the prospect.
Subject	String	False	The ID of the prospect.
FromEmail	String	False	The ID of the prospect.
FromName	String	False	The ID of the prospect.
FromUserId	String	False	The ID of the prospect.
ReplyToEmail	String	False	The ID of the prospect.
OperationalEmail	String	False	The ID of the prospect.
HTMLContent	String	False	The ID of the prospect.

Result Set Columns

Name	Type	Description
Success	String	This value shows a boolean indication of whether the operation was successful or not.

Update the locations for a shipping zone.

Execute

EXEC UnAssignProspect ProspectId = 432

Input

Name	Type	Required	Description
ProspectId	String	False	The ID of the prospect.

Result Set Columns

Name	Type	Description
Success	String	This value shows a boolean indication of whether the operation was successful or not.

Update the locations for a shipping zone.

Execute

EXEC UnDeleteOpportunity Id = 600

Input

Name	Type	Required	Description
Id	String	False	The ID of the opportunity.

Result Set Columns

Name	Type	Description
Success	String	This value shows a boolean indication of whether the operation was successful or not.

The CData Cloud models Salesforce Marketing Cloud Account Engagement Pardot API v5 objects as relational Tables, Views, and Stored Procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describes a sample of the available standard tables.

The Cloud models the data in Salesforce Marketing Cloud Account Engagement as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Salesforce Marketing Cloud Account Engagement Tables

Name	Description
CustomRedirects	Track links on your website or third-party site with Pardot custom redirects.
DynamicContent	Retrieve a collection of dynamic content
EmailTemplates	Query email templates.
Files	Upload images and other files to use in your Pardot emails, forms, and landing pages.
FormHandlers	Retrieve,Create,Updata,Delete a collection of Form Handlers
FormHandlersFields	Retrieve,Create,Update,Delete a collection of Form Handler Stages
LayoutTemplates	Retrieve and modify layout templates.
ListMemberships	Retrieve,Create,Update,Delete a collection of list memberships
Lists	Retrieve and modify lists.
Prospects	Retrieve and modify prospects.

Track links on your website or third-party site with Pardot custom redirects.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• IsDeleted supports '='.

For example, the following queries are processed server side:

SELECT * FROM CustomRedirects WHERE Id = 40
SELECT * FROM CustomRedirects WHERE IsDeleted = 'true'

Insert

The following attributes are required when performing an insert: Name, CampaignId, DestinationUrl.

INSERT INTO CustomRedirects (Name, CampaignId, DestinationUrl) VALUES ('name', '1', 'https://example.com/content.html')

Update

Update CustomRedirects attributes.

UPDATE CustomRedirects SET Name = 'newName', FolderId = '527425' WHERE Id = 24143

Delete

Remove all CustomRedirects objects or one by specifying the Id of the CustomRedirect.

DELETE FROM CustomRedirects WHERE Id = 10003

GetDeleted

To retrieve custom redirects that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM CustomRedirects 

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID for this campaign.
Name	String	False		Name of the object for identification in Pardot.
CampaignId	Int64	False		Pardot Campaign related to this object.
DestinationUrl	String	False		A fully qualified URL. The visitor who clicks the custom redirect is directed to this location.
FolderId	Int64	False		ID of the folder containing this object. Uses the asset type's uncategorized folder if not specified on create.
TrackerDomainId	Int64	False		ID of the tracker domain to use in the URL for this object. Uses the primary tracker domain for the account if not specified on create.
VanityUrl	String	True		Fully qualified vanity URL. Includes tracker domain and vanityUrlPath.
VanityUrlPath	String	False		Vanity URL path (excluding protocol and host). Must be unique for each tracker domain.
GASource	String	False		Google Analytics Campaign source.
GAMedium	String	False		Google Analytics Campaign medium.
GATerm	String	False		Google Analytics Campaign keyword.
GAContent	String	False		Google Analytics Campaign content.
GACampaign	String	False		Google Analytics Campaign ID.
SalesforceId	String	True		ID of the Salesforce object representing this object.
TrackedUrl	String	True		Fully qualified tracked URL. Does not include Vanity URL.
BitlyIsPersonalized	String	True		True if a custom Bitly URL was set.
BitlyShortUrl	String	True		If set, a fully qualified Bitly URL.
IsDeleted	Boolean	True		True if the object is in the Pardot recycle bin.
CreatedAt	Datetime	True		The object creation timestamp.
UpdatedAt	Datetime	True		The timestamp of the last update of the object.
CreatedById	Int64	True		ID of the individual who created this object.
UpdatedById	Int64	True		ID of the individual who last updated this object.

Retrieve a collection of dynamic content

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID for this dynamic content.
Name	String	True		Dynamic content's name.
EmbedCode	String	True		The code used to embed the dynamic content into a web page.
EmbedUrl	String	True		The URL to embed this dynamic content.
BaseContent	String	True		The default content that shows when the prospect doesn't meet the criteria for a variation.
BasedOn	String	True		The kind field that dynamic content is based on. Values are 'default', 'custom', 'grade', and 'score'.
CreatedAt	Datetime	True		Time the dynamic content was created; reported in API user's timezone.
UpdatedAt	Datetime	True		Time the dynamic content was last updated; reporting in API user's timezone.
IsDeleted	Boolean	True		When true, the object is in the recycle bin in Pardot.
CreatedByID	Int64	True		ID of the user who created this object.
UpdatedById	Int64	True		ID of the user who last updated this object.
FolderId	Int64	True		ID of the folder containing this object.
TrackerDomainId	Int64	True		The ID of the tracker domain associated with this object.

Query email templates.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• IsOneToOneEmail supports '='.
• IsDeleted supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM EmailTemplates WHERE Id = 50
SELECT * FROM EmailTemplates WHERE UpdatedAt > '01/01/2022'
SELECT * FROM EmailTemplates WHERE CreatedAt > '01/01/2022'

Insert

To insert EmailTemplates the following fields are required:

• CampaignId
• IsAutoResponderEmail
• IsDripEmail
• IsListEmail
• IsOneToOneEmail
• Name
• Subject
• TextMessage
• HtmlMessage
• SenderOptionsAggregate
• TrackerDomainId
• Type

For inserting into this table specifying the SenderOptionsAggregate is required. The input for this field follows CData principles of deep Inserts. In Deep Insert, you simultaneously create a base object and link it to the related field in the main table.

You may either submit JSON data, or you may create a temporary table for the child table(in this case SenderOptions) and then reference the temporary table in the insert to the base table.

Example: Deep Inserts using JSON

To submit JSON data, simply supply the values for the child table in JSON format.

For example, the following JSON adds two sender options in the email template.

[
	{
		"address": "[email protected]",
		"name": "CDataTest",
		"type": "general_user"
	},
	{
		"address": "[email protected]",
		"name": "CDataTest2",
		"type": "general_user"
	}
]

In order to execute the insert, simply reference or include as string literals the complete JSON. For example:

INSERT INTO EmailTemplates (..., SenderOptionsAggregate) VALUES (..., '[{"address":"[email protected]","name":"CDataTest","type":"general_user"},{"address":"[email protected]","name":"CDataTest2","type":"general_user"}]')

Example: Deep Inserts using Temporary Tables

If using temporary tables, they must be defined and inserted within the same connection. Closing the connection will clear out any temporary tables in memory.

Creating Temporary Tables

Insert the child table fields into temporary table.

INSERT INTO SenderOptions#TEMP (Type, Address) VALUES ('general_user', 'address1')

INSERT INTO SenderOptions#TEMP (Type, Address) VALUES ('general_user', 'address2')

Inserting the email template

In the INSERT statement for the EmailTemplates table, reference the temporary tables in the SenderOptionsAggregate input.

INSERT INTO EmailTemplates (..., SenderOptionsAggregate) VALUES (..., 'SenderOptions#TEMP')

Update

Updating EmailTemplates:

UPDATE EmailTemplates SET TextMessage = 'newMessage' WHERE Id = 24143

Delete

Remove all email templates or a email template by specifying the Id of the template.

DELETE FROM EmailTemplates WHERE Id = 10003

GetDeleted

To retrieve email templates that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM EmailTemplates

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		ID of the email.
Name	String	False		Name of the object for identification in Pardot.
Subject	String	False		Subject line for the email template.
TextMessage	String	False		The text content of the email. This field is retrieved only when filtering with email id.
HtmlMessage	String	False		The HTML content of the email. This field is retrieved only when filtering with email id.
IsOneToOneEmail	Boolean	False		If True, the template is available for one-to-one emails.
IsAutoResponderEmail	Boolean	False		If True, the template is available for autoresponder emails.
IsDripEmail	Boolean	False		If True, the template is available for Engagement Programs.
IsListEmail	Boolean	False		If True, the template is available for list emails.
TrackerDomainId	Int64	False		ID of the tracker domain used for the list email.
FolderId	Int64	False		ID of the folder containing this object.
Type	String	False		'html', 'text', and 'htmlAndText' values.
CampaignId	Int64	False		Pardot campaign related to this object.
IsDeleted	Boolean	True		True if the object is in the recycle bin in Pardot.
CreatedById	Int64	True		ID of the individual who created this object.
UpdatedById	Int64	True		ID of the individual who last updated this object.
CreatedAt	Datetime	True		The timestamp of when this object was created.
UpdatedAt	Datetime	True		The timestamp of when this object was last updated.
SenderOptionsType	String	True		The sending user type(s).
SenderOptionsAddress	String	True		The sending email address(s).
SenderOptionsName	String	True		The name(s) of the sender.
SenderOptionsUserId	String	True		The user ID(s) of the sender.
SenderOptionsProspectCustomFieldId	String	True		The ID of the prospect custom field that contains the sender value. Only available on read and query.
SenderOptionsAccountCustomFieldId	String	True		The ID of the account custom field that contains the sender value. Only available on read and query.
ReplyToOptionsType	String	True		The reply to user type(s).
ReplyToOptionsAddress	String	True		The sending email address(s).
ReplyToOptionsUserId	String	True		The user ID(s) of the sender.
ReplyToOptionsProspectCustomFieldId	String	True		The ID of the prospect custom field that contains the sender value. Only available on read and query.
ReplyToOptionsAccountCustomFieldId	String	True		The ID of the account custom field that contains the sender value. Only available on read and query.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
SenderOptionsAggregate	String	This input is used for INSERT/UPDATE statements. Set this to SenderOptions#TEMP temporary table, whose rows contain inputs for inserting/updating the sender options. Or simply reference or include as string literals the complete JSON.
ReplyToOptionsAggregate	String	This input is used for INSERT/UPDATE statements. Set this to ReplyToOptions#TEMP temporary table, whose rows contain inputs for inserting/updating the replyTo options. Or simply reference or include as string literals the complete JSON.

Upload images and other files to use in your Pardot emails, forms, and landing pages.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Files WHERE Id = 40
SELECT * FROM Files WHERE UpdatedAt < '2021-12-30T03:16:27-08:00' AND CreatedAt >= '2021-11-12T07:18:56-08:00'

Insert

The following attributes are required when performing an insert: Name, FilePath.

INSERT INTO Files (Name, FilePath) VALUES ('file1', 'C:\\file1.txt')

Update

Updating Files:

UPDATE Files SET Name = 'testingUpdate', FolderId = '33609' WHERE id = '804969'

Delete

Remove all files or a file by specifying the Id of the file.

DELETE FROM Files WHERE Id = 10003

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID of this file.
Name	String	False		Name of the object for identification in Pardot. Uses the name of the file being uploaded if not specified on create.
FolderId	Int64	False		ID of the folder containing this object. Uses the asset type's uncategorized folder if not specified on create.
CampaignId	Int64	False		Pardot campaign related to this object. Uses null if not specified on create.
VanityUrlPath	String	False		Vanity URL path (excluding protocol and host). Must be unique for each tracker domain.
TrackerDomainId	Int64	False		ID of the TrackerDomain to use in the URL for this object. Uses the primary tracker domain for the account if not specified on create.
SalesforceId	String	True		ID of the Salesforce object representing this object.
Url	String	True		URL where the downloadable file can be accessed.
Size	Integer	True		Size (in bytes) of the downloadable file.
BitlyIsPersonalized	Boolean	True		True if the object has a bitly URL that is personalized.
BitlyShortUrl	String	True		Bitly URL if present.
VanityUrl	String	True		Vanity URL if present.
IsTracked	Boolean	True		True if downloads of this file are recorded as visitor activity.
CreatedAt	Datetime	True		The timestamp of when this object was created.
UpdatedAt	Datetime	True		The timestamp of when this object was last updated.
CreatedById	Int64	True		ID of the individual who created this object.
UpdatedById	Int64	True		ID of the individual who last updated this object.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
FilePath	String	This parameter is used only in INSERT operations to upload the content of a file. It should be set to the path of the file including the name. Ex: C:\Users\User\Desktop\test.txt or to the BASE64 encoded content of the file.

Retrieve,Create,Updata,Delete a collection of Form Handlers

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		ID of this object.
Name	String	False		Name of the object for identification in Pardot.
FolderId	Int64	False		ID of the folder containing this object.
CampaignId	Int64	False		The ID of the campaign associated with this object.
TrackerDomainId	Int64	False		The ID of the tracker domain associated with this object.
IsDataForwarded	Boolean	False		When true, enables data forwarding to the success location.
SucessLocation	String	False		The URL the user is forwarded to if the submission is successful.
ErrorLocation	String	False		The URL the user is forwarded to if the submission has an error.
IsAlwaysEmail	Boolean	False		When true, disables visitor activity throttling and sends autoresponder emails after every submission. Learn more about throttling in Salesforce Help.
IsCookieless	Boolean	False		When true, the form handler is in Kiosk/Data Entry Mode, and does not cookie browser as a submitted prospect.
SalesforceId	String	True		Salesforce Id of the object.
EmbedCode	String	True		Code to embed this form handler in a webpage.
CreatedAt	Datetime	True		Creation time of this object.
UpdatedAt	Datetime	True		Last updated time for the object.
CreatedById	Int64	True		ID of the user who created the object.
IsDeleted	Boolean	True		True if the object is in the Pardot recycle bin.
UpdatedById	Int64	True		ID of the user who last updated the object.

Retrieve,Create,Update,Delete a collection of Form Handler Stages

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		ID of this object.
Name	String	False		Name of the object for identification in Pardot.
FormHandlerId	Int64	False		The ID of the form handler this field belongs to.
IsRequired	Boolean	False		When true, the field is required.
DataFormat	String	False		The field's data format. Values are 'text', 'number','email', 'email not from isps and free email providers', and 'email with valid mail server'.
ProspectApiFieldId	String	False		The name of the Pardot prospect field that the form handler field is mapped to. On standard fields the value is the name of the field. For example, 'firstName'. If mapping to a custom field, the value is the field name followed by __c. For example, the custom field 'foodChoice' becomes 'foodChoice__c'.
IsMaintainInitialValue	Boolean	False		When true, the field maintains the initial value upon subsequent form submissions.
ErrorMessage	String	False		Error message for the field.
CreatedAt	Datetime	True		Creation time of this object.
CreatedById	Int64	True		ID of the user who created the object.

Retrieve and modify layout templates.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• IsDeleted supports '='.

For example, the following queries are processed server side:

SELECT * FROM LayoutTemplates WHERE Id = 40
SELECT * FROM LayoutTemplates WHERE IsDeleted = 'true'

Insert

The following attributes are required when performing an insert: Name, LayoutContent.

INSERT INTO LayoutTemplates (Name, IsIncludeDefaultCss, LayoutContent, FolderId) VALUES ('name', 'true', 'content', 33621)

Update

Updating LayoutTemplates:

UPDATE LayoutTemplates SET Name = 'newName' WHERE Id = 24143

Delete

Remove all layout templates or a layout template by specifying the Id of the template.

DELETE FROM LayoutTemplates WHERE Id = 10003

GetDeleted

To retrieve layout templates that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM LayoutTemplates 

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		ID of this object.
Name	String	False		Name of the object for identification in Pardot. Uses the name of the file being uploaded if not specified on create.
LayoutContent	String	False		HTML content of this layout template encoded as JSON string.
FormContent	String	False		HTML content encoded as JSON string that controls form display logic. Uses default values if not provided.
SiteSearchContent	String	False		HTML content encoded as JSON string that controls the site search content.
IsIncludeDefaultCss	Boolean	False		True if not supplying custom CSS styling.
FolderId	Int64	False		ID of the folder containing this object. Uses the asset type's uncategorized folder if not specified on create.
IsDeleted	Boolean	True		True if the object is in the recycle bin in Pardot.
CreatedAt	Datetime	True		The timestamp of when this object was created.
UpdatedAt	Datetime	True		The timestamp of when this object was last updated.
CreatedById	Int64	True		ID of the individual who created this object.
UpdatedById	Int64	True		ID of the individual who last updated this object.

Retrieve,Create,Update,Delete a collection of list memberships

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID for this list membership.
IsDeleted	Boolean	True		When true, the list membership is in the Pardot recycle bin.
CreatedById	Int64	True		ID of the user who created this list membership.
CreatedAt	Datetime	True		The time that this list membership was created. Reported in API user's preferred timezone.
UpdatedAt	Datetime	True		The time that this list membership was last updated. Reported in API user's timezone.
UpdatedById	Int64	True		ID of the user who last updated this list membership.
OptedOut	Boolean	False		When true, the prospect is unsubscribed from receiving emails from this list.
ListId	Int64	False		Pardot ID of the list for this membership
ProspectID	Int64	False		Pardot ID of the prospect for this membership

Retrieve and modify lists.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.
• IsDeleted supports '='.

For example, the following queries are processed server side:

SELECT * FROM Lists WHERE Id = 40
SELECT * FROM Lists WHERE IsDeleted = 'true'
SELECT * FROM Lists WHERE CreatedAt > '01/01/2022'

Insert

The following attribute is required when performing an insert: Name.

INSERT INTO Lists (Name) VALUES ('name')

Update

Update Lists attributes.

UPDATE Lists SET Name = 'newName' WHERE Id = 24143

Delete

Remove all lists or a list by specifying the Id of the list.

DELETE FROM Lists WHERE Id = 10003

GetDeleted

To retrieve lists that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM Lists

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID of this list.
Name	String	False		List's name (internal to Pardot).
FolderId	Int64	False		ID of the folder containing this object.
CampaignId	Int64	False	Campaigns.Id	Pardot campaign related to this object. Uses null if not specified on create.
IsDeleted	Boolean	True		True if the object is in the recycle bin in Pardot.
IsDynamic	Boolean	True		True if the list is dynamic.
IsPublic	Boolean	False		True if the list is public.
Title	String	False		Title of the list object.
Description	String	False		List's description.
CreatedAt	Datetime	True		The timestamp of when this object was created.
UpdatedAt	Datetime	True		The timestamp of when this object was last updated.
CreatedById	Int64	True		ID of the individual who created this object.
UpdatedById	Int64	True		ID of the individual who last updated this object.

Retrieve and modify prospects.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• UserId supports '=', '<', '<=', '>', '>='.
• AssignedToId supports '=', '<', '<=', '>', '>='.
• LastActivityAt supports '=', '<', '<=', '>', '>='.
• IsDeleted supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Prospects WHERE Id = 3457992
SELECT * FROM Prospects WHERE UserId = 14
SELECT * FROM Prospects WHERE UpdatedAt < '2021-12-30T03:16:27-08:00' AND CreatedAt >= '2021-11-12T07:18:56-08:00'

Get Deleted

To retrieve deleted prospects, a GETDELETED statement can be executed.

GETDELETED FROM Prospects

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID for this prospect.
AddressOne	String	False		Prospect's address, line 1.
AddressTwo	String	False		Prospect's address, line 2.
AnnualRevenue	String	False		Prospect's annual revenue.
CampaignId	Int64	False		Pardot campaign related to this object.
CampaignParameter	String	True		Prospect's campaign parameter utm_campaign from Google Analytics.
SalesforceCampaignId	String	True		The alpha-numeric Id of the associated campaign in the Salesforce Org.
City	String	False		Prospect's city.
Comments	String	False		Comments about this prospect.
Company	String	False		Prospect's company.
ContentParameter	String	True		Prospect's content parameter utm_content from Google Analytics.
ConvertedAt	Datetime	True		Time when prospect was converted from visitor.
ConvertedFromObjectName	String	True		Name of object associated with visitor activity that resulted in prospect being converted from visitor.
ConvertedFromObjectType	String	True		ype of object associated with visitor activity that resulted in prospect being converted from visitor. Supported values include 'Form', 'FormHandler', 'LandingPage', 'MultivariateTestVariation', and 'Video'.
Country	String	False		Prospect's country.
SalesforceAccountId	String	True		Account ID in a supported Salesforce system.
SalesforceContactId	String	True		Prospect's contact ID in a supported Salesforce system.
SalesforceLastSync	Datetime	True		Last time this prospect was synced with a supported Salesforce system.
SalesforceLeadId	String	True		Prospect's lead ID in a supported Salesforce system.
SalesforceOwnerId	String	False		Prospect's owner ID in a supported Salesforce system.
SalesforceId	String	False		The alpha-numeric Id of the Salesforce Org.
Department	String	False		Prospect's department.
Email	String	False		Prospect's email address.
EmailBouncedAt	Datetime	True		Time
EmailBouncedReason	String	True		Reason why prospect email address hard bounced.
Employees	String	False		Prospect's number of employees.
Fax	String	False		Prospect's fax number.
FirstActivityAt	Datetime	True		Time when first visitor activity occurred for this prospect.
FirstAssignedAt	Datetime	True		Time prospect was first assigned to a user.
FirstName	String	False		Prospect's first name.
FirstReferrerQuery	String	True		First referrer's search query.
FirstReferrerType	String	True		First referrer's vendor and type (such as 'Google Natural Search').
FirstReferrerUrl	String	True		First referrer's URL.
Grade	String	True		Prospect's letter grade.
Industry	String	False		Prospect's industry.
IsDeleted	Boolean	True		True if the object is in the recycle bin in Pardot.
IsDoNotCall	Boolean	False		If value is 1, prospect prefers not to be called.
IsDoNotEmail	Boolean	False		If value is 1, prospect prefers not to be emailed.
isEmailHardBounced	Boolean	True		If true, prospect email address has hard bounced.
IsReviewed	Boolean	False		If value is 1, prospect has been reviewed.
IsStarred	Boolean	False		If value is 1, prospect has been starred.
JobTitle	String	False		Prospect's job title.
LastActivityAt	Datetime	True		Time stamp of this prospect's latest visitor activity.
LastName	String	False		Prospect's last name.
MediumParameter	String	True		Prospect's medium parameter utm_medium from Google Analytics.
Notes	String	False		Notes about this prospect.
OptedOut	Boolean	False		If value is 1, prospect has opted out of marketing communications.
Password	String	True		Prospect's password.
Phone	String	False		Prospect's phone number.
ProspectAccountId	Int64	False		Prospect's account ID.
Salutation	String	False		Prospect's formal prefix.
Score	Integer	False		Prospect's score.
Source	String	False		Prospect's source.
SourceParameter	String	True		Prospect's source parameter utm_source from Google Analytics.
State	String	False		Prospect's US state.
TermParameter	String	True		Prospect's term parameter utm_term from Google Analytics.
Territory	String	False		Prospect's territory.
Website	String	False		Prospect's website URL.
YearsInBusiness	String	False		Prospect's number of years in business.
Zip	String	False		Prospect's postal code.
AssignedToId	Int64	True		ID of the user who the object record is assigned to.
ProfileId	Int64	False		Pardot profile related to this object.
SalesforceUrl	String	True		URL to view the prospect within the Salesforce system.
LifecycleStageId	Int64	True		Pardot lifecycle stage related to this object.
UserId	Int64	False		User the prospect is assigned to.
RecentInteraction	String	True		Describes the prospect's most recent interaction with Pardot.
CreatedAt	Datetime	True		Time prospect was created in Pardot.
UpdatedAt	Datetime	True		Last time prospect was updated in Pardot.
CreatedById	Int64	True		ID of the individual who created this object.
UpdatedById	Int64	True		ID of the individual who last updated this object.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Salesforce Marketing Cloud Account Engagement Views

Name	Description
Account	Use the account resource to learn about the current user's Pardot account.
Campaigns	Retrieve and modify campaigns.
CustomFields	Retrieve custom fields metadata.
DynamicContentVariations	Retrieve a collection of Dynamic Content Variations
Emails	Query emails to learn how prospects responded to the emails.
EmailSuppressionList	A Lists used as suppression lists for the list email.
EngagementStudioProgram	Query EngagementStudioProgram to retrieve data related to the programs.
FolderContents	View all objects contained within Folders.
Folders	Retrieve folders or organize and nest your content and marketing assets in a way that makes sense for your team.
Forms	Retrieve forms.
LandingPages	Retrieving a collection of landing pages
LifecycleHistories	Use lifecycle history resources to learn how a prospect is moving through their sales journey, from visitor to won opportunity.
LifecycleStages	Retrieving a collection of lifecycle stages
ListEmail	A list email is a single email sent to a recipient list.
ListEmailRecipients	RecipientLists used by an email
Opportunities	Retrieve a collection of opportunities
ProspectAccounts	Retrieve and modify prospect accounts.
ReplyToOptions	Query the replyTo options of email templates.
SenderOptions	Query the sender options of email templates.
TrackerDomains	Query the available tracker domains.
Users	Query the available users.
VisitorActivities	Retrieve visitor activities.
Visitors	Retrieve visitors.
VisitorsPageViews	Retrieve visitors page views.
Visits	Retrieve visits.

Use the account resource to learn about the current user's Pardot account.

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this account.
Company	String		Account company.
Level	String		The level of product for the account.
Website	String		Account website.
PluginCampaignId	Int64		Plugin ID for account campaign.
AddressOne	String		Account's address, line 1.
AddressTwo	String		Account's address, line 2.
City	String		Account's city.
State	String		Account's state.
Zip	String		Account's postal code.
Territory	String		Account's territory.
Country	String		Account's country.
Phone	String		Account's phone number.
Fax	String		Account's fax number.
AdminId	Int64		ID of the individual who is the admin for this account.
CreatedAt	Datetime		The timestamp of when this object was created.
UpdatedAt	Datetime		The timestamp of when this object was last updated.
MaximumDailyApiCalls	Integer		Maximum daily API requests based on the timezone for this account.
ApiCallsUsed	Integer		Total daily API requests based on the timezone for this account.
CreatedById	Int64		ID of the individual who created this object.
UpdatedById	Int64		ID of the individual who last updated this object.

Retrieve and modify campaigns.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• Name supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Campaigns WHERE Id = 50
SELECT * FROM Campaigns WHERE Name = 'campaignName'
SELECT * FROM Campaigns WHERE CreatedAt > '01/01/2022'

GetDeleted

To retrieve campaigns that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM Campaings

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this campaign.
Name	String		Campaign's name.
Cost	Integer		Cost associated to the campaign.
IsDeleted	Boolean		True if the object is in the recycle bin in Pardot.
FolderId	Int64		ID of the folder containing this object.
ParentCampaignId	Int64		The ID of the parent campaign.
CreatedAt	Datetime		The timestamp of when this object was created.
UpdatedAt	Datetime		The timestamp of when this object was last updated.
CreatedById	Int64		ID of the individual who created this object.
UpdatedById	Int64		ID of the individual who last updated this object.
SalesforceId	String		The alphanumeric Id of the Salesforce Org.

Retrieve custom fields metadata.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM CustomFields WHERE Id = 50
SELECT * FROM CustomFields WHERE CreatedAt > '01/01/2022'
SELECT * FROM CustomFields WHERE UpdatedAt < '01/01/2022'

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this custom field.
Name	String		Custom field's name.
FieldId	String		API ID for custom field.
Type	String		The type of field.
CreatedAt	Datetime		Time custom field was created in Pardot.
UpdatedAt	Datetime		Last time custom field was updated.
CreatedById	Int64		ID of the individual who created this object.
UpdatedById	Int64		ID of the individual who last updated this object.
IsRecordMultipleResponses	Boolean		If true, this custom field will record multiple responses.
IsUseValues	Boolean		If true, this custom field will use predefined values.
SalesforceId	String		The alpha-numeric Id of the Salesforce Org.
IsRequired	String		True if the custom field is required.
isAnalyticsSynced	Boolean		True if the object is synced to Analytics.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
ValuesPrefill	String		Write-Only field on create operation. Possible values: Countries,

Retrieve a collection of Dynamic Content Variations

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this dynamic content variation.
Comparison	String		The logic used to set the criteria for a variation. This field works together with the Dynamic Content basedOn field to determine which variation a prospect is served.
Content	String		The text content of the variation.
DynamicContentId	String		ID of the dynamic content this variation is associated with.

Query emails to learn how prospects responded to the emails.

Table Specific Information

Select

The Cloud uses the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• ProspectId supports '=', '<', '<=', '>', '>='.
• ListEmailId supports '=', '<', '<=', '>', '>='.
• SentAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Emails WHERE Id = 50
SELECT * FROM Emails WHERE ProspectId = 23
SELECT * FROM Emails WHERE SentAt > '01/01/2022'

Columns

Name	Type	References	Description
Id [KEY]	Int64		ID of the email.
ProspectId	Int64		Pardot prospect id related to this email.
CampaignId	Int64		Pardot campaign related to this object.
CreatedById	Int64		ID of the individual who created this object.
ClientType	String		Value is 'Builder' if the email was sent via the Lightning Email Builder. Value is 'Web' if it was sent via the legacy Pardot email builder.
TextMessage	String		The text content of the email. This field is retrieved only when filtering with email id.
HtmlMessage	String		The HTML content of the email. This field is retrieved only when filtering with email id.
ListId	Int64		ID of the list associated with this email.
listEmailId	Int64		ID of the list email associated with this email.
Name	String		Name of the email for identification in Pardot.
SentAt	Datetime		Time the email was sent.
Subject	String		The email subject.

A Lists used as suppression lists for the list email.

Retrieve a Single Suppressed Email Address

To retrieve a single suppressed email address, specify its ID:

SELECT * FROM EmailSuppressionList WHERE Id = '55555555'

Retrieve Multiple Suppressed Email Addresses

To retrieve multiple suppressed email addresses, issue a nested SELECT statement to the ListEmail table.

SELECT * FROM EmailSuppressionList WHERE ListEmailId IN (SELECT Id FROM ListEmail)

Columns

Name	Type	References	Description
Id [KEY]	Int64		ID of the SuppressionList
ListEmailId [KEY]	Int64		The ListEmail id
ListEmailName	String		ListEmail's name (internal to Pardot).
ListEmailCampaignId	Int64		Pardot ListEmail campaign related to this object. Uses null if not specified on create.
Name	String		List's name (internal to Pardot).
FolderId	Int64		ID of the folder containing this object.
IsDynamic	Boolean		True if the list is dynamic.
IsPublic	Boolean		True if the list is public.
Title	String		Title of the list object.
Description	String		List's description.

Query EngagementStudioProgram to retrieve data related to the programs.

Columns

Name	Type	References	Description
Id [KEY]	Int64		ID of the object.
BusinessHoursDays	String		Array containing the business days of the week.
BusinessHoursEndTime	Time		The start time of the business day.
BusinessHoursStartTime	Time		The end time of the business day.
BusinessHoursTimezone	String		The timezone of the business hours.
CreatedAt	Datetime		Creation time of the object.
CreatedById	Int64		ID of the user who created this object.
Description	String		Description of the object.
FolderId	Int64		ID of the folder containing this object. Uses the asset type's uncategorized folder if not specified on create.
IsDeleted	Boolean		True if the object is in the recycle bin in Account Engagement.
Name	String		Name of the object for identification in Account Engagement.
ProspectsMultipleEntryMaximumEntries	Int64		The maximum amount of times a prospect can reenter the program. Unlimited entries are represented as null.
ProspectsMultipleEntryMinimumDurationInDays	Int64		The minimum amount of days before a prospect can reenter the program.
RecipientListIds	String		Array containing the program's Recipient List IDs.
SalesforceId	Int64		SalesforceID of the object.
ScheduleCreatedAt	Datetime		Creation time of the program schedule.
ScheduleStartOn	Datetime		Start time of the program schedule. If the program was started with the Now option startOn reflects the time the program started. If the program was started at some date in the future startOn reflects the user's selection.
ScheduleStopOn	Datetime		Stop time of the program schedule. A null value in this field represents the Never stop option.
ScheduleCreatedById	Int64		ID of the user who created the program's schedule.
Status	String		The status of the program. draft: The program has never been run and can be edited;running: The program is currently running and can't be edited.;paused: The program isn't running and is eligible for edits (not supported at this time).;starting: The program is in the process of starting and can't be edited.;scheduled: The program is scheduled to start.
SuppressionListIds	String		Array containing the program's Suppression List IDs.
UpdatedAt	Datetime		Last update time of the object.
UpdatedById	Int64		ID of the user who last updated this object.

View all objects contained within Folders.

Columns

Name	Type	References	Description
Id [KEY]	Int64		ID of the folder content storing the folder-to-object relationship.
FolderId	Int64		ID of the folder containing the object with ObjectId.
FolderRef	String		The URL for reading the specific folder with FolderId.
ObjectType	String		Type of object referenced by ObjectId.
ObjectId	Int64		ID of the object contained in the folder.
ObjectName	String		Name of the object contained in the folder.
ObjectRef	String		The URL for reading the specific object with ObjectId.
CreatedAt	Datetime		Creation time of the object.
UpdatedAt	Datetime		Last update time of the object.
CreatedById	Int64		ID of the user who created this object.
UpdatedById	Int64		ID of the user who last updated this object.

Retrieve folders or organize and nest your content and marketing assets in a way that makes sense for your team.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• ParentFolderId supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM Folders WHERE Id = 50
SELECT * FROM Folders WHERE ParentFolderId = 2

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID of this file.
Name	String		Name of the folder.
ParentFolderId	Int64		ID of the folder containing this folder. This value is null when the folder is a root folder and doesn't have a parent.
Path	String		The names of each folder from the root folder separated with forward slashes.
UsePermissions	Boolean		True if the folder is configured to use permissions.
CreatedAt	Datetime		The timestamp of when this object was created.
UpdatedAt	Datetime		The timestamp of when this object was last updated.
CreatedById	Int64		ID of the individual who created this object.
UpdatedById	Int64		ID of the individual who last updated this object.

Retrieve forms.

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>='.
• IsDeleted supports '='.

For example, the following queries are processed server side:

SELECT * FROM Forms WHERE Id <= 17
SELECT * FROM Forms WHERE IsDeleted = 'true'

GetDeleted

To retrieve forms that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM Forms 

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID for this form.
Name	String		Form's name.
CampaignId	Int64	Campaigns.Id	Pardot ID of the campaign associated with this form.
EmbedCode	String		The code used to embed the form on your webpage.
SalesforceId	String		The alpha-numeric Id of the Salesforce Org.
LayoutTemplateId	Int64		The ID of the layout template associated with this object
SubmitButtonText	String		The label used for the submit button
BeforeFormContent	String		The HTML text displayed above the form
AfterFormContent	String		The HTML text displayed below the form
ThankYouContent	String		The HTML text displayed after the form is submitted
ThankYouCode	String		Thank you code is typically Javascript used to call other systems like Google Conversion Tracking
IsAlwaysDisplay	Boolean		Always display form after submission. Disable the 10 minute waiting period after submitting a form
RedirectLocation	String		Redirect to this URL instead of showing the
IsUseRedirectLocation	Boolean		When redirect location is valid and enabled.
FontSize	String		Possible values: default, 8pt, 10pt, 12pt, 14pt , 18pt, 24pt, 36pt.
FontFamily	String		Possible values: default,
FontColor	String		6 digit number in hexadecimal format such as #123456
LabelAlignment	String		Possible values: default, above, left
RadioAlignment	String		Possible values: default, horizontal, stacked
CheckboxAlignment	String		Possible values: default, horizontal, stacked
RequiredCharacter	String		Possible values: default , *
IsCookieless	String		Kiosk/Data Entry Mode: Do not cookie browser as submitted prospect
ShowNotProspect	Boolean		Include
IsCaptchaEnabled	Boolean		Enable explicit bot protecting using reCAPTCHA
IsDeleted	Boolean		True if the object is in the recycle bin in Pardot.
TrackerDomainId	Int64		The ID of the tracker domain associated with this object.
FolderId	Int64		ID of the folder containing this object.
CreatedAt	Datetime		The timestamp of when this object was created.
UpdatedAt	Datetime		The timestamp of when this object was last updated.
CreatedById	Int64		ID of the individual who created this object.
UpdatedById	Int64		ID of the individual who last updated this object.

Retrieving a collection of landing pages

Table Specific Information

Select

The Cloud will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Cloud.

• Id supports '=', '<', '<=', '>', '>=', 'IN'.
• IsDeleted supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM LandingPages WHERE Id = 50
SELECT * FROM LandingPages WHERE IsDeleted = 'true'
SELECT * FROM LandingPages WHERE CreatedAt > '01/01/2022'

GetDeleted

To retrieve LandingPages that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM LandingPages

Columns

Name	Type	References	Description
Id [KEY]	Int64		ID of the object.
Name	String		Name of the object for identification in Pardot.
SalesforceId	String		ID of the Salesforce object representing this object.
IsDeleted	Boolean		True if the object is in the recycle bin in Pardot.
CreatedById	Int64		ID of the user who created this object.
CreatedAt	Datetime		Creation time of the object.
UpdatedAt	Datetime		Last update time of the object.
UpdatedById	Int64		ID of the user who last updated this object.
CampaignId	Int64		Pardot Campaign related to this object.
FormId	Int64		ID of the form related to this object.
LayoutTemplateId	Int64		ID of the layout template related to this object.
Title	String		The landing page's title.
Description	String		The landing page's meta description. Used for SEO.
IsDoNotIndex	Boolean		True if the landing page is hidden from search engine indexing.
LayoutType	String		Specifies where the landing page was created. Possible values are Layout Template, Landing Page Builder, Legacy Page Builder, Salesforce Builder.
ScriptFragment	String		Script specified for the landing page. Available only on read requests.
LayoutCss	LayoutCss		CSS code for the layout template. Available only on read requests.
LayoutCssGenerated	String		Generated CSS code for the layout template. Available only on read requests.
LayoutTableBorder	Int		Specifies the layout table border width.
RedirectLocation	String		The redirect URL used if the prospect is redirected instead of showing the thank you content.
IsUseRedirectLocation	Boolean		True if the prospect is redirected instead of showing the form thank you content.
BitlyIsPersonalized	Boolean		True if the object has a bitly URL that is personalized.
BitlyShortUrl	String		Bitly URL if present.
Url	String		Link for the landing page.
VanityUrl	String		Vanity URL if present.
VanityUrlPath	String		The unique path for the asset, used to create the vanity URL.
TrackerDomainId	Int64		The ID of the tracker domain associated with this object.
FolderId	Int64		The ID of the folder associated with this object.