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

[ap95]

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

[DavidKangni]

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

[gengaipandi03]

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

[FilipeFreitas]

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]

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 · Accepted solution]

@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-edg...