ResourcesKnowledge Base

Segmentation

Without a doubt segmentation of subscriber lists is one of the keys to successful email marketing. We recognise it's importance and have crafted a powerful set of tools that empower you with the freedom to segment in whatever ways work best for the success of your campaigns.

Working with dates

When working with dates, the following operators can be used: equal, not_equal, greater, greater_or_equal, less, less_or_equal

Simple comparison

The comparative value of the date must always be entered in the default MYSQL format, ie YYYY/MM/DD (2015/12/31).

1: Simple comparison of a date field with a specific date:

jednoducha-podminka-cas.PNG

Field Operator Value / Expression
change_date equal 2016/01/01

Comparison using expressions

Use the brackets to square brackets:
[format_DB_field;funkce_above_field|expression;function_above_expression]

Every time an expression is used, it must be filled in at least:

  • db field format, (for date field type "date", for the text field containing the date we enter the format in which the date is stored, see the supported formats)
  • expression - see supported terms

2: Comparison of the date field with the expression:
porovnani-cas.PNG

Field Operator Value / Expression Note
change_date greater_or_equal [datetime|CURDATE-7] The change_date is greater than or equal to the current date minus 7 days, with change_date being a datetime field.

When filtering over a text field, you must specify the format in which the date is stored in the field.

Supported formats:

Format name For type fields Format Example
datetime date of change, addition, last submission MySQL datetime Saved in a datetime format box 2015/12/31 23:59:59
date_us own field Y-m-d 2015-12-31
date_us_short own field y-m-d 15-12-31
date_us_md own field m-d 12-31
date_eu own field d.m.Y 31.12.2015
date_eu_short own field d.m.y 31.12.15
date_eu_dm own field d.m. 31.12.

Supported expressions:

Expression Use Importance
CURDATE [date|CURDATE+X] The current date + X days
  [date_us|CURDATE-X] The current date - X days
  [date_eu|CURDATE-X] The current date - X days
  [date_eu_short|CURDATE+X] The current date + X days

3: Comparison of text field with expression:

Field Operator Value / Expression Note
custom_2 greater_or_equal [date_eu|CURDATE-7] Custom field 2 (custom_2) is greater than or equal to the current date minus 7 days, custom_2 being a text field with a date stored in "d.m.Y" format (e.g., 31.12.2015).

Comparing with features

The function can be used both above the database field and above the comparison value. Therefore, we must always specify the function on the left and / or on the right.

[format_DB_field;expression_over_field|expression;function_over_expression]

Supported features

Function  
YEAR  
MONTH Months (1-12)
WEEK Weeks (1-53)
DAY Days (1-31)
DAYOFWEEK Den v týdnu
ANNIVERSARY Anniversary - compares the day and month of a given date regardless of the year.
AGE  
DATE DATE - Rounds the date and time for whole days, ie no time folder.

4: Using features:

Field Operator Value / expression Note
s_last_sent not_equal [datetime;DATE|CURDATE] The date of last submission (s_last_sent) is a datetime field. With this filter, we remove from the selection all the recipients who have already been sent something.
custom_1 equal [date_eu;DAYOFWEEK|CURDATE;DAYOFWEEK] Custom field # 1 (custom_1) is a text field where the date in the d.m.Y. format is stored. The day of the week in custom_1 is equal to the current day of the week.
custom_2 equal [date_eu;MONTH|12] Custom field # 2 (custom_2) is a text field where the date in the d.m.Y. format is stored. This filter selects all December dates in the custom_2 field.
  equal [date_us;ANNIVERSARY|CURDATE;ANNIVERSARY] Custom field 3 (custom_3) is a text field that holds the date of birth in the Y-m-d format. This filter will select all recipients who have their birthdays today.
  equal [date_eu_dm;ANNIVERSARY|CURDATE+1;ANNIVERSARY] Custom field # 4 (custom_4) is a text field where the date of birth in the d.m. format is stored. This filter will select all recipients who have tomorrow's birthday.
  equal [date_eu_dm;ANNIVERSARY|2015/12/24;ANNIVERSARY] Custom field # 5 (custom_5) is a text field where the date of birth in the d.m. format is stored. This filter selects all recipients who have a birthday for Christmas.
  greater_or_equal [date_us;AGE|18] Custom field # 6 (custom_6) is a text field where the Y-m-d birth date is stored. This filter selects all adult recipients.