SQL templates
Two types of SQL templates are available in enKod – for segmentation and for dynamic content. Segmentation templates allow for the deepest possible filtering of the database, using any customer data that can be stored in tables. SQL for DC is typically used to substitute required products or other personal information into emails. enKod supports PostgreSQL syntax. You can learn more about query syntax and SQL features in the freely available documentation.
You can see the practical use of enKod SQL templates in our client’s case.
SQL for segmentation
Templates of this type always return a list of contacts in the response. SQL for segmentation can also be used in segment conditions.
To create a SQL template for segmentation go to the tab Data ➔ SQL templates ➔ Create ➔ SQL for segments
Enter the SQL template name, description (if necessary) and the query body itself.
You can see the list of contacts matching the conditions after clicking the “Run Query” button.
Parameters in templates are needed to unify any standard queries and allow you to change the body of the query directly from the segment creation.
For example, if you are organising events, for each event you have a segment of participants selected in a certain way using a template. The general syntax of the template is the same for each event, only the event identifier changes. To avoid creating a separate template for each segment, you can set an empty parameter in the template, which can be filled with the corresponding identifier value directly from editing the segment.
The table with participants will look like this
Create a template for segmentation with the parameter
You can use a template in a segment as follows
For the SQL template for segmentation to work correctly, the column type in the data table must be “email”, “phone” or “sessionid” not text or other. Only one column must be selected in the template.
SQL for dynamic content
Templates of this type return fields from a table that match the specified conditions. They are used in emails for personalisation and trigger mechanics.
To create an SQL template for a DK go to the tab Data ➔ SQL templates ➔ Create ➔ SQL for dynamic content
Enter the name of the SQL template, a description (if necessary) and the query body itself.
Do not use the @ symbol in comments in the body of an SQL query.
You can see the list of matching fields after clicking the “Run Query” button.
You can also use parameters in the templates for DCs.
Viewing the SQL template when creating a segment
For convenience, you can view the SQL template body itself in the segment. To do this, click on the corresponding button after selecting the template. You cannot edit a query from the segment.
Setting the symbol accessing SQL template parameters
The @ symbol is used by default to access a parameter in an SQL template.
Some SQL queries may require the @ symbol for operations other than parameter invocation. If this is the case, you can customize the use of a different character for handling template parameters through Data ➔ SQL Templates ➔ gear icon, SQL Settings window.
Available characters to refer to parameters:
- @
- :
- $
If you change the symbol:
- you will be able to use it to refer to parameters in all new SQL templates;
- the new symbol will replace the current one in all previously created SQL templates for segmentation and dynamic content - the templates will continue to work correctly without the need to make manual changes to them;
- if referring to a parameter via an obsolete symbol is used in the layout of any Email messages - it will not be automatically replaced by the new one, and the code in this email will stop working;
- segments created before the symbol change and after will be counted in the same way as in the previous setting.
Note that the selected symbol, written anywhere in the template, will be treated as a symbol for accessing a parameter and cannot be used for anything else.
