How to use Claiming of coupon codes in relation to Triggered Send where claimed coupon code also needs to be passed to Triggered Send Data Extension

Question

Current Scenario:

We have a cloudpage that is working as an API endpoint where fields / parameters are passed and cloud page is responsible to trigger a triggered send email.

1. Cloudpage AMPScript triggered the Triggered Send
2. Get the unclaimed coupon code from Coupon_Master data extension and assigned to an email address
3. Injects the values to Triggered Send data extension (and triggered send parallelly triggers the email as well)

Cloud page code:

%%[
VAR @ts, @tsDef, @ts_sub, @ts_attr, @tsctr, @ts_subkey, @ts_statusCode, @ts_statusMsg, @get_offer_type

SET @ts = CreateObject("TriggeredSend")
SET @tsDef = CreateObject("TriggeredSendDefinition")
SET @ts_subkey = Trim(requestparameter("emailaddress"))
SET @get_offer_type = Trim(requestparameter("offer_type"))

set @triggername = "CouponEmail_APITriggeredSend"


SetObjectProperty(@tsDef, "CustomerKey",@triggername)
SetObjectProperty(@ts, "TriggeredSendDefinition", @tsDef)

SET @ts_sub = CreateObject("Subscriber")
SetObjectProperty(@ts_sub, "EmailAddress", @ts_subkey)
SetObjectProperty(@ts_sub, "SubscriberKey", @ts_subkey)


SET @ts_attr = CreateObject("Attribute")
SetObjectProperty(@ts_attr, "Name", "offer_type")
SetObjectProperty(@ts_attr, "Value", requestparameter("offer_type"))
AddObjectArrayItem(@ts_sub, "Attributes", @ts_attr)





set @coupon_Code = Lookup("TEST_COUPON_MASTER_DE","Coupon_Code","emailaddress",@ts_subkey,"offer_type",@get_offer_type)

IF EMPTY(@coupon_Code) THEN


 set @rowset = LOOKUPROWS("TEST_COUPON_MASTER_DE","offer_type",@get_offer_type, "IsClaimed", "False")

 if ROWCOUNT(@rowset) > 0 THEN
 set @row = row(@Rowset,1)
 set @coupon_Code = Field(@row, "Coupon_Code")

 UpsertDE("TEST_COUPON_MASTER_DE",1,"Coupon_Code", @coupon_Code, "IsClaimed","True","Emailaddress",@ts_subkey)
 ENDIF

ENDIF





SET @ts_attr = CreateObject("Attribute")
SetObjectProperty(@ts_attr, "Name", "coupon_code")
SetObjectProperty(@ts_attr, "Value", @coupon_Code)
AddObjectArrayItem(@ts_sub, "Attributes", @ts_attr)

AddObjectArrayItem(@ts, "Subscribers", @ts_sub)

SET @ts_statusCode = InvokeCreate(@ts, @ts_statusMsg, @errorCode)
]%%

%%=v(@ts_statusMSG)=%%
<br><br>
ERROR CODE = %%=v(@errorCode)=%% 
<br><br>
--------------
<br><br>
Emailaddress:%%=v(requestparameter("emailaddress"))=%%
<br>
Coupon_Code:%%=v(@coupon_code)=%%

Coupon master data extension - TEST_COUPON_MASTER_DE:

Fields	Data Type	Primary Key	Nullable	Default Value
coupon_code	Text	YES
offer_type	Text
emailaddress	EmailAddress
isClaimed	Boolean		nullable	FALSE

Triggered send data extension where data is being injected from cloud page (this DE is linked with the triggered send in the configuration) - Triggered_Send_Emails_DE:

Fields	Data Type	Primary Key	Nullable	Default Value
SubscriberKey	Text
EmailAddress	EmailAddress
coupon_code	Text		nullable
offer_type	Text		nullable
send_date	Date		nullable	Current Date

ISSUE:

With the existing approach, we are seeing multiple email addresses linked to same coupon code in Triggered_Send_Emails_DE for use-cases where cloud page is being triggered at exact same time & second for multiple customers (i.e. email address)

Some findings on ISSUE:

• Duplicate DOES NOT exist in TEST_COUPON_MASTER_DE (since coupon_code is the primary key)
• Duplicates ONLY exist in Triggered_Send_Emails_DE
• It looks like claiming / upsert DE row in the ampscript code is being NOT triggered for customers
• It looks like the behavior in the code (ampscript) is a race Condition, where multiple processes (email addressed pushed to the cloud page) are attempting to access the same resource at the same time.

Sample records from TEST_COUPON_MASTER_DE

coupon_code	offer_type	emailaddress	isClaimed
101	offer_type1	[email protected]	TRUE
102	offer_type2	[email protected]	TRUE

Sample records from Triggered_Send_Emails_DE

coupon_code	offer_type	emailaddress	isClaimed
101	offer_type1	[email protected]	TRUE
101	offer_type1	test_2@test_2.com	TRUE
102	offer_type2	[email protected]	TRUE
102	offer_type2	[email protected]	TRUE

CONCERN:

I do not know switching to ClaimRow() instead of UPSERTDE will necessary resolve the issue since duplicate code with multiple email addresses are ending up in triggered send data extension and NOT coupon master DE.

I still went ahead and tried changing the approach to ClaimRow() like this:

set @couponRow = ClaimRow('TEST_COUPON_MASTER_DE', 'IsClaimed', 'offer_type', @get_offer_type, 'EmailAddress', @ts_subkey)

if empty(@couponRow) then
RaiseError("No coupons available", false) 
else
        SET @couponCode = Field(@couponRow, 'coupon_code')
endif

But, like I said I do not know if issue is limited to just ClaimRow() over UPSERTDE and may need code update in terms of the approach.

Also, is there any way to create some kind of queue process to make sure all submissions to this cloud page go through same lines of code and nothing is bypassed and cloud page is capable of handling multiple requests at exact same time.

Any recommendations?

Answer 1

There is an AMPScript function to make sure that rows are only claimed once, exactly to prevent the issue you described.

Its called ClaimRow. Please take a look.

EDIT: If that doesnt resolve the issue and you are unsure if the cloud page handling/ timing causes issues, move the claiming code to the email.

In my example, I do not add the code to the sendable DE, but do it the other way around. I add the Subscriber Key to the coupon table.

The most important part here is to pay attention to the different message contexts that exist. Decide what to do in each message context. Here are a few examples and suggestions:

1. Do not claim a Coupon in preview context.
2. Do not claim a Coupon in forward-to-a-friend context.
3. Do not claim a new Coupon in view-as-webpage context, query the before drawn coupon instead.
4. Make sure to update the row where you have claimed the code from. Add the subscriber key to identify the row in the future.

Platform.Load("Core","1");
  try {
</script>
%%[
VAR @voucher, @url, @sk, @error, @errorUrl, @msgContext, @jid, @codeRows, @codeRow, @claimDate
SET @sk = _subscriberkey
SET @msgContext = _messagecontext
SET @jid = _jobid
 
IF @msgContext == "PREVIEW" THEN 
    SET @voucher = "PREVIEW"
ELSEIF  @msgContext == "FTAF" THEN
    SET @voucher = "ERROR"
ELSEIF @msgContext == "VAWP" THEN
    SET @codeRows = LookupOrderedRows("CodeRedemption", 1, "UsedDate DESC", "SubscriberKey", @sk) 
     
    IF RowCount(@codeRows) > 0 THEN 
       SET @codeRow = Row(@codeRows,1) 
       SET @voucher = Field(@codeRow,"Code") 
    ELSE 
       SET @voucher = "ERROR"
    ENDIF
ELSEIF @msgContext == "SEND" THEN 
    SET @codeRow = ClaimRow("CodeRedemption", "IsUsed", "SubscriberKey", @sk)
    SET @voucher = Field(@codeRow, "Code")
    SET @claimDate = Field(@codeRow, "UsedDate")
                 
    IF @voucher != 'ERROR' THEN
        SET @url = CONCAT(@url, @voucher, "&origin")
        IF Empty(@claimDate) THEN
           UpdateDE("CodeRedemption", 2, "SubscriberKey",@sk,"Code",@voucher,"UsedDate",NOW())
        ENDIF
    ELSE
        /* RaiseError so email is not sent to subscriber */
        RaiseError('No Coupon Code available. Email was not sent.', 1)
    ENDIF
ENDIF
]%%
<script runat="server">
  }
  catch(e) {
     Platform.Variable.SetValue("@error", e.message);
</script>
 
%%[
/* ErrorLog */
    InsertDE(
       'ErrorLog',
       'SubscriberKey', @sk,
       'JobID', @jid,
       'Source', 'CodeRedemption Error',
       'ErrorMessage', @error
    )
]%%
 
<script runat="server">
  }
</script>

In case of a reminder email the code would need to be a slightly different one.