Salesforce Marketing Cloud Account Engagement への接続を追加

Salesforce Marketing Cloud Account Engagement への接続を追加するには：

1. アプリケーションコンソールで、接続ページに移動します。
2. 接続の追加パネルで、追加したい接続のアイコンを選択します。
3. Salesforce Marketing Cloud Account Engagement アイコンが利用できない場合は、Add More アイコンをクリックしてCData サイトからSalesforce Marketing Cloud Account Engagement コネクタをダウンロードおよびインストールします。

必須プロパティについては、設定タブを参照してください。

通常必須ではない接続プロパティについては、高度な設定タブを参照してください。

Salesforce Marketing Cloud Account Engagement への接続

Salesforce Marketing Cloud Account Engagement に接続するには、Pardot のビジネスユニットID を取得する必要があります。これを行うには、Salesforce の［設定］を使用します。［設定］の［クイック検索］フィールドに「Pardot アカウントの設定」と入力します。

Pardot ビジネスユニットID は「0Uv」で始まる18 文字です。Pardot アカウント設定情報にアクセスできない場合は、Salesforce 管理者に問い合わせてPardot ビジネスユニットID を入手してください。

Salesforce Marketing Cloud Account Engagement への認証

OAuth

AuthScheme は、すべてのユーザーアカウントフローでOAuth に設定する必要があります。

デスクトップアプリケーション

CData は、OAuth デスクトップ認証を簡略化する埋め込みOAuth アプリケーションを提供します。代わりに、カスタムアプリケーションを作成することも可能です。カスタムアプリケーションの作成およびその理由については、カスタムOAuth アプリケーションの作成 を参照してください。

認証に関する2つの方法の違いは、カスタムOAuth アプリケーションを使用する場合に、2つの接続プロパティを追加で設定する必要があることだけです。

次の接続プロパティを設定して、接続してください。

• OAuthClientId：（カスタムアプリケーションのみ）アプリケーション設定のクライアントID に設定。
• OAuthClientSecret：（カスタムアプリケーションのみ）アプリケーション設定のクライアントシークレットに設定。

Web アプリケーション

Web アプリケーション経由で接続する場合は、Salesforce Marketing Cloud Account Engagement にカスタムOAuth アプリケーションを登録する必要があります。詳しくは、カスタムOAuth アプリケーションの作成 を参照してください。その後、ドライバーを使用してOAuth トークンの値を取得および管理します。

初めに、次の接続プロパティを設定してOAuthAccessToken を取得します。

• OAuthClientId：アプリケーション設定のクライアントId に設定。
• OAuthClientSecret：アプリケーション設定のクライアントシークレットに設定。

続いてストアドプロシージャを呼び出し、OAuth 交換を完了します。

1. GetOAuthAuthorizationUrl ストアドプロシージャを呼び出します。CallbackURL インプットをアプリケーション設定で指定したコールバックURL に設定します。必要に応じて、Scope パラメータを設定してカスタム権限をリクエストします。ストアドプロシージャがOAuth エンドポイントのURL を返します。
2. URL を開き、ログインして、アプリケーションを認可します。コールバックURL にリダイレクトされます。
3. GetOAuthAccessToken ストアドプロシージャを呼び出します。Authmode インプットをWEB に設定します。Verifier インプットを、コールバックURL のクエリ文字列の"code" パラメータに設定します。必要に応じて、Scope パラメータを設定してカスタム権限をリクエストします。

アクセストークンとリフレッシュトークンを取得した後、データに接続し、OAuth アクセストークンを自動または手動でリフレッシュすることが可能です。

OAuth アクセストークンの自動リフレッシュ

ドライバーがOAuth アクセストークンを自動的にリフレッシュするようにするには、最初のデータ接続で次のように設定します。

• InitiateOAuth：REFRESH に設定。
• OAuthClientId：アプリケーション設定のクライアントId に設定。
• OAuthClientSecret：アプリケーション設定のクライアントシークレットに設定。
• OAuthAccessToken：GetOAuthAccessToken によって返されたアクセストークンに設定。
• RefreshOAuthAccessToken：GetOAuthAccessToken によって返されたリフレッシュトークンに設定。
• OAuthSettingsLocation：Sync App がOAuth トークン値を保存する場所に設定。これは接続間で維持されます。

次回のデータ接続では、OAuthAccessToken およびOAuthRefreshToken の値は、OAuthSettingsLocation から取得されます。

OAuth アクセストークンの手動リフレッシュ

データ接続時に手動でOAuth アクセストークンをリフレッシュするために必要な値は、OAuth リフレッシュトークンのみです。

GetOAuthAccessToken によって返されたExpiresIn パラメータ値が経過した後に、RefreshOAuthAccessToken ストアドプロシージャを使用し、手動でOAuthAccessToken をリフレッシュします。次の接続プロパティを設定します。

• OAuthClientId：アプリケーション設定のクライアントId に設定。
• OAuthClientSecret：アプリケーション設定のクライアントシークレットに設定。

次に、RefreshOAuthAccessToken を呼び出し、OAuthRefreshToken にGetOAuthAccessToken によって返されたOAuth リフレッシュトークンを指定します。新しいトークンが取得できたら、OAuthAccessToken プロパティにRefreshOAuthAccessToken によって返された値を設定し、新しい接続をオープンします。

最後に、OAuth リフレッシュトークンを保存し、OAuth アクセストークンの有効期限が切れた後に手動でリフレッシュできるようにします。

ヘッドレスマシン

ヘッドレスマシンのユーザーアカウントでOAuth を使用するようにドライバーを設定するには、インターネットブラウザに対応した別の端末で認証する必要があります。

1. 以下のオプションから選択します。
   • オプション1：後述の「Verifier code を取得および交換」に従い、OAuthVerifier 値を取得します。
   • オプション2：ブラウザに対応したマシンにSync App をインストールし、後述の「OAuth 設定を転送」の説明に従い、通常のブラウザベースのフローで認証後にOAuth 認証値を転送します。

2. 次に、ヘッドレスマシンからアクセストークンを自動的にリフレッシュするようにSync App を設定します。

オプション1：Verifier code を取得および交換

Verifier code を取得するには、OAuth Authorization URL で認証する必要があります。

インターネットブラウザに対応したマシンから認証してOAuthVerifier 接続プロパティを取得する方法は次のとおりです。

1. 以下のオプションから選択します。
   • 埋め込みOAuth アプリケーションを使用する場合は、Salesforce Marketing Cloud Account Engagement OAuth endpoint をクリックし、ブラウザでエンドポイントを開きます。
   • カスタムOAuth アプリケーションを使用するには、以下のプロパティを設定し、認証URL を作成します。
     • InitiateOAuth：OFF に設定。
     • OAuthClientId：アプリケーションの登録時に割り当てられたクライアントId に設定。
     • OAuthClientSecret：アプリケーションの登録時に割り当てられたクライアントシークレットに設定。
     次に、適切なCallbackURL を指定してGetOAuthAuthorizationURL ストアドプロシージャを呼び出します。ストアドプロシージャによって返されたURL をブラウザで開きます。

2. ログインして、Sync App にアクセス許可を与えます。すると、verifier code を含むコールバックURL にリダイレクトされます。
3. verifier code の値を保存します。後ほどこれをOAuthVerifier 接続プロパティに設定します。

次に、OAuth verifier code をOAuth リフレッシュトークンおよびアクセストークンと交換する必要があります。次のプロパティを設定します。

ヘッドレスマシンでは、次の接続プロパティを設定してOAuth 認証値を取得します。

• InitiateOAuth：REFRESH に設定。
• OAuthVerifier：verifier code に設定。
• OAuthClientId：（カスタムアプリのみ）カスタムOAuth アプリケーション設定のクライアントId に設定。
• OAuthClientSecret：（カスタムアプリのみ）カスタムOAuth アプリケーション設定のクライアントシークレットに設定。
• OAuthSettingsLocation：これを設定すると、暗号化されたOAuth 認証値が指定された場所に永続化されます。

OAuth 設定ファイルが生成されたら、以下のように接続プロパティをリセットする必要があります。

• InitiateOAuth：REFRESH に設定。
• OAuthClientId：（カスタムアプリのみ）アプリケーションの登録時に割り当てられたクライアントId に設定。
• OAuthClientSecret：（カスタムアプリのみ）アプリケーションの登録時に割り当てられたクライアントシークレットに設定。
• OAuthSettingsLocation：暗号化されたOAuth 認証値を含む場所に設定。アクセストークンの自動リフレッシュを有効にするために、この場所がSync App に読み書きのアクセス許可を与えることを確認してください。

オプション2：OAuth 設定を転送

ヘッドレスマシンでの接続に先立ち、インターネットブラウザに対応したデバイスでドライバとの接続をインストールし、作成する必要があります。上述の「デスクトップアプリケーション」の説明に従って、接続プロパティを設定します。

「デスクトップアプリケーション」の手順が完了すると、生成された認証値は、OAuthSettingsLocation で指定された場所に暗号化されて書き込まれます。デフォルトのファイル名はOAuthSettings.txt です。

接続テストに成功したら、OAuth 設定ファイルをヘッドレスマシンにコピーします。

ヘッドレスマシンで、次の接続プロパティを設定し、データに接続します。

• InitiateOAuth：REFRESH に設定。
• OAuthClientId：（カスタムアプリのみ）アプリケーションの登録時に割り当てられたクライアントId に設定。
• OAuthClientSecret：（カスタムアプリのみ）アプリケーションの登録時に割り当てられたクライアントシークレットに設定。
• OAuthSettingsLocation：OAuth 設定ファイルの場所に設定。アクセストークンの自動リフレッシュを有効にするために、この場所がSync App に読み書きのアクセス許可を与えることを確認してください。

OAuthPassword

ユーザー名とパスワードで認証する場合は、以下の接続プロパティを設定します。

• AuthScheme：OAuthPassword に設定。
• User：ユーザーネームに設定。
• Password：ユーザーパスワードに設定。
• InitiateOAuth：GETANDREFRESH に設定。InitiateOAuth を使って、OAuth 交換や、手動でのOAuthAccessToken 接続プロパティの設定の繰り返しを避けられます。

OAuthJWT

AuthScheme をOAuthJWT に設定します。

OAuth アプリケーション（カスタムOAuth アプリケーションの作成 参照）作成後、次の接続プロパティを設定します。

• AuthScheme：OAuthJWT。
• InitiateOAuth：GETANDREFRESH。
• OAuthJWTCert：JWT 証明書のストア。
• OAuthJWTPassword：JWT 証明書ストアのパスワード。
• OAuthJWTIssuer：OAuth クライアントID。
• OAuthJWTCertType：OAuthJWTCert で指定された証明書ストアの種類。

Note：このフローではリフレッシュトークンは発行されません。

このセクションでは、Salesforce Marketing Cloud Account Engagement Sync App の高度な機能を厳選して説明します。

ユーザー定義ビュー

Sync App はユーザー定義ビューの使用をサポートします。これは事前設定されたユーザー定義クエリによって内容が決定される仮想テーブルです。 このビューは、ドライバーに発行されるクエリを直接制御できない場合に有効です。 カスタムビューの作成と設定の概要については、ユーザー定義ビュー を参照してください。

SSL の設定

SSL の設定 を使用して、Sync App が証明書のネゴシエーションをどのように扱うかを調整します。さまざまな証明書形式を選択できます。詳しくは、「接続文字列オプション」にあるSSLServerCert プロパティを参照してください。

ファイアウォールとプロキシ

Windows プロキシとHTTP プロキシを含むファイアウォールとプロキシ に合致するようSync App を設定します。トンネル接続を設定することもできます。

クエリ処理

Sync App は、Salesforce Marketing Cloud Account Engagement にできるだけ多くのSELECT ステートメント処理をオフロードし、残りのクエリをクライアント側のインメモリで処理します。

詳しくは、クエリ処理 を参照してください。

ログ

CData ログを調整するために使用可能な設定の概要については、ログ を参照してください。基本的なロギングに必要なのは2つの接続プロパティだけですが、より詳細なロギングをサポートする多くの機能があり、LogModules 接続プロパティを使用してロギングする情報のサブセットを指定することができます。

SSL 設定のカスタマイズ

デフォルトでは、Sync App はサーバーとのTLS のネゴシエーションを試みます。サーバー証明書は、デフォルトのシステム信頼済み証明書ストアで検証されます。SSLServerCert 接続プロパティを使用して、証明書の検証方法をオーバーライドできます。

別の証明書を指定するには、SSLServerCert 接続プロパティを参照してください。

Firewall またはProxy 経由の接続

HTTP プロキシ

HTTP プロキシへの認証には、以下のように設定します。

• ProxyServer：HTTP トラフィックをルートするプロキシサーバーのホストネームもしくはIP アドレス。
• ProxyPort：プロキシサーバーが起動しているTCP ポート。
• ProxyAuthScheme：プロキシサーバーに対して認証する際にSync App が使用する認証方法。
• ProxyUser：プロキシサーバーに登録されているユーザーアカウントのユーザー名。
• ProxyPassword：ProxyUser に紐付けられたパスワード。

その他のプロキシ

次のプロパティを設定します。

• プロキシベースのファイヤーウォールを使用するには、FirewallType、FirewallServer、およびFirewallPort を設定します。
• 接続をトンネルするには、FirewallType をTUNNEL に設定します。
• 認証するには、FirewallUser とFirewallPassword を設定します。
• SOCKS プロキシへの認証には、さらにFirewallType をSOCKS5 に設定します。

概要

このセクションでは、利用可能なAPI オブジェクトを示し、Salesforce Marketing Cloud Account Engagement API へのSQL の実行について詳しく説明します。

主要機能

• Sync App はドキュメント、フォルダ、およびグループなどのSalesforce Marketing Cloud Account Engagement エンティティをリレーショナルビューとしてモデル化し、SQL を記述してSalesforce Marketing Cloud Account Engagement データをクエリできるようにします。
• ストアドプロシージャを使用すると、アクセストークンの取得やOAuth 2.0でのリフレッシュの維持など、Salesforce Marketing Cloud Account Engagement の操作を実行できます。
• これらのオブジェクトへのライブ接続により、Sync App を使用するとSalesforce Marketing Cloud Account Engagement アカウントへのあらゆる変更が即座に反映されます。

CData Sync App は、Salesforce Marketing Cloud Account Engagement エンティティをリレーショナルテーブル、ビュー、およびストアドプロシージャにモデル化します。 このセクションでは、API の制限および要件について説明します。既定の SupportEnhancedSQL 機能を使って、これらの制限の大半を回避できます。

Sync App はSalesforce Marketing Cloud Account Engagement のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

Salesforce Marketing Cloud Account Engagement Connector for CData Sync テーブル

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

• 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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

• 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 Sync App 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 Sync App.

• 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 Sync App 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 Sync App.

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

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

Salesforce Marketing Cloud Account Engagement Connector for CData Sync ビュー

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

• 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 Sync App 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 Sync App.

• 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 Sync App 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 Sync App.

• 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 Sync App 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 Sync App.

• 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 Sync App 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 Sync App.

• 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 Sync App 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 Sync App.

• 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 Sync App 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 Sync App.

• 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 Sync App 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 Sync App.

• 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 Sync App 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 Sync App.

• 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 Sync App 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 Sync App.

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

CData Sync App は、Salesforce Marketing Cloud Account Engagement Pardot API v5 オブジェクトをリレーショナルテーブル、ビュー、およびストアドプロシージャにモデル化します。 このセクションでは、API の制限および要件について説明します。既定の SupportEnhancedSQL 機能を使って、これらの制限の大半を回避できます。

テーブル

テーブル では、利用可能な標準テーブルの例について説明します。

Sync App はSalesforce Marketing Cloud Account Engagement のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

Salesforce Marketing Cloud Account Engagement Connector for CData Sync テーブル

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.

テーブル固有の情報

Select

Sync App はSalesforce Marketing Cloud Account Engagement API を使用して、以下のカラムと演算子で構築されたWHERE 句条件を処理します。その他のフィルタはクライアントサイドでSync App 内部で処理されます。

• Id は '='、'<'、'<='、'>'、'>=' をサポートしています。
• IsDeleted は'=' をサポートしています。

例えば、次のクエリはサーバーサイドで処理されます。

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

Insert

挿入を実行する際は、次の属性が必須です：Name、CampaignId、DestinationUrl。

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

Update

CustomRedirects 属性を更新します。

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

Delete

すべてのCustomRedirects オブジェクト、またはCustomRedirect ID を指定することで1つのオブジェクトを削除します。

DELETE FROM CustomRedirects WHERE Id = 10003

GetDeleted

Pardot のごみ箱にあるカスタムリダイレクトを取得する場合、次の操作がサポートされています。

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

• 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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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.

テーブル固有の情報

Select

Sync App はSalesforce Marketing Cloud Account Engagement API を使用して、以下のカラムと演算子で構築されたWHERE 句条件を処理します。その他のフィルタはクライアントサイドでSync App 内部で処理されます。

• Id は '='、'<'、'<='、'>'、'>=' をサポートしています。
• CreatedAt は '='、'<'、'<='、'>'、'>=' をサポートしています。
• UpdatedAt は '='、'<'、'<='、'>'、'>=' をサポートしています。

例えば、次のクエリはサーバーサイドで処理されます。

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

挿入を実行する際は、次の属性が必須です：Name、FilePath

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

Update

Files の更新：

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

Delete

すべてのファイル、またはファイルId を指定して特定のファイルを削除します。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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.

テーブル固有の情報

Select

Sync App はSalesforce Marketing Cloud Account Engagement API を使用して、以下のカラムと演算子で構築されたWHERE 句条件を処理します。その他のフィルタはクライアントサイドでSync App 内部で処理されます。

• Id は '='、'<'、'<='、'>'、'>=' をサポートしています。
• IsDeleted は'=' をサポートしています。

例えば、次のクエリはサーバーサイドで処理されます。

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

Insert

挿入を実行する際は、次の属性が必須です：Name、LayoutContent

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

Update

LayoutTemplates の更新：

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

Delete

すべてのレイアウトテンプレート、またはテンプレートId を指定して特定のレイアウトテンプレートを削除します。

DELETE FROM LayoutTemplates WHERE Id = 10003

GetDeleted

Pardot のごみ箱にあるレイアウトテンプレートを取得する場合、次の操作がサポートされています。

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.

テーブル固有の情報

Select

Sync App はSalesforce Marketing Cloud Account Engagement API を使用して、以下のカラムと演算子で構築されたWHERE 句条件を処理します。その他のフィルタはクライアントサイドでSync App 内部で処理されます。

• Id は '='、'<'、'<='、'>'、'>=' をサポートしています。
• CreatedAt は '='、'<'、'<='、'>'、'>=' をサポートしています。
• UpdatedAt は '='、'<'、'<='、'>'、'>=' をサポートしています。
• IsDeleted は'=' をサポートしています。

例えば、次のクエリはサーバーサイドで処理されます。

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

Insert

挿入を実行する際は、次の属性が必須です：Name

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

Update

Lists 属性を更新します。

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

Delete

すべてのリスト、またはリストId を指定して特定のリストを削除します。

DELETE FROM Lists WHERE Id = 10003

GetDeleted

Pardot のごみ箱にあるリストを取得する場合、次の操作がサポートされています。

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.

テーブル固有の情報

Select

Sync App はSalesforce Marketing Cloud Account Engagement API を使用して、以下のカラムと演算子で構築されたWHERE 句条件を処理します。その他のフィルタはクライアントサイドでSync App 内部で処理されます。

• Id は '='、'<'、'<='、'>'、'>=' をサポートしています。
• UserId は '='、'<'、'<='、'>'、'>=' をサポートしています。
• AssignedToId は '='、'<'、'<='、'>'、'>=' をサポートしています。
• LastActivityAt は '='、'<'、'<='、'>'、'>=' をサポートしています。
• IsDeleted は'=' をサポートしています。
• CreatedAt は '='、'<'、'<='、'>'、'>=' をサポートしています。
• UpdatedAt は '='、'<'、'<='、'>'、'>=' をサポートしています。

例えば、次のクエリはサーバーサイドで処理されます。

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

削除されたプロスペクトを取得するには、GETDELETED 文を実行します。

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.

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

Salesforce Marketing Cloud Account Engagement Connector for CData Sync ビュー

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.

テーブル固有の情報

Select

Sync App はSalesforce Marketing Cloud Account Engagement API を使用して、以下のカラムと演算子で構築されたWHERE 句条件を処理します。その他のフィルタはクライアントサイドでSync App 内部で処理されます。

• Id は '='、'<'、'<='、'>'、'>=' をサポートしています。
• Name は'=' をサポートしています。
• CreatedAt は '='、'<'、'<='、'>'、'>=' をサポートしています。
• UpdatedAt は '='、'<'、'<='、'>'、'>=' をサポートしています。

例えば、次のクエリはサーバーサイドで処理されます。

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

GetDeleted

Pardot のごみ箱にあるキャンペーンを取得する場合、次の操作がサポートされています。

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.

テーブル固有の情報

Select

Sync App は Salesforce Marketing Cloud Account Engagement API を使用して、以下のカラムと演算子で構築されたWHERE 句条件を処理します。その他のフィルタはクライアントサイドでSync App 内部で処理されます。

• Id は '='、'<'、'<='、'>'、'>=' をサポートしています。
• CreatedAt は '='、'<'、'<='、'>'、'>=' をサポートしています。
• UpdatedAt は '='、'<'、'<='、'>'、'>=' をサポートしています。

例えば、次のクエリはサーバーサイドで処理されます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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.

テーブル固有の情報

Select

Sync App はSalesforce Marketing Cloud Account Engagement API を使用して、以下のカラムと演算子で構築されたWHERE 句条件を処理します。その他のフィルタはクライアントサイドでSync App 内部で処理されます。

• Id は '='、'<'、'<='、'>'、'>=' をサポートしています。
• ProspectId は '='、'<'、'<='、'>'、'>=' をサポートしています。
• ListEmailId は '='、'<'、'<='、'>'、'>=' をサポートしています。
• SentAt は '='、'<'、'<='、'>'、'>=' をサポートしています。

例えば、次のクエリはサーバーサイドで処理されます。

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.

テーブル固有の情報

Select

Sync App はSalesforce Marketing Cloud Account Engagement API を使用して、以下のカラムと演算子で構築されたWHERE 句条件を処理します。その他のフィルタはクライアントサイドでSync App 内部で処理されます。

• Id は '='、'<'、'<='、'>'、'>=' をサポートしています。
• ParentFolderId は '='、'<'、'<='、'>'、'>=' をサポートしています。

例えば、次のクエリはサーバーサイドで処理されます。

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.

テーブル固有の情報

Select

Sync App はSalesforce Marketing Cloud Account Engagement API を使用して、以下のカラムと演算子で構築されたWHERE 句条件を処理します。その他のフィルタはクライアントサイドでSync App 内部で処理されます。

• Id は '='、'<'、'<='、'>'、'>=' をサポートしています。
• IsDeleted は'=' をサポートしています。

例えば、次のクエリはサーバーサイドで処理されます。

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

GetDeleted

Pardot のごみ箱にあるフォームを取得する場合、次の操作がサポートされています。

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

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

Use lifecycle history resources to learn how a prospect is moving through their sales journey, from visitor to won opportunity.

Columns

Name	Type	References	Description
Id [KEY]	Int64		Pardot ID of this lifecycle history.
ProspectId	Int64	Prospects.Id	Pardot ID for the prospect in this stage.
PreviousStageId	Int64		Pardot ID of the stage this prospect previously was in.
NextStageId	Int64		Pardot ID of the stage this prospect is in next.
SecondsElapsed	Integer		Number of seconds for prospect to get to current stage.
CreatedAt	Datetime		The timestamp of when this object was created.

Retrieving a collection of lifecycle stages

Table Specific Information

Select

The Sync App 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 Sync App.

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

For example, the following queries are processed server side:

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

GetDeleted

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

GETDELETED FROM LifecycleStages

Columns

Name	Type	References	Description
Id [KEY]	Int64		ID of the lifecycle stage.
Name	String		Pardot ID of this lifecycle stage.
IsDeleted	Boolean		True if the lifecycle stage is in the recycle bin in Pardot.
CreatedAt	Datetime		Creation time of the lifecycle stage.
UpdatedAt	Datetime		Last updated time for the lifecycle stage.
IsLocked	Boolean		When true, lifecycle stage is locked.
Position	Int		Lifcycle stage's position in lifecycle.
MatchType	String		Match all is returned when a prospect must match all rules in order to be in this stage. Match any is returned when a prospect can match 1 or more rules

A list email is a single email sent to a recipient list.

テーブル固有の情報

Select

Sync App はSalesforce Marketing Cloud Account Engagement API を使用して、以下のカラムと演算子で構築されたWHERE 句条件を処理します。その他のフィルタはクライアントサイドでSync App 内部で処理されます。

• Id は '='、'<'、'<='、'>'、'>=' をサポートしています。
• IsDeleted は'=' をサポートしています。
• CreatedAt は '='、'<'、'<='、'>'、'>=' をサポートしています。
• UpdatedAt は '='、'<'、'<='、'>'、'>=' をサポートしています。
• SentAt は '='、'<'、'<='、'>'、'>=' をサポートしています。

例えば、次のクエリはサーバーサイドで処理されます。

SELECT * FROM ListEmail WHERE Id = 50
SELECT * FROM ListEmail WHERE SentAt > '01/01/2022'
SELECT * FROM ListEmail WHERE CreatedAt > '01/01/2022'

GetDeleted

Pardot のごみ箱にあるリストメールを取得する場合、次の操作がサポートされています。

GETDELETED FROM ListEmail

Columns

Name	Type	References	Description
Id [KEY]	Int64		ID of the email.
Name	String		Name of the object for identification in Pardot.
Subject	String		The email subject.
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.
CampaignId	Int64		Pardot campaign related to this object.
IsPaused	Boolean		True if the email send is paused.
IsSent	Boolean		True if the email has been sent.
IsDeleted	Boolean		True if the object is in the recycle bin in Pardot.
CreatedById	Int64		ID of the individual who created this object.
UpdatedById	Int64		ID of the individual who last updated this object.
CreatedAt	Datetime		The timestamp of when this object was created.
UpdatedAt	Datetime		The timestamp of when this object was last updated.
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.
SentAt	Datetime		Time the email was sent.
OperationalEmail	Boolean		True if the email is operational.
EmailTemplateId	Int64		ID of the email template used for the list email.
TrackerDomainId	Int64		ID of the tracker domain used for the list email.
SenderOptionsType	String		The sending user type(s).
SenderOptionsAddress	String		The sending email address(s).
SenderOptionsName	String		The name(s) of the sender.
SenderOptionsUserId	String		The user ID(s) of the sender.
ReplyToOptionsType	String		The reply to user type(s).
ReplyToOptionsAddress	String		The sending email address(s).
ReplyToOptionsUserId	String		The user ID(s) of the sender.

RecipientLists used by an email

Retrieve a Single Email Recipient

To retrieve a single email recipient, specify its ID:

SELECT * FROM ListEmailRecipients WHERE Id = '55555555'

Retrieve Multiple Email Recipients

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

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

Columns

Name	Type	References	Description
Id [KEY]	Int64		ID of the RecipientList.
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.