Data Table templates
Data table templates are SQL patterns which may be used in segmentation and dynamic content. To access data table templates, select Data Tables > Templates from the left-hand navigation.
Info#
SQL templates are an advanced feature and are usually written by experienced users with a technical background. If you face problems with writing an SQL query, the usual solution is to consult an IT specialist or a programmer at your company.
Apart from this, you may want to learn some SQL from one of the available online tutorials. We recommend SQLCourse.com and W3Schools.
To create a new data table template, click Create a new template for segmentation or Create a new template for Dynamic Content.
The two types of templates are very similar, however, the fundamental difference is that templates for segmentation can only return a column with the primary system identifier (email address, phone, custom subscriber id), whereas in the Dynamic Content type of template you may select any columns from the database.
It naturally follows that for segmentation, you may only use a database which contains an unambiguous system identifier. If you also want to include data from another database, you have to use the JOIN clause in the SQL statement to combine it with a database which includes a system identifier.
Which identifier will a template for segmentation return?#
By default, the system identifiers are checked in the database in the following order:
- Phone
- Custom subscriber id.
The search mechanism will return the first identifier it will encounter.
What if you want it to return phone numbers or subscriber ids and the database contains email addresses too? There is a convenient workaround solution available:
- Select Data Tables > Tables from the left-hand navigation.
- Find the table you intend to use.
- Change the Email column type from Subscriber Email to Text.
- If necessary, change the Phone column type from Subscriber phone number to Text.
This way, the email and/or phone number will be invisible to the segmentation template and it will return the next system identifier it will find in the database.
Then, follow these steps:
- Enter a template name and a short description.
- Under Table, choose the table from which you want to extract data.
- Under SELECT, choose the table columns which the search should return. As indicated above, if you’re creating a template for segmentation purposes, you may only return the default system id column (usually email).
- Write your SQL query in the space provided below FROM (database) AS (name).
Warning#
Please note that it is highly advisable to follow good practices while formulating your SQL query, such as avoiding redundant statements. For instance, it is pointless to use an ORDER BY clause for segmentation purposes, because the order of subscribers within a segment does not matter. What is more, using it may lead to undesirable consequences, since ORDER BY clauses use a lot of processing power. This may heavily influence the performance of the whole ExpertSender infrastructure and even affect other ExpertSender customers.
- Optionally, you may add some parameters, which will later help you formulate a specific query.
Parameters in SQL queries#
A parameter is one of the columns of the data table which you want to use in your search, e.g. «number of purchases». Of course, you may refer to any column within the body of the query, but please note that then you need to enter the precise value which you are looking for (for instance, the number of orders is bigger than 10). However, you may want to do similar searches where the value is a bit different (i.e. search for all customers who have more than 15 orders). Without a parameter, you would have to create another SQL template for this purpose, but a parameter lets you specify the value for each search individually.
Let’s take a look at this example step by step:
- Click Add parameter. A new empty row will appear.
- Specify the name, friendly name, type and test value of your parameter. The type should match the type of data field which we are referring to (the number of orders must be a number).
- Return to the SQL query box. Instead of providing a fixed value, use @parameter, where «parameter» is the parameter name provided at the previous step (in our example it’s @number).
- Click Save changes.
You’ve got a functional query template with a parameter now. How can you put it into use?
- When you are creating a segment with a data tables restriction, select the Add comparison to result of SQL query with parameters option.
- Choose the template which you’ve created in the previous step.
- You will have an opportunity to set the value for the parameters in the selected template:
- Select the desired value and click OK.
- In the template for Dynamic Content, you may also click Search below the legend to display an exemplary Dynamic Content code which will allow you to retrieve data in email content:
- Click Save changes.
That’s it! You have created an SQL query template which is ready for use!