【第349回】 Marketing Cloud Next ： Query Editor で分析に使えるクエリ集

Marketing Cloud Next Growth & Advanced Edition の Query Editor は Data Cloud 内のデータを調査するのに便利です。今回、私がよく利用している調査用クエリを公開します。

Query Editor が存在しているものの、その使い道がよく分からないとお悩みの場合は、以下の 7 つを入れて保存しておくと良いでしょう。どこかで利用できる可能性があります。

Email Engagement を確認する

最新の Email Engagement の情報を取得するのに便利です。WHERE 句を使えば、Salesforce ID、フロー名、メール要素 API 名などで絞り込みが可能です。

SELECT
    a.ssot__IndividualId__c AS Id,
    a.ssot__SendtimeEmailAddress__c AS Email,
    i.ssot__FirstName__c AS FirstName,
    i.ssot__LastName__c AS LastName,
    a.ssot__EngagementDateTm__c + interval '9 hour' AS EngagementDateTime,
    a.ssot__EngagementChannelActionId__c AS ActionName,
    a.ssot__EmailRecipientSendStatus__c AS SendStatus,
    c.ssot__Name__c AS EmailElementAPIName,
    e.ssot__Name__c AS FlowName,
    d.ssot__VersionNumber__c AS VersionNumber,
    h.ssot__Name__c AS SegmentName,
    a.ssot__EngagementActionReasonText__c AS ActionReason,
    a.ssot__EmailBounceType__c AS BounceType,
    a.ssot__BounceReasonText__c AS BounceReason,
    a.ssot__UnsubscribeSourceText__c AS UnsubscribeSource,
    a.ssot__ResolvedURL__c AS LinkURL,
    g.ssot__Subject__c AS EmailSubject,
    g.ssot__FromAddress__c AS FromAddress,
    g.ssot__MessagePurpose__c AS MessagePurpose
FROM ssot__EmailEngagement__dlm a
JOIN ssot__FlowElementRun__dlm b ON a.ssot__FlowElementRunId__c = b.ssot__Id__c
JOIN ssot__FlowElement__dlm c ON b.ssot__FlowElementId__c = c.ssot__Id__c
JOIN ssot__FlowVersion__dlm d ON c.ssot__FlowVersionId__c = d.ssot__Id__c
JOIN ssot__Flow__dlm e ON d.ssot__FlowId__c = e.ssot__Id__c
JOIN ssot__BulkEmailMessage__dlm g ON a.ssot__BulkEmailMessageId__c = g.ssot__Id__c
LEFT OUTER JOIN ssot__MarketSegment__dlm h ON g.ssot__MarketSegmentId__c = h.ssot__Id__c
LEFT OUTER JOIN ssot__Individual__dlm i ON a.ssot__IndividualId__c = i.ssot__Id__c
-- WHERE a.ssot__EngagementChannelActionId__c != 'OPEN' /*Action Name Filter*/
-- WHERE a.ssot__IndividualId__c = '***' /*Individual Id Filter*/
-- WHERE a.ssot__SendtimeEmailAddress__c = '***' /*Email Filter*/
-- WHERE i.ssot__FirstName__c = '***' AND i.ssot__LastName__c = '***' /*Name Filter*/
-- WHERE e.ssot__Name__c = '***' /*Flow Name Filter*/
-- WHERE h.ssot__Name__c = '***' /*Segment Name Filter*/
-- WHERE c.ssot__Name__c = '***' /*Email Element API Name Filter*/
ORDER BY a.ssot__EngagementDateTm__c DESC
LIMIT 10

Website Engagement を確認する

最新の Website Engagement の情報を取得するのに便利です。

SELECT
    ssot__IndividualId__c AS Id, 
    ssot__EngagementDateTm__c + interval '9 hour' AS EngagementDateTime,
    ssot__PagePublicTitleName__c AS PageTitle, 
    ssot__EngagementChannelActionId__c AS ActionName, 
    ssot__LinkURL__c AS ClickLinkURL,
    ssot__AnchorLinkLabelText__c AS ClinkLinkText,
    -- ssot__UtmMediumName__c AS Medium,
    -- ssot__UtmTermDescription__c AS Term,
    -- ssot__UtmContentDescription__c AS Content,
    -- ssot__UtmCampaignName__c AS Campaign,
    -- ssot__UtmId__c AS CampaignId,
    -- ssot__UtmSourcePlatformName__c AS SourcePlatform,
    ssot__ReferrerURL__c AS ReferrerURL,
    ssot__OSName__c AS OSName,
    ssot__OSModelName__c AS OSVersion, 
    ssot__BrowserName__c AS BrowserName
FROM ssot__WebsiteEngagement__dlm 
-- WHERE ssot__IndividualId__c = '***' /*Individual Id Filter*/
ORDER BY ssot__EngagementDateTm__c DESC 
LIMIT 10

個人 ID から統合レコードを確認する

個人 ID を入力すると、それに紐づく 統合個人 ID などが分かります。その統合 ID に複数の個人 ID が紐づいている場合は、すべてが表示されます。

SELECT UnifiedId, Id, FirstName, LastName, Email, DataSource, IRUpdatedDate
FROM (
    SELECT
        b.UnifiedRecordId__c AS UnifiedId,
        b.SourceRecordId__c AS Id,
        d.ssot__FirstName__c AS FirstName,
        d.ssot__LastName__c AS LastName,
        c.ssot__EmailAddress__c AS Email,
        b.ssot__DataSourceObjectId__c AS DataSource,
        b.CreatedDate__c + interval '9 hour' AS IRUpdatedDate,
        ROW_NUMBER() OVER (
            PARTITION BY b.SourceRecordId__c, c.ssot__EmailAddress__c
            ORDER BY c.ssot__LastModifiedDate__c DESC
        ) AS rn
    FROM
        IndividualIdentityLink__dlm a /* ← 自分の Unified Link Individual DMO の API 名に変更する */
    LEFT OUTER JOIN IndividualIdentityLink__dlm b /* ← 自分の Unified Link Individual DMO の API 名に変更する */
        ON a.UnifiedRecordId__c = b.UnifiedRecordId__c
    LEFT OUTER JOIN ssot__ContactPointEmail__dlm c 
        ON b.SourceRecordId__c = c.ssot__PartyId__c
    LEFT OUTER JOIN UnifiedIndividual__dlm d /* ← 自分の Unified Individual DMO の API 名に変更する */
        ON a.UnifiedRecordId__c = d.ssot__Id__c
    WHERE a.SourceRecordId__c = '***' /*Individual Id Filter*/
    -- WHERE c.ssot__EmailAddress__c = '***' /*Email Address Filter*/
) t
WHERE rn = 1
ORDER BY Id
LIMIT 10

統合個人 ID から個人レコードを確認する

統合個人 ID を入力すると、それに紐づく 個人 ID などが分かります。その統合 ID に複数の個人 ID が紐づいている場合は、すべてが表示されます。

SELECT UnifiedId, Id, FirstName, LastName, Email, DataSource, IRUpdatedDate
FROM (
    SELECT
        a.UnifiedRecordId__c AS UnifiedId,
        a.SourceRecordId__c AS Id,
        c.ssot__FirstName__c AS FirstName,
        c.ssot__LastName__c AS LastName,
        b.ssot__EmailAddress__c AS Email,
        a.ssot__DataSourceObjectId__c AS DataSource,
        a.CreatedDate__c + interval '9 hour' AS IRUpdatedDate,
        ROW_NUMBER() OVER (
            PARTITION BY a.SourceRecordId__c, b.ssot__EmailAddress__c
            ORDER BY b.ssot__LastModifiedDate__c DESC
        ) AS rn
    FROM
        IndividualIdentityLink__dlm a /* ← 自分の Unified Link Individual DMO の API 名に変更する */
    LEFT OUTER JOIN ssot__ContactPointEmail__dlm b
        ON a.SourceRecordId__c = b.ssot__PartyId__c
    LEFT OUTER JOIN UnifiedIndividual__dlm c /* ← 自分の Unified Individual DMO の API 名に変更する */
        ON a.UnifiedRecordId__c = c.ssot__Id__c
    WHERE a.UnifiedRecordId__c = '***' /*Unified Individual Id Filter*/
) t
WHERE rn = 1
ORDER BY Id
LIMIT 10

メールアドレスから同意ステータスを確認する

メールアドレスから現在の同意ステータスを確認します。

SELECT ContactPointValue, Status, SubscriptionName, ChannelName, LastModifiedDate, SourceType, SourceName
FROM (
    SELECT
        a.ssot__ContactPointValueText__c AS ContactPointValue,
        a.ssot__ConsentStatus__c AS Status,
        c.ssot__Name__c AS SubscriptionName,
        d.ssot__Name__c AS ChannelName,
        a.ssot__LastModifiedDate__c + interval '9 hour' AS LastModifiedDate,
        a.ssot__ConsentCapturedSourceType__c AS SourceType,
        a.ssot__ConsentCapturedSourceName__c AS SourceName,
        ROW_NUMBER() OVER (
            PARTITION BY a.ssot__ContactPointValueText__c, c.ssot__Name__c
            ORDER BY a.ssot__LastModifiedDate__c DESC
        ) AS rn
    FROM ssot__CommunicationSubscriptionConsent__dlm a
    JOIN ssot__CommunicationSubscriptionChannelType__dlm b 
        ON a.ssot__CommunicationSubscriptionChannelTypeId__c = b.ssot__Id__c
    JOIN ssot__CommunicationSubscription__dlm c 
        ON b.ssot__CommunicationSubscriptionId__c = c.ssot__Id__c
    JOIN ssot__EngagementChannelType__dlm d 
        ON b.ssot__EngagementChannelTypeId__c = d.ssot__Id__c
    -- WHERE a.ssot__ContactPointValueText__c = '***' /*Email Filter*/
) sub
WHERE rn = 1
ORDER BY LastModifiedDate DESC 

このクエリでデータが正しく取得できない場合は、DLO のマッピングが正しくありません。以下のマッピングを置き換えることで正しく動きます。マッピングを削除はできませんが、置き換えは可能です。

上記クエリで取得できない人用の簡略化したクエリ

マッピングの置き換えまではしたくない人は、以下を利用してください。

SELECT ContactPointValue, Status, LastModifiedDate, SourceType, SourceName
FROM (
    SELECT
        a.ssot__ContactPointValueText__c AS ContactPointValue,
        a.ssot__ConsentStatus__c AS Status,
        a.ssot__LastModifiedDate__c + interval '9 hour' AS LastModifiedDate,
        a.ssot__ConsentCapturedSourceType__c AS SourceType,
        a.ssot__ConsentCapturedSourceName__c AS SourceName
    FROM ssot__CommunicationSubscriptionConsent__dlm a
    JOIN ssot__CommunicationSubscriptionChannelType__dlm b 
        ON a.ssot__CommunicationSubscriptionChannelTypeId__c = b.ssot__Id__c
    -- WHERE a.ssot__ContactPointValueText__c = '***' /*Email Filter*/
) sub
ORDER BY LastModifiedDate DESC 
LIMIT 10

同意レコードのないメールアドレスを確認する

Marketing Cloud Next の運用開始前に、現在 Contact Point Email にメールアドレスがあるにも関わらず、まだ同意レコードができていないメールアドレスを取得することができます。 

SELECT
    e.ContactPointValue,
    s.SubscriptionChannelTypeId
FROM (
    SELECT
        LOWER(TRIM(ssot__EmailAddress__c)) AS EmailKey,
        MIN(ssot__EmailAddress__c) AS ContactPointValue
    FROM ssot__ContactPointEmail__dlm
    WHERE ssot__EmailAddress__c IS NOT NULL
      AND TRIM(ssot__EmailAddress__c) <> ''
    GROUP BY
        LOWER(TRIM(ssot__EmailAddress__c))
) e
CROSS JOIN (
    SELECT DISTINCT
        ssot__Id__c AS SubscriptionChannelTypeId
    FROM ssot__CommunicationSubscriptionChannelType__dlm
) s
LEFT JOIN ssot__CommunicationSubscriptionConsent__dlm a
    ON LOWER(TRIM(a.ssot__ContactPointValueText__c)) = e.EmailKey
   AND a.ssot__CommunicationSubscriptionChannelTypeId__c
       = s.SubscriptionChannelTypeId
WHERE a.ssot__Id__c IS NULL
ORDER BY
    e.ContactPointValue,
    s.SubscriptionChannelTypeId
LIMIT 100

以下のようなメールアドレスはインポートすることができません。

• @ がない：abc999.gmail.com

• @ が複数：abc999@@gmail.com

• @ より前が空：@gmail.com

• ドメインが空：abc999@

• ローカル部の先頭がピリオド：.abc999@gmail.com

• ローカル部の末尾がピリオド：abc999.@gmail.com

• ピリオドが連続：abc..999@gmail.com

• ドメインの先頭・末尾がピリオド：abc999@.gmail.com

• ドメイン内でピリオドが連続：abc999@dd..com

• ドメイン名の先頭・末尾がハイフン：abc999@-gmail.com

• 半角スペースを含む：abc 999@gmail.com

• 前後に空白がある： abc999@gmail.com

• 改行・タブなどの制御文字を含む：abc999↵@gmail.com

• 全角の ＠ を使用：abc999＠gmail.com

• ドメイン部分がない：abc999@localhost

• 使用できない記号を含む：abc999,abc@gmail.com

• ローカル部が長すぎる：@より前が 64 文字超

• アドレス全体が長すぎる：全体が 254 文字超

個人 ID からセグメントを確認する

個人の氏名などから参加しているセグメントを確認します。
※ Unified Individual - Latest DMO の API 名の修正が必要です。

SELECT
    a.Id__c AS UnifiedId,
    c.ssot__ExternalRecordId__c AS Id,
    c.ssot__FirstName__c AS FirstName,
    c.ssot__LastName__c AS LastName,
	d.ssot__EmailAddress__c AS CurrentEmail,
    b.ssot__Name__c AS SegmentName,
    a.Delta_Type__c AS DeltaType,
    a.Timestamp__c + interval '9 hour' AS PublishDateTime
FROM Individual_Unified_SM_1755567709613__dlm a /* ← 自分の Unified Individual - Latest DMO の API 名に変更する */
JOIN ssot__MarketSegment__dlm b
    ON a.Segment_Id__c = SUBSTRING(b.ssot__Id__c, 1, 15)
JOIN UnifiedIndividual__dlm c /* ← 自分の Unified Individual DMO の API 名に変更する */
    ON a.Id__c = c.ssot__Id__c
LEFT JOIN ssot__ContactPointEmail__dlm d
    ON c.ssot__ExternalRecordId__c = d.ssot__PartyId__c
-- WHERE b.ssot__Name__c = '***' /*Segment Name Filter*/
-- WHERE c.ssot__ExternalRecordId__c = '***' /*Individual ID Filter*/
-- WHERE c.ssot__FirstName__c = '***' AND c.ssot__LastName__c = '***' /*Full Name Filter*/
ORDER BY a.Timestamp__c DESC, a.Delta_Type__c DESC
LIMIT 10

Identity Match の連携状況を確認する

システムにより、Identity Match が適用されたものが取得できます。

SELECT
    ssot__DataSourceObjectId__c AS DataSounceName,
    ssot__RecordId__c AS RecordId,
    ssot__MatchingRecordId__c AS MatchingRecordId,
	ssot__IdentityMatchType__c AS IdentityMatchType,
    ssot__CreatedDate__c + interval '9 hour' AS CreatedDate,
    ssot__IdentityMatchWeight__c AS IdentityMatchWeight,
    ssot__IsAMatch__c AS IdentityMatchFlag
FROM
    ssot__IdentityMatch__dlm
ORDER BY
    ssot__IdentityMatchWeight__c DESC,
    ssot__CreatedDate__c DESC
LIMIT 10

Identity Match については、以下をご確認ください。

Individual DMO への取り込み状況を確認する

Salesforce CRM でレコードを作成したり、何か編集を加えたものが、データストリーム経由で、Individual DMO まで取り込まれたかを確認します。

SELECT 
    a.ssot__Id__c AS Id,
    a.ssot__FirstName__c AS FirstName,
    a.ssot__LastName__c AS LastName,
	b.ssot__EmailAddress__c AS Email,
    a.ssot__DataSourceObjectId__c AS DataSource,
    /*a.ssot__IsAnonymous__c AS IsAnonymous,*/
    a.ssot__LastModifiedDate__c + interval '9 hour' AS LastModifiedDate,
    a.ssot__CreatedDate__c + interval '9 hour' AS CreatedDate
FROM ssot__Individual__dlm a
LEFT JOIN (
    SELECT ssot__PartyId__c,
           ssot__EmailAddress__c,
           ROW_NUMBER() OVER (PARTITION BY ssot__PartyId__c ORDER BY ssot__LastModifiedDate__c DESC) AS rn
    FROM ssot__ContactPointEmail__dlm
) b ON a.ssot__Id__c = b.ssot__PartyId__c
   AND b.rn = 1
-- WHERE a.ssot__Id__c = '***'
-- WHERE a.ssot__IsAnonymous__c = '1'
ORDER BY a.ssot__LastModifiedDate__c DESC
LIMIT 10

今回は以上です。

次の記事はこちら

前回の記事はこちら

私の note のトップページはこちら