Hi all,
I'm currently building a system to allocate discount vouchers in Marketing Cloud using a CloudPages form.
The discount vouchers are generated externally by our ecommerce system, then uploaded into a Data Extension containing three values (CouponCode, isClaimed and EmailAddress).
When users enter their details in the CloudPages form, they're entered into a Data Extension, and every hour I have a Journey running which checks for new entries and sends them a code from the codes Data Extension, using AmpScript to select a code, set "isClaimed" to "True" and to put the user's email into "EmailAddress". All so far, so functional.
My issue is that we only want codes to be valid for 12 months after a user has requested them, but also to remain in the Data Extension to ensure that a user cannot request multiple codes.
We plan to upload new codes every three months, valid for a total of 15 months (at the start, to ensure that whenever they're requested, there's always at least 12 months of usage left).
What is the easiest way to deal with this via email? There's a couple of ideas I've considered but don't seem viable:
- Have each batch of codes stored in a new data extension with each one set to purge all data after 12 months via Data Retention settings
- Not viable because it needs to retain the record of the original code to prevent a user from requesting another one
- Also would lead to four data extensions each year which seems excessive
- Include the expiry and upload date fields with the codes and use some sort of logic to purge the database of their records after 1 year, allowing the customer to request a new code
- To my knowledge, you can't set data retention on a row-by-row basis
- It may be possible to use some logic in the AmpScript for this but I'm unsure.
I can't be the only one who's faced a similar issue in the past, but to summarise we need a system where:
- Users can self-serve to request codes
- We use Journey filtering to ensure that only users with approved email format are able to receive the emails, as these coupons are ostensibly a closed system.
- Codes are uploaded in their own data extension after being externally generated
- Codes can only be requested by a user once per year
Thanks!
Nice, yeah please do! Journey re-entry criteria is a good idea, as long as that journey is the only place a contact can claim a coupon code from.
You should absolutely be able to write some AMPscript to look up/claim rows based on the date column in your coupon DE, using some combination of LookupRows (or LookupOrderedRows) and UpdateDE.
Have a good weekend!
