I am new to salesforce marketing cloud. How to send an email, three days before birthday date using SQL query or using data filter Automation studio. Could anybody please help me.
4
-
@GS650X, Thank you for replying. But i need to using SQL Query.– ShriyaCommented Feb 13, 2019 at 9:39
-
Use SQL to subtract the birthdate minus 3 days (you use dateadd for this with a minus to the adding part). docs.microsoft.com/de-de/sql/t-sql/functions/… You store this value into a new dataextension and dont manipulate the real birthday. Then you create a journey and take this new date as "anniversay of" inside the filter part od the entry dataextension and you are ready to go.– Johannes SchapdickCommented Feb 13, 2019 at 9:52
-
1can also use automation studio with a filter activity and User Initiated Send to handle this.– GortoningtonCommented Feb 13, 2019 at 15:34
-
@Gortonington, I have done this task using automation studio with a filter activity. But I need to done using SQL Query in salesforce marketing cloud.– ShriyaCommented Feb 14, 2019 at 5:54
Add a comment
|
1 Answer
The SQL you are looking for is below:
SELECT SubscriberKey
, Birthdate
, ..yourfields..
FROM [yourDE]
WHERE CONVERT(date, Birthdate) = CONVERT(date, DATEADD(day,+3, GETDATE()))
/* Target: yourBdayDE */
/* Action: Overwrite */
This query will pull those who have an birthday exactly 3 days in the future. Please notice I used CONVERT() to change the fields from DATETIME to just Date. This way it will only match the day, so the time field will not mess with your results. Also note that I have it with the action of 'Overwrite'. This is so that only those relevant records will be inside of this DE, so you do not need to worry about sending it to anyone who has already received it.
From there you can either push this into Automation Studio or JourneyBuilder.
Automation Studio:
Step 1 - Query Activity
Step 2 - Validation Activity (to check if [yourBdayDE] is empty)
Step 3 - User Initiated Send to send out the email to [yourBdayDE]
Journey Builder:
Create a Journey and use [yourBdayDE] as the entry source. Once the person gets added to this DE, they will then be pushed into your Journey and follow the logic and actions you have set in there.
answered Feb 14, 2019 at 13:55
