このページでは、CData Cloud でのSalesforce Marketing Cloud Account Engagement への接続の確立 のガイド、利用可能なリソースに関する情報、および使用可能な接続プロパティのリファレンスについて説明します。

Salesforce Marketing Cloud Account Engagement への接続

接続の確立 は、CData Cloud にデータベースを作成するためのSalesforce Marketing Cloud Account Engagement への認証方法と必要な接続プロパティの設定方法について示します。

CData Cloud サービスからデータにアクセス

利用可能な標準サービスを経由してSalesforce Marketing Cloud Account Engagement からデータにアクセスする方法と、CData Cloud の管理については、CData Cloud ドキュメント で詳しく説明します。

Database タブで対応するアイコンを選択して、Salesforce Marketing Cloud Account Engagement に接続します。必須プロパティはSettings にリストされています。Advanced タブには、通常は必要ない接続プロパティが表示されます。

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：Cloud が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：ブラウザに対応したマシンにCloud をインストールし、後述の「OAuth 設定を転送」の説明に従い、通常のブラウザベースのフローで認証後にOAuth 認証値を転送します。

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

オプション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. ログインして、Cloud にアクセス許可を与えます。すると、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 認証値を含む場所に設定。アクセストークンの自動リフレッシュを有効にするために、この場所がCloud に読み書きのアクセス許可を与えることを確認してください。

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

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

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

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

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

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

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：このフローではリフレッシュトークンは発行されません。

SSL 設定のカスタマイズ

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

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

Firewall またはProxy 経由の接続

HTTP プロキシ

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

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

その他のプロキシ

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

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

概要

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

主要機能

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

CData Cloud は、Salesforce Marketing Cloud Account Engagement エンティティをリレーショナルテーブル、ビュー、およびストアドプロシージャにモデル化します。 Cloud は、Salesforce Marketing Cloud Account Engagement API を使用してサポートされているフィルタを処理します。その他のフィルタは、Cloud 内でクライアント側で処理されます。

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

CData Cloud - Salesforce Marketing Cloud Account Engagement テーブル

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

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

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

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

CData Cloud - Salesforce Marketing Cloud Account Engagement ビュー

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.

ストアドプロシージャはファンクションライクなインターフェースで、Salesforce Marketing Cloud Account Engagement の単純なSELECT/INSERT/UPDATE/DELETE 処理にとどまらずCloud の機能を拡張します。

ストアドプロシージャは、パラメータのリストを受け取り、目的の機能を実行し、プロシージャが成功したか失敗したかを示すとともにSalesforce Marketing Cloud Account Engagement から関連するレスポンスデータを返します。

CData Cloud - Salesforce Marketing Cloud Account Engagement ストアドプロシージャ

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.

CData Cloud は、Salesforce Marketing Cloud Account Engagement Pardot API v5 オブジェクトをリレーショナルテーブル、ビュー、およびストアドプロシージャにモデル化します。 Cloud は、Salesforce Marketing Cloud Account Engagement API を使用してサポートされているフィルタを処理します。その他のフィルタは、Cloud 内でクライアント側で処理されます。

テーブル

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

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

CData Cloud - Salesforce Marketing Cloud Account Engagement テーブル

Name	Description
CustomFields	Retrieve custom fields metadata.
CustomRedirects	Track links on your website or third-party site with Pardot custom redirects.
DynamicContent	Retrieve a collection of dynamic content
DynamicContentVariations	Retrieve a collection of Dynamic Content Variations
EmailTemplates	Query email templates.
Files	Upload images and other files to use in your Pardot emails, forms, and landing pages.
FormFields	Retrieve form fields to capture and store data related to leads, contacts, accounts, and other objects within the system.
FormHandlers	Retrieve,Create,Updata,Delete a collection of Form Handlers
FormHandlersFields	Retrieve,Create,Update,Delete a collection of Form Handler Stages
Forms	Retrieve forms.
LandingPages	Retrieving a collection of landing pages
LayoutTemplates	Retrieve and modify layout templates.
ListMemberships	Retrieve,Create,Update,Delete a collection of list memberships
Lists	Retrieve and modify lists.
ProspectAccounts	Query the available prospect accounts.
Prospects	Retrieve and modify prospects.
Tags	Retrieve tags.

Retrieve custom fields metadata.

テーブル固有の情報

Select

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

• 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	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID for this custom field.
Name	String	False		Custom field's name.
FieldId	String	False		User-defined ID for the custom field.
Type	String	False		The type of custom field. Possible values: Text, Radio Button, Checkbox, Dropdown, Textarea, Multi-Select, Hidden, Number, Date, CRM User
CreatedAt	Datetime	True		Time custom field was created in Pardot.
UpdatedAt	Datetime	True		Last time custom field was updated.
CreatedById	Int64	True		ID of the individual who created this object.
UpdatedById	Int64	True		ID of the individual who last updated this object.
IsRecordMultipleResponses	Boolean	False		If true, this custom field will record multiple responses.
IsUseValues	Boolean	False		If true, this custom field will use predefined values.
SalesforceId	String	False		The alpha-numeric Id of the Salesforce Org.
IsRequired	Boolean	False		True if the custom field is required.
isAnalyticsSynced	Boolean	True		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, Country Codes, US States, US State Codes, Canadian Provinces, Canadian Province Codes, US States and Canadian Provinces, US State and Canadian Province Codes, Japanese Prefectures, Japanese Prefecture Codes

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

テーブル固有の情報

Select

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

• 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	False		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	False		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'.
TagReplacementLanguage	String	True		Merge language of the dynamic content. Can be 'pml', 'hml', or 'undetermined'.
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	False		ID of the folder containing this object.
TrackerDomainId	Int64	False		The ID of the tracker domain associated with this object.
BasedOnProspectApiFieldId	String	False		The name of the Account Engagement prospect field that the Dynamic Content is mapped to. On standard fields the value is the name of the field. For example, 'firstName'.

Retrieve a collection of Dynamic Content Variations

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID for this dynamic content variation.
Comparison	String	True		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.
Operator	String	False		The operator found in the logic used to set the criteria for a variation. Possible values: is, contains, between, less-than, less-than-or-equal, greater-than, greater-than-or-equal
Content	String	False		The text content of the variation.
DynamicContentId	Int64	False		ID of the dynamic content this variation is associated with.
Value1	String	False		The value found in the logic used to set the criteria for a variation. All operators except between require one value.
Value2	String	False		The value found in the logic used to set the criteria for a variation when using the between operator. All other operators don't support two values.

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

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

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

• 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 form fields to capture and store data related to leads, contacts, accounts, and other objects within the system.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		ID of the form field.
FormId	Int64	False		The ID of the form the Form Field belongs to.
Type	String	False		Data type of the object. Data Type can be: text, radio button, checkbox, dropdown, textarea, hidden, date, multi-select.
DataFormat	String	False		The field's data format. Data Format can be: text, number, email, email with valid mail server, email not from isps and free email providers. Default 'text'.
ProspectApiFieldId	String	False		The name of the Pardot prospect field that the form field is mapped to. For example,
Label	String	False		Label of the form field.
Description	String	False		User-assigned description for the field. This field isn't queryable.
ErrorMessage	String	False		Error to display when the field isn't submitted correctly.
CssClasses	String	False		CSS class to apply to the paragraph element that wraps the form input and label.
IsRequired	Boolean	False		Set to true to make populating this field required for form submission. Default false.
IsAlwaysDisplay	Boolean	False		Set to true to always display this field, even if a value has already been collected for the prospect. Default false.
isMaintainInitialValue	Boolean	False		Set to true to maintain the prospect field value of the initial form submission. Subsequent submissions with new values will be ignored. Default false.
isDoNotPrefill	Boolean	False		Set to true to prevent prospect data from being pre-filled into the field. Default false.
SortOrder	Int64	True		Each field in a given form will be assigned a number that indicates the display order.
HasDependents	Boolean	True		Set to true if this field has Dependents.
HasProgressives	Boolean	True		Set to true if this field has Progressives.
HasValues	Boolean	True		Set to true if this field has Values.
CreatedAt	Datetime	True		Creation time of the object.
UpdatedAt	Datetime	True		Last update time of the object.
CreatedById	Int64	True		ID of the user who created this object.
UpdatedById	Int64	True		ID of the user who updated this object.

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

テーブル固有の情報

Select

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

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

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

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

GetDeleted

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

GETDELETED FROM Forms 

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID for this form.
Name	String	False		Form's name.
CampaignId	Int64	False	Campaigns.Id	Pardot ID of the campaign associated with this form.
EmbedCode	String	True		The code used to embed the form on your webpage.
SalesforceId	String	True		The alpha-numeric Id of the Salesforce Org.
LayoutTemplateId	Int64	False		The ID of the layout template associated with this object
SubmitButtonText	String	False		The label used for the submit button
BeforeFormContent	String	False		The HTML text displayed above the form
AfterFormContent	String	False		The HTML text displayed below the form
ThankYouContent	String	False		The HTML text displayed after the form is submitted
ThankYouCode	String	False		Thank you code is typically Javascript used to call other systems like Google Conversion Tracking
IsAlwaysDisplay	Boolean	False		Always display form after submission. Disable the 10 minute waiting period after submitting a form
RedirectLocation	String	False		Redirect to this URL instead of showing the
IsUseRedirectLocation	Boolean	True		When redirect location is valid and enabled.
FontSize	String	False		Possible values: default, 8pt, 10pt, 12pt, 14pt , 18pt, 24pt, 36pt.
FontFamily	String	False		Possible values: default,
FontColor	String	False		6 digit number in hexadecimal format such as #123456
LabelAlignment	String	False		Possible values: default, above, left
RadioAlignment	String	False		Possible values: default, horizontal, stacked
CheckboxAlignment	String	False		Possible values: default, horizontal, stacked
RequiredCharacter	String	False		Possible values: default , *
IsCookieless	String	False		Kiosk/Data Entry Mode: Do not cookie browser as submitted prospect
ShowNotProspect	Boolean	False		Include
IsCaptchaEnabled	Boolean	False		Enable explicit bot protecting using reCAPTCHA
IsDeleted	Boolean	True		True if the object is in the recycle bin in Pardot.
TrackerDomainId	Int64	False		The ID of the tracker domain associated with this object.
FolderId	Int64	False		ID of the folder containing 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.
CreatedById	Int64	True		ID of the individual who created this object.
UpdatedById	Int64	True		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	ReadOnly	References	Description
Id [KEY]	Int64	True		ID of the object.
Name	String	False		Name of the object for identification in Pardot.
SalesforceId	String	True		ID of the Salesforce object representing this object.
IsDeleted	Boolean	True		True if the object is in the recycle bin in Pardot.
CreatedById	Int64	True		ID of the user who created this object.
CreatedAt	Datetime	True		Creation time of the object.
UpdatedAt	Datetime	True		Last update time of the object.
UpdatedById	Int64	True		ID of the user who last updated this object.
CampaignId	Int64	False		Pardot Campaign related to this object.
FormId	Int64	False		ID of the form related to this object.
LayoutTemplateId	Int64	False		ID of the layout template related to this object.
Title	String	False		The landing page's title.
Description	String	False		The landing page's meta description. Used for SEO.
IsDoNotIndex	Boolean	False		True if the landing page is hidden from search engine indexing.
LayoutType	String	True		Specifies where the landing page was created. Possible values are Layout Template, Landing Page Builder, Legacy Page Builder, Salesforce Builder.
ScriptFragment	String	False		Script specified for the landing page. Available only on read requests.
LayoutCss	LayoutCss	True		CSS code for the layout template. Available only on read requests.
LayoutCssGenerated	String	True		Generated CSS code for the layout template. Available only on read requests.
LayoutTableBorder	Int	True		Specifies the layout table border width.
RedirectLocation	String	False		The redirect URL used if the prospect is redirected instead of showing the thank you content.
IsUseRedirectLocation	Boolean	True		True if the prospect is redirected instead of showing the form thank you content.
BitlyIsPersonalized	Boolean	True		True if the object has a bitly URL that is personalized.
BitlyShortUrl	String	True		Bitly URL if present.
Url	String	True		Link for the landing page.
VanityUrl	String	True		Vanity URL if present.
VanityUrlPath	String	False		The unique path for the asset, used to create the vanity URL.
TrackerDomainId	Int64	False		The ID of the tracker domain associated with this object.
FolderId	Int64	False		ID of the folder containing this object.
ArchiveDate	Date	False		The landing page's archive date('yyyy-mm-dd').
Content	String	False		The editable content if using a stock template. This field isn’t queryable.
OpeningGeneralContent	String	False		If using a layout template without pardot-region attributes, this value is displayed in the %%form-opening-general-content%% tag. This field isn’t queryable.
RegionContent	String	False		If using a layout template that contains pardot-region attributes, the values here override the default values in the layout template. This field isn’t queryable.

Retrieve and modify layout templates.

テーブル固有の情報

Select

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

• 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

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

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

Query the available prospect accounts.

テーブル固有の情報

Select

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

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

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

SELECT * FROM ProspectAccounts WHERE Id > 31

Get Deleted

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

GETDELETED FROM ProspectAccounts

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		ID of the object.
Name	String	True		Name of the object.
SalesforceId	String	True		ID of the Salesforce object representing this object.
IsDeleted	Boolean	True		True if the object is in the recycle bin in Pardot.
AnnualRevenue	String	True		Annual Revenue of the object.
BillingAddressOne	String	True		Billing Address of the object.
BillingAddressTwo	String	True		Second Billing Address of the object.
BillingCity	String	True		City of the object.
BillingCountry	String	True		County of the object.
BillingState	String	True		State of the object.
BillingZip	String	True		Zip of the object.
Description	String	True		Description of the object.
Employees	String	True		Employees of the object.
Fax	String	True		Fax of the object.
Industry	String	True		Industry of the object.
Number	String	True		Number of the object.
Ownership	String	True		Ownership of the object.
Phone	String	True		Phone Number of the object.
Rating	String	True		Rating of the object.
ShippingAddressOne	String	True		Shipping Address of the object.
ShippingAddressTwo	String	True		Second Shipping Address of the object.
ShippingCity	String	True		Shipping Address city of the object.
ShippingCountry	String	True		Shipping Address country of the object.
ShippingState	String	True		Shipping Address state of the object.
ShippingZip	String	True		Shipping Address zip of the object.
Sic	String	True		SIC of the object.
Site	String	True		Site of the object.
TickerSymbol	String	True		Ticker Symbol of the object.
Type	String	True		Type of the object.
Website	String	True		Website of the object.
CreatedAt	Datetime	True		Creation time of the object.
UpdatedAt	Datetime	True		Last update time of the object.
AssignedToId	Int64	True		ID of the user who the object record is assigned to.
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

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

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

Retrieve tags.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		Pardot ID for this tag.
Name	String	False		The name of the tag.
ObjectCount	Int64	True		Number of objects tagged with the tag.
CreatedAt	Datetime	True		Creation time of the tag.
UpdatedAt	Datetime	True		Last update time of the tag.
CreatedById	Int64	True		ID of the user who created this tag.
UpdatedById	Int64	True		ID of the user who last updated this tag.

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

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

CData Cloud - Salesforce Marketing Cloud Account Engagement ビュー

Name	Description
Account	Use the account resource to learn about the current user's Pardot account.
Campaigns	Retrieve and modify campaigns.
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.
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
ListEmailStats	Query statistical information for a list email, such as the number of clicks, opens, and opt-outs.
Opportunities	Retrieve a collection of opportunities
ReplyToOptions	Query the replyTo options of email templates.
SenderOptions	Query the sender options of email templates.
TaggedObjects	Retrieve tagged objects.
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.