Email marketing should be targeted – it's not recommended to send all the campaigns to all the recipients, but it is desirable to be able to target your campaigns only to the recipients they are relevant to.The Delivery capping function is used for this purpose.
Activation of Delivery capping
Delivery capping is set in the campaign via the Delivery capping button.
After clicking the button, the Query builder appears, where you can define complex, but simple rules, that the recipient must meet in order to get the campaign. This Query builder has the same basis as the Filter recipients function, so its functionality is similar.
The 1st step of Delivery capping – recipient data
Query builder has four steps, it is not mandatory to have rules filled in all steps at the same time – individual steps can be omitted.
In the first step you can (but don't have to) create rules for segmentation based on recipient data. To create a rule, click the Add rule (to add one rule) or the Add group button (add multiple rules in one level). The total number of rules and their nesting is not limited.
TIP no.1: Comparison of text fields applies to the use of operators equal, not equal, begins with, doesn't begin with, contains, doesn't contain – all comparison of text strings is ie. CASE INSENSITIVE.
Example: A city equals "prague" finds records with the value "prague" as well as with the values "Prague", "PRAGUE" or "PrAgUe".
TIP no. 2: Comparing text fields IGNORES diacritics for the use of operators equal, not equal, begins with, doesn't begin with, contains, doesn't contain:
Example 1: A city equals to "Červené Pečky" finds not only records with a value of "Červené Pečky" but also "Cervene Pecky".
Example 2: A city equals to"Cervené Pecky" finds not only records with a value of "Cervené Pecky", but also "Červené Pečky".
TIP no. 3: Comparing text fields by using operators exact match and not exact match DOESN'T IGNORE diacritics and at the same time is CASE SENSITIVE:
Example 1: A city has an exact match with "Červené Pečky" finds ONLY records with a value of "Červené Pečky".
Example 2: A city does not exactly match with "Červené Pečky" finds records with "Cervene Pecky", "červené Pečky", "ČERVENÉ PEČKY" and others, but DOESN'T find a record with a value of "Červené Pečky".
While creating filters, you can click the Verify rules button at any time to check that the set rules have the correct syntax. Therefore, the corresponding records (recipients) are not recalculated, which can greatly speed up the rule-making process in cases where a large number of records need to be checked.
The syntax is also checked when going to the next step by clicking on the "Next" button.
If the syntax of any of the rules is incorrect, the system will notify you and indicate the conditions/fields that need to be modified.
By clicking the Get count button, you can check how many recipients meet the specified conditions at any time. Here, in cases where it is necessary to go through a larger amount of data, it is necessary to take into account that the calculation may take longer.
You can save the created rules and then reuse them. To do this, use the Load rules (in the first step) and Save rules (in the fourth step) buttons. Rules management can be found in the menu Profile –> Queries.
By clicking on Clear rules, you can simply delete all the set rules (in all steps at once) with one click.
The 2nd step of Delivery capping – exclude data
In the second step, you can (but do not have to) select the mailing list(s) that you want to completely exclude from the rule set in the first step. In this step, it is no longer possible to filter in the selected list(s) (select only some recipients from the list(s) to be excluded).
The 3rd step of Delivery capping – campaign delivery data
In the third step of delivery capping, you can (but don't have to) create rules regarding the distribution of campaigns to the recipients and their interactions.
Start by clicking on the Add Campaign/Message button and select the campaign(s) or message(s) from the list. Only campaigns and messages from the past 1 year (365 days) are available for selection. Click the OK button to confirm the selection.
Using the Add rule or Add group buttons, you can define activities related to campaigns/messages (campaign deliveries), for example, whether the selected campaigns/messages were sent to the recipients or not, whether the recipients opened them, or whether they clicked on one of the links.
If you select multiple campaigns/campaign deliveries from the drop-down bar and at the same time work within the rules with information on whether the message was sent/recipient read it/recipient clicked, then the following applies to the YES/NO values:
- YES –> at least one of the selected campaigns/deliveries was sent to the recipient (the same applies to information about reading (i.e. the recipient read at least one of the sent messages) and clicks (i.e. the recipient clicked on one of the links in at least one of the messages))
- NO –> none of the selected campaigns/deliveries were sent to the recipient (the same applies to information about reading (i.e. the recipient did not read any of the sent messages) and clicks (i.e. the recipient did not click on the link in any of the sent messages))
The 4th step of Delivery capping – data sources
In the fourth step, you can narrow the selection by filtering over SQL data sources, such as the feed with orders from your e-shop, as shown in the picture below – from recipients who were filtered in the previous steps will be selected those who bought goods in the e-shop from categories starting with the numbers 23 or 24.
Start by clicking on the "Add Datasource" button and select the appropriate data source (the system will offer you SQL data sources that are stored in your account). The system analyzes the selected data source. Based on this analysis it selects the field in which the email address is stored. You can adjust this selection using the drop-down menu, in which you will find all the fields identified during the data source analysis. Only fields that have been marked as a primary key or as an indexed field in the data source are available for selection. On the next line, you define whether the recipients who meet the condition of this step should be included in the selection or, on the contrary, they should be excluded from the selection. Then it is enough to define the condition itself.
Note: For a feed with orders, it is always necessary to keep in mind that the unique records in the feed are order items - ie. if there is an order that contains 3 items (different products), 3 records will be represented in the feed, with the same email address, delivery address and other data, but the items will differ eg. in product ID, product category, its quantity, price, etc.
Applying delivery capping rules
After completing the 4th step, you can save the created rules for use in other filters. To activate delivery capping, click the "Use Filter" button. To confirm that the delivery capping feature is used, a green check mark appears next to the Delivery capping button: 
How to work with dates
Targeting recipients who purchased the goods 7 days ago? Or targeting those whose contract ends in 2 months? Or selecting those who have birthday today? You can do anything...
When creating rules, you can work with dates stored, for example, in the recipient's custom fields, it is possible to use system dates such as the date added, date of last message sent, consent date, etc.
The following operators can be used when working with dates: equal, not equal, greater, greater or equal, less, less or equal, is empty, is not empty.
Simple comparison
The date value to be compared must always be entered in the default MYSQL format, i.e. Y/m/d (2020/02/16).
Example 1: Simple comparison of a date field with a specific date:
| Field | Operator | Value/expression |
|---|---|---|
| change_date | equal | 2019/08/01 |
Comparison using expressions
You must always enter the expression by enclosing the value in square brackets: [DB_field_format; field_function | expression; expression_function]
Each time the expression is used, the following must be filled in:
- DB field format, that you compare with (for date type field enter "date", for text field containing date enter the format in which the date is stored, see supported formats)
- expression - see supported expressions
Example 2: Comparison a date field with an expression:
| Field | Operator | Value/expression | Note |
|---|---|---|---|
| change_date | greater_or_equal | [datetime|CURDATE-7] | Date of last change (change_date) is greater than or equal to the current date minus 7 days (7 days before today) and change_date field is of type datetime. |
When filtering over a field in text format, you must specify the format in which the date is stored in the field.
Supported formats:
| Format name | Field type use | Format | Example |
|---|---|---|---|
| datetime | date updated, date added, date of last message sent, consent date, SQL data sources with custom data format with assigned format (type) "datetime" |
MySQL datetime | Saved in a datetime field type formatted as 2018/12/31 23:59:59 |
| date | SQL data sources with custom data format with assigned format (type) "date" | MySQL date | Saved in a date field type formatted as 2018/12/31 |
| date_us | custom field | Y-m-d | 2018-12-31 |
| date_us_short | custom field | y-m-d | 18-12-31 |
| date_us_md | custom field | m-d | 12-31 |
| date_eu | custom field | d.m.Y | 31.12.2018 |
| date_eu_short | custom field | d.m.y | 31.12.18 |
| date_eu_dm | custom field | d.m. | 31.12. |
Supported expressions:
| Expression | Use | Meaning |
|---|---|---|
| CURDATE | [date|CURDATE+X] | Current date + X days (for “date” field type) |
| [datetime|CURDATE+X] | Current date + X days (for “datetime” field type , i.e. value is compared with the date with time component 00:00:00) | |
| [date_us|CURDATE-X] | Current date - X days | |
| [date_eu|CURDATE-X] | Current date - X days | |
| [date_eu_short|CURDATE+X] | Current date + X days |
Example 3: Comparison a text type field with an expression:
| Field | Operator | Value/expression | Note |
|---|---|---|---|
| custom_2 | equal | [date_eu|CURDATE-7] | Custom field nr. 2 (custom_2) equal to the current date minus 7 days. Custom_2 is a text field with a date saved in the “date_eu” format – "d.m.Y" (e.g. 31. 12. 2018). |
–> Recipients who have today's date (CURDATE) in their custom field 1 will be selected. The date is stored in custom field 1 in the date_eu format.
NOT EQUAL
Example: Custom field 1 ≠ (NOT EQUAL) [date_eu|CURDATE]
–> Recipients who DO NOT HAVE today's date (CURDATE) in their custom field 1 will be selected. The date is stored in custom field 1 in the date_eu format. ATTENTION, an empty field also meets the condition!
LESS
Example: Custom field 1 < (LESS) [date_eu|CURDATE]
–> Recipients who have a date less (earlier) than today's date (CURDATE) in their custom field 1 will be selected. The date is stored in custom field 1 in the date_eu format. ATTENTION, an empty field also meets the condition!
LESS OR EQUAL
Example: Custom field 1 ≤ (LESS OR EQUAL) [date_eu|CURDATE]
–> Recipients who have today's date (CURDATE) or a date less (earlier) than today's date (CURDATE) in their custom field 1 will be selected. The date is stored in custom field 1 in the date_eu format. ATTENTION, an empty field also meets the condition!
GREATER
Example: Custom field 1 > (GREATER) [date_eu|CURDATE]
–> Recipients who have a date greater (later) than today's date (CURDATE) in their custom field 1 will be selected. The date is stored in custom field 1 in the date_eu format.
GREATER OR EQUAL
Example: Custom field 1 ≥ (GREATER OR EQUAL) [date_eu|CURDATE]
–> Recipients who have today's date (CURDATE) or a date greater (later) than today's date (CURDATE) in their custom field 1 will be selected. The date is stored in custom field 1 in the date_eu format.
Comparison using functions
Functions can be used for a database field, as well as for a comparison value. Therefore, you must always specify the function on the left and / or right side separately.
[DB_field_format;field_function|expression;expression_function]
Supported functions
| Function | Meaning |
|---|---|
| YEAR | Year |
| MONTH | Month |
| WEEK | Week |
| DAY | Day (1-31) |
| DAYOFWEEK | Day of week |
| ANNIVERSARY | Anniversary – compares the day and month of a given date regardless of the year. |
| AGE | Age |
| DATE | Date – rounds the date and time to whole days, ie without the time component. |
Example 4: Using functions:
| Field | Operator | Value/expression | Note |
|---|---|---|---|
| s_last_sent | not_equal | [datetime;DATE|CURDATE] | The date of last message sent (s_last_sent) is a datetime field. This filter excludes all recipients to whom something has already been sent today. |
| custom_1 | equal | [date_eu;DAYOFWEEK|CURDATE;DAYOFWEEK] | Custom field 1 (custom_1) is a text field with the date stored in d.m.Y format. Day of week in custom_1 equals to the current day of the week. |
| custom_2 | equal | [date_eu;MONTH|12] | Custom field 2 (custom_2) is a text field with the date stored in the d.m.Y format. This filter selects all December dates in the custom_2 field. |
| custom_3 | equal | [date_us;ANNIVERSARY|CURDATE;ANNIVERSARY] | Custom field 3 (custom_3) is a text field e.g. with the recipient's date of birth stored in Y-m-d format. This filter selects all recipients who have a birthday today. |
| custom_4 | equal | [date_eu_dm;ANNIVERSARY|CURDATE+1;ANNIVERSARY] | Custom field 4 (custom_4) is a text field with the recipient's date of birth stored in d.m. format. This filter selects all recipients who have a birthday tomorrow. |
| custom_5 | equal | [date_eu_dm;ANNIVERSARY|2015/12/24;ANNIVERSARY] | Custom field 5 (custom_5) is a text field with the recipient's date of birth stored in d.m. format. This filter selects all recipients who have a birthday for Christmas. |
| custom_6 | greater_or_equal | [date_us;AGE|18] | Custom field 6 (custom_6) is a text field with the recipient's date of birth stored in Y-m-d format. This filter selects all adult (18 years old or older) recipients. |
Operators for comparing MySQL data fields
You can use several operators to compare the contents of MySQL fields that contain a date (in MySQL date/MySQL datetime formats). The same logic can be applied to fields in the MySQL date/MySQL datetime formats as for text fields, only the names of the operators used differ (see above for Operators for comparing text fields).
For fields in the MySQL datetime format (e.g. 2018/12/31 23:59:59), in addition to the date (2018/12/31), the time component (23:59:59) may (but does not have to) be included in the comparison. We then distinguish 2 entries:
-
[datetime;DATE|CURDATE] – the value in the field is in the datetime format (e.g. 2018/12/31 23:59:59), but only the DATE (2018/12/31) enters the comparison, i.e. it does not matter the value of the time component.
-
[datetime|CURDATE] – the value in the field is in the datetime format (e.g. 2018/12/31 23:59:59) and not only the date (2018/12/31) but also the time (23:59:59) is included in the comparison.
EQUAL
Example 1: SQL data field = (EQUAL) [datetime;DATE|CURDATE]
–> Records that have today's date (CURDATE) in the field will be selected. The date is stored in the field in datetime format and only the DATE (without the time component) is taken into account when comparing.
Example 2: SQL data field = (EQUAL) [datetime|CURDATE]
–> Records that have today's date (CURDATE) in the field will be selected. The date is stored in the field in datetime format and not only the date but also the time is taken into account when comparing.
If CURDATE (today's date) corresponds, for example, to the date 20. 3. 2021, in datetime format, when both date and time are compared, the value to be compared will be written as follows: 2021/03/20 00:00:00 –> i.e. only records that HAVE the value “2021/03/20 00:00:00” in the field will be selected.
NOT EQUAL
Example 1: SQL data field ≠ (NOT EQUAL) [datetime;DATE|CURDATE]
–> Records that DO NOT HAVE today's date (CURDATE) in the field will be selected. The date is stored in the field in datetime format and only the DATE (without the time component) is taken into account when comparing.
Example 2: SQL data field ≠ (NOT EQUAL) [datetime|CURDATE]
–> Records that DO NOT HAVE today's date (CURDATE) in the field will be selected. The date is stored in the field in datetime format and not only the date but also the time is taken into account when comparing.
If CURDATE (today's date) corresponds, for example, to the date 20. 3. 2021, in datetime format, when both date and time are compared, the value to be compared will be written as follows: 2021/03/20 00:00:00 -> I.e. only records that do NOT have the value “2021/03/20 00:00:00” in the given field will be selected (therefore records with values “2021/03/20 00:00:01”, “2021/03/20 00:00:02”,..., “2021/03/20 23:59:59” will also be selected).
BEFORE
Example: SQL data field < (BEFORE) [datetime;DATE|CURDATE]
–> Records that have a date earlier/less than today's date (CURDATE) in the field will be selected. The date is stored in the field in datetime format and only the DATE (without the time component) is taken into account when comparing.
(Note: If SQL data field <(BEFORE) [datetime | CURDATE] is used, the same number of records will be selected.)
NOT AFTER
Example 1: SQL data field ≤ (NOT AFTER) [datetime;DATE|CURDATE]
–> Records that have a current date (CURDATE) or a date earlier/less than today's date (CURDATE) in the field will be selected. The date is stored in the field in datetime format and only the DATE (without the time component) is taken into account when comparing.
Example 2: SQL data field ≤ (NOT AFTER) [datetime|CURDATE]
–> Records that have a current date (CURDATE) with "00:00:00" time compoment or a date earlier/less than today's date (CURDATE) in the field will be selected. The date is stored in the field in datetime format and not only the date but also the time is taken into account when comparing.
If CURDATE (today's date) corresponds, for example, to the date 20. 3. 2021, in datetime format, when both date and time are compared, the value to be compared will be written as follows: 2021/03/20 00:00:00 –> I.e. only records that have the value “2021/03/20 00:00:00” or a date earlier/less than the value “2021/03/20 00:00:00” (i.e. “2021/03/19 23:59:59 ”,“ 2021/03/19 23:59:58 ”,“ 2021/03/19 23:59:57 ”, ....). In other words – all records with yesterday's date and earlier + records with the value “2021/03/20 00:00:00” will be selected.
AFTER
Example 1: SQL data field > (AFTER) [datetime;DATE|CURDATE]
–> Records that have a date later (greater) than today's date (CURDATE) in the field will be selected. The date is stored in the field in datetime format and only the DATE (without the time component) is taken into account when comparing.
Example 2: SQL data field > (AFTER) [datetime|CURDATE]
–> Records that have today's date (CURDATE) in the field will be selected (except for the value with the time component “00:00:00”), or a later (larger) date. The date is stored in the field in datetime format and not only the date but also the time is taken into account when comparing.
If CURDATE (today's date) corresponds, for example, to the date 20. 3. 2021, in datetime format, when both date and time are compared, the value to be compared will be written as follows: 2021/03/20 00:00:00 -> I.e. only records that have the value “2021/03/20 00:00:01” or the date later/larger (ie “2021/03/20 00:00:02”, “2021/03/20 00:00:03 ”, ....,“ 2021/03/20 23:59:59 ”,“ 2021/03/21 00:00:00 ”,“ 2021/03/21 00:00:02 ”,“ 2021/03/21 00:00:00 ”, ....). In other words – all records with a value greater than (after) "2021/03/20 00:00:00 ”will be selected.
NOT BEFORE
Example 1: SQL data field ≥ (NOT BEFORE) [datetime;DATE|CURDATE]
–> Records that have a current date (CURDATE) or a date later/greater than today's date (CURDATE) in the field will be selected. The date is stored in the field in datetime format and only the DATE (without the time component) is taken into account when comparing.
(Note: If SQL data field ≥ (NOT BEFORE) is used, the same number of records will be selected.)
