| Table of Contents |
|---|
...
| Info |
|---|
This article supports Audience Studio - Legacy. |
Review the following
...
operator information to help you create accurate SQL code for your segment attribute conditions. You can sort the Operator or Description column by selecting the column heading.
| Operator | Description |
|---|---|
| equal to |
...
| Include profiles that are exactly equal to the selected value. |
...
| not equal to | Include profiles that are not equal to the selected value. |
| greater than | Match all profiles |
...
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_1249"."customers" a
where a."gender" = 'Female'
-- set session distributed_join = 'true'
) cs |
not equal to
Use the not equal to operator when you want to include profiles that are not equal to the selected value.
Example
Match all profiles in which the gender is not Male.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from (
select
a."cdp_customer_id"
from "cdp_audience_1249"."customers" a
where not coalesce(a."gender" = 'Male', false)
-- set session distributed_join = 'true'
) cs |
Operator | Include Profiles where the selected attribute is | Example Use Case | SQL Example |
equal to | exactly equal to the selected value | match that are greater than the specified value. | |
| greater than or equal to | Match all profiles that are greater or equal to the selected value. | ||
| less than | Match all profiles that are less than the selected value. | ||
| less than or equal to | Match all profiles that are less than or equal to the selected value. | ||
| contains | Match all profiles that contain the selected value. | ||
| starts with | Match all profiles that begin with the selected value. | ||
| ends with | Match all profiles that end with the selected value. | ||
| regex | Match all profiles based on a regular expressions pattern (regex). | ||
| not regex | Exclude all profiles based on a regular expressions pattern (regex). | ||
| is between | Match all profiles between two provided values. | ||
| does not contain | Match all profiles that do not contain the selected value. | ||
| does not start with | Match all profiles that do not begin with the selected value. | ||
| does not end with | Match all profiles that do not end with the selected value. | ||
| is one of | Match all profiles that contain one of the selected values. | ||
| is not one of | Match all profiles that do not contain one of the selected values. | ||
| is not between | Match all profiles that do not contain values that are between the selected values. | ||
| exists | Match all profiles where the selected attribute exists. | ||
| does not exist | Match all profiles where the attribute does not exist. | ||
| does not exist or greater than | Match all profiles where the attribute does not exist or is greater than a certain value. | ||
| does not exist or greater than or equal to | Match all profiles where the attribute does not exist or is greater than or equal to a certain value. | ||
| does not exist or less than | Match all profiles where the attribute does not exist or is less than a certain value. | ||
| does not exist or less than or equal to | Match all profiles where the attribute does not exist or is equal to a certain value. |
equal to
Use the equal to operator to include profiles that are exactly equal to the selected value.
Example
Match all profiles in which the gender is Female.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_1249"."customers" a |
...
where a."gender" = 'Female' |
...
-- set session distributed_join = 'true' |
...
) cs |
not equal to
Use the not equal
...
to operator to include profiles that are not equal to the selected value.
Example
...
Match all profiles in which the gender is not Male.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_1249"."customers" a |
...
where not coalesce(a."gender" = 'Male', false) |
...
-- set session distributed_join = 'true' |
...
) cs |
greater than
Use the greater than operator to match all profiles that are greater than the
...
specified value.
Example
...
Match all profiles with a likely_to_shop score greater than zero.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_1249"."customers" a |
...
where a."td_predictive_score_177" > 0 |
...
-- set session distributed_join = 'true' |
...
) cs |
...
greater than or equal to
Use the greater than or equal
...
to operator to match all profiles that are greater or equal to the selected value.
Example
...
Match all profiles with a likely_to_shop score greater than OR equal to zero.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_1249"."customers" a |
...
where a."td_predictive_score_177" >= 0 |
...
-- set session distributed_join = 'true' |
...
) cs |
less than
Use the less than operator to match all profiles that are less than the selected value.
Example
...
Match all profiles with a likely_to_shop score less than 10.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_1249"."customers" a |
...
where a."td_predictive_score_177" < 10 |
...
-- set session distributed_join = 'true' |
...
) cs |
...
less than or equal to
Use the less than or equal
...
to operator to match all profiles that are less than or equal to the selected value.
...
Example
Match all profiles with a likely_to_shop score less or equal to 10.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_1249"."customers" a |
...
where a."td_predictive_score_177" <= 10 |
...
-- set session distributed_join = 'true' |
...
) cs |
contains
Use the contains operator to match all profiles that contain the selected value.
Example
...
Match all profiles where the company contains Johnston LLC.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_6148"."customers" a |
...
where position('Johnston LLC' in a."company") > 0 |
...
-- set session distributed_join = 'true' |
...
) cs |
...
starts with
Use the starts with
...
operator to match all profiles that begin with the selected value.
...
Example
Match all profiles where the company begins with Johnston LLC
...
.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_6148"."customers" a |
...
where position('Johnston LLC' in a."company") = 1 |
...
-- set session distributed_join = 'true' |
...
) cs |
...
ends with
Use the ends with operator to match all profiles that end with the selected value.
...
Example
Match all profiles where the company ends with
...
LLC.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where substr(a."company", -length(' |
...
LLC'), length(' |
...
LLC')) = ' |
...
LLC' |
...
-- set session distributed_join = 'true' |
...
) cs |
...
regex
...
regex
...
Use the regex operator to match all profiles based on a regular expressions pattern (regex). You must provide the regex pattern that works for your use case.
...
Example
Match all profiles where the job_title begins with 'Manager', based on the regex expression: ^Manager
...
.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where regexp_like(a."job_title", '^Manager') |
...
-- set session distributed_join = 'true' |
...
) cs |
...
not regex
Use the not regex
...
operator to not match all profiles based on a regular expressions pattern (regex). You must provide the regex pattern that works for your use case.
Example
...
Match all profiles where the company does not end with the word
...
Google based on the regex pattern
...
Google$.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where not coalesce(regexp_like(a."company", ' |
...
Google$'), false) |
...
-- set session distributed_join = 'true' |
...
) cs |
...
is between
Use the in between operator to not match all profiles between two provided values.
...
Example
Match all profiles with a likely_to_shop score between 10 and 20.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where (a."likely_to_shop" between 10 and 20) |
...
-- set session distributed_join = 'true' |
...
) cs |
does not contain
Use the does not contain operator to match all profiles that do not contain the selected value.
Example
...
Include all profiles where the company does not contain Johnston LLC.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where not coalesce(position('Johnston LLC' in a."company") > 0 |
...
-- set session distributed_join = 'true' |
...
) cs |
does not start with
Use the does not start with operator to match all profiles that do not begin with the selected value.
...
Example
Include all profiles where the company does not start with Johnston LLC.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where not coalesce(position('Johnston LLC' in a."company") = 1 |
...
-- set session distributed_join = 'true' |
...
) cs |
does not end with
Use the does not end with operator to match all profiles that do not end with the selected value.
...
Example
Include all profiles where the last_name does not end with Saint John.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where not coalesce(substr(a."last_name", -length('Saint John'), length('Saint John')) = 'Saint John' |
...
-- set session distributed_join = 'true' |
...
) cs |
is one of
Use the is one of operator to match all profiles that do not contain one of the selected
...
values.
Example
Include all profiles where the job_title is either 'VP Marketing' or 'VP sales' or 'VP Product Management'.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where a."job_title" in ('VP Marketing','VP sales','VP Product Management') |
...
-- set session distributed_join = 'true' |
...
) cs |
is not one of
Use the is not one of operator to match all profiles that do not contain one of the selected
...
values.
Example
Include all profiles where the job_title is NOT Teacher or Physical Therapist or Chemical Engineer.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where not coalesce(a."job_title" in ('Teacher','Physical Therapist','Chemical Engineer'), false) |
...
-- set session distributed_join = 'true' |
...
) cs |
is not between
Use the is not between operator to match all profiles that do not contain values that are between the selected values.
...
Example
Include all profiles where the likely_to_shop_score is not between 31
...
and 40.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where not coalesce((a."likely_to_shop_score" between 31 and 40), false) |
...
-- set session distributed_join = 'true' |
...
) cs |
exists
Use the exists operator to match all profiles where the selected attribute exists.
...
Example
Match all profiles that have an affinity_sub_category
...
score.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where a."affinity_sub_category" is not null |
...
-- set session distributed_join = 'true' |
...
) cs |
does not exist
Use the does not exist
...
operator to match all profiles where the attribute does not exist.
Example
Match all profiles that do not have a td_ip_city_postal_code value
...
.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where a."td_ip_city_postal_code" is null |
...
-- set session distributed_join = 'true' |
...
) cs |
does not exist or greater than
Use the does not exist or greater than operator to match all profiles where the attribute does not exist or is greater than a certain value.
...
Example
Match all profiles that do not have a td_ip_city_postal_code_value or have a td_ip_city_postal_code_value
...
greater than 94119.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where not coalesce(a."td_ip_city_postal_code" <= '94119', false) |
...
-- set session distributed_join = 'true' |
...
) cs |
does not exist or greater than or equal to
Use the does not exist or greater than or equal to operator to match all profiles where the attribute does not exist or is greater than or equal to a certain
...
value.
Example
Match all profiles that do not have a td_ip_city_postal_code_value or have a td_ip_city_postal_code_value greater than or equal to 94119.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where not coalesce(a."td_ip_city_postal_code" < '94119', false) |
...
-- set session distributed_join = 'true' |
...
) cs |
does not exist or
...
less than
Use the does not exist or less than operator to match all profiles where the attribute does not exist or is less than a certain value.
Example
...
Match all profiles that do not have a td_ip_city_postal_code_value or have a td_ip_city_postal_code_value less than 94119.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where not coalesce(a."td_ip_city_postal_code" >= '94119', false) |
...
-- set session distributed_join = 'true' |
...
) cs |
does not exist or less than or equal to
Use the does not exist or less than or equal
...
to operator to match all profiles where the attribute does not exist or is
...
equal to a certain value.
Example
...
Match all profiles that do not have a td_ip_city_postal_code_value or have a td_ip_city_postal_code_value less than or equal to 94119.
SQL Example
| Code Block | ||
|---|---|---|
| ||
select count(*) from ( |
...
select |
...
a."cdp_customer_id" |
...
from "cdp_audience_409"."customers" a |
...
where not coalesce(a."td_ip_city_postal_code" > '94119', false) |
...
-- set session distributed_join = 'true' |
...
) cs |
| Info | ||
|---|---|---|
| ||
When creating a segment, certain values might not appear in the contains dropdown. Only a sample subset of available values are used when generating the data used to populate the dropdown with a max 1000 records shown in the dropdown. You can manually enter the field name in instances where it does now appear in the dropdown. |
...