Using Data Variables

Overview

Data Variables are a feature of Audiences that helps the Audiences become more versatile. Data Variables can be included in the Audience SQL statement as placeholder values. When creating/launching a Campaign, Data Variables can then be populated with values. This article will provide you with the tools to populate Data Variables with values to easily get more value from Audiences.

Table of Contents

Creating Data Variables

Default Values

FreeMarker IF Statements

Populating Data Variables in Campaigns

Process

Creating Data Variables

When creating an Audience, whether using the Drag-and-Drop method of the SQL method, Data Variables are available for use. This example will use the SQL method of creating an Audience.

Navigate to People > Audiences and create a new Audience. Give this Audience a name and description.

Create the SQL statement for the Audience to select the appropriate columns.

Before creating the WHERE clause of the SQL statement, scroll down and expand the Data Variables option. This is where Data Variables are established.

Click the Add Variable button.

There is no limit to how many Data Variables can be created for an Audience. After creating a new Data Variable, populated the following fields:

• Name - The name of the Data Variable. This name does not support spaces. This is the name of the variable used within the SQL queries that run to the external datasource.
• Label - The front-end display name of the Data Variable. This may differ from the Name of the Data Variable if the Name of the variable is more technical and the Label needs to be more user-friendly.
• Default Value - A starting value for the Data Variable. Can be overwritten by Campaigns for use. If not overwritten, the Data Variable maintains the default value.
• Help Text - A helpful tip that displays with the Data Variable. The Help Text is shown when clicking on a help icon next to the Data Variable.

Create a Data Variable called “stateCode”. This Data Variable will be populated at Campaign Launch to allow a Marketer to dynamically change which records will be targeted.

To include Data Variables in the SQL statement, FreeMarker must be used.

When including the Data Variable in the Audience SQL, use the following format:

${Criteria.variableName}

Complete the Audience SQL statement with a WHERE clause. The full SQL for this example is below:

SELECT
 age, address, first_name, last_name, id,
 email AS EmailAddress, locale, profile_complete, state
FROM
 city_and_glory.customer
WHERE
 state = '${Criteria.stateCode}'

The purpose of this SQL statement is to allow a User to populate the stateCode when launching a Campaign for varying results.

Default Values

When using Data Variables in WHERE statements, considerations must be made about what happens if a User launching a Campaign does not populate the stateCode. One method of addressing this is to populate a default value for the Data Variable.

Without a default value populated, then the SQL statement, when rendered, would look like:

SELECT
 age, address, first_name, last_name, id,
 email AS EmailAddress, locale, profile_complete, state
FROM
 city_and_glory.customer
WHERE
 state = ''

In the case of this SQL statement, this won’t result in a very useful Audience, as it would only return records that do not have a state listed in their record.

FreeMarker IF Statements

Another approach to address Data Variables that are not updated is to surround the WHERE clause with FreeMarker IF statements.

The purpose of the IF statement is to detect whether the Data Variable is populated or if it is empty. If the Data Variable is populated, then the Data Variable is used within the WHERE clause. If the Data Variable is not populated, then it is ignored.

Below is an example of the FreeMarker statement surrounding the Data Variable:

SELECT
 age, address, first_name, last_name, id,
 email AS EmailAddress, locale, profile_complete, state
FROM
 City_and_glory.customer
<#if Criteria.stateCode?trim != ''>
WHERE
state = '${Criteria.stateCode}'
</#if>

The ?trim function is a method of determining if a value is null. Trim removes whitespace.

If the value of the Data Variable after being trimmed is an empty string, then the code within the IF statement will not run. If the value of the Data Variable after being trimmed is not an empty string (it contains a state code), then the code within the IF statement will run.

If the stateCode Data Variable is not populated, then the SQL statement will render as:

SELECT
 age, address, first_name, last_name, id,
 email AS EmailAddress, locale, profile_complete, state
FROM
 city_and_glory.customer

If the stateCode is populated with “GA”, then the SQL statement will render as:

SELECT
 age, address, first_name, last_name, id,
 email AS EmailAddress, locale, profile_complete, state
FROM
 city_and_glory.customer
WHERE
 state = 'GA'

Multiple IF statements can be included in the SQL statement if needed.

Below is an example of a FreeMarker statement surrounding a boolean Data Variable - note that the true or false condition needs to be treated as a string by surrounding it in double quotes:

SELECT
 age, address, first_name, last_name, id,
 email AS EmailAddress, locale, profile_complete, state
FROM
 City_and_glory.customer
<#if Criteria.Georgia == "true">
WHERE
 state = 'GA'
</#if>

Populating Data Variables in Campaigns

In order to take advantage of Data Variables, they must be populated within a Marketing Campaign.

Open a new Marketing Campaign or edit an existing Campaign. Open the Settings screen and view the Audience associated with the Campaign.

If the Audience has any Data Variables, they are displayed beneath the Audience.

If the Help Text field has been populated for the Data Variable, then hovering over the question mark next to the Data Variable displays the Help Text.

After populating the Data Variables, it is important to review that the Templates are rendering correctly and Recipient Data is returned by the Audience successfully.

It is important that the users Launching Campaigns are aware of the importance of Data Variables.