Saturday, October 26, 2019

Coupons in Email Marketing

If you've worked with email long enough, eventually you've come to a point where you needed to send a coupon code to a group of users.  The easiest way - your CRM team provides you with pre-assigned codes.

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 aware 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 and numRow 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.  

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

(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 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.



Sunday, June 02, 2019

Dystopener

I lie about my age. If people knew I was born on The Day, I'm positive I'd be banished from our community, to the wilderness to fend for myself.

Don't get me wrong, I could totally survive on my own, even within the community, I have been fending for myself for years. Well, if you don't count my older brother Ben, though I don't, I rarely see him these days and he pretends not to know me.

Things are livable now, but the early years were the hardest. Miss Maureen tries not to dwell on it, but some days when she's feeling particularly sad, she tells us about The Day. People have tried to give it other names, but it seems distasteful, so mostly we just call it The Day.

I still have some canned food, but I know at this point, everything is expired. The cans are rusty, the labels disintegrating and most worrisome of all, more and more are starting to bulge. Dad said to avoid bulging cans, that eating from them could bring pain worse than the feeling of starving, though that's hard to imagine.

So, I ration. Some days there isn't much to eat and I go to bed early with a strong headache and my arms wrapped tightly around my grumbling stomach, but there hasn't been a day yet where I haven't had anything at all to eat. I could open more of the cans, but I'm scared to find out what happens when they're all gone.

The saving grace is my vegetable garden. When people realized life as they knew it was over, they raided the grocery stores and restaurants first. During one of his foraging trips, my dad had encountered a group planning to break into a nearby hardware store. Eventually it would be looted for building materials, but their aim was the seeds, potting soil and plants.

So my garden guarantees I'll have food, as long as I have access to water. But, each summer, it gets harder and harder, the period without reliable access to water gets longer and longer. Soon the food will be gone and the water, too. Every summer, people die from the heat and lack of water.

I will not be one of them. I will have to leave. Eventually. Sooner than I'd like.

~~~
(How I'd open a dystopian novel.)