文档Journey OptimizerJourney Optimizer 指南

查询示例 query-examples

最近更新： 2026年9月13日

创建对象：

  • Experienced
  • 管理员
  • 开发人员

在此页面上：​浏览适用于Journey Optimizer系统数据集的现成的SQL查询示例，以便分析电子邮件和推送跟踪、消息反馈、历程步骤和决策数据以进行报告和故障排除。

style
shade-box

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

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

另请参阅几个用于查询历程步骤事件🔗的常用示例。

选择正确的数据集 choose-the-correct-dataset

运行查询之前，请确认哪个数据集与要在历程中分析的操作类型匹配。

  1. 若要检查本机Journey Optimizer渠道操作（如sent或bounce状态）的消息投放反馈，请使用消息反馈事件数据集。
  2. 要检查电子邮件交互事件（如打开数和点击数），请使用电子邮件跟踪体验事件数据集。
  3. 要验证Journey Optimizer是否执行了自定义操作，并检查其执行状态、延迟和错误详细信息，请使用历程步骤事件数据集。
NOTE
成功的自定义操作HTTP调用仅确认调用已完成。 它不会确认外部系统是否传递了消息。 要确认下游投放，请检查外部系统的日志或报表。 了解如何对您的实时历程执行进行故障排除。

如果查询返回“未为数据集配置表” table-not-provisioned

此消息不一定意味着数据集无法预配。 在联系Adobe支持之前，请检查以下各项：

  1. 在数据集工作区中，启用​显示系统数据集。 默认情况下，系统生成的数据集处于隐藏状态。 了解如何访问数据集。
  2. 确认查询中使用的确切表名称与沙盒的数据集工作区中显示的表名称匹配。
  3. 确认历程操作类型与您正在查询的数据集相匹配。 请参阅选择正确的数据集。
  4. 对于使用批量摄取的数据集（如消息反馈事件数据集），最多允许两小时以使数据可用。
  5. 对于自定义操作，请查询历程步骤事件数据集，而不是需要外部投放的消息反馈事件记录。

如果数据集应包含数据并且表仍不可用，请在联系Adobe支持部门之前收集沙盒名称、数据集名称、查询ID和时间戳。

电子邮件跟踪体验事件数据集 email-tracking-experience-event-dataset

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

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

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

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

select
    _experience.customerJourneyManagement.messageInteraction.interactionType AS interactionType,
    count(1) eventCount
from ajo_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 ajo_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;

消息反馈事件数据集 message-feedback-event-dataset

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

AJO消息反馈事件数据集存储Adobe Journey Optimizer生成的消息投放反馈。 它支持跨消息渠道（包括电子邮件、SMS/RCS/MMS和直邮）的投放反馈分析。 反馈事件可用于报告和受众创建用例。

相关架构是AJO消息反馈事件架构。

NOTE
此数据集使用批次摄取。 查询此数据集或将其用于报表用途时，预计数据延迟最长为2小时。

有关字段、字段路径、数据类型和描述的完整列表，请参阅Adobe Journey Optimizer架构引用。

NOTE
不能保证在每个消息反馈事件中填充特定于渠道的上下文字段。 字段可用性可能取决于渠道、提供商反馈有效负荷、事件类型和投放阶段。 将消息执行标识符、反馈状态、故障详细信息、时间戳和身份信息用作主要关联字段。

对测试和非测试执行进行分类 classify-test-executions

填充字段后，使用isTestExecution字段将测试执行与非测试执行区分开来。

在构建查询之前，请使用Adobe Journey Optimizer架构引用确认AJO消息反馈事件架构的当前字段路径、数据类型和描述。

按如下方式解释填充的值：

值
解释
true
消息是测试执行的一部分。
false
消息不是测试执行的一部分。
NULL或缺失
没有为该字段记录任何值。 除非已验证特定于渠道和时间的映射，否则请将此视为未知。

不要自动将NULL转换为false，并且不要假定每个null值都表示生产执行。 如果报表实施已验证null值表示特定渠道或历史时段的非测试记录，请在下游报表视图中应用该映射，并明确记录规则。

某些历史记录或特定于渠道的记录可能不会填充每个消息上下文字段。 因此，您应该按渠道测试字段可用性并保留null，而不是将它们视为空字符串或推断值。

仅在确认Adobe Journey Optimizer架构引用中的isTestExecution路径后运行此查询：

SELECT
  _experience.customerJourneyManagement.messageProfile.isTestExecution AS isTestExecution,
  _experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus AS feedbackStatus,
  COUNT(*) AS eventCount
FROM ajo_message_feedback_event_dataset
GROUP BY
  _experience.customerJourneyManagement.messageProfile.isTestExecution,
  _experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus
ORDER BY
  isTestExecution,
  feedbackStatus;

此查询按测试执行指标和投放反馈状态对消息反馈记录进行分组。 结果保留null或缺少的isTestExecution值，以便可以单独审查没有记录的测试执行值的记录。

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

select
    _experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus AS feedbackStatus,
    count(1) eventCount
from ajo_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 ajo_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 ajo_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 ajo_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 ajo_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的列表：

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 ajo_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 ajo_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 ajo_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
NOTE
在某些历程中，每个投放的messageID可能不是唯一的。 如果历程将同一操作重新发送到同一配置文件，则可以重用相同的messageID。 因此，要在单个发送级别准确跟踪或归因事件，请合并journeyVersionID、journeyActionID和batchInstanceID（对于批处理历程）或identityMap字段以获得更准确的唯一性。

在ISP中断后确定隔离地址 isp-outage-query

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

SELECT
    _experience.customerJourneyManagement.emailChannelContext.address AS RecipientAddress,
    timestamp AS EventTime,
    _experience.customerJourneyManagement.messageDeliveryfeedback.messageFailure.reason AS "Invalid Recipient"
FROM ajo_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禁止显示列表中删除这些地址。 了解详情。

NOTE
在消息反馈事件数据集中引用identityMap时，请注意，它仅反映运行时使用的标识。 对于推送通知，“已发送”事件将仅依赖于链接到用于发送此通知的推送令牌的ECID，而“排除”事件可能依赖于自定义身份。 例如，如果由于未找到推送令牌而排除用户档案，则将选择在历程或操作营销活动级别使用的标识来注册此事件。 如果您需要其他命名空间（例如，自定义ID），请将这些反馈记录与配置文件相关的数据集（例如：profile_snapshot数据集）连接起来，以检索完整的身份列表。

推送跟踪体验事件数据集 push-tracking-experience-event-dataset

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

用于从Journey Optimizer中提取推送的移动跟踪体验事件的数据集。

相关架构是AJO推送跟踪体验事件架构。

查询示例：

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

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

历程步骤事件 journey-step-event

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

用于在历程中引入步骤事件的数据集。

相关架构是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划分的步骤输入计数。 由于nodeLabel对于不同的历程节点可以是相同的，因此此处包含了nodeId。

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;

此查询会使用其用户档案ID和消息反馈事件数据集，检索历程中的哪些节点（按nodeID和nodeName）与向用户档案的消息投放相关联：

select
    _experience.journeyorchestration.stepevents.nodeID, JSE._experience.journeyorchestration.stepevents.nodeName
from journey_step_events JSE
where
    _experience.journeyOrchestration.stepEvents.actionID
    in

    (
    select
        _experience.customerJourneyManagement.messageExecution.journeyActionID
    from  ajo_message_feedback_event_dataset
    where
        _experience.customerJourneyManagement.messageProfile.messageProfileID = '<PROFILE ID>'
    group by
        _experience.customerJourneyManagement.messageExecution.journeyActionID
    )

group by
    _experience.journeyorchestration.stepevents.nodeID, JSE._experience.journeyorchestration.stepevents.nodeName

另请参阅几个用于查询历程步骤事件🔗的常用示例。

了解如何对journey_step_events中丢弃的事件类型进行故障排除。

决策事件数据集 ode-decisionevents

界面中的​名称： 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_20230925_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;

次要收件人反馈事件数据集(BCC) bcc-feedback-event-dataset

界面中的​名称： AJO次要收件人反馈事件数据集（系统数据集）。 在查询服务中，数据集表的名称可能仍为ajo_bcc_feedback_event_dataset.

启用密送存档时电子邮件密送（辅助收件人）消息的数据集。

在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 ajo_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 ajo_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

实体数据集 entity-dataset

界面中的​名称： ajo entity_dataset （系统数据集）_

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

相关架构是AJO实体架构。

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

重要备注

  • 仅在发布历程或营销策划后创建消息条目。

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

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

如果您想在报表中对特定历程发送的电子邮件按照发送它们的操作进行排序。 您可以将消息反馈数据集与实体数据集连接起来。 要使用的字段为： _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 ajo_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 ajo_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
AI Knowledge Reference

This section contains structured knowledge intended to support interpretation, retrieval, and question answering related to this topic.

For complete understanding, this information should be combined with the documentation on this page. Neither source is intended to stand alone; the page describes the feature, while this section provides additional context that helps disambiguate terminology, intent, applicability, and constraints.

  • TL;DR: This page provides ready-to-use SQL query examples for Journey Optimizer system datasets so you can analyze email and push tracking, message feedback, journey step, decisioning, BCC, and entity data for reporting and troubleshooting.

Intents:

  • Choose the correct system dataset for a given analysis (message feedback versus email tracking versus journey step).
  • Query email and push interaction counts (opens, clicks).
  • Query message delivery feedback statuses (sent, bounce) and bounce categories.
  • Distinguish test executions from non-test executions using the isTestExecution field.
  • Join the Entity Dataset with feedback and tracking datasets using messageID or correlationID.
  • Troubleshoot the “Table not provisioned for dataset” message.

Glossary:

  • AJO Email Tracking Experience Event Dataset: system dataset for email tracking events; queried as ajo_email_tracking_experience_event_dataset (product-specific)
  • AJO Message Feedback Event Dataset: message delivery feedback across channels (Email, SMS/RCS/MMS, Direct Mail); queried as ajo_message_feedback_event_dataset; uses batch ingestion (product-specific)
  • AJO Push Tracking Experience Event Dataset: push interaction events; queried as ajo_push_tracking_experience_event_dataset (product-specific)
  • Journey Step Event: journey step events; queried as journey_step_events (product-specific)
  • Decisioning Event Dataset (ODE DecisionEvents): offer proposition events (product-specific)
  • Secondary Recipient Feedback Event Dataset (BCC): email BCC events when BCC archiving is enabled; table may still be named ajo_bcc_feedback_event_dataset (product-specific)
  • Entity Dataset: stores entity metadata for messages; queried as ajo_entity_dataset (product-specific)
  • isTestExecution: field distinguishing test executions (true) from non-test executions (false), with NULL or missing treated as unknown (product-specific)

Guardrails:

  • The AJO Message Feedback Event Dataset uses batch ingestion; expect a data latency of up to 2 hours (expected latency, batch ingestion — not a configurable limit) when querying it or using it for reporting.
  • If a query returns “Table not provisioned for dataset”, for batch-ingested datasets allow up to two hours for data to become available before contacting Adobe Support.
  • System datasets are hidden by default; enable Show system datasets in the Datasets workspace to query them.
  • An Entity Dataset entry for a message is created only after the journey or campaign is published, and may appear about 30 minutes after publication (observed delay).
  • Do not automatically convert a NULL or missing isTestExecution value to false, and do not assume null represents a production execution.
  • A successful custom action HTTP call confirms only that the call completed, not that the external system delivered a message.

Terminology:

  • Canonical name: Journey Step Event — table: journey_step_events
  • Do not confuse: “AJO Message Feedback Event Dataset” (delivery feedback such as sent or bounce) ≠ “AJO Email Tracking Experience Event Dataset” (interaction events such as opens and clicks) ≠ “Journey Step Event” (custom action execution status and errors)
  • Do not confuse: “isTestExecution = NULL” (unknown) ≠ “isTestExecution = false” (non-test execution)

FAQ:

  • Q: Which dataset do I query for opens and clicks? — The AJO Email Tracking Experience Event Dataset.
  • Q: Which dataset do I query for sent and bounce delivery status? — The AJO Message Feedback Event Dataset.
  • Q: Why does my query return “Table not provisioned for dataset”? — It does not necessarily mean provisioning failed; enable Show system datasets, confirm the table name matches the Datasets workspace, and for batch datasets allow up to two hours for data to become available.
  • Q: How do I separate test executions from non-test executions? — Use the isTestExecution field; treat NULL or missing values as unknown rather than converting them to false.
  • Q: How do I enrich feedback records with campaign, journey, and message metadata? — Join the Entity Dataset using messageID or _experience.decisioning.propositions.scopeDetails.correlationID.
recommendation-more-help
journey-optimizer-help