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


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

Saturday, May 04, 2019

who am I today?

Who I am, my identity, is something I've tried to understand and write about over the years. Clearly, it hasn't been something I've made a priority, as it seems like so many posts start out this exact same way.

So, who am I today?  Well, it's a Saturday.  So I'm a coffee drinker and my Pandora station of the day is Saturday Morning Coffee. With the wife and daughter off seeing the latest Avengers movie, I've been at home with my son.  He's had a chill day of watching acapella groups on YouTube and hanging out outside.  At the moment, he's gone over to visit his grandma, so I've grabbed the laptop, gone out to the patio and thought I'd just write a little bit.  The skies are blue, there's a breeze, the sun is out and the palm trees are rustling and the birds are chirping.  Going to sit down at the table, I scared a squirrel who was hanging out on the table.  Beautiful Southern California day.  I've shaved, gotten some things done off my to do list and also not been stressed out or anxious about all the stuff I think I need to do.  Have knocked a few things off my list that have been there since we moved.  The list will always exceed what I can do, but I'm not going to stress about it.

So, who am I today?  I'm chill.  I'm going to get some stuff done, but I'm not going to make my whole Saturday into a crush of self-imposed busyness.  I'm going to stop eating cookies and banana bread, have some carrots and veggies and drink my water and take my vitamins.  And prepare for the noise that will herald the return of Lori and Ellie so that it doesn't feel like an interruption or disruption to my plans.

Happy weekend, friends!

Wednesday, May 01, 2019

Say My Name

It's always bothered me when people call me "Jim" - it's not my name. 

My dad, also goes by Jim. So when people would call me Jim, it would bug me, but I knew most people wouldn't know this, so I'd just have to be careful and tactful in clarifying my name.

But when someone called me Jim today, it still bothered me. This person, somewhere else on the planet, I'll never meet them in person, saw my post on Social Media, complete with my name, and decided to call me a different name.

Why? It can't be laziness, because then you could use the silly abbreviation Jms.  (Wm. for William makes sense.  Jms. seems silly.)  It wasn't Twitter so there wasn't a character-count.

Is it a sense of familiarity? "Hey, pal of mine, Jim, Jimbo, Jimmy, Jimster, the Jiminator..." Do people think it's endearing? It's not a nickname, it's someone else's name.  I've told you, plain as day on my social profile, my name. You don't make me think we're good buds if you call me by some other name that I haven't told you I go by.  (I love how some languages go with "I am called..." vs "You can call me...")  So.. James... Jamebo... Jamester... the Jamesinator... making copies.  (Who makes copies anymore?)

Autocorrect?  Good heavens, I hope not.  You need to dramatically break that phone in half and throw it in a lake while saying something really cool.  Cue: CSI: Miami scream.

So, yeah, if you're calling me Jim, you're doing it wrong.

Saturday, March 16, 2019

An Open Letter to the @Microsoft @Office @MSExcel team

click to enlarge

Avoid AI #Recruiters - @wadeandwendy from @randstad is no Alexa

Randstad is a third-party recruiting firm companies use when they can't or aren't interested in directly recruiting.  Their Twitter header boldly proclaims "human forward." though I'm not sure what that means.

click on any image in this article to enlarge

Randstad themselves have outsourced (fourth-party?) the communications of recruiting to Wade and Wendy, which bills itself as the first AI Recruiter.  In reality, it's not.

First, their emails have MailChimp code showing.  I've tweeted this to them but they continue to send me emails with it, so maybe there's no I, A or otherwise, there.

Second, the emails only have the option for "Yes" - there are FAQs and even an email address, but it doesn't appear that the email box is monitored.  You'd think an Artificial Intelligence company would have something in place to handle incoming messages.  I wrote to them to say that the role wasn't a fit for me, the location was all wrong and even more importantly, the company they were recruiting for wasn't a company I'd ever consider working for.

I never heard back.

Until I got a reminder from Wendy.

So, I looked more closely at the email.  (This is the second company they emailed me about - I would work for Philips - I've stared at their monitors in hospitals on so many occasions that I have a strong appreciation for them.  My first portable CD player back in 1990 - took 10 AA batteries - was also a Philips.)

No real way to select "No" in the email so I clicked yes.  I was prompted to register with my LinkedIn, Google or Microsoft accounts or to create a Username and Password.  So, signed in/registered, and then I met Wendy.

Wendy is a glorified chatbot. Maybe not even glorified.  Maybe not even a chatbot.  More of a progressive form.  Here's my chat:

Red Circle = this was my only choice.

Blue Circle = I had yes/no options.

Green Circle = I had multiple choices, perhaps as many as 9, but it captured the first one and then moved on.  You'd think an AI would want to collect as much data as it could, allowing me to select multiple options, such as Company, Role, Location, I'm Happy Where I Am, and so on.  If I could only pick one, I would have picked Company.

However, I clicked on Role first, so that's what it selected.

Never once did it prompt me for any free-entry typing.

The survey link at the bottom isn't linked - probably to reduce the number of submissions telling them their product is a steaming pile of dog droppings.

Which makes it all the more interesting that Wendy is again writing to me today about the same role at another company.  The Artificial Intelligence Recruiter wasn't paying attention when I told it that it was the wrong kind of role.  

So, with no "No" option in the email, I've clicked Yes.  I'm now asked to sign-in again.  The Artificial Intelligent Recruiter has forgotten who I am.

So, went through the "chat" - the first thing I notice is that the content and prompts are 100% the same. The Artificial Intelligent Recruiter has forgotten it's already had this conversation with me and so it re-introduces itself and tells me the exact same thing it told me last time.  (Which wasn't interesting the first time and wasn't new this time.)

While I wanted to click "Location" or "Other," for the sake of this experiment, I went with "Role" - we'll see if Wendy is capable of learning of it will keep sending me Project Manager roles.  (Unless this post gets me suppressed from their mailing list... yes, please.)

From now on, if I get any more of these, I'm going to click "No" with the reason "Other" - and if it lets me type, I'm going to tell them I'm not interested because of Wendy.

1. made or produced by human beings rather than occurring naturally, especially as a copy of something natural, not existing naturally; contrived or false.
synonyms: synthetic, fake, false, imitation, mock, simulated, faux, ersatz, substitute
2. insincere or affected.
synonyms: feigned, insincere, false, affected, mannered, unnatural, stilted, contrived, pretended, put-on, exaggerated, actorly, overdone, overripe, forced, labored, strained, hollow, spurious
3.conventional as opposed to natural.
(from Google)
Wendy leaves a bad impression and suggests if recruiting is boiled down to a crappy form-based interface, it's only going to recruit people willing to suffer the crappy form-based interface.  (CyberCoder's field-merge approach or LinkedIn's InMail are much better because if you reply, you're immediately talking to a person.)

Saturday, March 09, 2019

User Experience: Does your "flow" make sense?

I recently read an article on jobs to avoid.  In one case, it said "avoid job x as it's projected to be outsourced more and more and instead try job y."   I thought to myself... how odd... job x and job y are the same thing... aren't they?

So I searched "x vs y" on Google.  The top result was in a box and based on the snippet, looked to be exactly what I was looking for.

I read the article, a user-generated submission and thought it was spot on and confirmed my theory - x and y are the same thing and this other article is written by someone who doesn't know what they're talking about.

At the end of the article was an opportunity to provide feedback:

So I clicked "I found this answer useful."

Immediately, I was taken to a new page that simply had this (plus their standard header and footer):

I immediately hit the back button because it was not what I was expecting.  Had I clicked the wrong thing?  Nope, trying again resulted in the same screen.

What have they done wrong?

They have failed to set expectations.  In soliciting feedback, there's no indication that an account is required (or that one will need to be created to provide feedback).   This could easily be solved with the addition of a few tweaks:

1. Replace the signup page with a popup/overlay modal so you never leave the page you're on.
2. Indicate on the signup prompt that an account is required to leave feedback.
3. After "Did you find this answer useful?" simply add  "(Create account or signin to leave feedback)" if the user is unauthenticated.

Even if all they had done is step 3, they'd probably get more signups.

I bet if they were to look at their page analytics, they'd see traffic come in from Google, read an article, click on the feedback buttons, see the sign up screen, go back, click on the feedback buttons, see the signup screen again and then the session ends.

The lack of clarity is costing them the chance to create a relationship with a brand new user who's never heard of them before but would have been acquired simply by the nature of the user-generated content.

Saturday, January 05, 2019

2019 Charities

I have a small house and simple tastes, except when it comes to technology (where stuff is exacting and expensive.)  As such, it's been said that I'm difficult to shop for... that just getting me another Starbucks gift certificate isn't very fulfilling for the giver.

For several years now, I've included charities on my Amazon Wish List - for the gift-givers in my life, it gives you a way to honor me and do good at the same time.  In 2018, I decided that I wanted to be thoughtful about the charities I focused and also give some more context about why I chose them and with my birthday fast approaching, I thought I'd make it an annual way to highlight a few charities I care about.  I'm keeping the spotlight on the two I mentioned last year while adding a third.  All are tax-deductible organization with 503(c) status.

Autism Society (
The Autism Society works with local, state and federal lawmakers to make sure people on the Autism Spectrum and their families (and the challenges they face) are understood, represented and where necessary, protected, particularly in the areas of fair hiring practices.  As the parent of a child on the Spectrum, I love to know there are people who have devoted their lives to fighting for my son in ways I can't.

Burbank Arts for All (
I believe arts are an important part of educational formation amongst children, but it is sadly something that gets cut when budgets get tight because it's harder to show the impacts, compared to subjects like reading or math.  Beyond just the beauty and creativity we get to be immersed in, there are many studies that suggest arts education has benefits to those other subjects.  BAFA supplements art education in our local school district. (I learned last week that our son's class was a beneficiary of a BAFA grant - parents at his school pay for a local dance studio to teach dance to all the students, a BAFA grant doubled the amount of time the studio spent working with my son and his classmates.)

Women of Email (
It's often said that Email Marketing isn't a field that anyone sets out to enter, but that it's a calling that one falls into, often accidentally. There are few (if any) formal education opportunities in the field.  Often, we're left to our own defenses to figure out what we're doing and then keep up with what's changing.  There are some great companies that help with ongoing education, but there are some companies that actively work against us (looking at you, Google and Microsoft) and it's often overshadowed by web development and not well-understood by companies, leading to challenges when it comes to recruiting or applying for positions.  A few years ago, someone observed that while conference attendees seemed to be pretty evenly split, a majority of speakers at Email Marketing conferences were men.  This profit was formed to raise the collective of profile of women in our industry and assist with everything from career growth to salary negotiations as well as making sure women are equipped and encouraged to "take to the stage."