Send email when non-claimed vouchers is less than 2000

Question

I have a data extension setup with the following columns:

CouponCode | EmailAddress | IsClaimed | CustomerID

This code then claims the vouchers by writing CustomerID/Email Address next to the Coupon Code, and changing IsClaimed to True.

set @couponCode = ClaimRowValue("welcomeVoucher","CouponCode","IsClaimed","None Available","CustomerID",@CustomerID)

My question is if i can somehow (automation studio?) get an email notification when the amount of "free" vouchers (IsClaimed = False) is less than a certain threshold, say 2000?

One of the ideas that i had was to have an email fire every day, but raiseError() if the amount of rows with isClaimed is less than x - I'm not too sure this would work, though.

Answer 1

What you've described is typically how I've done it. You can set up an report Data Extension and use a Query Activity to populate it. Then in an email (and UI Send), you can iterate through the DE and display the counts.

The Query Activity would be something like this:

select
'ASCkCoupons' as CouponDEName
, isClaimed
, count(*) Count
from ASCCoupons
group by isClaimed

union

select
'WelcomeCoupons' as CouponDEName
, isClaimed
, count(*) Count
from WelcomeCoupons
group by isClaimed

If you have multiple Coupon DEs, you can aggregate the counts with a union.