I'm taking a quick break from my series on onboarding new subscribers to share with you a programming pro-tip that has made me a better email marketer.
Many email marketing products, including my tool of choice – ConvertKit, don't make it really easy to answer what should be a pretty simple question...
"Who are all the people on my list who became a customer in 2020?"
The reason this question is hard to answer is because the way most people would classify, or segment, a customer would be to tag them with "customer".
...But you can't query based on when a tag was added to a contact.
So while you could fetch a list of everyone tagged "customer", you're probably going to need to wrestle with importing a bunch of fragmented CSV sales reports to figure out who, actually, became a new customer in 2020.
It becomes really messy.
What becoming a database programmer taught me about email marketing
The cardinal rule of good database design is to shy away from using booleans, or true's and false's.
What seems like a lifetime ago, back in the mid-2000s I was teaching myself a nascent web framework called Ruby on Rails.
One thing I learned to do, which has stuck with me ever since, was to use *_at columns when setting up your database tables.
So instead of customer = true/false to determine if a user is also a customer, I'd instead use customer_at and set the value to be the timestamp they became a customer.
This meant that if I wanted to see if someone was a customer, all I would need to do is see if something was set in the *_at database column.
But I could go further, and query that column – i.e. give me everyone who has a timestamp between 1 January 2020 and 31 December 2020 – to find everyone who became a customer between those dates.
Pretty clever.
And I haven't looked back since.
How to do this when segmenting email subscribers
How you'd do this fluctuates from product to product, but since most of you reading use ConvertKit (31.8% 😎), I'll show you how to do this with that platform.
It should be easily portable to your tool of choice.
The simplest and most non-invasive way of doing this is to simply set up an automation that waits for your "Customer" tag to be applied, and then sets a customer_at custom field with the current timestamp.
The Liquid you'll be using is {{ "now" | timestamp }} – which will come out as something like 1620118819. The number will actually be the number of seconds since 1 January, 1970 (known as the "epoch time", or UNIX timestamp)
Geeky, yeah.
But powerful, because since it's a number and not something like "4 May 2021", you can safely do greater than or less than comparisons.
How to query the right subscribers
Once you've set this up and have it running for a while, you can then start to use this data when segmenting email recipients.
Example #1: To email everyone who isn't a customer
Sure, you could just email everyone who isn't tagged "Customer" (since in the above example I'm still waiting for that tag to get applied), but if you're a die-hard like me and don't use any tags for segmenting, it's as simple as...
Example #2: To email customers who became a customer before or after a specific date
Head on over to https://www.unixtimestamp.com/
This is a great website where you can take human dates (like 4 May 2021) and turn it into a timestamp.
Copy the Unix Timestamp and then set up a greater than or less than check against your customer_at field.
Example #3: To email customers who became a customer within a range of dates
Finally, targeting everyone between two dates is just a matter of setting up your recipient segmentation to look for both people who became a customer after a timestamp and anyone who became a customer before another timestamp.
Here's how I'd target anyone who bought within the last 365 days:
"There has to be a better way..."
And there is!
They're called Events.
But, unfortunately, most email marketing platforms don't have them – or if they do, they tend to be half baked.
In a perfect world, you wouldn't have a "Customer" tag.
You'd have a bunch of "Purchase" events, and each event would have data attached to it that included when they purchased, what they purchased, what they paid, and so on.
Figuring out if someone is a customer is as simple as querying for any "Purchase" events on a contact.
When did someone become a new customer? Grab the first "Purchase" event and looks at the date it occurred.
Who's bought more than 3+ times in 2020? Look for 3 or more "Purchase" events within 2020.
If you're like me and like to splice-and-dice your subscriber database for fun and profit, then definitely join with me in putting the pressure on your email marketing platform to support both the storing and querying of events.
But, until then, get comfortable with replacing your tags with *_at custom fields 🚀
