SendQL cookbook

Every query on this page is type-checked against a registry when this site is built, so all of them parse and analyze. Adjust the attribute and activity names to match your own account.

Engagement

Engaged in the last month.

count(open within 30d) >= 3

Clicked a specific page this week.

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

Opened a specific email. Not a special construct — a property filter.

exists(open where template = "welcome")

Received it, never opened it. The two halves of a reminder’s audience.

exists(delivery where template = "welcome")
  and not exists(open where template = "welcome")

Gone quiet — including the people who were never loud. The or count(...) = 0 is not optional. See absence.

last(open) < now - 90d or count(open) = 0

Lifecycle

Mid-trial. Signed up between three and fourteen days ago, still on trial.

attr.plan = "trial" and now - attr.signup_date between 3d and 14d

Activated but not converted. Used the product, has not bought.

attr.plan = "trial"
  and count(activity.login within 14d) >= 2
  and not exists(activity.order)

Never activated. Signed up over a week ago and never once logged in.

now - attr.signup_date > 7d and count(activity.login) = 0

Upgrade intent. Looked at the upgrade page, has not upgraded.

exists(click where url contains "/upgrade" within 14d)
  and attr.plan in ["free", "trial"]
  and not suppressed

Future dates are not a segment

Notice what is missing from the list above: “trial ends in three days”, “renewal is next week”. You cannot express those in SendQL, and it is worth knowing why rather than fighting it.

now - attr.x measures the time since x. Durations are unsigned, so the age of a date in the future is not something the term can produce. A segment answers “who matches, right now” — and “renews in seven days” is not a property of a contact, it is an appointment.

Appointments are SendFlow’s job:

enter on 3d before attr.renewal_date

That is a date-relative trigger, and it enrols each contact exactly three days before their own renewal date. Reach for it whenever you catch yourself trying to subtract now from a date that has not happened yet.

Revenue

High-value customers.

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

Bought once, never came back.

count(activity.order) = 1 and last(activity.order) < now - 90d

Average order value above a threshold.

avg(amount of activity.order where status = "paid") > 80

Abandoned a cart in the last hour and did not check out.

exists(activity.cart_updated within 1h) and not exists(activity.checkout within 1h)

Hygiene and deliverability

Safe to send. The preamble that belongs on nearly every marketing segment.

subscribed to "marketing" and not suppressed and not unsubscribed from all

Recently bounced. Candidates for review before they harm your reputation.

count(bounce within 30d) >= 1

Hard bounces only.

exists(bounce where type = "Permanent")

Ever complained. No window — once is enough, forever.

count(complaint) = 0

A clean, engaged, sendable marketing audience, combining most of the above:

subscribed to "marketing"
  and not suppressed
  and count(complaint) = 0
  and count(bounce within 90d) = 0
  and (count(open within 60d) >= 1 or count(click within 60d) >= 1)

Composition

Build on a saved segment rather than copying its predicate around:

in segment "power-users" and not exists(activity.login within 30d)

Exclude a list — a manual do-not-contact bag, say:

attr.plan = "pro" and not in list "vip"

Idioms worth internalising