January 4

I hope this year and decade improve.

Free Money 2020 #1

I said in 2020, if a bank was going to offer me free money, I was going to take it. (Unless the deal was really awful, like Capital One which wants to tie up $10k for an insane amount of time just for $100.)

Deal #1 - A Credit Union - Open a 37-month IRA with $50 (3% APY) and they'll give you $50.  Small price to pay to double what would have otherwise just been a small amount of float.  But at 3% APY, pretty tempting to direct more money that way.

I'll be watching out for other deals as they come my way in email or postal mail.

Mail Call

I've been saying for a long time now that I'd pay the post office to deliver mail less frequently to my house, if I could just subscribe to some once-a-week delivery service for $48/year, it would be totally worth it.  Nothing urgent, valuable or time-sensitive comes in the mail. That's what email is for.   Of course, I'd still like to get packages.

Processing all the junk mail, obscuring our name and address, sorting, recycling, it's a pain and takes time.  I've tried just letting it pile up on the counter, but that's not without its own annoyances. 

I've found a compromise. Through the USPS' website, you can - for free - do short-term mail holds.  I tried one a few weeks ago. It was confusing...

* Starts On should be the day they start holding, right?  They still delivered mail that day.
* Ends On - is that the last day they hold mail, or the first day they start delivering normally again?  Not quite sure.
* I still got the daily emails telling me what mail was queued
* We got at least one notice from Amazon saying that a package couldn't be delivered (though it ultimately way)
* We got a notice from the post office saying we needed to come to the post office to pick up a package (we didn't; it was delivered)

I've also started unsubscribing from mail at the source where possible (a local realtor was really confused about us demanding to stop receiving his newsprint newsletter) and refusing obvious junk mail.  (No, SiriusXM, the 14th offer you send won't be the one that gets us to sign up.)

But just having the mail delivered less often is great.  It doesn't clutter up the house and we're still able to dispatch it to the recycling bin quickly. It does mean regularly going to their website to start a new mail hold and it does mean to expect some packages won't arrive as quickly as expected, but it's just one less source of incoming clutter. 

And that makes me happy.

2010: A Look Back

2010 was ten years ago.  I was ten years younger, I had been married for ten fewer years.  I had fewer kids. I was employed somewhere else, doing the same sort of thing, except with a lot less influence and for quite a bit less pay. I had a different car and a different address. But now it's ten years later, I'm ten years older, I've been married for ten more years.  I have twice as many kids.  Half as many counts and infinitely fewer dogs. Both of the 2010 cars are now charity donations and the 2020 cars aren't. So... yeah... happy new years. We shall see what the next year or ten holds.

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

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

Feed Sift (06/07/2019)

-1-

ARCHITECTURALDIGEST.COM -- 12 Times When Architects Defied Gravity with Incredible Cantilevers

-2-

ENGADGET.COM -- Google shows how it could make a cloud-savvy game controller

-3-

CSSFX.DEV -- Beautifully simple click-to-copy CSS effects

-4-

CNN.COM -- DMC DeLorean: The troubled past of the car that went back to the future

-5-

SETHS.BLOG -- Roads or Buildings?

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