Getting Journey Optimizer Reporting in AEP Query Service | Community
Skip to main content
E
edgar_herrera
Level 2
March 17, 2025
Question

Getting Journey Optimizer Reporting in AEP Query Service

  • March 17, 2025
  • 1 reply
  • 468 views

Hello.

 

I have been trying to get the metrics that are obtained by Journeys reporting in Journey Optimizer, such as number of messages sent per messaging node, number of messages bounced per messaging node, etc, however, I get values that, in most cases, are close to what the reporting shows, but not exact. Next SQL statements tries to get number of messages opens per date, per Journey Version ID, per Journey Action ID, per profile (BP):

 

SELECT DISTINCT CAST(tracking.timestamp AS DATE) AS date, tracking._experience.customerJourneyManagement.messageExecution.journeyVersionID AS journeyVersionID, tracking._experience.customerJourneyManagement.messageExecution.journeyActionID AS journeyActionID, tracking.identityMap['bp'] [0] ['id'] AS profile, COUNT(tracking._id) AS opens FROM ajo_email_tracking_experience_event_dataset tracking WHERE tracking._experience.customerJourneyManagement.messageInteraction.interactionType = 'open' AND tracking._experience.customerJourneyManagement.messageExecution.journeyVersionID IS NOT NULL AND tracking._experience.customerJourneyManagement.messageExecution.journeyVersionID = '83c30c4c-af80-4bdd-b0de-054b0f00d4a8' AND tracking._experience.customerJourneyManagement.messageExecution.journeyActionID IS NOT NULL AND tracking.identityMap['bp'] [0] ['id'] IS NOT NULL GROUP BY CAST(tracking.timestamp AS DATE), tracking._experience.customerJourneyManagement.messageExecution.journeyVersionID, tracking._experience.customerJourneyManagement.messageExecution.journeyActionID, tracking.identityMap['bp'] [0] ['id'] ORDER BY date, journeyVersionID, journeyActionID, profile

 

 

Once I execute the SQL statement, inside a SUM function to get the total value, I get this:

 

In the meantime, this is what I get in Journey Optimizer:

 

I consulted with Adobe about this topic and they gave me an example SQL statement, from which I concluded that, every time an event related to a Journey or Campaign (Journey Steps Events, AJO Email Tracking Experience Event Dataset, AJO Message Feedback Event Dataset) occurs, a record associated to a Journey Optimizer entity (AJO Entity Dataset) is generated. Next SQL statement is the one that Adobe gave me:

 

SELECT date_format (timestamp, 'y-MM-dd') as date, count(ev._id) as counting FROM journey_step_events ev LEFT JOIN ajo_entity_dataset en ON ev._experience.decisioning.propositions[0].scopeDetails.correlationID = en._id WHERE ev._experience.journeyOrchestration.stepEvents.instanceType = 'unitary' AND ev._experience.journeyOrchestration.stepEvents.nodeType = 'start' AND en._experience.customerJourneyManagement.entities.journey.journeyVersionId = 'JOURNEY-VERSION-ID' AND timestamp > '2024-06-01' GROUP BY date ORDER BY date

 

 

I modify the SQL statement keeping in mind Adobe SQL statement:

SELECT DISTINCT CAST(tracking.timestamp AS DATE) AS date, tracking._experience.customerJourneyManagement.messageExecution.journeyVersionID AS journeyVersionID, tracking._experience.customerJourneyManagement.messageExecution.journeyActionID AS journeyActionID, tracking.identityMap['bp'] [0] ['id'] AS profile, COUNT(tracking._id) AS opens FROM ajo_email_tracking_experience_event_dataset tracking LEFT JOIN ajo_entity_dataset entity ON tracking._experience.decisioning.propositions[0].scopeDetails.correlationID = entity._id AND tracking._experience.customerJourneyManagement.messageExecution.journeyVersionID = entity._experience.customerJourneyManagement.entities.journey.journeyVersionId WHERE tracking._experience.customerJourneyManagement.messageInteraction.interactionType = 'open' AND tracking._experience.customerJourneyManagement.messageExecution.journeyVersionID IS NOT NULL AND tracking._experience.customerJourneyManagement.messageExecution.journeyVersionID = '83c30c4c-af80-4bdd-b0de-054b0f00d4a8' AND tracking._experience.customerJourneyManagement.messageExecution.journeyVersionID IN ( SELECT DISTINCT entity._experience.customerJourneyManagement.entities.journey.journeyVersionId FROM ajo_entity_dataset entity ) AND tracking._experience.customerJourneyManagement.messageExecution.journeyActionID IS NOT NULL AND tracking.identityMap['bp'] [0] ['id'] IS NOT NULL GROUP BY CAST(tracking.timestamp AS DATE), tracking._experience.customerJourneyManagement.messageExecution.journeyVersionID, tracking._experience.customerJourneyManagement.messageExecution.journeyActionID, tracking.identityMap['bp'] [0] ['id'] ORDER BY date, journeyVersionID, journeyActionID, profile

 

I got same values:

 

Strangest thing about all this is that, in some Journeys, SQL statement brings values ​​that match with Journey Optimizer.

 

Do you know what I'm doing wrong? Is there a filter in that SQL statement I'm missing?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

1 reply

GigiCotruta
GigiCotruta
Level 4
March 18, 2025

Hi, @edgar_herrera,

 

I think that you are getting more results than AJO´s reporting because you have to deduplicate you identity. 

Meanwhile it is true that the GROUP BY clause creates distinct groups based on the combined values of date, journeyVersionID, and journeyActionID, along with the profile (identity), an identity can absolutely appear in multiple rows with different combinations of:

  • date (a different day)
  • journeyVersionID (a different journey)
  • journeyActionID (a different action within a journey)

Hence, you might have your identity repeated, therefor it will count an email open more than once. 

 

 

  • Imagine a customer (with a specific profile ID) who:
    • Opens an email from "Journey A, Action 1" on Monday.
    • Opens another email from "Journey B, Action 2" on Tuesday.
  • In this case, the customer's profile ID would appear twice in the query results, once for each distinct combination of date, journey, and action.

I don´t know if it helps, but, with our queries we had that issue so now, we have to deduplicate each time we want to compare reports.

 

    Terms & ConditionsAccessibility statement

    Loading footer...