Tech Tip (LISTSERV Maestro) – Issue 1 – 2005

Q. How can I send members of my data warehouse Happy Birthday Greetings on their actual birthdays?

Answer by Johannes Hubert
Senior Applications Programmer
L-Soft

Follow the steps below to create a birthday greeting using a hosted recipient list and target group in LISTSERV Maestro 2.0.

1. Create a hosted recipient list with the profile fields you require, including one field for birthday. For this example, the field is called "DOB".

2. Set the input validation for that field DOB so that it is validated as a "Date", for example in the American format "MM/dd/yyyy".

3. Proceed with creating your list and add subscribers to it, taking care to input the birthday in the correct format.

4. Create a target group based on this recipient list where in the condition tree you add a condition that compares two "formulas" with each other, using the "equals (=)" comparison. The formulas are:

Formula 1: Substring(&DOB;, 0, 5)

Formula 2: ToDate(CurrentMillis, "MM/dd")

The formula tree will look something like this:

5. Create a job with recipients based on this target group.

6. In the job, define content, with drop-ins enabled, like this:

Happy Birthday &fname;!

We wish you all the best on your birthday. Being {{*Calc ToNum(ToDate(CurrentMillis, "yyyy")) - ToNum(Substring(&DOB;, 6))}} years old is an occasion to celebrate!

Sincerely,

{{company_name}}

7. Define the job to be "recurring" with a delivery time of, for example, 8 a.m and a delivery interval of 1 day, so that the job is sent out every day.

Every day the job will go to exactly those recipients who have a birthday on that day, with a nice message telling the recipient his/her age. If there are no matching records for a given day, the job for that day will be marked as failed, but the recurring job will still be rescheduled for the following day, targeted to only those recipients who have a birthday on that day.

How does this work?

The condition that defines to which recipients the mail is sent says that only those recipients will be included where the following is true:

Substring(&DOB;, 0, 5) = ToDate(CurrentMillis, "MM/dd")

"Substring(&DOB;, 0, 5)" extracts a substring from the value of the text &DOB; that starts at position "0" and ends at position "5 - 1", up to position "4" meaning the first five characters are retrieved.

Since the merged field &DOB; contains birthday dates that are of the form "MM/dd/yyyy", the first five characters are exactly the part "MM/dd" of each recipient's birthday.

"ToDate(CurrentMillis, "MM/dd")" formats the current time into a text string using the format pattern "MM/dd", the result of which will also be five characters with the current month (two digits) , a "/", and then the current day (two digits).

So if we compare the two strings returned by each formula with the equal comparison "=", the result will be true if both are the same. This means that if the month/day component of the birthday of the recipient is the same as the month/day component of the current date, we catch exactly those recipients who have their birthday that day.

To make the message more personalized, we also want to merge the current age of each recipient into the content. We do this by using a {{*Calc}} system drop-in with the following formula:

ToNum(ToDate(CurrentMillis, "yyyy")) - ToNum(Substring(&DOB;, 6))

What does this formula do?

"ToDate(CurrentMillis, "yyyy")" returns the current date, formatted with the pattern "yyyy" so it returns the year of the current date as four digits.

The "ToNum(...)" around this simply converts the text string containing the four year digits into a number.

"Substring(&DOB;, 6)" extracts a substring from the text string in &DOB; that starts at the character position "6" and extends to the end of the string. Since &DOB; contains the birthday of the current recipient always in the format "MM/dd/yyyy", the character at position "6" is the first "y" of the four-digit year number (which is the seventh character in the string, but character positions are zero-based, therefore the seventh character has the index "6").

This "Substring" extracts exactly the four year digits from the birthday of the recipient. The surrounding "ToNum(...)" then again converts this text string containing the four year digits into a number. The only thing remaining is to subtract the two number results from the two "ToNum(...)" functions from each other to get the difference between the current year and the birth year of the subscriber – the age of the subscriber.