In most cases, you'll probably be using a User ID of some sort as a proxy for email address. To simplify my explanation, I'm going to use EmailAddress in my examples.
That was a good first start - one code per person, no overlap, no one left without a code.
But what if it eventually gets more complex?
- You need to award a coupon code as soon as someone completes an action
- You don't know in advance who will be eligible for a coupon
- You don't know how many coupons you'll need
- The coupon codes have a real value (cost to your company) so you don't want to issue a code to a known bad address
So someone wants to give you a bunch of codes and have you just assign them to people when they meet the eligibility requirements. So now you've got coupons and you've got an eligible audience, but how on earth do you connect them up? (Example assumes audience is smaller than available codes. Also assumes that the website handles redemption and validation of codes. More thoughts on that below.)
While Salesforce Marketing Cloud aka ExactTarget does have a couponing feature, it was built to serve a particular function long ago and may not be flexible enough to meet your needs - or you're on a platform that doesn't have this functionality. Here's how you can roll your own.
My examples are roughly based on something I did at a previous job years ago using ExactTarget, but any code or examples below were created today and kept simple for the purposes of this example.
First, add a few more columns to your tables.
Next, we're going to take advantage of SELECT and ROWID by selecting the rows from the coupon table that meet our criteria.
Target: tblCoupon (Update)
SELECT ROWID as numRow, strCoupon FROM tblCoupon WHERE EmailAddress IS NULL AND dteClaimed IS NULL
Target: tblAudience (Update)
SELECT ROWID as numRow, EmailAddress FROM tblAudience WHERE strCoupon IS NULL and numRow IS NULL
What's happening:
- For each query, the SQL server is creating a new temporary table of just the records that meet the criteria. Then it applies that back to the original table. (Edit: The left table should have numbers in every row for "numRow" - this is an error in the original graphic. Sorry.)
Now - finally, we have something in each table we can use to tie them together.
Target: tblCoupon (Update)
SELECT c.strCoupon, a.EmailAddress, GETDATE() as dteClaimed FROM tblCoupon c INNER JOIN tblAudience a ON c.numRow = a.numRow WHERE c.EmailAddress IS NULL
Target: tblAudience (Update)
SELECT a.EmailAddress, c.strCoupon FROM tblAudience a INNER JOIN tblCoupon c on a.numRow = c.numRow WHERE a.strCoupon IS NULL
To be safe, I'm likely to then go back and set the numRow to -1 to prevent overlap.
Target: tblCoupon (Update)
SELECT strCoupon, -1 as numRow FROM tblCoupon WHERE numRow IS NOT NULL
Target: tblAudience (Update)
SELECT strEmailAddress, -1 as numRow from tblAudience WHERE numRow IS NOT NULL
Now you can email your audience from tblAudience.
In my typical setup, I'll usually keep a final table of all issued coupons (with email address and coupon both as primary keys, allowing an email address to appear more than once).
Ways to take this to the next level:
(1) Plan for cases where you run out of codes. The logic that populates the tblAudience table should should continue to include eligible people who haven't received a coupon until they are finally issued a coupon.
(2) Build a system to alert your Coupon Manager when you're getting low on coupons. Run this as part of your automation that distributes codes. Find the value for
SELECT count(strCoupon) FROM tblCoupon WHERE EmailAddress IS NULL
If it's > 999, set tblReminder.numReminder = 0
If it's < 1000 and tblReminder.numReminder = 0 then set tblReminder.numReminder = 1
If it's < 1000 and tblReminder.numReminder = -1, do nothing
if tblReminder.numReminder = 1 -- send an email and set tblReminder.numReminder = -1
if tblReminder.numReminder = 1 -- send an email and set tblReminder.numReminder = -1
(3) Make it possible to support multiple Coupon Types ($5 of a purchase of $20, free download, discount subscription, whatever). This would require another column for coupon type in both tblCoupon and tblAudience as well as a new table to describe the Coupon Types.
(4) Put limits on the number of a particular Coupon Type a person can earn in a time frame to avoid someone gaming the system.
(5) Create Coupon Types with fixed or limited-time expiration. (The website would ultimately handle if the coupon is valid or not, but this allows you to put the expiration date in your email.)
(6) Create a separate table of redeemed codes and create an endpoint so that your website can query issued codes so that someone can view their code online, or so that your CRM can record that a coupon has been issued. Nightly, remove redeemed codes from tblCoupon to keep the table clean and lean.
(7) Create a daily/weekly summary email to your Coupon Manager of all the unissued codes so they can make sure the codes are being issued at the rate forecast and so they have even more warning if it looks like they're going to run out of codes.
(8) Make it possible for your Coupon Manager to replenish the codes by dropping a file on your FTP server or by pushing new codes into your ESP via API so that you don't have to get involved in keeping codes topped off.
