[SalesForce] Query Retention Email based on Data Extension in ExactTarget

I need to create an automation in ExactTarget which would send Emails to inactive consumers "x" (e.g. 100 days) days after subscribers make it to the Data Extension. Inactive means: subscribers haven't opened eMails and/or haven't clicked no links in these eMails. Is there a workaround to make it without a query in ExactTarget?

If there isn't, where can I find a sample SQL query to use in this case? Any ideas?

Thank you

Best Answer

A query may be the most reliable method in this scenario but you should be able to achieve similar results with "Measures" that you use within data filters. Below link has some further information on measures where this data is available:

  • Not Clicked - The information related to email message from your account that were not clicked.
  • Not Opened - The information related to email messages from your account that were not opened.

http://help.exacttarget.com/en/documentation/exacttarget/subscribers/measures/ http://help.exacttarget.com/en/documentation/exacttarget/subscribers/data_filters_for_exacttarget_marketing_cloud/

If a query is something you'd like to use, something similar will give you subscribers that have been sent to in the past 100 days but have not opened an email in the past 100 days. You can tweak that to fit your needs and you would also need to join your data extension if you only want to include subscribers or certain criteria that lives there. So in your case, you would have a "date added" field on your Data Extension to filter based on as well. Hope that helps!


SELECT distinct SubscriberKey FROM _Sent s WHERE EventDate > dateadd(day,-100,getdate()) AND SubscriberID NOT IN (SELECT SubscriberID FROM _Open WHERE EventDate > dateadd(day,-100,getdate()))

Related Topic