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 Name | Primary Key | Data Type | Nullable | Example Value |
CouponCode | Y | Text(100) | N | XMS2323MFZ |
IsClaimed | N | Boolean | N | TRUE |
EmailAddress | N | Y | hello@ampscript.com | |
ClaimDate | N | Date | Y | 2/26/2021 |
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.