Adding Segmentation Criteria to a Database Query

Overview

Segmentation is a powerful tool within Accelerator, allowing users to create variables within their SQL database queries with matching Segmentation fields for customizing those queries from the Campaign level.

Adding Segmentation to a Database Query can eliminate the need for Campaign authors to touch any SQL or interact with the database directly, because they can still build segmented recipient lists using the Segmentation Criteria with a Recipient data type query.

The end result for Segmentation added to a Query is the availability List Segmentation Criteria like this on the Settings page of a Marketing Campaign, when the segmented Query is selected as the List:

The first thing to do is to add FreeMarker code to the SQL of the Database Query.

For our example, we want to be able to segment our recipient list on whether or not the recipient is a Loyalty Member. With this in mind, the FreeMarker we will use is ${Criteria.LoyaltyMember}. (Note that each value is prefixed with the label “Criteria”)

Segmentation Criteria

You can add as many or as few FreeMarker variables to your SQL query. Once they’re in place, you must then add corresponding Segmentation Criteria.

When starting with our first Segmentation Criteria, we’ll select the Add Criteria button and define the name, label, default value, and help text for our Segmentation. For example:

Using the blue plus or minus signs to the right of each Segmentation Criteria, more can be added (plus) or existing ones can be removed (minus).

After saving the SQL and Segmentation Criteria, it’s recommended to test the newly created query. At this point you can identify if the FreeMarker populates with the correct default value. To test the query, click the drop-down arrow on the right side of the Test button and select Preview SQL. [screenshot float right]

The result of this test will show you the complete SQL, including all default values for the FreeMarker variables with Segmentation Criteria.

Once the FreeMarker and corresponding Segmentation Criteria are in place, they will be usable on the Settings page of a Marketing Campaign using that Database Query for its Recipient List.

You can then mouseover the tooltip to view the help text:

You should now be able to easily view your current Segmentation Criteria settings on the Overview page of the Marketing Campaign. From here, you can refresh the Approximate Size of the list after making changes to Segmentation Criteria values. The default value will automatically populate, and the resulting recipient list will be recipients who are Loyalty Members (LoyaltyMember=true). If we want to send our Marketing Campaign to NON-members instead, we can change the “Loyalty Member?” value to false to bring in the non-members with LoyaltyMember=false from our database.

Campaign Settings will display all Segmentation Criteria created for that query, but they will only function when the corresponding FreeMarker is in SQL query. If the Approximate Size doesn’t change as expected when Segmentation Criteria values are altered to produce a different recipient list, it may be necessary to verify the FreeMarker in the Database Query.