My goal is to get a report of how many members will receive an email based on exclusions. For example if I have my target list with 1,000 members, and two exclusion lists with 250 members each, how many of the original 1,000 target group will the email be sent to? I realize there are other factors that can change the final send count so right now I'm focused only on exclusion groups...

Initially I'd thought I'd be able to use the rowcount function in ampscript and send a simple email to myself about 30 minutes before send. However I'm not seeing a way to use wildcards in a lookup (or perhaps I'm thinking about this in the wrong way). So now I'm thinking the best way is to use a query activity?

SELECT target.PrimaryEmail
FROM [Target DE] target
LEFT JOIN [Exclusion DE 1] exclusion1 ON exclusion1.PrimaryEmail = target.PrimaryEmail
WHERE exclusion1.PrimaryEmail IS NULL
LEFT JOIN [Exclusion DE 2] exclusion2 ON exclusion1.PrimaryEmail = target.PrimaryEmail
WHERE exclusion2.PrimaryEmail IS NULL

am I on the right track with this one or am I entirely off base?

1 Answer 1


That's how I've done it in the past.

If you want to retrieve the counts in an email, add a LookupKey number column to your sending data extension and give it a default value of 1. Then you can use that in your AMPScript lookup function.

You'll want to fix the where clause in your query:

FROM [Target DE] t
LEFT JOIN [Exclusion DE 1] ex1 ON ex1.PrimaryEmail = t.PrimaryEmail
LEFT JOIN [Exclusion DE 2] ex2 ON ex2.PrimaryEmail = t.PrimaryEmail
ex1.PrimaryEmail IS NULL
and ex2.PrimaryEmail IS NULL
  • Awesome - that seems reasonable to me and I do I like the idea of doing it in an email as well. Thanks for the pointers! Sep 26, 2016 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.