HI Everyone
Observing Illogical results when message feedback and email tracking datasets (AJO OOTB) are joined using query service
We're getting results where records are bounced/error/ignored in message feedback dataset and having interaction type as open/click in email tracking dataset which is not possible.
We're using Journey version ID and Journey Action ID to join both datasets.
Below is the query Used and screen shot of the result
select distinct ET._id ETID, MF._experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus EmailDeliveryStatus,
ET._experience.customerJourneyManagement.messageInteraction.interactionType interactionType,
case when ET._experience.customerJourneyManagement.messageInteraction.interactionType = 'open'
and MF._experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus ='sent'
then ET.timestamp
else Null
end as OpenTime,
case when ET._experience.customerJourneyManagement.messageInteraction.interactionType = 'click'
and MF._experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus ='sent'
then ET.timestamp
else Null
end as ClickTime,
case when ET._experience.customerJourneyManagement.messageInteraction.interactionType = 'unsubscribe'
and MF._experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus ='sent'
then ET.timestamp
else Null
end as UnsubTime
from ajo_message_feedback_event_dataset MF inner join ajo_email_tracking_experience_event_dataset ET
on MF._experience.customerJourneyManagement.messageExecution.journeyActionID = ET._experience.customerJourneyManagement.messageExecution.journeyActionID
and ET._experience.customerJourneyManagement.messageExecution.journeyVersionID = MF._experience.customerJourneyManagement.messageExecution.journeyVersionID
Is there any other attribute which should be used apart from Journey version ID and Journey Action ID to see the logical results
Thanks & Regards,
Gajendra
+91-9008933285
Solved! Go to Solution.
Topics help categorize Community content and increase your ability to discover relevant content.
Hello @gajendrahs1
Journey Version and journey Action ID will be same for all profiles for a given journey. So you can't use those 2 attributes to get a delivery/tracking status for a specific profile.
You will have to use journey_step_events in the mix to use the profileId to get a profile based output.
Here are examples of how to use journey_step_events with profile and events datasets.
Hello @gajendrahs1
Journey Version and journey Action ID will be same for all profiles for a given journey. So you can't use those 2 attributes to get a delivery/tracking status for a specific profile.
You will have to use journey_step_events in the mix to use the profileId to get a profile based output.
Here are examples of how to use journey_step_events with profile and events datasets.
Views
Likes
Replies
Views
Likes
Replies