Presend email counts

Question

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?

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:

SELECT 
t.PrimaryEmail
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
WHERE 
ex1.PrimaryEmail IS NULL
and ex2.PrimaryEmail IS NULL