0

I am a novice when it comes to SQL. I'm trying to identify all records in Marketing Cloud that are not part of an active journey (ie. they have never been a member of one or have exited one in the past). I see there is a Data View for Journeys, which is probably useful here.

Would it possible to query the Journey table with logic something like: show all Leads & Contacts not in any Journey or member status of Journey is not Active? If so, what would that need to look like? Would I need to list out all Journey Names or could I group all names with a Where clause like "contains "Journey" in the name"?

1

Without some additional logging steps in your journey, the best you can do is use the Journey data views to determine who's been sent an email from your journey and then select those that aren't in that result.

select
  sub.SubscriberKey
, sub.Emailaddress
from ent._Subscribers sub
where not exists (

    select
      j.JourneyName
    , j.VersionNumber
    , ja.ActivityName EmailName
    , s.EventDate SendTime
    , s.subscriberid
    , s.SubscriberKey
    , s.JobID
    , s.ListID
    , s.BatchID
    from _Journey j
    inner join _JourneyActivity ja on ja.VersionID = j.VersionID
    inner join _Sent s on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
    where sub.SubscriberID = s.SubscriberID
    /* selects all subscribers who were sent an email from a journey */

)

NOTE: All activity system data views only hold data for 6 months, so if the Journey send is outside of that window, it won't show. A workaround would be to not use the system data views and build some logging into your journey (see link above).

Reference:

| improve this answer | |
  • I shared your answer with MC Support to see if there is any other way to identify records outside that 6 month period you mentioned, and they said a) MC's DBs store the data only for past 60 days, not 6 months, and b) it's possible to ask MC Support to provide a DE populated with all unused contacts. – Greg Beazley Apr 3 at 7:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.