I want to exclude people who have not interacted with the particular triggered email in XX days. In order to arrive at the XX days I need to make an analysis about which number best fits the XX days. Example: 14 days, 7 Days etc. Does anyone know about how I can arrive at these days? What is the best way to analyse ideal number of days (in Marketing cloud )?

The idea I have now is, Say I sent 10,000 emails on April 1st. Then I check how many people opened it each day for 30 days(April 1st to April 30) and then arrive at xx days.

Is there any other way to analyse in order to get the ideal days??

I know that I can use Data views to get the numbers but I want to know how can I analyse the number of days?

1 Answer 1


So you have two tasks (dimensions here).

First find a query that finds the best "cut off" date (XX days).

You can group by the number of days on the _open data view, to find the number of users that have interacted with the email on the date:

select count(SubscriberID), EventDate from _open group by EventDate Where JobID = 123... and TriggeredSendDefinitionObjectID = "..." 

or if you are interested in the number of days from today:

select count(SubscriberID), DATEDIFF(day, EventDate, GETDATE()) from _open group by DATEDIFF(day, EventDate, GETDATE())

and then you can just by analyzing the results find the optimal cutoff date...

Then use it to select all SubscriberIDs after certain date :)

You must log in to answer this question.

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