Query against subscribers in a journey send

Question

I had a journey that I stopped because we are updating our email strategy on the journey.

Out of 4MM subscribers, only about 1MM received the first message. I want to segment the 4MM into 4 groups and keep the 1MM that already received email 1 from receiving the same email.

Struggling to identify where to pull that group from. Here's my query:

    SELECT 
usr.contact_key,
usr.email,
usr.platform,
usr.platform_user_id,
usr.first_name,
usr.last_name,
usr.created_at,
usr.last_login_at 

FROM ENT.[Users] usr 
INNER JOIN _Sent s ON usr.contact_key = s.subscriberkey
INNER JOIN _job j ON s.jobid = j.jobid

WHERE j.EmailName = 'NGB Email 3 - 82f87a6eb77e4687ba09fa088ec05abe'

I wanted to use JobId as the link between _send and job but there's not a good way for me to identify that without created a separate data extension.

Answer 1

Hi please try the below approach instead querying users you can use subscribers and find-out the email send for the user

    SELECT 
    a.EmailAddress as email
,   a.SubscriberKey as External_Id
,   b.EventDate  
,   c.EmailName
FROM
    Ent._Subscribers a  INNER JOIN _Sent b  ON a.SubscriberKey=b.SubscriberKey 
                        INNER JOIN  _Job c  ON b.JobID=c.JobID
WHERE
    c.EmailName IN ('NGB Email 3 - 82f87a6eb77e4687ba09fa088ec05abe')