ClaimRow Function

The ClaimRow function returns a row in a data extension and locks that row from being used again. A common use case for the ClaimRow function is for unique coupon codes.

Unique coupon code values can be stored in a data extension and returned in the email content of an email send. When the ClaimRow function is run at send time, the row in the coupon data extension is locked so that no two recipients should receive the same unique coupon code. This is helpful with unique coupon codes as you do not want to be able to redeem the same unique coupon code twice.

Syntax:

                    

ClaimRow(1,2,3,4)
1 = Name of data extension to retrieve and return value and lock row
2 = Name of column to identify which value to return
3 = Name of key columns used to identify the recipient claiming the row (requires a nullable column in the data extension)
4 = Value of key column used to identify the recipient claiming the row  
(One or more key column pairs can be used for 3 & 4 above)

                

Example:

                    

%%[
SET @em = AttributeValue(“emailAddr”)
SET @couponRow = ClaimRow(“Retail_Coupon_Codes”, “CouponCode”, “EmailAddress”, @em, “ClaimDate”, Now(1))
   if Empty(@couponRow) then
	RaiseError(“No coupons available”, false) 
   else 
        SET @Code = Field(@couponRow, “CouponCode”)
   endif

]%%
Your Coupon Code: %%=v(@Code)=%%

                

Output:

Your Coupon Code: XMS2323MFZ

Explanation:

First, the AttributeValue function is used to determine the “emailAddr” or email address from the data extension and setting that value as the @em variable.

The ClaimRow function is used in the @couponRow variable and locking the row in the data extension named “Retail_Coupon_Codes.” The data extension structure is as follows:

Field NamePrimary KeyData TypeNullableExample Value
CouponCodeYText(100)NXMS2323MFZ
IsClaimedNBooleanNTRUE
EmailAddressNEmailYhello@ampscript.com
ClaimDateNDateY2/26/2021
Example Data Extension used for the ClaimRow function. Data Extension is named “Retail_Coupon_Codes”

Breakdown of the ClaimRow function:

  • “Retail_Coupon_Codes” – The name of the data extension (example above) where we are retrieving the coupon code and locking the row so that it can only be used once.
  • CouponCode – The name of the fields where the coupon code value is retrieved. In this example, the value of the coupon code is “XMS2323MFZ”
  • EmailAddress – The name of the key column used to identify the recipient claiming the coupon code
  • @em – The variable value that will populate the EmailAddress field above with “hello@ampscript.com” as the recipient who claimed the coupon code
  • ClaimDate – The name of the second key column pair which will be used to determine when the coupon code is claimed
  • Now(1) – The Now() function used to determine the date/time for when the coupon code is claimed

The RaiseError function is used in case the data extension has run out of coupon codes.

A helpful note is that when using the send preview of an email, the ClaimRow function will still run and thus claim and lock the row for each time it is previewed. When using “live” coupon codes, this can be problematic as it could burn through unique codes fairly quickly when performing testing. A solution to this is using the _messagecontext string function.

                    

%%[
if _messagecontext == “PREVIEW” then
   set @Code = “XX TEST XX”
else
   /* Use ClaimRow function from above here */
endif 
]%%
Your Coupon Code: %%=v(@Code)=%%

                

Output: (During a Send Preview)

Your Coupon Code: XX TEST XX

Explanation:

Using the _messagecontext string function with and if function together will show the coupon code of “XX TEXT XX” when previewing the email in a Send Preview. This will prevent the locking of the row using the ClaimRow function during previewing the email and will show the actual unique code when the a test email or actual email send is performed.

Leave a Reply

Your email address will not be published. Required fields are marked *