【第257回】 Marketing Cloud Next ： エンゲージメントデータの確認方法

SELECT
    IndividualId__c,
    ContactPointEmailAddress__c,
    EventDateTime__c,
    EventType__c,
    SendStatus__c,
    NotSentReason__c,
    BounceType__c,
    BounceReason__c,
    SendName__c,
    ResolvedUrl__c
FROM
    MessagingEventsEmailV2_EmailEng_6C0EBACA__dll
ORDER BY
    EventDateTime__c DESC
LIMIT 10

SELECT *
FROM 
    MessagingEventsEmailV2_EmailEng_6C0EBACA__dll
ORDER BY 
    EventDateTime__c DESC
LIMIT 
    10

SELECT 
    IndividualId__c, 
    ContactPointEmailAddress__c, 
    EventDateTime__c, 
    EventType__c, 
    SendStatus__c, 
    NotSentReason__c, 
    BounceType__c, 
    BounceReason__c, 
    ContentName__c, 
    ResolvedUrl__c
FROM 
    MessagingEventsEmailV2_EmailEng_6C0EBACA__dll
WHERE 
    IndividualId__c = 'test-send-individual-id'
ORDER BY 
    EventDateTime__c DESC
LIMIT 
    10

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