1

In creating an audience for a Journey, I'm pulling everyone who already received one of our Welcome emails so they skip that step in the Journey. To do this, we've written the query below and it works for the most part.

SELECT 
SubscriberKey as EMAIL_ADDRESS_, 
   CASE WHEN EMAILID = '14146' then 'TRUE'
        WHEN EMAILID = '3661' then 'TRUE'
          ELSE 'FALSE' END
 as WelcomeSent
FROM _SENT s
JOIN _JOB j on S.JobID = J.JobID
WHERE 
SubscriberKey in (SELECT EMAIL_ADDRESS_ FROM [WELCOME])

The issue arises when someone received multiple emails (for example, a Welcome and an Abandoned Cart email) -- because a record appears multiple times in the _SENT table, the LAST email received will populate the value in WelcomeSent.

This means we get a few False-positives. That is, people who received the WELCOME but the data results shows as FALSE

How do we modify the code such that only the WELCOME email is considered in the query?

What complicates this further is that we have no idea if the WELCOME email was sent before or after other emails received by the subscriber.

4

I think you need to filter the query to only pull those email ids instead of creating a case around it.

For example:

SELECT 
SubscriberKey as EMAIL_ADDRESS_, 
'TRUE' as WelcomeSent
FROM _SENT s
JOIN _JOB j on S.JobID = J.JobID
WHERE 
SubscriberKey in (SELECT EMAIL_ADDRESS_ FROM [WELCOME])
AND j.EMAILID IN ('14146','3661')

This will return only those from the SENT Dataview that are in the WELCOME DE and that have the EmailID of 14146 or 3661 inside of the JOB dataview. Which I believe is what your goal was.

2
  • and a default value of FALSE should take care of the rest -- THANK YOU! – George Rosedale Sep 13 '18 at 17:31
  • My only comment is that I had to move the EMAILID in statement to before the SELECT EMAIL_ADDRESS_ FROM WELCOME statement – George Rosedale Sep 13 '18 at 17:40

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.