Create Advanced Rule – Help Center

Advanced Segmentation Rules make it easy to search for contacts that match a variety of conditions requiring very complex queries. Advanced Rules allow for manually entering statements into the editor for better control over the conditions, including combining AND and OR statements. The custom query is similar to a SQL query using logical phrases.

To Add an Advanced Segmentation Rule

Go to SEGMENTATION > SEGMENTATION RULES, then click the arrow next to Add Rule, and select Add Advanced Rule.

For versions prior to 9.8.0, go to LISTS > SEGMENTATION > CREATE ADVANCED RULE.

Enter a Rule Name.
Enter an optional Description.
Define a command in the Commands box . Refer to the list of available data fields in the Field Names box.
Click VALIDATE to ensure that the query is formatted correctly.
To test the rule, select a List from the Test Against field, then click TEST.
Click CREATE to save the new rule. Click CANCEL to return to the Segmentation Rules page without creating the new rule.

Defining a Command

Commands are defined using a Field Name, Operator, and Value. Common formats for commands include:

field + operator (i.e.: custom_interest not exists)
field + operator + value (i.e.: first beginswith 'bob')

Example commands include:

zipcode > 98000 and zipcode < 98100
(company = 'ace' or company = 'acme') and updated_date > '2001-01-01'

Parentheses, like in the example above, are necessary if you are checking more than two fields. This helps the query group operations. Use double (") or single (') quotes to surround text or date values. Numerical values do not require special designations.

NOTE: Advanced Segmentation is the only way to query the Zip field using the greater than (> >=) and less than (< <=) operators.

Matching Empty or Non-Existent Fields

Advanced Segmentation Rules can match empty or non-existent (NULL) fields. This is done using the "exists" operator. For example, to find contacts that have no data stored in the field "exists", define the command:

custom_age not exists or custom_age = ""

This checks if the field does not exist or, if it does exist, if the field empty.

To find all non-empty fields, define the command:

custom_age exists and custom_age != ""

This checks if the field exists and if the field is not empty.

This is an important difference between Advanced and Standard segmentation. When you add or import contacts, data fields are not always filled or created if the data is blank or empty. For instance, if a field is blank in your import file and you import with the SmartSubscribe option, a blank custom field is added. However, if you do not use SmartSubscribe, the custom field is not created. If a field is not filled on import or save, then it essentially does not exist. When you search for contacts, you must take into account fields where data exists vs. fields that don't exist at all.

For example, if you want to search for contacts who do not want to receive the weekly version of your email (custom_weekly != 1), in Standard segmentation, your rule would look like:

custom_weekly != 1 or custom_weekly Is Null

But in Advanced segmentation, your rule should be:

custom_weekly != 1 OR custom_weekly not exists

Parts of a Command

A command is composed of at least one field and operator and can also include one or more values.

Fields

The Fields list displays the default fields available to query. You can also query custom fields by preceding the field name with "custom_". For example, a custom field called "interest" would be "custom_interest" within a segmentation rule. As with standard segmentation rules, large text and Unicode fields cannot be searched.

Operators

The following operators can be used in a custom segmentation rule:

Operator Type	Operator	Description
Text Only	contains	Does the field contain the value?
	beginswith	Does the field begin with the value?
	endswith	Does the field end with the value?
Zip Only	in	Does the field match one or more of the values?
Numbers, Dates & Text	=	Is the field equal to the value?
	<>	Is the field not equal (!=) to the value?
	>	Is the field greater than the value?
	>=	Is the field greater than or equal to the value?
	<	Is the field less than the value?
	<=	Is the field less than or equal to the value?
	exists	Is the field present and have data stored?
	not	For use with another operator to make a statement negative.

Operator Grouping

Parentheses can be used in Advanced Segmentation rules to force groupings. There are three types of operators:

unary: exists, in
arithmetic: +, -
comparison: =, >, >=, <, <=
logical: and, or

The higher up the list an operator is, the tighter the grouping. For example:

a + b > c and c + d > e and g exists

Is automatically grouped like you would intuitively expect:

((a + b)) > c and ((c + d) > e) and (g exists)

If we have the same level of operator, then we group from the left to the right:

a + b - c - d > 0

Becomes:

(a + b) - c) - d) > 0

However, you can force a different grouping order by inserting your own parentheses:

(a + b) - (c - d) > 0

Values

A Value is required for all conditions except those using the "exists" operator. The following are all acceptable ways to format dates in your custom segmentation rule:

Format	Example
yyyy-mm-dd	'2004-08-26'
mm-dd-yyyy	'08-26-2004'
yyyy/mm/dd	'2004/08/26'
mm/dd/yyyy	'08/26/2004'
yyyy.mm.dd	'2004.08.26'
mm.dd.yyyy	'08.26.2004'

Keywords

There are several special keywords you can use in your query to specify more specific date information.

Date fields

The keywords ".month" or ".day" or ".year" can be added to any date field to extract an integer.

For example, where the custom field is named "dob":

custom_dob.month = 9 and custom_dob.day = 27 and custom_dob.year = 1987

Now field

The "now" field represents the current date.

For example, to pull all contacts with a birthday in this month:

now.month = custom_dob.month

To pull all contacts with a birthday next month:

now.month + 1 = custom_dob.month

'In' clause

The keyword 'in' can be used to create a segmentation rule that looks for one of several values. For example:

Contact born in February - April:

custom_dob.month in (2,3,4)

Contacts with zip code 98001 or 98002:

zip in ('98001','98002')

zip in (98001,98002)

'In' clause with Range*

To match contacts with a zip code within a certain range, use the keyword 'in' and define the range and zip code to test. For example:

zip_code_range in (miles,zipcode)

For example, contacts with zip code in 10 mile range of 30075:

zip_code_range in (20,30075)

This will return all the zip codes within 20 miles from 30075.

Miles must be one of the following (increments of 5 up to 50):

5, 10, 15, 20, 25, 30, 35, 40, 45, 50

Comparison Arithmetic

Calculations can be performed to query for contacts that match some resulting value.

Addition and Subtraction

Simple arithmetic can be performed on any integer field. For example, where the custom fields are named "start_balance" and "end_balance": custom_start_balance - custom_end_balance > 0

Date Arithmetic

Date arithmetic can be performed with the "now" field. The segmentation rule looks for a number followed (with no spaces) by a 'd', or 'm', or 'y'.

For example:

All customers created within the last two weeks:

created_date >= now -14d

All customers created in the last two months:

created_date >= now - 2m

All customers created in the last year:

created_date >= now - 1y

When calculating a date, use the 'now' field whenever possible. That is the most efficient and reliable method. You can do arithmetic on parts of a date from a field (e.g. custom_DOB.month + 1), but they do not wrap. If the contact was born in December (12), then custom_DOB.month + 1 returns "13" instead of "1".

The "now" field does wrap. So, if it is December (12), then now.month + 1 returns "1" not "13".

Subscribed date

Segmentation rules can be built around when someone was subscribed to a list: For example:

subscribed_date > now - 1m

*New in v11.2