Events and activities

An event term asks about things that have happened. It has four parts, and only the first is mandatory:

count( open   where template = "welcome"   within 30d ) >= 3
//     source filter                       window       comparison

The source is what happened. The filter narrows it by the source’s properties. The window bounds it in time. The comparison turns the whole thing into a boolean.

Sources: events versus activities

There are two kinds of source, and confusing them is the most common mistake in the language.

An event is a fact about an email you sent. There are exactly eight, written bare: send, delivery, open, click, bounce, complaint, reject, delivery_delay.

An activity is a fact about what the contact did in your product. Activity names are yours, written with an activity. qualifier: activity.login, activity.order, activity.cart_updated.

count(open within 30d) >= 3 and exists(activity.login within 7d)

Write an activity as if it were an event and you get told:

exists(order)

unknown event "order" — because the eight events are a closed set, and order is not one of them. You meant activity.order.

The reverse mistake is quieter. Activity names are not validated — the language cannot know which activities you record — so exists(activity.oder) is accepted and matches nothing. Spelling matters more on the activity side, not less.

The operators

Eight of them, in three families.

exists and count

exists(...) is a boolean. count(...) is a number, and always needs a comparison — a bare count(open) is a parse error, not a truthy value.

exists(click within 7d) and count(open within 30d) >= 3

Both are absence-safe: a contact with no matching events counts as zero. count(open) = 0 is how you say “never opened”, and not exists(open) says the same thing. Neither will surprise you.

Aggregates

sum, avg, min and max take a numeric property of a source, and compare to a number:

sum(amount of activity.order where status = "paid" within 90d) > 500

avg(processing_ms of delivery within 7d) < 2000

The field has to exist on the source and has to be numeric. sum(status of activity.order) is an error, and so is sum(amount of open) — open has no amount.

Recency

last and first return when something happened, so they compare against a time, not a number:

last(activity.login) < now - 14d

first(activity.signup) >= 2026-01-01

Read last(activity.login) < now - 14d as “the most recent login is older than fourteen days ago” — that is, they have gone quiet.

Recency terms are absence-sensitive, and this is the trap. A contact who has never logged in has no last(activity.login) at all, so the comparison is not false — it is undefined, and they are excluded. If you are writing a winback campaign, the people who never logged in are exactly the people you were trying to reach.

To include them, say so:

last(activity.login) < now - 14d or count(activity.login) = 0

SendQL warns you about this rather than letting you find out from the send volume. See the absence contract.

Filters: where

A where clause filters a source by its properties. It is a full boolean expression — and, or, not and parentheses all work:

exists(click where url contains "/pricing" and not user_agent contains "bot")

exists(click where (url contains "/pricing" or url contains "/upgrade") within 14d)

Event properties. Every event carries subject, template, sender_domain, config_set, channel_purpose and the numeric processing_ms. Some carry more: open and click add ip and user_agent; click adds url; bounce adds type and sub_type; complaint adds feedback_type; reject adds reason; delivery_delay adds delay_type.

template deserves special mention, because “did they open this specific email” is not a construct in the language — it is a property filter:

exists(open where template = "welcome")

There is no of "<template>" qualifier on an event source. This is the spelling.

Activity properties must be promoted. An activity can carry any payload you like, but before a property can be filtered or aggregated on, it has to be promoted to a typed, queryable column. Until then:

exists(activity.purchase where colour = "red")

activity "purchase" has no promoted property "colour"; promote it to filter on it

which is an unusually actionable error message: it does not merely tell you that you are wrong, it tells you what to go and do.

Windows

A window bounds the source in time. Two forms:

count(open within 30d) >= 3

count(open between 2026-01-01 and 2026-02-01) > 0

within <duration> is relative to now. between <time> and <time> takes two absolute or relative times, and is rejected if you write the bounds backwards.

The window binds to the source, and comes after any where clause. This is the one bit of the grammar worth memorising, because the alternative reading is plausible:

exists(click where url contains "/pricing" within 7d)

That is “a click on /pricing, in the last 7 days” — the window applies to the click, not to the URL. There is nowhere else it could go.

Omit the window entirely and the source means “ever”:

not exists(activity.order)

Putting it together

// Showed upgrade intent, is reachable, and has not already bought.
exists(click where url contains "/upgrade" within 14d)
  and count(open within 30d) >= 2
  and subscribed to "marketing"
  and not suppressed
  and not exists(activity.order within 90d)