It is your birthday. How to Ensure Marketing Cloud Doesn’t Skip Leap Day Birthdays from Offers

It is your birthday. How to Ensure Marketing Cloud Doesn’t Skip Leap Day Birthdays from Offers

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:

  1. If the year is evenly divisible by 4 – check step 2; if not, it isn’t a Leap Year
  2. If the year is evenly divisible by 100 – check step 3; if not, it is a Leap Year
  3. 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.

  1. Add New Value
  2. Name your value – in this case ‘Birthday’ makes sense
  3. Select the option to ‘Apply formula to a field’
  4. Select the option to ‘Apply any function’ – this will let you add freehand SQL code
  5. Paste above SQL code into the open field and save

leap year birthday sql code

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

Stay Connected

Stay Connected

Reach the most targeted<br/> audiences in half the time

Reach the most targeted
audiences in half the time

Deselect icon
Subscribe to the Newsletter
Receive Salesforce Marketing Cloud tips,
tricks, and DESelect updates

By clicking the “Subscribe now” button, you agree to the DESelect Terms of Use and Privacy Policy.