0

Use Case: I have a data extension with a list of available coupons and a second data extension that contains an email address and how many coupons can be assigned to each email address.

enter image description here

enter image description here

Current code below is assigning the same coupon to multiple email address'. At the time of send, it is processing multiple coupons before marking them as consumed.

%%[
 
 set @TicketCount = AttributeValue (‘NumOfTickets’)
 set @TicketRows = LookupOrderedRows(‘EmployeeBarCodes’, @TicketCount, “Barcode asc”, “Assigned”, “Available”)
 set @rowCount = rowcount(@TicketRows) 
]%%

%%[ IF @ticketCount > 0 THEN ]%% 

%%[for @counter = 1 to @TicketCount do
set @row = row(@Ticketrows, @counter)
set @orderItem = field(@row,”Barcode”)
set @orderURL = field(@row,”WalletURL”) 
set @Consumed = ‘Consumed’
set @rowsUpdated = UpdateDE(“EmployeeBarCodes”,1,”Barcode”, @orderItem, “Assigned”, @Consumed)
]%%
<a style=”margin-left:250px;line-height:1.5″ href=”%%=RedirectTo(@orderURL)=%%” target=”_blank”>%%=v(@orderItem)=%%</a><br>

%%[ NEXT @counter ]%% %%[ ENDIF ]%%

The first email works but in the second email, they are given some of the same coupons as the first email person. This does not have to be in real time. I can assign the coupons before sending but not sure how I could assign that using SQL.

Would love your thoughts and ideas.

I also tried claim row but it was only working for 1 coupon. How could I use this multiple times in one email? I thought I read somewhere that it can only be used once.

%%[

var @em, @couponRow,  @couponCode

if _messagecontext == "PREVIEW" then

    set @couponCode = "XX TEST XX"

else

    /* include your sendable attribute/column here */
    set @em = AttributeValue("Email") 

    set @couponRow = ClaimRow("CouponsCodes", "IsClaimed", "EmailAddress", @em)

    if empty(@couponRow) then

        /* You can do other error handling here if you want.*/
        /* This aborts the send */    
        RaiseError("No coupons available", false) 

    else
        SET @couponCode = Field(@couponRow, "CouponCode")
    endif

endif

]%%

Here's your coupon code: %%=v(@couponCode)=%%

3 Answers 3

1

Hello fellow campaigner,

one coupon is being assigned to multiple email addresses as the the actual database update happens after all emails are personalized. Basically you can see the data extension updated after you send the email and not within the personalization loop.

How you suppose to assign coupons in marketing cloud is the use of claimrow() function and you can use it multiple times in within one email.

set @rows = ClaimRow("voucher_data_extension", "claimed", "email", "[email protected]",...)
3
  • I tried claimrow as one of my solutions but didn't know how to assign the coupons with a variable amount. Each email will get a different number of coupons. I edited my response above to show my claimrow code. How could I adjust it?
    – user47606
    Commented Nov 6, 2023 at 15:00
  • Same as per your example just use claimrow instead of updatede Commented Nov 6, 2023 at 21:14
  • Learning that the DE Update happens after all the personalization is added to the emails is what told me I had to find another solution. Marking this the most helpful but I found another solution that I will try and post.
    – user47606
    Commented Nov 7, 2023 at 18:59
0

Your first attempt should be fine. I think you were seeing some previous coupons due to the preview mode in Content Builder.

However, I would recommend using the ClaimRow function in your second attempt. In order to use the function multiple times in the email, you would want to add a loop and put some additional columns so that you can distinguish each of the function calls, like Lukas's answer HERE

In the code below, I used the EmailAddress, subKey, and claimNumber columns in the "CouponCodes" DE to distinguish each of the function calls.

%%[

var @em, @couponRow, @couponCode


    /* include the send context attributes or columns to record here */
    set @em = emailaddr
    set @sk = _subscriberkey

    set @TicketCount = AttributeValue("NumOfTickets")

    if @TicketCount > 0 then
        for @counter = 1 to @TicketCount do
            set @couponRow = ClaimRow("CouponCodes", "IsClaimed", "EmailAddress", @em, "subKey", @sk ,"claimNumber", @counter)

            if not empty(@couponRow) then

                set @couponCode = Field(@couponRow, "CouponCode")
]%%
Coupon code: %%=v(@couponCode)=%%<br>
%%[                

            else

            /* You can do other error handling here */

            /* This aborts the entire send */
                RaiseError("No coupons available", false)
            endif
        
        next @counter

    endif



]%%
0

The only way I could figure out how to assign multiple coupons per subscriber was to pre-assign the coupons before sending the email. This works for our use case. We used a cloud page to process.

<script runat="server">
Platform.Load("Core","1.1.1");
try{
</script>

%%[
 SET @subscribers = LookupRows("EmployeeRequests","Flag", 'All')
 SET @subscribersCount = rowcount(@subscribers)
 
 FOR @i = 1 TO  @subscribersCount DO
    SET @subscriberRow = row(@subscribers, @i)
    SET @Email = field(@subscriberRow,"Email")
    SET @NumTickets = field(@subscriberRow, "NumTickets")
    
    SET @barcodeRows = LookupOrderedRows("EmployeeBarCodes", @NumTickets, "Barcode asc", "Status", "Available")
    SET @barcodeRowCount = rowcount(@barcodeRows)
    IF @barcodeRowCount > 0 THEN
     FOR @j = 1 TO  @barcodeRowCount DO
      SET @barcodeRow = row(@barcodeRows, @j)
      SET @barCode = field(@barcodeRow,"Barcode")
      
      SET @rowsUpdated  = UpdateData("EmployeeBarCodes",1,"Barcode", @barCode, "Email", @Email, "Status", "Used")
      
     NEXT @j
    ENDIF
    
 NEXT @i
]%%

<script runat="server">
}catch(e){
 Write(Stringify(e));
}
</script>

You must log in to answer this question.

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