33. Create an email campaign with expiration date

Note: The scenario below uses data from the DESelect Demo Data Package.

Scenario: 

You want to create a customer birthday email campaign with a call-to-action that is only available for a limited amount of time. You already have the birthdate of all the customers on your Customers Data Extension (although, the birthdate's date values include the year that needs to be converted into the current year). In addition, you also want to add a new field to your Target Data Extension that contains the call-to-action expiration date which is 45 days from the customers' birthdate.

In this case, we are going to create 2 selections to achieve the wanted result.

Note: This scenario uses the feature Custom Values, which is available in DESelect Segment Advanced.

Let's proceed!

Selection 1 - Converting the birthdate values to the current year's birthdate values.

1. Selection Criteria

On the selections overview screen, click on New to create a new selection. 

Drag Data Extension DESELECT_DEMO_Customers to the Selected Data Extensions section. 

2. Target Definition

Hit the Create Data Extension button, enter a name, e.g. Customers with current birthdate, and press Save.

• Hit Add all fields from DESELECT_DEMO_Customers.

Now we're going to add a Custom Value:

• Under Custom Values on the left, click Add new value.
• Under Name, enter: "Current year's birthdate" and hit Next.
• Choose Type: Apply formula to a field.
• Choose Type: Apply any function and set the below setting.
  • On the field type, choose Date.
  • On the syntax board, enter the below syntax.

DATEADD(YEAR, DATEDIFF(YEAR, "DESELECT_DEMO_Customers"."Birthday", GETDATE()), "DESELECT_DEMO_Customers"."Birthday")

• Hit Save.
• Add the Current year's birthdate custom value field to the Target Data Extension fields.
• Hit Save Data Extension and Create.

3. Preview

When you click Run Preview on the Preview screen, a results table will be shown with fields.

• Id, First Name, Last Name, Email, Birthday, Language, and Country from DESELECT_DEMO_Customers.
• Current year's birthdate from Custom Value.

Selection 2 - Transform the current year's birthdate values to the call-to-action expiration date.

1. Selection Criteria

On the selections overview screen, click on New to create a new selection. 

Drag Data Extension Customers with current birthdate to the Selected Data Extensions section. 

2. Target Definition

Hit the Create Data Extension button, enter a name, e.g. "Customer with the call-to-action expiration date", and press Save.

• Add Id and Email and Current year's birthdate fields to the Target Data Extension fields.

Now we're going to add a Custom Value:

• Under Custom Values on the left, click Add new value.
• Under Name, enter: "Call-to-Action Expiration Date" and hit Next.
• Choose Type: Apply formula to a field.
• Choose Type: Transform date and set the below syntax.
• On the first drop-down list with the label Data Extension choose Customers with current birthdate.
• On the second drop-down list with the label Field choose Current year's birthdate.
• On the Add field, enter 45 and on the date part drop-down list choose Days.
• Under the Format label choose Date to include only the date.
• On the convert timezone button, set it as disabled to keep the same time zone.
• Hit Save.
• Add the Call-to-Action Expiration Date custom value field to the Target Data Extension fields.
• Click Save Data Extension and Create.

We now have created an extra field in our Target Data Extension that will contain the call-to-action expiration date. The logic defined above will create a custom value for each row on the Current year's birthdate column adding 45 days on each value as the call-to-action expiration date.

3. Preview

When you click Run Preview on the Preview screen, a results table will be shown with fields

• Id, Email, and Current year's birthdate from DESELECT_DEMO_Customers.
• Call-to-Action Expiration Date from Custom Value.

In the results, the date in the Call-to-Action Expiration Date field is 45 days later than in the Current year's birthdate field.