0

Every week we send out a mail on tuesdays. This mail is named so it always contains this: "TOBERESENT".

Every saturday an automation sends out a mail that is an altered version of the tuesday mail. This mail is named so it always contains this: "THERESENDMAIL".

To keep this as automated as possible, I'd like queries, that on saturdays:

1) Populates a data extension (using overwrite) with everyone, who was sent the "TOBERESENT" mail. As a weekly mail is named like this, the mail to be found must not be more than a week old

2) Populates a data extension (using overwrite) with everyone, who opened the "TOBERESENT" mail. Again, the mail to be found must not be more than a week old

3) Subtracts 2) from 1) and this way populating (using overwrite) a third data extension with everyone, who did not open the tuesday mail.

Then when all queries are run, the automation will send the saturday mail to the third data extension, only targeting the ones who did not open the tuesday mail.

I'm quite sure I'll be able to set up every part of the automation, but I don't know much about queries. Can anyone help me write the SQL for these 3 queries?

Thank you

2

You will use the system data views to build your SQL.

SQL 1: Captures those who received an email ---> Populates DE: ResultingDE_fromSQL1 SELECT s.subscriberKey, j.jobid FROM [_job] j JOIN [_Sent] s ON s.jobid = j.jobid AND s.EventDate > dateadd(d,-7,getdate()) AND j.EmailName like '%TobeResent%'

SQL 2: Captures those who have opened the email ---> Populates DE: ResultingDE_fromSQL2 SELECT s.subscriberKey FROM [ResultingDE_fromSQL1] j JOIN [_Open] o ON s.jobid = o.jobid AND s.subscriberkey = o.subscriberKey WHERE s.EventDate > dateadd(d,-7,getdate()) AND j.EmailName like '%TobeResent%'

SQL 3: Identifies the Non-Openers ---> Populates DE: ResultingDE_fromSQL3 SELECT s.subscriberKey FROM [ResultingDE_fromSQL1] WHERE s.subscriberKey NOT IN (SELECT subscriberKey FROM [ResultingDE_fromSQL2])

Hope this helps!

  • Thank you! Had to make small changes, but with these small changes it works like a charm, and I see it as my own free version of what Journey Builder offers. I'm grateful for your help and making this function not only saves me 30 minutes of work every week but also is a perfect introduction to SQL. – FelixSanchez Aug 20 '18 at 6:07
  • SELECT s.subscriberKey, j.jobid, sub.EmailAddress FROM [_job] j JOIN [_Sent] s ON s.jobid = j.jobid AND s.EventDate > dateadd(d,-7,getdate()) AND j.EmailName like '%TOBERESENTDK%' LEFT JOIN _Subscribers sub ON s.SubscriberKey = sub.SubscriberKey SELECT s.subscriberKey FROM [AutomatedRetargetStep1of3DK] s JOIN [_Open] o ON s.jobid = o.jobid AND s.subscriberkey = o.subscriberKey SELECT s.subscriberKey, s.EmailAddress FROM [AutomatedRetargetStep1of3DK] s WHERE s.subscriberKey NOT IN (SELECT subscriberKey FROM [AutomatedRetargetStep2of3DK]) – FelixSanchez Aug 20 '18 at 6:07

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.