Engagement - Exclude Subscribers from a Send who previously received a Marketing Cloud Engagement email

To exclude subscribers who were previously sent an email message for a specific job you must:

Create a Data Extension

1. In Marketing Cloud Engagement Email, click Subscribers | Data Extensions.
2. Click Create.
3. In "Properties," enter Exclusion_[YYYY-MM-DD] in the Name field.  

Enter today's date between the brackets as YYYY-MM-DD.  For example, if today's date is January 1, 2015, the name will be Exclusion_2015-01-01.

4. Click Next | Next.
5. In "Fields," enter the following:

• Name = EmailAddress.
• Data Type = Email Address

6. In the next row, enter:

• Name = SubscriberKey
• Data Type = Text
• Length = 254

7. Click Create.
8. On the "Data Extensions" page, click the name of the Data Extension you just created.
9. On the "Properties" tab, select the checkbox labeled Used for Sending.
10. In the left dropdown that appears, select SubscriberKey.
11. In the right dropdown that appears, select Subscriber Key.
12. Click Save.

Find the appropriate Job ID

1. In Marketing Cloud Engagement Email, click Tracking | Sends.
2. Note the value in the "Job ID" field for the job in question.

Create the Query Activity

Follow these steps to create a Query Activity: 

1. In the Marketing Cloud Engagement, you can create a query activity under Automation Studio.  Navigate to Journey Builder > Automation Studio > Activities. 
2. Click Create Activity > SQL Query.
3. Under "Name," enter Exclusion_[YYYY-MM-DD].

Enter today's date between the brackets as YYYY-MM-DD.  For example, if today's date is January 1, 2015, the name is Exclusion_2015-01-01.

4. Under "Query," enter the appropriate query text as noted below (Note: Make sure to replace the value @JobID with the Job ID value retrieved above.). 

a. Enter this query if you wish to exclude all Subscribers who did not bounce in a previous send:

SELECT su.EmailAddress as Emailaddress,su.SubscriberKey
FROM _Sent se
LEFT JOIN _Bounce b
ON se.JobID = b.JobID
AND se.ListID = b.ListID
AND se.BatchID = b.BatchID
AND se.SubscriberID = b.SubscriberID
JOIN _Subscribers su
ON su.SubscriberID = se.SubscriberID
WHERE se.JobID = xxxxx -- Please enter Job ID
AND b.JobID IS NULL

b. Enter this query if you wish to exclude all Subscribers who were previously sent in a cancelled send:

SELECT su.EmailAddress as Emailaddress,su.SubscriberKey 
FROM _Subscribers su WITH (NOLOCK)
JOIN _Sent se WITH (NOLOCK) 
ON su.SubscriberID = se.SubscriberID
where se.JobID = xxxxx --Add JobID

5.  Under "Target," select the Data Extension named Exclusion_[YYYY-MM-DD].
6. Click Save.

*Note*

Depending upon your Marketing Cloud Engagement Support Entitlement, the complexity of the SQL Query below may be unsupported.

Modify the User-Initiated Email

1. In Marketing Cloud Engagement Email, click Interactions | User-Initiated Emails.
2. Find the User-Initiated Email used to perform the Send.
3. Click the name.
4. Under "Recipients," click Edit Recipients.
5. Under "Select Recipients," click the Data Extension named Exclusion_[YYYY-MM-DD].
6. Click the right pointing arrow ( >>) icon next to "Excluded" to move the Data Extension into the Excluded box.
7. Click Save | Save.

Create, schedule, and run the Automation

Define an Automation using these steps: 

1. In Marketing Cloud Engagement, click Journey Builder | Automation Studio.
2. Click Create Automation | Ok.
3. Overwrite Untitled Automation with the name Exclusion_[YYYY-MM-DD].

Enter today's date between the brackets as YYYY-MM-DD.  For example, if today's date is January 1, 2015, the name will be Exclusion_2015-01-01.

4. Under "Activities," click and drag SQL Query to the Workflow Canvas.
5. Click Choose...
6. Click on the folder named Query.
7. Click Exclusion_[YYYY-MM-DD].
8. Click Done.
9. Under "Activities," click and drag Send Email to Step 2 of the Workflow Canvas.
10. Click the arrow next to "Configure." 
11. Click Send Definition Template.
12. Find the User-Initiated Email used to perform the Send.
13. Click on the name.
14. Click Done | Save.
15. Click the Schedule tab.
16. Schedule the Automation to run as desired.
17. Click Run Once.