Coupon Claiming in AMPScript

One cool thing you can do in the Salesforce Marketing Cloud is offer your subscribers a unique coupon code as part of a promotional email campaign. Here’s how to do it.

To start, create a “CouponCodes”Data Extension with the following columns:
coupon-claimrow2

Next, generate your own list of unique coupon codes in Excel. Include a “CouponCode” column header. Save the worksheet as CSV or Unicode (tab-delimited).
coupon-claimrow1

Then, import your coupons into the data extension with the adhoc import button:
coupon-claimrow3

Finally, add this AMPScript to your email. This code will try to find a coupon row for the specified email address in the CouponCodes data extension. If it finds one, it’ll return the coupon code. If not, it will “claim” an unclaimed code and associate it with the specified email address. The bit about the message context — that will prevent you from claiming coupon rows when you’re doing a send preview when building the email.

Keep these things in mind:

  • Make sure you upload enough coupons for your target audience so you don’t run out of unclaimed coupons. For recurring coupon campaigns, you should build a system to monitoring unused coupon codes.
  • Make sure the target of the coupon code (e-commerce site, POS system, etc) is using the same set of coupon codes as your campaign.
  • You can also do this in an SMS campaign.
  • The data extension name in the claimrow() function cannot be a variable
  • Any data extension referenced in a claimrow() function must exist

For further reading:

Data Extensions
Data Extension AMPScript Functions
MobileConnect Coupon Scenario
Live Offers

Update 5/28/2013: Added additional gotchas about claimrow() function
Update 2/9/2015: Added empty() check after claimrow() function
Update 3/9/2016: Move code snippet to gist, added variable declaration for emailaddr
Update 7/24/2017: Removed unnecessary lookup. Thanks, Brian (see comments below).

10
Leave a Reply

avatar
6 Comment threads
4 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
6 Comment authors
SohailsfdcFanBoyKatBrianAdam Spriggs Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
David
Guest
David

Love this post! Wanted to say that I tried it out and it worked perfectly!

Quick Question – If i wanted to add another field to the data extension (OnlineCode) is the extra one:

CouponCode
OnlineCode
EmailAddress
IsClaimed

How would I go about adding that to the AMPscript? I am having difficulty making it work as I am an AMPscript newbie. Any help on this would be much appreciated.

  • David
Brian
Guest
Brian

There should be no reason to do a separate LookupRows() to see if a coupon has already been claimed. ClaimRows() already does that for you, and also locks the coupon DE to ensure that no two simultaneous calls to ClaimRow() can interfere with each other. It should be something like this: %%[ var @em, @couponRow, @couponCode if _messagecontext == "PREVIEW" then set @couponCode = "XX TEST XX" else set @em = AttributeValue("emailAddr") set @couponRow = ClaimRow("Coupons", "IsClaimed", "EmailAddress", @em) if Empty(@couponRow) then RaiseError("No coupons available", false) /* stop the send */ else SET @couponCode= Field(@couponRow, "CouponCode") endif endif ]%%

Kat
Guest
Kat

Hi, thanks for this explanation! I’m wondering whether Claimrow can be used on a cloudpage/landing page instead of in an email – and whether a sendable attribute (ie email address) is required, as opposed to unique identifying number?

sfdcFanBoy
Guest

Nice post. I see these extra params in the code. Care to explain, what these extra params are for? Especially the “None Available” and “DateIssued”?

set @couponCode = ClaimRowValue(“RegCodeDE”, “CouponCode”, “IsIssued”,”None Available”,”DateIssued”,NOW(),”JobID”,jobid,”ListID”,ListID,”BatchID”,JobSubscriberBatchID,”SubscriberKey”,_subscriberKey,”SubscriberID”, SubscriberID,”EmailAddress”,@em,”EmailName”,emailname)

Sohail
Guest
Sohail

Hi Adam,

I followed the instructions and created Coupon DE and used your code in email but it’s not sending an email.. I didn’t see any error but when I try to sent an email it’s not sending any…

Sohail
Guest
Sohail

After Removing the below URL from code,

if _messagecontext == “PREVIEW”
then set @couponCode = “XX TEST XX” else

Now I can see an error “No coupons available”, though I already have uploaded sample coupon codes in coupon DE….