Coupon codes or discount codes allow companies to attract new customers and increase the conversion rate during marketing campaigns. But how do we manage them in Salesforce Marketing Cloud?
In short, it’s easy… But there is a catch!
There is a function called ClaimRow that allows us to assign an identifier (Id, email address, SubscriberKey…) to a record in a Data Extension, if the record is currently unassigned.
Not a fan of reading? Jump to the code snippet.
How it works
First of all, let’s build a Data Extension called CouponCodes with the following structure:
| SubscriberKey | Text | 254 char. |
| IsClaimed | Boolean | False |
| CouponCode | Text | 254 char. |
| ClaimedDate | Date |
Then, fill it out with the following data:
| SubscriberKey | IsClaimed | CouponCode | ClaimedDate |
| True | 000001 | ||
| False | 000002 | ||
| False | 000003 |
Now, let’s see how to claim each record and assign it to a SubscriberKey.
%%[
SET @SubscriberKey = "MY@WES0MEKEY"
SET @CouponRow = ClaimRow('CouponCodes', 'IsClaimed', 'SubscriberKey', @SubscriberKey)
IF EMPTY(@CouponRow) THEN
OUTPUT(CONCAT('No coupons available'))
ELSE
OUTPUT(CONCAT('Your coupon is: ', FIELD(@CouponRow,'CouponCode')))
ENDIF
]%%Run the code and check back the Data Extension:
| SubscriberKey | IsClaimed | CouponCode | ClaimedDate |
| True | 000001 | ||
| MY@WES0MEKEY | True | 000002 | 5/9/2019 1:54:46 PM |
| False | 000003 |
Congratulations! We have successfully claimed the first available coupon code!
The code took the first record where the IsClaimed value was False, filled out the ClaimedDate field with the current date and time, and assigned the provided SubscriberKey to this record.
If we run the code again, it will always return 000002 as the CouponCode for our SubscriberKey.
What’s the catch?
This seems like a perfect solution for our needs, but there are 2 downsides:
- There is no way to convert the system date to the local date within the function.
- The coupon codes need to be imported to the Data Extension.
This basically means that we have to generate all the coupon codes , import them into the Data Extension and later on convert the date somehow.
Is there a better way?
Instead of generating the coupon codes in bulk and importing them into the Data Extension, we could imagine a system that generates the coupon code on the fly (with an API call for exemple) and on assign it on demand (by clicking on a button).
In this case, the ClaimRow function is just not enough… but UpsertDE might do the trick!
Making it better!
First, let me explain the logic by breaking it into several general steps.
- Check the Data Extension for the SubscriberKey
- If the SubscriberKey doesn’t exist, generate a coupon code and create a new record.
- When the button is clicked, switch the Boolean value of the IsClaimed field for the SubscriberKey and write a local date in the ClaimedDate field.
Note that in order for this to work, the SubscriberKey should be unique and therefore needs to be defined as the Primary Key in the Data Extension.
Full code
The code is pretty self explanatory. Copy-paste it in a Marketing Cloud page and watch the magic happen!
Keep in mind that I use only one page to manage the form submissions and therefore look for a variable value in the URL to check if the form has been submitted or not.
Instead of making an API call to generate a new code, I simply use a GUID.
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Get my coupon code</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
<pre>%%[
SET @SubscriberKey = RequestParameter("k")
SET @Submitted = RequestParameter("v")
SET @Now = SystemDateToLocalDate(Now())
IF NOT EMPTY(@SubscriberKey) THEN
Outputline(concat("SubscriberKey is ",@SubscriberKey))
SET @CouponCode = Lookup('CouponCodes','CouponCode','SubscriberKey',@SubscriberKey)
IF EMPTY(@CouponCode) THEN
Outputline(concat("Subscriber doesn't have a CouponCode"))
SET @CouponCode = GUID()
Outputline(concat("Generating the CouponCode: ",@CouponCode))
UpsertDE('CouponCodes', 1, 'SubscriberKey', @SubscriberKey, 'CouponCode', @CouponCode)
Outputline(concat("Updating the Subscriber with the generated CouponCode"))
ELSE
Outputline(concat("Subscriber has a CouponCode: ",@CouponCode))
SET @IsClaimed = Lookup('CouponCodes','IsClaimed','SubscriberKey',@SubscriberKey)
IF EMPTY(@IsClaimed) OR @IsClaimed != "True" THEN
Outputline(concat("Subscriber didn't claim his CouponCode"))
IF @Submitted == 1 THEN
UpsertDE('CouponCodes', 1, 'SubscriberKey', @SubscriberKey, 'IsClaimed', 'True', 'ClaimedDate', @Now)
Outputline(concat("CouponCode has been claimed by the Subscriber on ",@Now))
ENDIF
ELSE
Outputline(concat("Subscriber has claimed his CouponCode"))
ENDIF
ENDIF
ELSE
Outputline(concat("No SubscriberKey found."))
ENDIF
]%%</pre>
%%[ IF NOT EMPTY(@SubscriberKey) AND @Submitted != 1 THEN ]%%
<form>
<input name="k" type="hidden" value="%%=v(@SubscriberKey)=%%">
<input name="v" type="hidden" value="1">
<button>Claim my coupon code</button>
</form>
%%[ ELSEIF NOT EMPTY(@SubscriberKey) AND @Submitted == 1 THEN ]%%
<p>Your coupon code is: %%=v(@CouponCode)=%%</p>
%%[ ELSE ]%%
<p>These are not the droids you are looking for.</p>
%%[ ENDIF ]%%
</body>
</html>
Have I missed anything?
Please poke me with a sharp comment below or use the contact form.
Hi Ivan,Do have a vedio of this. i tried doing it but unable to get any output as you mentioned. or can you put in the detail steps on the execution part as i might be doing it in a wrong way
Hi Venson, unfortunately no. The steps really depend on your use case.