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
- DocCobrancaDE_NotSent > with the filter SendDate = Empty
- 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.