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.

  • What does determine a newsletter on your side. Are 20 newsletter totally different topics. Are they just country specific? Can these 20 newsletters be condensed into a lower number of newsletters if you make the newsletter more dynamic? Be sure what you want. How i understand Did not open or clicked => No Open AND no Click, describe very precise. Feb 23, 2021 at 14:42
  • Are the sendable data extensions the same for every send (overwrite) or do you create 20 new DEs for each send every month? Feb 23, 2021 at 15:06

2 Answers 2


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:

, 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
  • Hi Adam thank you for your response. Just making sure if I am following your proposed solution. In my case there are 20 newsletter so email name is always new each month for all 20 campaigns. Are you saying to hardcode email name within audience DE for each newsletter? I will need to update email name each month in query, no? which is still manual step? In my case, resend for each newsletter go after 10 days or original send (if it helps in context?).
    – kl2
    Feb 23, 2021 at 3:07
  • No, I'm not suggesting hardcoding the email name. Simply add a field to all of your newsletter DEs named Campaign and set its default value to newsletter. Shouldn't have to update the query every time. The Campaign value recorded in the Send Log should give you what you need (in addition to the date range in the query). Feb 23, 2021 at 12:46
  • Updated my query to look for newsletter sends exactly 10 days ago. Feb 23, 2021 at 12:48
  • Hi Adam - First of all, thank you for your response. I just wanted to make sure if I explained my use-case correctly. (1)I have 20 different newsletters so if we are using sl.campaign = 'newsletter' in the query, how I will distinguish non openers or non clickers for each newsletter? Each newsletter has its own resend. (2). In the query above, I see you are looking for non openers AND non clickers while, my use case is non openers OR non clickers. I believe, I will need to wrap it in parenthesis separated by an OR
    – kl2
    Feb 23, 2021 at 12:59
  • Updated my query to include the emailName from the _Job data view. I don't get your "non-clickers or non-openers" logic. By that logic, if they didn't open but did click, they'd get selected for the resend. Feb 23, 2021 at 13:19

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

enter image description here

That's a solution to this problem ;)

  • Hi Johannes, thank you for sharing the journey approach. Yes, I thought of using engagement split like yous but by "together" I meant the logic (no opens OR no clicks), NOT no opens AND no clicks, I hope that make sense? Now that I'm thinking, in that case, I may have to check no clicks prior to no open, or may be not even check no opens. I guess that raises a question, simply engagement split for checking clicks, will NO brach going to include no opens by default? Since you can't click email without opening, so if someone didn't click it means either they didn't open or opened & didn't click
    – kl2
    Feb 23, 2021 at 11:25
  • Also - As I mentioned, I have 20 separate newsletters where audience list is new each month and email content is new as well (new email name each month). Do you think journey did each newsletter is the right approach and not too much work to maintain since we will have to stop old version reach month, start a new version. It means 20 journeys and starting 20 new versions every month.
    – kl2
    Feb 23, 2021 at 11:31
  • You can click in an email without opening it. Easy case for many clients that disable certain open tracking pixels. But the link click will be tracked. Be aware of this. Feb 23, 2021 at 12:05
  • Got it, yes that makes sense. Do you mind guiding me in the right direction with the use case I mentioned above, please? (1) Creating logic, either no open OR no click, (2) if journey is a good alternative solution for monthly newsletters
    – kl2
    Feb 23, 2021 at 12:10
  • Hi Johannes - I just wanted to follow up if you got a chance to review my comments / question. Any help / guidance would be appreciated. Thanks!
    – kl2
    Feb 24, 2021 at 2:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .