How to claim the same row without claiming a new row for already claimed customers using claimrow() in SFMC?

Question

I am trying to implement unique coupon codes in SFMC.

I have created a data extension - Unique_Coupon_Codes like this,

This is the code I have written

%%[
    if _messagecontext == "PREVIEW" then
        set @couponCode = "TEST"
    else
     set @email=AttributeValue('EmailAddress')
     <h1>email: %%=v(@email)=%%</h1>
     set @IsClaimedValue = Lookup("Unique_Coupon_Codes","IsClaimed",'EmailAddress', @email)
       if @IsClaimedValue==true then 
         set @CouponCode= Lookup("Unique_Coupon_Codes","CouponCode",'EmailAddress', @email)
       else
          set @CouponDE = "Unique_Coupon_Codes"
       TreatAsContent(Concat("%","%[ SET @couponRow = ClaimRow('", @CouponDE, "',  'IsClaimed','ClaimDate',Now(1), 'EmailAddress', '", @email, "') ]%","%"))
    ") ]%","%"))
    
     if not empty(@couponRow) then
        set @CouponCode = Field(@couponRow,"CouponCode")
      else
        RaiseError("No coupons available", true)
      endif
    
       endif  
    endif   
       
       
    ]%%
    
    CouponCode: %%=v(@CouponCode)=%% 

What is expected: When I send the email for the first time to a customer the code should pick up a new coupon code and assign it to that customer. Next time when same customer enters the journey instead of claiming a new row, the code should fetch the values from the row that was claimed earlier and display that, instead of claiming a new row.

My problem: When the email is sent for first time, the rows are claimed. When the email is sent for the second time to same customer a new row is claimed (This should not happen) I am able to display the old claimed code to the customer but at same time a new rows gets claimed.

Can anyone help me fix this?

Answer 1

I ran into this exact issue and although it is a kinda obscure use case, it's one that can be catastrophic if not accounted for.

If you utilize the prebuilt option for sending your emails and use a dynamic variable filled via a lookup it can result in varying outcomes and unexpected outputs (like how yours is always creating a new one even if already exists)

See this Knowledge Article I asked support to create around the issue which should give more details.

My solution was to pre-assign via SQL and use claimrow as a backup only with hard coded de value in function. Not optimal, but the other option was to turn off prebuild which could increase processing/sending speed by 10 to 20 times more. Looking at a 1 hour send being almost a full day to send out.

---

EDIT
This is a basic example of some SQL to pre-assign coupons prior to a send. You could have this run inside the automation in the step prior to the send. This would then replace the claimrow with a lookup inside the email to get the code in.

SELECT
send.EmailAddress,
1 as IsClaimed,
coupon.CouponCode,
GetDate() as ClaimedDate
FROM (
    SELECT sde.[Email Address] as EmailAddress,
    ROW_NUMBER() OVER(ORDER BY newID()) as rn
    FROM [myDE]sde
) send
INNER JOIN (
    SELECT coup.CouponCode,
    ROW_NUMBER() OVER(ORDER BY newID()) as rn
    FROM [couponDE] coup
    WHERE coup.EmailAddress IS NULL
) coupon
ON send.rn = coupon.rn

/* target: couponDE */
/* action: Update */

You then do a lookup in the email to the DE to find the coupon values.

As to turning off prebuild, that is handled by SF Support, which basically means that each email will load fully individually inside the send queue instead of pre-building like normal. This can greatly bloat send time and is not really recommended.