AMPScript to send me a message

Question

We use AMPScript to deliver coupon codes to our Clients at the BU level from a DE.

The one thing I need is a way to automatically send me a message when any of the DE containing the codes falls below 5% so I can replenish the codes across 40 BU's.

Answer 1

I've used a data extension, query, an email and a daily automation as an alert system. The data extension is for storing the coupon counts, the query populates the counts and the email contains the threshold logic. If the alert criteria is not met, the email send aborts. You can set the alert audience in a list that's defined in your user-initiated send definition.

Here's the scripting for the email:

%%[

var @rows, @number_Available, @number_Claimed, @total, @runDate

set @rows = LookupRows("Coupon_Codes","Active",1)
set @number_Available = Field(Row(@rows,1),"Number_Available")
set @number_Claimed = Field(Row(@rows,1),"Number_Claimed")
set @total = Field(Row(@rows,1),"Total")
set @runDate = Field(Row(@rows,1),"RunDate")

if @number_Available > 6000 then
  raiseerror("Has not met threshold for alert", true)
endif

]%%

<div style="font-family:sans-serif; font-size:0.8em;">

    <h3> Coupon Code Alert</h3>

    <p>The unused coupon count has fallen below 6000.</p>

    <p>Data Extensions &gt; Coupon_Codes</p>

    <table cellspacing="0" cellpadding="3" border="0">
        <tr><td><b>Run Date</b></td><td>%%=v(@runDate)=%%</td></tr>
        <tr><td><b>Available</b></td><td>%%=v(@number_Available)=%%</td></tr>
        <tr><td><b>Claimed</b></td><td>%%=v(@number_claimed)=%%</td></tr>
        <tr><td><b>Total</b></td><td>%%=v(@total)=%%</td></tr>
    </table><br/>
    <br/>
    This email was sent by:<br/>
    <b>%%Member_Busname%%</b><br/>
    %%Member_Addr%% %%Member_City%%, %%Member_State%%, %%Member_PostalCode%%, %%Member_Country%%<br/>
    <a style="font-size:0.75em; font-family:sans-serif;" href="%%profile_center_url%%" alias="Update Profile">Update Profile</a>
</div>
<custom name="opencounter" type="tracking">

Answer 2

I know that is not the topic, but it can be useful. I got an automation in automation studio with a SQL activity that gets all the active coupons into another data extension updating daily.

After that I got a step 2 with a Verification activity pointing to the DE of step 1, that sends me an email if the DE got 1000 active coupons or less.

The cool thing about the verification activity is that you can set the email there with no code.

More info here:

https://help.salesforce.com/s/articleView?id=sf.mc_as_verification_activity.htm&type=5

Omar