Order of sequence of nodes in AJO Journey from datasets

[akwankl]

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.

[_Manoj_Kumar_ · Accepted solution]

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;

[akwankl]

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,

[_Manoj_Kumar_]

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;