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.
- Cloudpage AMPScript triggered the Triggered Send
- Get the unclaimed coupon code from Coupon_Master data extension and assigned to an email address
- 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?