数据集用例

在本页中，您将找到Adobe Journey Optimizer数据集列表和相关用例：

电子邮件跟踪体验事件数据集
消息反馈事件数据集
推送跟踪体验事件数据集
历程步骤事件
决策事件数据集
同意服务数据集
密送反馈事件数据集
实体数据集

要查看每个架构的字段和属性的完整列表，请参阅 Journey Optimizer模式字典.

电子邮件跟踪体验事件数据集

界面中的名称：CJM电子邮件跟踪体验事件数据集

用于从Journey Optimizer中摄取电子邮件跟踪体验事件的系统数据集。

相关架构是CJM电子邮件跟踪体验事件架构。

此查询显示给定消息的不同电子邮件交互（打开数、点击数）计数：

select
    _experience.customerJourneyManagement.messageInteraction.interactionType AS interactionType,
    count(1) eventCount
from cjm_email_tracking_experience_event_dataset
where
     _experience.customerJourneyManagement.messageExecution.messageExecutionID IN ('UMA-30647505')
group by
    _experience.customerJourneyManagement.messageInteraction.interactionType

此查询显示按给定历程的消息划分不同电子邮件交互（打开数、点击数）的计数：

select
    _experience.customerJourneyManagement.messageExecution.messageExecutionID AS messageExecutionID,
    _experience.customerJourneyManagement.messageInteraction.interactionType AS interactionType,
    count(1) eventCount
from cjm_email_tracking_experience_event_dataset
where
     _experience.customerJourneyManagement.messageExecution.journeyVersionID IN ('0e86ac62-c315-48cc-ab4f-3f8b741ae667')
group by
    _experience.customerJourneyManagement.messageExecution.messageExecutionID,
    _experience.customerJourneyManagement.messageInteraction.interactionType
order by
    _experience.customerJourneyManagement.messageExecution.messageExecutionID,
    _experience.customerJourneyManagement.messageInteraction.interactionType
limit 100;

消息反馈事件数据集

界面中的名称：CJM消息反馈事件数据集

用于从Journey Optimizer中摄取电子邮件和推送应用程序反馈事件的数据集。

相关架构为CJM消息反馈事件架构。

此查询显示给定消息的不同电子邮件反馈状态（发送、退回等）的计数：

select
    _experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus AS feedbackStatus,
    count(1) eventCount
from cjm_message_feedback_event_dataset
where
     _experience.customerJourneyManagement.messageExecution.messageExecutionID IN ('UMA-30647505')
group by
    _experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus;

此查询显示给定历程中按消息划分的不同电子邮件反馈状态（发送、退回等）计数：

select
    _experience.customerJourneyManagement.messageExecution.messageExecutionID AS messageExecutionID,
    _experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus AS feedbackStatus,
    count(1) eventCount
from cjm_message_feedback_event_dataset
where
     _experience.customerJourneyManagement.messageExecution.journeyVersionID IN ('0e86ac62-c315-48cc-ab4f-3f8b741ae667')
group by
    _experience.customerJourneyManagement.messageExecution.messageExecutionID,
    _experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus
order by
    _experience.customerJourneyManagement.messageExecution.messageExecutionID,
    _experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus
limit 100;

在聚合级别，域级别报表（按顶级域排序）：域名、已发送消息、退回

SELECT split_part(_experience.customerJourneyManagement.emailChannelContext.address, '@', 2) AS recipientDomain, SUM( CASE WHEN _experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus = 'sent' THEN 1 ELSE 0 END)AS sentCount , SUM( CASE WHEN _experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus = 'bounce' THEN 1 ELSE 0 END )AS bounceCount FROM cjm_message_feedback_event_dataset WHERE _experience.customerjourneymanagement.messageprofile.channel._id = 'https://ns.adobe.com/xdm/channels/email' GROUP BY recipientDomain ORDER BY sentCount DESC;

每天发送电子邮件：

SELECT date_trunc('day', TIMESTAMP) AS rolluptimestamp, SUM( CASE WHEN _experience.customerjourneymanagement.messagedeliveryfeedback.feedbackstatus = 'sent' THEN 1 ELSE 0 END) AS deliveredcount FROM cjm_message_feedback_event_dataset WHERE _experience.customerjourneymanagement.messageprofile.channel._id = 'https://ns.adobe.com/xdm/channels/email' GROUP BY date_trunc('day', TIMESTAMP) ORDER BY rolluptimestamp ASC;

查找特定电子邮件ID是否收到了电子邮件，如果没有，则错误、退回类别、代码：

SELECT _experience.customerjourneymanagement.messagedeliveryfeedback.feedbackstatus AS status, _experience.customerjourneymanagement.messagedeliveryfeedback.messagefailure.reason AS failurereason, _experience.customerjourneymanagement.messagedeliveryfeedback.messagefailure.type AS bouncetype FROM cjm_message_feedback_event_dataset WHERE _experience.customerjourneymanagement.messageprofile.channel._id = 'https://ns.adobe.com/xdm/channels/email' AND _experience.customerjourneymanagement.emailchannelcontext.address = 'user@domain.com' AND TIMESTAMP >= now() - INTERVAL '7' DAY ORDER BY status ASC

查找在过去x小时/天内发生特定错误、退回类别或代码的所有电子邮件ID的列表，或查找与特定消息投放相关联的电子邮件ID:

SELECT _experience.customerjourneymanagement.emailchannelcontext.address AS emailid, _experience.customerjourneymanagement.messagedeliveryfeedback.feedbackstatus AS status, _experience.customerjourneymanagement.messagedeliveryfeedback.messagefailure.reason AS failurereason, _experience.customerjourneymanagement.messagedeliveryfeedback.messagefailure.type AS bouncetype FROM cjm_message_feedback_event_dataset WHERE _experience.customerjourneymanagement.messageprofile.channel._id = 'https://ns.adobe.com/xdm/channels/email' AND _experience.customerjourneymanagement.messagedeliveryfeedback.feedbackstatus != 'sent' AND TIMESTAMP >= now() - INTERVAL '10' HOUR AND _experience.customerjourneymanagement.messageexecution.messageexecutionid = 'BMA-45237824' ORDER BY emailid

聚合级别的硬跳出率：

select hardBounceCount, case when sentCount > 0 then(hardBounceCount/sentCount)*100.0 else 0 end as hardBounceRate from ( select SUM( CASE WHEN _experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus = 'bounce' AND _experience.customerJourneyManagement.messageDeliveryfeedback.messageFailure.type = 'Hard' THEN 1 ELSE 0 END)AS hardBounceCount , SUM( CASE WHEN _experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus = 'sent' THEN 1 ELSE 0 END )AS sentCount from cjm_message_feedback_event_dataset WHERE _experience.customerjourneymanagement.messageprofile.channel._id = 'https://ns.adobe.com/xdm/channels/email' )

按退件代码分组的永久错误：

SELECT _experience.customerjourneymanagement.messagedeliveryfeedback.messagefailure.reason AS failurereason, COUNT(*) AS hardbouncecount FROM cjm_message_feedback_event_dataset WHERE _experience.customerjourneymanagement.messagedeliveryfeedback.feedbackstatus = 'bounce' AND _experience.customerjourneymanagement.messagedeliveryfeedback.messagefailure.type = 'Hard' AND _experience.customerjourneymanagement.messageprofile.channel._id = 'https://ns.adobe.com/xdm/channels/email' GROUP BY failurereason

确定ISP中断后的隔离地址

如果互联网服务提供商(ISP)中断，您需要在某个时间范围内将错误地标记为特定域的退回（隔离）电子邮件地址。 要获取这些地址，请使用以下查询：

SELECT
    _experience.customerJourneyManagement.emailChannelContext.address AS RecipientAddress,
    timestamp AS EventTime,
    _experience.customerJourneyManagement.messageDeliveryfeedback.messageFailure.reason AS "Invalid Recipient"
FROM cjm_message_feedback_event_dataset
WHERE
    eventtype = 'message.feedback' AND
    DATE(timestamp) BETWEEN '<start-date-time>' AND '<end-date-time>' AND
    _experience.customerjourneymanagement.messagedeliveryfeedback.feedbackstatus = 'bounce' AND
    _experience.customerJourneyManagement.emailChannelContext.address ILIKE '%domain.com%'
ORDER BY timestamp DESC;

其中日期格式为：YYYY-MM-DD HH:MM:SS。

识别后，从Journey Optimizer抑制列表中删除这些地址。 了解详情。

推送跟踪体验事件数据集

界面中的名称：CJM推送跟踪体验事件数据集

用于为从Journey Optimizer推送引入移动跟踪体验事件的数据集。

相关架构为CJM推送跟踪体验事件架构。

查询示例：

select _experience.customerJourneyManagement.pushChannelContext.platform, sum(pushNotificationTracking.customAction.value)  from cjm_push_tracking_experience_event_dataset
group by _experience.customerJourneyManagement.pushChannelContext.platform

select  _experience.customerJourneyManagement.pushChannelContext.platform, SUM (_experience.customerJourneyManagement.messageInteraction.offers.offerCount) from cjm_email_tracking_experience_event_dataset
  group by _experience.customerJourneyManagement.pushChannelContext.platform

历程步骤事件

内部名称：历程步骤事件（系统数据集）

用于在历程中摄取步骤事件的数据集。

相关架构是历程步骤事件架构，用于Journey Orchestration。

此查询按给定历程的操作标签显示操作成功计数的细分：

select
    _experience.journeyOrchestration.stepEvents.actionName AS actionLabel,
    count(1) actionSuccessCount
from journey_step_events
where
     _experience.journeyOrchestration.stepEvents.journeyVersionID IN ('0e86ac62-c315-48cc-ab4f-3f8b741ae667')
     AND _experience.journeyOrchestration.stepEvents.actionID IS NOT NULL
     AND _experience.journeyOrchestration.stepEvents.actionType IS NOT NULL
     AND _experience.journeyOrchestration.stepEvents.actionExecutionErrorCode IS NULL
group by
    _experience.journeyOrchestration.stepEvents.actionName;

此查询显示按nodeId和nodeLabel为给定历程输入的步骤计数的细分。 nodeId包含在此处，因为nodeLabel对于不同的历程节点可以是相同的。

select
    _experience.journeyOrchestration.stepEvents.nodeID AS nodeID,
    _experience.journeyOrchestration.stepEvents.nodeName AS nodeLabel,
    count(1) stepEnteredCount
from journey_step_events
where
     _experience.journeyOrchestration.stepEvents.journeyVersionID IN ('0e86ac62-c315-48cc-ab4f-3f8b741ae667')
     AND _experience.journeyOrchestration.stepEvents.journeyNodeProcessed = TRUE
     AND _experience.journeyOrchestration.stepEvents.eventID IS DISTINCT FROM 'createInstance'
group by
    _experience.journeyOrchestration.stepEvents.nodeID,
    _experience.journeyOrchestration.stepEvents.nodeName;

决策事件数据集

界面中的名称：ODE DecisionEvents（系统数据集）

用于摄取数据的数据集向用户提供建议。

相关架构为ODE DecisionEvents。

此查询显示前一天返回的所有选件：

SELECT date_format(Decision.Timestamp, 'MM/dd/yyyy') as Date
,HOUR(Decision.timestamp) as Hour
,COUNT(*)  as Count
FROM ode_decisionevents_b699fa78_efec_41b1_99fa_78efecc1b1ef_decision AS Decision
WHERE date_format(Decision.timestamp, 'MM/dd/yyyy') = date_format(CURRENT_DATE, 'MM/dd/yyyy') and Decision._experience.decisioning.propositionDetails.activity[0].id = 'xcore:offer-activity:13ab41890a335ad6'
GROUP BY date_format(Decision.Timestamp, 'MM/dd/yyyy')
,HOUR(Decision.timestamp)
ORDER BY 1, 2 DESC;

此查询显示特定活动/决策的最近30天内建议的选件及其关联的选件优先级的次数。

select proposedOffers.id,proposedOffers.name, po._experience.decisioning.ranking.priority, count(proposedOffers.id) as ProposedCount from (
select explode(propositionexplode.selections) AS proposedOffers from
(select explode(_experience.decisioning.propositionDetails) AS propositionexplode,timestamp FROM ode_decisionevents_itca_decisioning_20200925_235340_379  where date_format(timestamp, 'MM/dd/yyyy') >= date_format(DATE_ADD(CURRENT_DATE, -30), 'MM/dd/yyyy') and _experience.decisioning.propositionDetails.activity[0].id = 'xcore:offer-activity:12ae6f35a055c6f0')) a, decision_object_repository_personalized_offers po where proposedOffers.id LIKE 'xcore:personalized-offer%' and po._id=proposedOffers.id
group by proposedOffers.id, proposedOffers.name, po._experience.decisioning.ranking.priority;

界面中的名称：CJM同意服务数据集（系统数据集）

Journey Optimizer同意服务的数据集。

相关架构是CJM同意服务架构。

查询以列出同意接收电子邮件的电子邮件ID:

select key as email FROM (
  select explode(value) FROM (
  select explode(consents.idSpecific)
  from cjm_consent_service_dataset
 )
)
where value.marketing.email.val == 'y'

查询以返回将电子邮件ID作为输入的电子邮件ID的同意值：

select value.marketing.email.val FROM (
  select explode(value) FROM (
  select explode(consents.idSpecific)
  from cjm_consent_service_dataset
 )

密送反馈事件数据集

界面中的名称：AJO密送反馈事件数据集（系统数据集）

用于存储密送消息信息的数据集。

在2天内查询所有密件抄送消息（针对特定营销活动）：

SELECT bcc.*
FROM ajo_bcc_feedback_event_dataset AS bcc
WHERE
    bcc._experience.customerJourneyManagement.messageExecution.messageExecutionID = '<message-execution-id>' AND
    bcc.timestamp >= now() - INTERVAL '2' day;

包含反馈数据集的查询，以显示未收到（所有退回和抑制）以及具有特定消息的密送条目的用户：

SELECT
    distinct bcc._experience.customerJourneyManagement.secondaryRecipientDetail.originalRecipientAddress AS OriginalRecipientAddress
FROM ajo_bcc_feedback_event_dataset  AS bcc
WHERE
    bcc.timestamp > now() - INTERVAL '2' DAY AND     bcc._experience.customerJourneyManagement.messageExecution.messageExecutionID  = '<message-execution-id>' AND      bcc._experience.customerJourneyManagement.secondaryRecipientDetail.originalRecipientAddress != '' AND
    (
            bcc._experience.customerJourneyManagement.secondaryRecipientDetail.originalRecipientAddress NOT IN (
        SELECT distinct mfe._experience.customerJourneyManagement.emailChannelContext.address
        FROM cjm_message_feedback_event_dataset AS mfe
        WHERE
            mfe.timestamp > now() - INTERVAL '2' DAY AND
            mfe._experience.customerJourneyManagement.messageExecution.messageExecutionID  = '<message-execution-id>' AND
            mfe._experience.customerjourneymanagement.messagedeliveryfeedback.feedbackstatus = 'sent'
        )
    OR     bcc._experience.customerJourneyManagement.secondaryRecipientDetail.originalRecipientAddress IN (
        SELECT distinct mfe._experience.customerJourneyManagement.emailChannelContext.address
        FROM cjm_message_feedback_event_dataset AS mfe
        WHERE
        mfe.timestamp > now() - INTERVAL '2' DAY AND
            mfe._experience.customerJourneyManagement.messageExecution.messageExecutionID  = '<message-execution-id>' AND
            mfe._experience.customerJourneyManagement.messageDeliveryfeedback.messageFailure.category = 'async' AND
            mfe._experience.customerjourneymanagement.messagedeliveryfeedback.feedbackstatus

实体数据集

界面中的名称：ajo_entity_dataset（系统数据集）

用于存储发送给最终用户的消息的实体元数据的数据集。

相关架构是AJO实体架构。

利用此数据集，可访问营销人员定义的元数据，当导出Journey Optimizer数据集以用于外部工具中的报表可视化时，利用这些元数据可更好地分析报表。 可使用messageID属性实现此目的，该属性有助于拼合各种数据集（如消息反馈数据集和体验事件跟踪数据集），以获取从在用户档案级别发送到跟踪的消息投放的详细信息。

重要说明

  • 消息的条目仅在历程或营销活动发布后创建。

  • 营销活动/历程发布30分钟后，您可能会看到该条目。

注意

出于将来的兼容性原因，目前实体数据集中每个消息发布有两个条目。 这不会影响您根据需要在数据集中使用连接查询来获取所需信息的能力。

如果要在报表中根据发送特定历程的操作对由特定历程发送的电子邮件进行排序。 您可以将消息反馈数据集与实体数据集结合使用。 要使用的字段包括： _experience.decisioning.propositions.scopeDetails.correlationID 和 _id field in entity dataset.

以下查询可帮助您获取给定营销活动的关联消息模板：

SELECT
  AE._experience.customerJourneyManagement.entities.channelDetails.template
from
  ajo_entity_dataset AE
    WHERE AE._experience.customerJourneyManagement.entities.campaign.campaignVersionID = 'd7a01136-b113-4ef2-8f59-b6001f7eef6e'

以下查询有助于获取与所有反馈事件关联的历程详细信息和电子邮件主题：

SELECT
  AE._experience.customerJourneyManagement.entities.journey.journeyActionName,
  AE._experience.customerJourneyManagement.entities.journey.journeyActionID,
  AE._experience.customerJourneyManagement.entities.journey.journeyVersionID,
  AE._experience.customerJourneyManagement.entities.channelDetails.email.subject
from
  ajo_entity_dataset AE
  INNER JOIN cjm_message_feedback_event_dataset MF ON AE._experience.customerJourneyManagement.entities.channelDetails.messageID = MF._experience.customerJourneyManagement.messageExecution.messageID
WHERE
  AE._experience.customerJourneyManagement.entities.channelDetails.channel._id = 'https://ns.adobe.com/xdm/channels/email'
  AND MF._experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus = 'sent'
  AND AE._experience.customerJourneyManagement.entities.journey.journeyVersionID IS NOT NULL

您可以拼合历程步骤事件、消息反馈和跟踪数据集，以获取特定用户档案的统计资料：

SELECT
  AE._experience.customerJourneyManagement.entities.journey.journeyActionName,
  AE._experience.customerJourneyManagement.entities.journey.journeyActionID,
  AE._experience.customerJourneyManagement.entities.journey.journeyVersionID,
  AE._experience.customerJourneyManagement.entities.channelDetails.email.subject,
    JE._EXPERIENCE.JOURNEYORCHESTRATION.STEPEVENTS.PROFILEID,
    JE._EXPERIENCE.JOURNEYORCHESTRATION.STEPEVENTS.NODENAME
from
  ajo_entity_dataset AE
  INNER JOIN cjm_message_feedback_event_dataset MF
    ON AE._experience.customerJourneyManagement.entities.channelDetails.messageID = MF._experience.customerJourneyManagement.messageExecution.messageID
    INNER JOIN journey_step_events JE
    ON AE._experience.customerJourneyManagement.entities.journey.journeyActionID = JE._experience.journeyOrchestration.stepEvents.actionID
WHERE
  AE._experience.customerJourneyManagement.entities.channelDetails.channel._id = 'https://ns.adobe.com/xdm/channels/email'
  AND MF._experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus = 'sent'
  AND AE._experience.customerJourneyManagement.entities.journey.journeyVersionID IS NOT NULL

在此页面上