Query filters
The condition builder used by segments, reports and journey conditions, with its fields, operators, groups and event aggregates.
A filter is a set of conditions that selects profiles, events or dataset records. You build one with the same condition builder in every place that needs one: segments, reports and ad-hoc queries, and the audience and condition settings of campaigns and journeys. This page explains the builder's parts so you can read and write filters with confidence.
A filter is a question, not a list. Each time it is used, Optima answers it against current data.
A condition
A condition has three parts, read left to right:
- A field to look at, such as a profile attribute.
- An operator, such as
=or contains. - A value to compare with. Some operators take no value (is set) and some take two (between) or many (in).
You group conditions with and (every condition must hold) or or (any one is enough), and you can nest groups inside groups to say things like (country is SK and tier is gold) or lifetime value is above 1000. Use the add buttons to add a condition or a group, and the remove buttons to take one away. Groups can be nested up to three levels inside the top group; a deeper filter is refused when it is saved or run.
Where a surface lets values come from the person rather than being typed, a Use a personalization value button beside the value switches it to a value read from the profile or the triggering event when the journey reaches the node. Use a fixed value switches back.
Operators
The builder only offers the operators that make sense for the field's type.
| Field type | Operators |
|---|---|
| Text | =, ≠, contains, begins with, ends with, in, not in, is set, is not set |
| Number | =, ≠, <, ≤, >, ≥, between, in, not in, is set, is not set |
| Date and time | =, ≠, <, ≤, >, ≥, between, is set, is not set |
| Yes/no | =, ≠, is set, is not set |
List of values (the values aggregate) | contains, is set, is not set |
| Cohort membership | = |
| Sub-filter | matches |
- in and not in take a list. Type each value and press Enter to add it as a chip.
- between takes two values, a start and an end.
- is set is true when the field has any value, and is not set when it has none.
Fields
The field picker groups what you can filter on. Only groups that apply to your data are shown.
| Group | What it contains |
|---|---|
| profile | The attributes of the chosen entity type. |
| identifiers | The entity's identifiers, such as email. |
| computed | Computed attributes of the entity that are enabled. |
| signals | The entity's signals; the newest stored value is compared. |
| cohorts | One entry per cohort. The only operator is =, with the box ticked for "is a member". |
| consent | One yes/no field per active purpose: ticked means consent is currently granted. |
| event: NAME | One group per event type, with the aggregate… field and the event type's own attributes. |
| dataset: NAME | One group per custom dataset linked to profiles. |
| related: NAME | One group per entity type, for conditions on linked profiles. |
| other | Fields that a saved filter refers to but your schema no longer defines. They are kept so the filter reads exactly as saved. |
Entity-scoped groups (signals, cohorts, computed) appear after you choose an entity type. If a filter shows a field in other, check that the attribute, signal or cohort still exists.
Event aggregates
The aggregate… field under an event group answers questions such as how many purchases in the last 30 days or when was the last login. Choosing it opens three selectors:
| Selector | Choices |
|---|---|
| aggregate function | count, sum, avg, min, max, uniq (distinct values), values (the set of values), first, last |
| what to aggregate | events (count the events), event time (the event timestamps), or one of the event type's attributes |
| time window | all time, a number of days such as 30d, or custom… with a window size and a window unit of minutes, hours, days, weeks or months |
Examples:
| Filter | Reads as |
|---|---|
count of events in 30d ≥ 3 | Placed at least three orders in the last 30 days. |
sum of amount in all time > 1000 | Spent more than 1000 in total. |
last of event time in all time < a date | Has not been seen since that date. |
uniq of product_id in 90d ≥ 5 | Looked at five or more distinct products in 90 days. |
The numeric functions (count, sum, avg, uniq) give numbers. first and last of event time give dates. min, max, first and last of an attribute take that attribute's type.
Linked profiles
Under related: NAME, you can filter on profiles linked to the one you are selecting, for example the household of a customer.
| Field | Meaning |
|---|---|
| linked | Ticked: has at least one linked profile of that entity type. |
| count | How many linked profiles of that type. |
| link count | How many links to a linked profile. |
| An attribute | The linked profile's attribute. Beside it, any means at least one linked profile matches, and all means every linked profile does. |
| matches sub-filter… | A whole nested filter, applied to each linked profile ("conditions on the related customer"). All its conditions must hold for the same linked profile. |
Entity types with no direct links to the one you chose are labelled (no direct links). A sub-filter can itself look at linked profiles. The chain can go up to three links deep.
Linked datasets
Under dataset: NAME, linked is ticked when the profile has a linked record, link type compares how the record is linked, and the dataset's attributes compare record values (with the same any or all choice). matches sub-filter… applies conditions to one linked record ("conditions on one linked record"): every condition in it must hold on the same record.
Filtering events and dataset records
When a report or query counts events or dataset records directly rather than profiles, the builder offers the row's own columns plus its attributes.
| Rows | Columns |
|---|---|
| Events | id, profile_id, event_type, timestamp, created_at |
| Dataset records | id, record_key, dataset_name, created_at |
An attribute with the same name as a column is shown as NAME (attribute) and written attr.NAME. You can also add profile conditions to an event or record filter by choosing an entity type: the condition is then checked against the profile the row belongs to.
Reading a saved filter
A filter is stored as plain data, and its fields are written with a prefix that says which group they belong to. You see this prefix when you view or edit a filter as JSON, and in the API:
| Prefix | Group |
|---|---|
profile.NAME | Profile attribute |
identifier.NAME | Identifier |
computed.NAME | Computed attribute |
signal.NAME | Signal |
cohort.NAME | Cohort membership |
consent.PURPOSE | Consent granted |
event.TYPE.agg.FUNCTION.WINDOW.ATTRIBUTE | Event aggregate. ATTRIBUTE is - for the event count and @ts for the event time. WINDOW is all or a number with a unit: 30d, 12h, 2w, 6mo, or minutes as 15m. |
event.TYPE.NAME | An event type's attribute. For an event type with a dot in its name (lead.qualified), use the aggregate form event.lead.qualified.agg.last.all.NAME: the aggregate, count and occurrence forms find the type up to their keyword, while this short form takes the type only up to the first dot. |
dataset.NAME.linked, dataset.NAME.any.ATTRIBUTE | Dataset conditions (any or all) |
related.ENTITY.linked, .count, .any.link_count, .any.ATTRIBUTE, .any.matches | Linked profile conditions (any or all) |
A filter that uses a name without one of these prefixes is refused as an unknown namespace.
An example, in the stored form, for customers in Slovakia who ordered at least three times in the last 30 days:
{
"combinator": "and",
"rules": [
{"field": "profile.country", "operator": "=", "value": "SK"},
{"field": "event.order.agg.count.30d.-", "operator": ">=", "value": 3}
]
}The operator names in the stored form are =, !=, <, <=, >, >=, in, not_in, contains, begins_with, ends_with, null, not_null, between and matches. Note that not_null is what the console labels is set, and null is is not set.
Who can see what
A filter can only use fields the person (or key) running it is cleared to see. A condition on a field above your clearance, or on data collected for a purpose you do not hold, is refused with a message that names the field. See Consent and purposes. Saved segments, reports and cohorts are checked against their current definition every time they run, not only when they were saved.
Common problems
| Symptom | Cause | What to do |
|---|---|---|
| A field appears under other | The attribute, signal or cohort in the saved filter no longer exists. | Pick the replacement field, or restore the missing one. |
| unknown namespace | A hand-written field has no prefix. | Add the prefix from the table above. |
| The filter is refused as too deeply nested | More than three levels of groups, or more than three linked-profile hops. | Flatten the groups, or compute the inner part as a segment or computed attribute and filter on that. |
| A cohort condition is missing from a picker | Cohort conditions are only offered where cohort membership can be evaluated. | Build the filter on a screen that offers cohorts, such as Segments. |
Related
- Segments, signals and cohorts
- Attribute types
- Expressions for formulas that a filter cannot express.