4

I am trying to implement unique coupon codes in SFMC.

I have created a data extension - Unique_Coupon_Codes like this, enter image description here

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?

2
  • 2
    Welcome to Salesforce Stack Exchange (SFSE). Unfortunately I know nothing of SFMC or claiming rows. Hopefully someone with knowledge in that area will come along soon to help. I just wanted to thank you for such a well written question, and for including screen shot and code. That is refreshing for a first-time poster.
    – Moonpie
    Aug 26, 2021 at 15:44
  • thank you :)Moonpie
    – Sonia S
    Aug 27, 2021 at 8:51

1 Answer 1

3

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.

4
  • Hi @Gortonington. Thank you very much for your reply. You mentioned - ' pre-assign via SQL and use claimrow ' . I am a beginner and not sure what exactly it means. It would be helpful if you could give me more details about this method. Also about the 2nd method you added in your answer.
    – Sonia S
    Aug 27, 2021 at 8:55
  • On a bus in Disney right now but will try to get examples when I get back to a computer Aug 27, 2021 at 17:08
  • @SoniaS - added some samples to my answer Aug 27, 2021 at 19:33
  • Thanks a lot for your help. Will try to implement it
    – Sonia S
    Aug 28, 2021 at 14:34

You must log in to answer this question.

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