1

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?

1 Answer 1

0

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.

8
  • Hi @DonL, thank you for your response. If you look into the details that I have provided, I have already shared the updated code where I have swapped UpsertDE with ClaimRow function. To me, it seems like the issue is not limited to just claiming function and probably related to cloud page is sending data to the triggered send data extension.
    – kl2
    Commented Mar 14, 2023 at 13:39
  • Indeed I missed that. Then my suggestion would be to move the coupon claiming into the email. There you would need to be aware of the send context tho, meaning that you shouldnt claim a new coupon for the VAWP context e.g.
    – DonL
    Commented Mar 14, 2023 at 13:43
  • Hi DonL, thanks for your suggestion. Do you have any recommendation for the approach used to prevent claiming a coupon in VAWP? Also, cloud page in current scenario is being used to pass some additional parameters / fields like "offer_type" which is critical to determine from which offer type coupon code will be picked. There are some additional fields that I have not mentioned to keep things simple.
    – kl2
    Commented Mar 14, 2023 at 13:55
  • My question would be how the end-to-end process looks like so the passed fields / parameters are linked with the customer record row in the data extension. In my use-case, cloud page will inject a new row in triggered data extension now if I move the coupon claiming in the email, how same would needs to be updated and how adding of that coupon will be done to the exact same row?
    – kl2
    Commented Mar 14, 2023 at 13:55
  • I will try to get my code into the initial answer.
    – DonL
    Commented Mar 14, 2023 at 14:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .