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

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

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

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

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 

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

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

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

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

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