Level 6

Solved

Order of sequence of nodes in AJO Journey from datasets

Forum|Forum|1 year ago
April 4, 2024
1 reply
952 views

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.

Journey Optimizer

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

Best answer by Manoj_Kumar

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_Kumar

Accepted solution

Community Advisor

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

A

akwanklAuthor

Level 6

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

Community Advisor

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;

Manoj | https://themartech.pro

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded