Print this page

Exclude Subscribers from a Send who were previously sent an email from Marketing Cloud

Knowledge Article Number 000221668
Description
You may choose to re-send an email for a number of reasons. For example, a large number of Subscribers were not sent an email due to a block in place at a specific domain, but the block has now been lifted. Another reason might be that a Send was cancelled after it was already in progress and some Subscribers were sent the email and not others. For each of these scenarios, you can re-send the email and exclude subscribers that already received the email using the steps below. 
Resolution

 

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


Create a Data Extension


1. In Marketing Cloud 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.
6. In "Fields," enter the following:

  • Name = EmailAddress.
  • Data Type = Email Address

7. In the next row, enter:

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

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

 

Find the appropriate Job ID

 

1. In Marketing Cloud 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 Marketing Cloud Email, click Interactions | Query.
2. Click Create.
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 WITH (NOLOCK)

LEFT JOIN _Bounce b WITH (NOLOCK)

ON se.JobID = b.JobID

AND se.ListID = b.ListID

AND se.BatchID = b.BatchID

AND se.SubscriberID = b.SubscriberID

JOIN _Subscribers se WITH (NOLOCK) 

ON su.SubscriberID = se.SubscriberID

WHERE s.JobID = @JobID

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 s.JobID = @JobID

 

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

 

Modify the User-Initiated Email

 

1. In Marketing Cloud 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, click Marketing Automation | 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.

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

 





promote demote