Illogical results when message feedback and email tracking datasets (AJO OOTB) are joined using query service | Community
Skip to main content
G
gajendrahs1
March 26, 2024
Solved

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

  • March 26, 2024
  • 2 replies
  • 724 views

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

    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 @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#profile-based-queries

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

     

    2 replies

    _Manoj_Kumar_
    Community Advisor
    _Manoj_Kumar_Community AdvisorAccepted solution
    Community Advisor
    April 2, 2024

    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#profile-based-queries

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

     

         Manoj     Find me on LinkedIn
      J
      JyotiVa1
      New Member
      October 25, 2024

      Hi @_manoj_kumar_ 

      Quick question - how long is email interaction data stored in AJO example, sent, delivered, clicked, opened.

      Is this is a standard of 90 days, so if a member has flown through a particular journey and has received the email?

      I am asking you for 2 things - Journey data vs Email interaction data for any given profile that has been sent an email.

      Thanks

      Terms & ConditionsAccessibility statement

      Loading footer...