How to use SQL to join profile identities to Email Tracking Experience Event Data? | Community
Skip to main content
A
ap95
Level 3
July 31, 2025
Solved

How to use SQL to join profile identities to Email Tracking Experience Event Data?

  • July 31, 2025
  • 3 replies
  • 547 views

Hi all,

 

I have an interesting use case of joining Journey and email name/id data with profile identifiers in order to make an export to HTTP destination.

With Email Tracking Dataset I could see the Journey and email details with ID Map, however we are not using ID map for identities, so was wondering how we can join the primary (customer ID) and secondary ID (email) along with Journey & email sent info. 

 

Looking for your suggestions!

Thanks

Best answer by Mayank_Gandhi

@ap95 Identity Map is recommended for event datasets: it allows flexible association of multiple identities (email, customerID, ECID, etc.) per event row. However, if your organization doesn't use identityMap, but instead stores identities as flat fields (e.g. customerID, email), you must make sure those fields are designated as identities in your schemas, each tied to a known Identity Namespace.

Once you have all the data joined in CJA or via AEP query service, you can build segments/audiences, then push these to an HTTP destination by using Adobe Real-Time CDP Destinations with the selected identity mapping.

https://experienceleague.adobe.com/en/docs/experience-platform/destinations/ui/activate/activate-edge-profile-lookup

3 replies

DavidKangni
Community Advisor
DavidKangniCommunity Advisor
Community Advisor
July 31, 2025

Hi @ap95 

 

Here an example of query

 

SELECT AE._experience.customerJourneyManagement.entities.journey.journeyActionName, AE._experience.customerJourneyManagement.entities.journey.journeyActionID, AE._experience.customerJourneyManagement.entities.journey.journeyVersionID, AE._experience.customerJourneyManagement.entities.channelDetails.email.subject, JE._EXPERIENCE.JOURNEYORCHESTRATION.STEPEVENTS.PROFILEID, JE._EXPERIENCE.JOURNEYORCHESTRATION.STEPEVENTS.NODENAME from ajo_entity_dataset AE INNER JOIN ajo_message_feedback_event_dataset MF ON AE._experience.customerJourneyManagement.entities.channelDetails.messageID = MF._experience.customerJourneyManagement.messageExecution.messageID INNER JOIN journey_step_events JE ON AE._experience.customerJourneyManagement.entities.journey.journeyActionID = JE._experience.journeyOrchestration.stepEvents.actionID WHERE AE._experience.customerJourneyManagement.entities.channelDetails.channel._id = 'https://ns.adobe.com/xdm/channels/email' AND MF._experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus = 'sent' AND AE._experience.customerJourneyManagement.entities.journey.journeyVersionID IS NOT NULL

 

Thanks,

David

David Kangni
    gengaipandi03
    gengaipandi03
    Level 4
    August 4, 2025

    Normally message id will be different from before and after execution, will that be same for all the tables? 

    AE._experience.customerJourneyManagement.entities.channelDetails.messageID = MF._experience.customerJourneyManagement.messageExecution.messageID

    F
    FilipeFreitas
    Level 3
    August 1, 2025

    Hi @ap95 ,

     

    May I kindly ask you to provide some additional details, and perhaps share what your final report should look like? My apologies, but it is not clear to me what you are trying to achieve.

     

    Best Regards,

    Filipe Freitas

    gengaipandi03
    gengaipandi03
    Level 4
    August 4, 2025

    Based on the question, we need to combine two different table to see the journey name. This is common issue for everyone, instead, they would have place that attribute in tracking and feedback status table itself will solve unwanted query in the system. 

    Mayank_Gandhi
    Adobe Employee
    Mayank_GandhiAdobe EmployeeAccepted solution
    Adobe Employee
    August 4, 2025

    @ap95 Identity Map is recommended for event datasets: it allows flexible association of multiple identities (email, customerID, ECID, etc.) per event row. However, if your organization doesn't use identityMap, but instead stores identities as flat fields (e.g. customerID, email), you must make sure those fields are designated as identities in your schemas, each tied to a known Identity Namespace.

    Once you have all the data joined in CJA or via AEP query service, you can build segments/audiences, then push these to an HTTP destination by using Adobe Real-Time CDP Destinations with the selected identity mapping.

    https://experienceleague.adobe.com/en/docs/experience-platform/destinations/ui/activate/activate-edge-profile-lookup

      Terms & ConditionsAccessibility statement

      Loading footer...