Skip to main content
akwankl
Level 6
Apr 4, 2024 10:29 PM UTC
Solved

Order of sequence of nodes in AJO Journey from datasets

Hi there,

 

Is there a way to query the order of sequence of nodes/events/actions in a journey purely by looking at the datasets? For example, I am looking at journey_step_events, I am able to define certain parent-child relationship by using the parentnode/parenttransitionID etc, but it still doesn't indicate the chronological order of steps happening, is there a way to do this?

 

Thanks.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Manoj_Kumar
Manoj_KumarAccepted solution
Community Advisor
Apr 4, 2024 11:03 PM UTC

Hello @akwankl  

 

Refer to the query given on this page: https://experienceleague.adobe.com/en/docs/journey-optimizer/using/reporting/reports/query-examples

 

This query returns all the step events and service events for the given profile and journey for the specified time in chronological order.

SELECT timestamp, _experience.journeyOrchestration.stepEvents.journeyVersionID, _experience.journeyOrchestration.stepEvents.profileID, _experience.journeyOrchestration.stepEvents.nodeName, _experience.journeyOrchestration.stepEvents.journeyNodeProcessed, _experience.journeyOrchestration.serviceType, to_json(_experience.journeyOrchestration.profile), to_json(_experience.journeyOrchestration.serviceEvents) FROM journey_step_events WHERE _experience.journeyOrchestration.stepEvents.journeyVersionID='<journeyVersionID>' AND DATE(timestamp) > (now() - interval '<last x hours>' hour) AND ( _experience.journeyOrchestration.stepEvents.profileID='<profileID>' OR _experience.journeyOrchestration.profile.ID='<profileID>' ); ORDER BY timestamp;

 

Manoj  | https://themartech.pro
View in thread

3 replies

Manoj_Kumar
Community Advisor
Manoj_KumarCommunity AdvisorAccepted solution
Community Advisor
Apr 4, 2024 11:03 PM UTC

Hello @akwankl  

 

Refer to the query given on this page: https://experienceleague.adobe.com/en/docs/journey-optimizer/using/reporting/reports/query-examples

 

This query returns all the step events and service events for the given profile and journey for the specified time in chronological order.

SELECT timestamp, _experience.journeyOrchestration.stepEvents.journeyVersionID, _experience.journeyOrchestration.stepEvents.profileID, _experience.journeyOrchestration.stepEvents.nodeName, _experience.journeyOrchestration.stepEvents.journeyNodeProcessed, _experience.journeyOrchestration.serviceType, to_json(_experience.journeyOrchestration.profile), to_json(_experience.journeyOrchestration.serviceEvents) FROM journey_step_events WHERE _experience.journeyOrchestration.stepEvents.journeyVersionID='<journeyVersionID>' AND DATE(timestamp) > (now() - interval '<last x hours>' hour) AND ( _experience.journeyOrchestration.stepEvents.profileID='<profileID>' OR _experience.journeyOrchestration.profile.ID='<profileID>' ); ORDER BY timestamp;

 

Translate
Manoj  | https://themartech.pro
    akwanklAuthor
    Level 6
    Apr 4, 2024 11:12 PM UTC

    Hey @_manoj_kumar_,

     

    Thanks for the response. I looked into the query seems like it's order by chronological order at a profile level. I am hoping there is a similar one but at a Journey Level (not by person), an output that can be used to produce something similar to the AJO journey report, and help create  a waterfall report.

     

    For example:

    Sequence | Event | Count of Profiles

    1 | Start | 800

    2 | Condition A | 600

    3 | Condition B | 400

    4 | Sent notifications | 10

     

    Thanks,

    Translate
    Manoj_Kumar
    Community Advisor
    Manoj_KumarCommunity Advisor
    Community Advisor
    Apr 4, 2024 11:54 PM UTC

    Hello @akwankl 

    When we talk about waterfall counts, the starting node always has higher counts then the last node.

    So you can sort the output by counts to get the desired output.

     

    SELECT count(_id)as counts, _experience.journeyOrchestration.stepEvents.nodeName FROM journey_step_events WHERE _experience.journeyOrchestration.stepEvents.journeyVersionID='JOURNEY_VERION_HERE' AND DATE(timestamp) > (now() - interval '240' hour) GROUP BY _experience.journeyOrchestration.stepEvents.nodeName order BY counts DESC;

     

     

    Translate
    Manoj  | https://themartech.pro
      Terms & ConditionsAccessibility statement

      Loading footer...