Skip to content
Optima docs
Reference

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:

  1. A field to look at, such as a profile attribute.
  2. An operator, such as = or contains.
  3. 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 typeOperators
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-filtermatches
  • 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.

GroupWhat it contains
profileThe attributes of the chosen entity type.
identifiersThe entity's identifiers, such as email.
computedComputed attributes of the entity that are enabled.
signalsThe entity's signals; the newest stored value is compared.
cohortsOne entry per cohort. The only operator is =, with the box ticked for "is a member".
consentOne yes/no field per active purpose: ticked means consent is currently granted.
event: NAMEOne group per event type, with the aggregate… field and the event type's own attributes.
dataset: NAMEOne group per custom dataset linked to profiles.
related: NAMEOne group per entity type, for conditions on linked profiles.
otherFields 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:

SelectorChoices
aggregate functioncount, sum, avg, min, max, uniq (distinct values), values (the set of values), first, last
what to aggregateevents (count the events), event time (the event timestamps), or one of the event type's attributes
time windowall 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:

FilterReads as
count of events in 30d ≥ 3Placed at least three orders in the last 30 days.
sum of amount in all time > 1000Spent more than 1000 in total.
last of event time in all time < a dateHas not been seen since that date.
uniq of product_id in 90d ≥ 5Looked 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.

FieldMeaning
linkedTicked: has at least one linked profile of that entity type.
countHow many linked profiles of that type.
link countHow many links to a linked profile.
An attributeThe 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.

RowsColumns
Eventsid, profile_id, event_type, timestamp, created_at
Dataset recordsid, 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:

PrefixGroup
profile.NAMEProfile attribute
identifier.NAMEIdentifier
computed.NAMEComputed attribute
signal.NAMESignal
cohort.NAMECohort membership
consent.PURPOSEConsent granted
event.TYPE.agg.FUNCTION.WINDOW.ATTRIBUTEEvent 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.NAMEAn 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.ATTRIBUTEDataset conditions (any or all)
related.ENTITY.linked, .count, .any.link_count, .any.ATTRIBUTE, .any.matchesLinked 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

SymptomCauseWhat to do
A field appears under otherThe attribute, signal or cohort in the saved filter no longer exists.Pick the replacement field, or restore the missing one.
unknown namespaceA hand-written field has no prefix.Add the prefix from the table above.
The filter is refused as too deeply nestedMore 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 pickerCohort conditions are only offered where cohort membership can be evaluated.Build the filter on a screen that offers cohorts, such as Segments.

On this page