how to automatically send email to contact who did not open or clicked email in marketing cloud?

Question

For our monthly newsletters, we get a new list every month.

• Original send - We have 20 different newsletter every month.

• Resend - Each newsletter also need to go out to contacts who did not open or clicked on the send email.

Problem: Current process is very manual because it depend on creating sql for each send after the deployment that joins audience data extension with open and click data view to pull contacts who did not open or clicked.

Question: Is there any way to completely automate the process there is no need to manually write sql with job id for resent?

I am thinking about journey builder engagement spit as I don't think there is another way to do this in marketing cloud. But in engagement spilt, we can only do either open or click (not together). Can anyone suggest if there is any way to automate the process without journey builder? I think creating journey for each newsletter 20 times and new versions every monthly will be a lot of work.

Answer 1

Perhaps you can set a common value in each sending data extension -- like a field named Campaign with a default value of Newsletter.

Then configure send-logging and add a sentDate and Campaign field.

The sentDate column should default to today's date.

Then you could have a query that pulls an audience from the send log where there are no opens or clicks for sends in the previous day:

select
sl.subscriberkey
, sl.emailaddress
, j.emailName
from YourSendLog sl
inner join _job j on j.jobid = sl.jobid
where sl.campaign = 'newsletter'
and sl.sentDate > convert(date, getDate()-11)
and sl.sentDate <= convert(date, getDate()-10)
and not exist (
  select o.*
  from _Open o
  where o.jobid = sl.jobid 
  and o.subscriberkey = sl.subscriberkey
)
and not exist (
  select c.*
  from _Click c
  where c.jobid = sl.jobid 
  and c.subscriberkey = sl.subscriberkey
)

Answer 2

You said an engagement Split is not possible for Open and Click together. Why don't you just perform them after each other.

That's a solution to this problem ;)