Documentation Journey Optimizer Journey Optimizer Guide

Query examples query-examples

Last update: September 13, 2026

CREATED FOR:

Experienced
Admin
Developer

On this page: Explore ready-to-use SQL query examples for Journey Optimizer system datasets so you can analyze email and push tracking, message feedback, journey step, and decisioning data for reporting and troubleshooting.

style

shade-box

In this page, you will find the list of Adobe Journey Optimizer datasets and related use cases:

Email Tracking Experience Event Dataset
Message Feedback Event Dataset
Push Tracking Experience Event Dataset
Journey Step Event
Decisioning Event Dataset
Secondary recipient feedback event dataset (BCC)
Entity Dataset

To view the complete list of fields and attributes for each schema, consult the Journey Optimizer schema dictionary.

See also several commonly used examples to query Journey Step Events.

Choose the correct dataset choose-the-correct-dataset

Before running a query, confirm which dataset matches the type of action you want to analyze in your journey.

To check message-delivery feedback for native Journey Optimizer channel actions (such as sent or bounce statuses), use the Message Feedback Event Dataset.
To check email interaction events such as opens and clicks, use the Email Tracking Experience Event Dataset.
To verify that Journey Optimizer executed a custom action, and to inspect its execution status, latency, and error details, use the Journey Step Event dataset.

NOTE

A successful custom action HTTP call confirms only that the call completed. It does not confirm that the external system delivered a message. To confirm downstream delivery, check the external system’s logs or reporting. Learn how to troubleshoot your live journey execution.

If a query returns “Table not provisioned for dataset” table-not-provisioned

This message does not necessarily mean the dataset failed to provision. Before contacting Adobe Support, check the following:

In the Datasets workspace, enable Show system datasets. System-generated datasets are hidden by default. Learn how to access datasets.
Confirm the exact table name used in your query matches the table name shown in the Datasets workspace for your sandbox.
Confirm that the journey action type matches the dataset you are querying. See Choose the correct dataset.
For datasets that use batch ingestion, such as the Message Feedback Event Dataset, allow up to two hours for data to become available.
For custom actions, query the Journey Step Event dataset rather than expecting a Message Feedback Event record for the external delivery.

If the dataset should contain data and the table remains unavailable, collect the sandbox name, dataset name, query ID, and timestamp before contacting Adobe Support.

Email tracking Experience event dataset email-tracking-experience-event-dataset

Name in the interface : AJO Email Tracking Experience Event Dataset

System dataset for ingesting email tracking experience events from Journey Optimizer.

The related schema is AJO Email Tracking Experience Event Schema.

This query shows the counts of different email interactions (opens, clicks) for a given message:

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

This query shows the breakdown of counts of different email interactions (opens, clicks) by message for a given journey:

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 message-feedback-event-dataset

Name in the interface: AJO Message Feedback Event Dataset

The AJO Message Feedback Event Dataset stores message delivery feedback generated by Adobe Journey Optimizer. It supports delivery-feedback analysis across message channels, including Email, SMS/RCS/MMS, and Direct Mail. Feedback events can be used for reporting and audience-creation use cases.

The related schema is AJO Message Feedback Event Schema.

NOTE

This dataset uses batch ingestion. Expect a data latency of up to 2 hours when querying this dataset or using it for reporting purposes.

For the complete list of fields, field paths, data types, and descriptions, see the Adobe Journey Optimizer Schema Reference.

NOTE

Channel-specific context fields are not guaranteed to be populated on every message-feedback event. Field availability can depend on the channel, provider feedback payload, event type, and delivery phase. Use the message execution identifiers, feedback status, failure details, timestamp, and identity information as the primary correlation fields.

Classify test and non-test executions classify-test-executions

Use the isTestExecution field to distinguish test executions from non-test executions when the field is populated.

Before building a query, use the Adobe Journey Optimizer Schema Reference to confirm the current field path, data type, and description for the AJO Message Feedback Event Schema.

Interpret populated values as follows:

Value

Interpretation

true

The message was part of a test execution.

false

The message was not part of a test execution.

NULL or missing

No value was recorded for the field. Treat this as unknown unless a channel- and time-specific mapping has been validated.

Do not automatically convert NULL to false, and do not assume that every null value represents a production execution. If a reporting implementation has validated that null values represent non-test records for a specific channel or historical period, apply that mapping in a downstream reporting view and document the rule explicitly.

Some historical or channel-specific records may not populate every message-context field. You should therefore test field availability by channel and preserve nulls rather than treating them as empty strings or inferred values.

Run this query only after confirming the isTestExecution path in the Adobe Journey Optimizer Schema Reference:

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;

This query groups message feedback records by test-execution indicator and delivery-feedback status. The result preserves null or missing isTestExecution values so that records without a recorded test-execution value can be reviewed separately.

This query shows the counts of different email feedback status (sent, bounce, etc) for a given message:

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;

This query shows the breakdown of counts of different email feedback status (sent, bounce, etc) by message for a given journey:

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;

At aggregate level, domain level report (sorted by top domains): Domain Name, Message Sent, Bounces

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;

Email sends on daily basis:

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;

Find if a particular email id received an email or not and if not, then what was the error, bounce category, code:

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

Find the list of all individual email ids which had a particular error, bounce category or code in the last x hours/days or associated with a particular message delivery:

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

Hard Bounce Rate at aggregate level:

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' )

Permanent errors grouped by bounce code:

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

In some journeys, messageID may not be unique for each individual delivery. If a journey re-sends the same action to the same profile, the same messageID can be reused. Therefore, to accurately track or attribute events at the individual send level, combine the journeyVersionID, journeyActionID, and batchInstanceID (for batch journeys) or identityMap fields for more precise uniqueness.

Identify quarantined addresses after an ISP outage isp-outage-query

In case of an Internet Service Provider (ISP) outage, you need to idenfity email addresses wrongly maked as bounces (quarantined) for specific domains, during a timeframe. To get those adresses, use the following query:

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;

where the format of dates is: YYYY-MM-DD HH:MM:SS.

Once identified, remove those addresses from Journey Optimizer suppression list. Learn more.

NOTE

When referencing the identityMap in the Message Feedback Event Dataset, please note that it only reflects the identity used at runtime. For push notifications, a ‘sent’ event would rely only on the ECID linked to the push token used to send this notification while an ‘exclusion’ event could rely on a custom identity. For instance, if a profile was excluded because no push token was found, the identity used at the journey or action campaign level will be selected to register this event. If you need additional namespaces (e.g., custom IDs), join these feedback records with a profile-related dataset (eg: profile_snapshot ones) to retrieve the full identity list.

Push tracking Experience event dataset push-tracking-experience-event-dataset

Name in the interface: AJO Push Tracking Experience Event Dataset

Dataset for ingesting mobile tracking experience events for push from Journey Optimizer.

The related schema is AJO Push Tracking Experience Event Schema.

Query example:

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-step-event

Internal name: Journey Step Events (system dataset)

Dataset for ingesting step events in the journey.

The related schema is Journey Step Event schema for Journey Orchestration.

This query shows the breakdown of action success counts by action label for a given journey:

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;

This query shows the breakdown of step entered counts by nodeId & nodeLabel for a given journey. nodeId is included here as nodeLabel can be the same for different journey nodes.

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;

This query retrieves which nodes (by nodeID and nodeName) in the journey are associated with the delivery of a message to a profile, using its profile ID and the Message Feedback Event dataset:

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

See also several commonly used examples to query Journey Step Events.

Learn how to troubleshoot discarded event types in journey_step_events.

Decisioning event dataset ode-decisionevents

Name in the interface: ODE DecisionEvents (system dataset)

Dataset for ingesting offer propositions to the users.

The related schema is ODE DecisionEvents.

This query shows all the offers returned the previous day:

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;

This query shows the number of times offers were proposed over the last 30 days of a particular activity/decision and its associated offer priority.

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;

Secondary recipient feedback event dataset (BCC) bcc-feedback-event-dataset

Name in the interface: AJO Secondary Recipient Feedback Event Dataset (system dataset). In Query Service, the dataset table may still be named ajo_bcc_feedback_event_dataset.

Dataset for email BCC (secondary recipient) messages when BCC archiving is enabled.

Query for all BCC messages within 2 days (for a particular campaign):

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;

Query with feedback dataset to show users who did not receive (all bounces and suppressions) and who have BCC entry for a particular message:

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 entity-dataset

Name in the interface: ajo_entity_dataset (system dataset)

Dataset to store entity metadata for messages sent to the end user.

The related schema is AJO Entity Schema.

This dataset gives you access to marketer defined metadata which allows you to get better reporting insights when Journey Optimizer datasets are exported out for reporting visualization in external tools. This is achieved using the messageID attribute which helps stitch various datasets such as Message Feedback Dataset and Experience Event Tracking Datasets to get details of a message delivery from sending to tracking at a profile level.

Important notes

An entry for a message is created only after journey or campaign is published.
You may see the entry 30 minutes after the publication of the campaign/journey.

NOTE

For the time being, there are two entries for each message publication in the entity dataset for future compatibility reasons. This does not impact your ability to use join queries as needed across datasets to fetch the desired information.

If you want to sort, in your reports, the emails sent by a specific journey according to the action that sent them. you can join the Message Feedback dataset with the Entity dataset. The fields to use are: _experience.decisioning.propositions.scopeDetails.correlationID and _id field in entity dataset.

The following query helps you get the associated message template for a given campaign:

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

The following query helps get the Journey Details and email subject associated with all feedback events:

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

You can stitch journey step events, Message Feedback and tracking datasets to get the stats for a particular profile:

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