Illogical results when message feedback and email tracking datasets (AJO OOTB) are joined using query service

[gajendrahs1]

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

[Manoj_Kumar_]

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.

https://experienceleague.adobe.com/en/docs/journey-optimizer/using/reporting/reports/query-examples#...

https://experienceleague.adobe.com/en/docs/journey-optimizer/using/reporting/reports/query-examples#...