Send a Second email to subscribers that didnt open 1st email sent via automation studio

Question

I need to create an automation to:

1- Send and Email (from a created email template) to subscribers and informations from a Data Extension DocCobranca.

2- After 2 days has passed I need to make a check and send a second email ONLY for the subscribers who didn't open the first email.

This automation is scheduled to run every weekday. And I am going to receive new data that need to be sent every day (new subscribers)

The Mkt Clud org don't have Journey Builder. Only Automation Studio.

I have created the DocCobranca DE with the fields SendDate and OpenDate and JobId.

And have created 2 filtered DE

1. DocCobrancaDE_NotSent > with the filter SendDate = Empty
2. DocCobrancaDE_SentNotOpen > with the filter SendDate Not Empty + OpenDate = Empty

But I am having trouble to find out how can I fill those fields in the data extension once the original email have been sent.

How can I get the JobID form the sent email / the SendDate and OpenDate and fill the filterd data extension to be able to send the sencond email only who those who didn't open the fisrt one.

I have tried to use this SQL query activity to fill those atributes but no success:

s.SubscriberKey,
s.JobID,
s.BatchID,
convert(char(19),s.EventDate,20) as SendDate
from [_sent] s
left join [_open] o
on s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1
where
s.JobID = JobID
and o.SubscriberID is NULL```

I am having trouble to get the JobId from the original email sent and fill it in the query. 

Can some one guide / help me in this Mkt Cloud issue. 

Answer 1

I would first create a field called JobID inside your DocCobranca DE (gonna call this DC). Then use an AMPscript Upsert inside the email to insert the JobID and 'SentDate' into the DE.

For example:

UPSERT("DocCobranca",1,"SubscriberKey",_SubscriberKey,"JobID",jobid,"SentDate",NOW())

Now if you are going to be including multiple iterations of each subscriber, you will need a second or third primary key in your DE and will need to adjust the above accordingly.

This will get you the JobID and the exact Date/Time it was sent. From there you utilize your query above (can remove the _Sent DV stuff) to fill in the Open times.

Something like:

SELECT   dc.SubscriberKey
       , op.EventDate as OpenDate
FROM [DocCobranca] dc
INNER JOIN [_Open] op
ON dc.JobID = op.JobID AND dc.SubscriberKey = op.SubscriberKey

You then utilize your filters, etc. as sendable and suppression lists, etc.

---

Now for your current situation, your only bet is to go from Job DV using the 'EmailName' and possibly the DeliveredTime (can convert to just date via SQL) to collect the appropriate JobID for your send.

For example: (assuming that the email was named: 'SkullHeadFace' and it was sent on 7/15/2019)

SELECT j.JobID
FROM [_Job]
WHERE EmailName = 'SkullHeadFace
AND CONVERT(varchar,'07/15/2019', 101) = CONVERT(varchar, DeliveredTime, 101)

This will get you the appropriate JobID to then use your query in your question to add in the sent/open info.