文件 Journey Optimizer Journey Optimizer 指南

查詢範例 query-examples

最後更新： 2026年9月22日

建立對象：

Experienced
管理員
開發人員

在此頁面上：探索Journey Optimizer系統資料集的現成可用SQL查詢範例，以便分析電子郵件和推播追蹤、訊息回饋、歷程步驟和決定資料，以進行報告和疑難排解。

style

shade-box

在此頁面中，您會找到Adobe Journey Optimizer資料集和相關使用案例的清單：

電子郵件追蹤體驗事件資料集
訊息回饋事件資料集
推播追蹤體驗事件資料集
歷程步驟事件
決策事件資料集
次要收件者回饋事件資料集（密件副本）
實體資料集

若要檢視每個結構描述的欄位與屬性完整清單，請參閱 Journey Optimizer 結構描述字典。

另請參閱幾個查詢歷程步驟事件🔗的常用範例。

選擇正確的資料集 choose-the-correct-dataset

在執行查詢之前，請確認哪個資料集符合您要在歷程中分析的動作型別。

若要檢查原生Journey Optimizer頻道動作（例如sent或bounce狀態）的訊息傳遞回饋，請使用訊息回饋事件資料集。
若要檢查電子郵件互動事件（例如開啟和點按），請使用電子郵件追蹤體驗事件資料集。
若要驗證Journey Optimizer是否執行自訂動作，以及檢查其執行狀態、延遲和錯誤詳細資料，請使用歷程步驟事件資料集。

NOTE

成功的自訂動作HTTP呼叫只會確認呼叫已完成。它不會確認外部系統已傳遞訊息。若要確認下游傳送，請檢查外部系統的記錄或報告。瞭解如何疑難排解您的即時歷程執行。

如果查詢傳回「資料集未布建表格」 table-not-provisioned

此訊息不一定表示資料集無法布建。在聯絡Adobe支援之前，請檢查下列專案：

在資料集工作區中，啟用顯示系統資料集。預設會隱藏系統產生的資料集。瞭解如何存取資料集。
確認查詢中使用的確切表格名稱符合沙箱的資料集工作區中顯示的表格名稱。
確認歷程動作型別符合您正在查詢的資料集。請參閱選擇正確的資料集。
針對使用批次擷取的資料集（例如訊息回饋事件資料集），最多可允許兩小時讓資料變為可用。
對於自訂動作，請查詢歷程步驟事件資料集，而不是期望外部傳遞的訊息回饋事件記錄。

如果資料集應包含資料，但表格仍無法使用，請先收集沙箱名稱、資料集名稱、查詢ID和時間戳記，再聯絡Adobe支援。

電子郵件追蹤體驗事件資料集 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 Message Feedback Event結構描述。

NOTE

在Adobe Journey Optimizer中，sent是用於成功訊息傳遞結果的值；訊息回饋事件資料集中不使用delivered。
此資料集使用批次擷取，因此資料最多可能延遲2小時。
我們並不保證會在每個事件填入管道特定的內容欄位。使用訊息執行識別碼、回饋狀態、失敗詳細資料、時間戳記和身分資訊作為主要相互關聯欄位。

如需欄位、欄位路徑、資料型別和說明的完整清單，請參閱Adobe Journey Optimizer結構描述參考。

將測試和非測試執行分類 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

如果網際網路服務提供者(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，該ECID是用來傳送此通知，而「排除」事件則會依賴自訂身分。例如，如果因為找不到推播權杖而排除設定檔，則會選取在歷程或動作行銷活動層級使用的身分來註冊此事件。如果您需要其他名稱空間（例如自訂ID），請將這些意見記錄與設定檔相關的資料集（例如：設定檔快照資料集）聯結，以擷取完整的身分清單。

推播追蹤體驗事件資料集 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劃分的步驟輸入計數。 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;

此查詢會使用其設定檔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-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