2

I want to send a happy birthday email at D-14 in Marketing Cloud

My problem is that I find the right clients well but I also find clients whose with a birthday date shifted by a day compared to what I want.

I created my automation the June 16th so I should have clients whose birthday is June 30th. But when I look my DE I can see clients whose birthday is June 29th.

Is it due to leap years?

In my query I used:

WHERE DATEPART(DAYOFYEAR, CTS.Birthdate) = DATEPART(DAYOFYEAR,(getUTCDate()+14))

How I can have just customers whose their birthday is in 14 days?

0

Here's a query that I've used in the past:

select
  x.emailaddress
, x.Birthday
, x.age
, x.nextBirthDay
from (

    select
      a.emailaddress
    , a.Birthday
    , datediff(year,a.Birthday, getDate()) as Age
    , case
        when (dateadd(year,datediff(year,a.Birthday,getDate()), a.Birthday) > getDate()) then dateadd(Year,datediff(year,a.Birthday,getDate()), a.Birthday)
        else dateadd(year, datediff(year,a.Birthday,getDate())+1 , a.Birthday)
      end nextBirthDay
    from MasterDE a
    where a.Birthday is not null
) x
where 
datediff(day, getdate(), x.nextBirthDay) = 14

The inner query calculates the Age and nextBirthday for rows in the MasterDE data extension. The outer query chooses only those that are 14 days from today.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.