Relational segments

Segment Templates let you predefine complex scenarios for segmentation from SQL queries, and then describe them in natural language sentences so that your less tech-savvy colleagues can easily use them to build segments.

In the example below, we have created a segment template that can target everyone who:

• Departs in x days (this can be in the past, in which case the number will be negative)
• Traveling in x class (Premium, Economy, etc.)

To create a segment template, proceed as follows:

• Set up the DB connection (1) - Select the database to connect to from the drop-down menu.
• Create parameter (2) - Create a parameter of the appropriate type for each of the variable criteria in your segment. Give it a name that will make sense in the segment template definition. The available types are:
  • Text input - Creates a free text field that accepts alphanumeric and special characters.  
  • Numeric input - Creates a numeric field that only accepts numbers (including negative values) and decimal points.
  • Date input - Creates a date field with the format dd-mmm-yyyy. It also offers a calendar function.
  • Custom drop-down - Creates a single-choice list. The name will appear in the segment (i.e. what marketer sees) and the list item values are what you use in the SQL query. You can choose different types of option for the drop-down; as well as text and numeric input you can also define the filter operators here.

The parameters let you create multiple segments from the same template with different criteria values. On the right-hand side, you can see the created parameters (green tags), which you can drag and drop into the SQL query. Parameters are reusable - there is no need to create another one if you need, for example, two destination parameters.

• Segment template definition (3) - This is the statement you will use for segment creation. It contains the parameters you have created for the definition, and should be written as a natural language statement.
• Underlying SQL query (4) - This is probably the most important element, as this contains the actual query used to extract the data from the databases. It will run in the background whenever you are using this segment template. It is possible to create segment templates solely with this SQL query, without adding parameters, but it would be less handy, as you would not be able to add different types of input later. We recommend that you test the query on your database first, then copy and paste it in here.
• Quick Test (5) - This function button lets you check if the SQL query is executable and syntactically correct by running the query in an EXPLAIN statement. In this statement, the variables are replaced with the correspondent dummy data (e.g. string -> "" [empty string]). The results may be different for each database engine types. In case of an MS SQL database, the query will return with an Execution Plan in XML format, which you can open with SQL Server Management Studio. If the configured database user doesn't have the sufficient privileges to run your query in an EXPLAIN statement, the query will be executed with LIMIT 1.
• Preview and Test (6) - Here you can preview what your colleagues will see when they create a segment, and can test it with real data. Select the values for the parameters and click Run. The segment should return the corresponding number of contacts from your database.
• Contact field in Emarsys and Field in query (7) - Here you map a pair of field that will identify your contacts in both databases. Typically this is the email address, but it can be anything you want as long as it is a unique identifier in all the databases.
  • Contact field in Emarsys - The unique identifier key in your Emarsys contact database (you can choose it from a drop-down menu).
  • Reference field - The same unique identifier key in your own database, which you are using to create relational segments and segment templates. This can be an alias (e.g. it’s customer_id in your Emarsys contact database, but it is id in your database. In this case you should begin the SQL query like this: SELECT customer_id as id, then have id in the reference field).

Click Save when finished.

Once you have created your segment templates, you can use them to create actual segments.

• Click Create New to start the segment creation.
• Give your segment a descriptive name that will help you find it later.
• Choose your Scenario. This is the segment template that you created earlier. When you select one, it will appear in the Segment Definition with red question marks to indicate where you need to add the criteria values.
• Click the question marks and select or enter the values for the parameter. In this example the segment targets those who traveled less than (operator) 6 times (number input) in the last 20 (number input) days, where the destination was Budapest (custom drop-down) and they departed from London Heathrow Airport (custom drop-down).
• Click Run to create (and save) the segment and display the results. The segment will now appear on the Relational Segments list. You can edit the segment later if you need to change the parameters or their values.

In order to use this segment in the application, you must add it to a combined segment (Contacts > Combined Segments). You do not actually have to combine it with another segment; it is enough just to select it as the only included segment:

This segment can now be used anywhere that supports combined segments, for example as a message recipient source or in an Automation Center filter node.