Creating relational segment templates
The Segment Template creator is not available to clients with a fully-managed service.
Self-service and hosted self-service clients can find it under the Add-ons > Relational Data > Segment Templates tab.
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, check the following:
- Segment template status - The label displays the current status: New, Draft, or Ready.
- Set up the DB connection - Select the database to connect to from the drop-down menu.
- Save Draft button - You can save unfinished templates, marked as drafts. The Draft status indicates that these are work in progress templates. Draft templates are not validated and can not be used for segmentation yet.
- Segment template definition - 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.
Create parameter - 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 integers (including negative values).
Please note that you cannot use decimal points in Numeric input fields. If you need to use decimal points, select the Text input parameter type instead.
- 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.
- First character must be a letter character.
- Alphanumeric characters are allowed.
- Space character is not allowed, use symbol underscore( _ ) instead.
- Underlying SQL query - 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.
Test Query - This function button lets you check if the SQL query is executable and syntactically correct by running the query in an
EXPLAINstatement. 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
EXPLAINstatement, the query will be executed with
Contact reference field and Reference field - 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 reference field - 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. You can use an alias too (e.g.
customer_id), but in this case you should begin the SQL query like this:
SELECT customer_id as id, then have
idin the reference field.
- Preview and test - Here you can preview what your colleagues will see when they create a segment, and can test it with real data.
When you use timestamp and date/time be aware that the displayed result may be in a different timezone than you specified in your database. This does not affect how the data is stored or used when Emarsys calculates your segment.
The Preview and test function can only display a maximum of 10 results. To fine-tune the results, we recommend using multiple conditions.
In case of receiving an SQL syntax error, even though your SQL query is correctly formulated, then rerun the Preview and test to check your code again. The Platform might show you a false positive error message based on its cache for the previous incorrect state.
- Click Run when you selected the values for the parameters. The segment should return the corresponding number of contacts from your database.
- Click Save to finalize it when you finished editing the template and satisfied with the preview and the test results. In the saving process, we validate the query and set it as Ready, if it is correct.
Ready templates are available for segmentation.
Creating relational segment templates for specific connection types
This section is valid for the following connection types:
- SAP HANA Cloud
- SAP HANA On-premise
For the most part, these connections work the same way when creating segment templates as the other Relational Data connections.
The default is full uppercase for names for fields, tables and so on. If you have lowercase characters in the field names or table names, you have to put them between quotes.
You do not have to use extra quotes in case you want to use a value in the parameter which has lowercase characters.
For example, here we created a custom defined list, and the values do not have to be in between quotes, even though the value contains lowercase characters:
When you write string values in a query you still have to use single quotes as you would normally:
Creating relational segments
Once you have created your segment templates, you can use them to create actual segments.
To create a relational segment, proceed as follows:
1. Select Contacts > Segments, then click Create Segment.
2. Click Standard Segment, then choose Relational segment in the pop-up.
3. Give your segment a Name and add a Description to it.
This can help you identify segments easily on the Segments page.
4. Select the Template that you previously created.
5. Click the fields highlighted in blue and select or enter the values for the parameter. In the example above the segment targets those contacts who will travel with
Business (custom drop-down) class to
Singapore (custom drop-down) in
7 (number input) days.
6. To create (and save) the segment and to display the results, click Save & Apply. The segment will now appear on the Relational Segments list.
Currently, Send Time Optimization does not support campaigns which use relational segments and personalization with relational data at the same time. In such cases, do not enable STO as contacts may be excluded from the launches.
- Segment results are cached for 10 minutes. So, if you run the same segment again within 10 minutes, you will get the previous (cached) results.
- You can edit the segment later if you need to change the parameters or their values. For more information, see Editing segments.
The duration of creating Relational Data segments - FAQ
What is the time limit for Relational Data segment template queries?
Although the whole segment has to be finished within 20 minutes (segments that exceed that threshold will time out), there are multiple tasks to be done before and after the query. Therefore, we suggest to keep the query run time below 15 minutes.
Where can I check how long it took to run my segment?
After you created a Relational Data segment from a template, you can check the list in the Segments menu to see how long it took to create the whole segment.
This time duration is the time needed to create the segment, so not only the query run time.
Contacts > Segments
Also, you will get a notification if your segment query time is more than 15 minutes. In every hour Emarsys checks the previous runs of segments, and if the query was slower than 15 minutes, you will be notified once. You will not be notified again in the next 7 days about the same segment.
When you run the segment on the segment template creation page, there is a 1 minute timeout. Even if that times out, that does not necessarily mean that the segment will time out as well.
What can I do when a segment template query is too slow?
Creating a well-optimized segment query requires deep understanding of the type of database you use, the data you store in your database, the use-case you are trying to create as well as general understanding of query optimization.
We strongly recommend consulting an expert if you experience performance issues.
When creating a segment template, always consider the following:
- How large will the result set be?
- How large is the dataset the query scans to determine the results?
- How often the query is executed?
After you considered these, you can decide:
- Whether to use indexes: Depending on the type of database you use, this might be different, but generally you should consider using indexes. Most of the time with larger datasets, indexes can help performance.
- Whether to use functions in the where clause: Some functions can increase query time significantly because they can result in unintended table scans. Before using them we recommend to research their performance impact.
- What you really need to include in the query: Avoid to use select *. Instead, include the specific columns that you need individually.
- Whether to use join instead of correlated subqueries: Most of the time when operating with larger datasets, joins have better performance than correlated subqueries.
- How to use joins: Joins can be time-consuming as well. Do not join unused tables, and always try to join on indexed fields.
- Whether to use wildcards: Using wildcards will definitely slow down your query, especially for huge tables. Try to avoid it if you can.
Segmentation troubleshooting for specific connection types
This section is valid for the following connection types:
- SAP HANA Cloud
- SAP HANA On-premise
The SQL syntax is incorrect. Please make sure your query contains not errors and try again.
If the field name contains a lowercase character and you do not use quotes around it, the error message will say that your syntax is incorrect. Use the required quotes and try again.
One or more tables or views cannot be found in test_snowflake. Please make sure you have the right connection selected and you are referencing existing tables or view, then try again.
If the table name contains a lowercase character and you do not use quotes around it, the error message will say that the table does not exist or you do not have permission to use it. Use the required quotes and try again.