How to retrieve random DE rows without matching criteria using AMP script?

Question

I have a situation where we want to assign a CouponCode to anyone who registers through our web form. I am using a landing page with AMP script to insert the form filled into DE(Data Extension), additionally I want to retrieve the Coupons present in another DE and put it along with the new record being inserted in DE. There is a 'product qty.' field in form, we want to assign 3 coupons if product qty = 5, 1 coupon to qty < 5.

How do I retrieve 3 coupons if qty = 5? LOOKUP function is not helpful because it asks for matching column which I don't have, I just need any random 5 coupons which I can add against the record is being submitted.

Answer 1

In cases like this, I usually create a Coupon_DE like this:

CouponCode | IsClaimed | SubscriberKey
 (pkey)    |   T/F     |   Text(254)

Where Isclaimed and SubscriberKey are nullable and IsClaimed defaults to false.

You then have the AMPscript in your form perform a lookuporderedrows with columns returned being 1 or 3, and this will then return a rowset of your coupons. After you get the coupons, I would also add an update to run to update the Coupon_DE to push the SubscriberKey and IsClaimed status.

So I would look at something like this:

%%[
SET @cpnCnt = IIF(@productQty > 4, 3, 1)

SET @cpnRows = LookupOrderedRows('Coupon_DE',@cpnCnt,"CouponCode ASC","IsClaimed",0)

SET @cpnRowCount = RowCount(@cpnRows)

FOR @i=1 TO @cpnRowCount DO
  
  SET @row = ROW(@cpnRows,@i)
  SET @coupon = FIELD(@row,"CouponCode")

  OUTPUT(CONCAT("Coupon Code ",IIF(@cpnRowCount > 1,CONCAT("#",@i," "),""),"is ", @coupon))
  
  UpdateDE("Coupon_DE",1, "CouponCode", @coupon, "IsClaimed",1,"SubscriberKey", _subscriberkey)

NEXT @i
]%%

This will not only help you correctly dispense the coupons, but can be used to keep track of the coupons used and who took them.

I know that a lot of this can also be accomplished via the ClaimRow function, reducing the manual need there, but I tend to find it more reliable when I do the lookup/update on my own.

The official recommendation of SFMC is to utilize Claimrow as it is more performant. So I wanted to share both in case you have better luck with it than I do. Below is a very basic sample. I have not tested it and you may need to tweak it more to fit your needs though.

SET @cpnCnt = IIF(@productQty > 4, 3, 1)

FOR @i=1 TO @cpnCnt DO
  
    set @couponRow = ClaimRow("CouponCodes", "IsClaimed", "EmailAddress", @em, "CouponNumber",@i, "JobID",AttributeValue("jobid"))

    if not empty(@couponRow) then
      set @couponCode = Field(@couponRow, "CouponCode")
      OUTPUT(CONCAT("Coupon Code ",IIF(Rowcount(@couponRow) > 1,CONCAT("#",@i," "),""),"is ", @coupon))
    else
       RaiseError("Coupon Claim Error")
    endif
NEXT @i
]%%