How would you feel if your birthday was forgotten for multiple years in a row and you feel left out on your special day? That’s exactly the feeling most companies give their customers who were born on the infamous Leap Day – February 29th.
Companies often overlook Leap Day birthdays due to its rarity. February 29th happens once every four years – 2.5 times per decade. Many people never even meet a person with this birthday, so it’s easy to miss when marketers set up their birthday campaigns.
How about your birthday or anniversary campaigns?
Did you think about leap years? If not, it is time to change that.
You may think that the group of people affected is probably very small – this might be true, but spending just a little time to include Leap Day birthdays in your marketing campaigns can generate great reactions from your customers!
If you take that special date into account but only send an email the day February 28th with your usual birthday wishes plus a coupon, it won’t have a huge effect. However, if you combine that with dynamic content in your email, you can create personalized communications that drive action.
As an example,
“We’d never forget your birthday, even if there isn’t a Leap Day this year. We’re sure others do, and therefore we celebrate your special day even more – instead of the usual 10% off, you’ll get 5% on top of that. Happy Birthday!”
would put a smile on any customer’s face and strengthen the customer relationship for sure! And isn’t that what it’s all about?!
How can you select the relevant people in your birthday campaign segmentation?
There’s three rules that allow us to find out if a given year is a leap year:
- If the year is evenly divisible by 4 – check step 2; if not, it isn’t a Leap Year
- If the year is evenly divisible by 100 – check step 3; if not, it is a Leap Year
- If the year is evenly divisible by 400 – it is a Leap Year; if not, it isn’t a Leap Year
So after following this criteria, if the current year is not a leap year, then you need to check your audience for people born on February 29th already a day early.
Note: If you’re wondering why a year divisible by 100 but not 400 isn’t considered a Leap Year, the length of the solar year is slightly less than 365.25 days. By skipping the leap year every 100 years (but not every 400 years), we can keep our calendar in closer alignment with the solar year. For example, the year 2000 was a leap year (divisible by 400), but the year 1900 was not (only divisible by 100).
And this is how it’s done using SQL
In order to check if something is evenly divisible you can use the so-called “Modulus” operator or Mod-function in SQL. This will return the remainder of one number divided by the other.
Everything else that needs to be done is comparing the date parts (month, day and year) to the ones of today (using the getdate function) – once for the current date and in case we found out that this year isn’t a leap year, we also check for February 29th if it is still the 28th. That’s all that’s needed.
SELECT
SubscriberKey,
Birthdate,
Email,
FirstName,
LastName
FROM
Your_Segment
WHERE
(
(
DAY(Birthdate) = DAY(GETDATE())
AND
MONTH(Birthdate) = MONTH(GETDATE())
)
OR
(
(
CASE
WHEN DATEPART(year, GETDATE()) % 4 <> 0 THEN 0
ELSE
CASE
WHEN DATEPART(year, GETDATE()) % 100 = 0 THEN
CASE
WHEN DATEPART(year, GETDATE()) % 400 = 0 THEN 1
ELSE 0
END
ELSE 1
END
END
) = 0
AND
DAY(GETDATE()) = 28
AND
DAY(Birthdate) = 29
AND
MONTH(GETDATE()) = 2
AND
MONTH(Birthdate) = 2
)
)
Note: Depending on the relevant time zone and in which time zone the dates are, you might need to use the “AT TIME ZONE” function in combination with the getdate function call. For better readability this is omitted in the example.
If you use DESelect, follow this easy step:
When creating a new Selection in DESelect, all it takes is adding an advanced custom value to the Target Data Extension. Just copy and paste the SQL code into the field. Here’s the step-by-step.
- Add New Value
- Name your value – in this case ‘Birthday’ makes sense
- Select the option to ‘Apply formula to a field’
- Select the option to ‘Apply any function’ – this will let you add freehand SQL code
- Paste above SQL code into the open field and save
It’s that easy!
Conclusion
Even if it won’t have the biggest business impact, going the extra mile to show a subscriber you care builds trust. And all that with just a little SQL required in addition to what you normally do for your birthday segments – and as you now have the code in this article it is now a quick fix.
Markus Dang
Markus is a guest author at DESelect. He's a Marketing Cloud Unit Lead & Solution Architect. He is also a Salesforce Marketing Cloud Champion.
