LQL reference: every field, metric and function

LQL is the language behind Deep Analytics → Your own reports. The report builder and Ask AI write it for you; this page is for when you want to write or adjust a query yourself. The same reference is in the app — the Docs button next to Run, where every example has a Try button. New to custom reports? Start with Your own reports and dashboards.

Getting started
Conditions (WHERE)
Values, people and saved filters
Dates and periods
History (WAS / CHANGED)
Grouping (GROUP BY)
Columns and metrics (SHOW)
Sorting and limits (ORDER BY, LIMIT)
Combining queries (COMBINE, CALC)
Clickable numbers (DRILL)
Team scorecard (FROM team)
Asking the AI
Limits and safety
Data reference
Functions and operators
More examples

Getting started

Every report is one query. It starts with FROM and the thing you count, then optional clauses in any order. Keywords are not case-sensitive. A query returns one of three shapes: a list of records, one summary row, or a row per group.

FROM <entity> WHERE … GROUP BY … SHOW … ORDER BY … LIMIT n — The full shape. Only FROM is required.

FROM orders WHERE created >= -7d SHOW number, customer, total

A list: SHOW names fields, one row per order.

FROM orders WHERE created >= -30d SHOW count(), revenue()

A summary: SHOW names only metrics, one row.

FROM orders WHERE created >= -30d GROUP BY governorate SHOW count(), revenue()

Grouped: one row per governorate, with the metrics for each.

Conditions (WHERE)

WHERE keeps only the records that match. Join conditions with AND, OR and NOT, and use brackets to group them. Filtering on an indexed field (marked in the data reference) is fastest.

source = facebook — Equals. Also != > >= < <=.
governorate IN (Cairo, Giza) · stage.name NOT IN ("Lost") — One of a list, or none of it.
customer ~ "ahmed" · name ~ "test*" · name !~ "demo" — Contains (not case-sensitive). * and ? are wildcards; !~ means does not contain.
owner IS EMPTY · ad IS NOT EMPTY — Has no value / has a value.
tags ANY ("vip", "wholesale") · tags ALL ("vip", "repeat") — List fields: has at least one of / has all of.
text ~ "acme" — Searches the main text fields of the entity at once (names, phone, company …).
(source = facebook OR source = instagram) AND NOT cod = true — Brackets and NOT. Note: != and NOT IN skip empty values — add “OR field IS EMPTY” to keep them.

Values, people and saved filters

Write text in “double quotes” when it has spaces; single words and list values (won, pending, whatsapp) can be bare. Numbers and true / false are written as they are.

stage.name = "Follow up" · value > 5000 · cod = true — Text, number and yes/no values.
owner = currentUser() — You — whoever runs the report.
owner = "sara@company.com" · owner IN membersOf("Sales") — A teammate by email, or everyone in a team.
filter = "VIP leads" — Reuses the WHERE of a saved report on the same entity (up to 3 levels deep).

Dates and periods

Dates use your company’s time zone. Use a relative amount, a calendar date, or a function. Big tables (orders, messages, chats …) look at a recent window when the query has no date condition — the result says which window was used.

created >= -30d · -12h · -2w · -3M · -1y · "-4w 2d" — Relative: m minutes, h hours, d days, w weeks, M months, y years.
created >= "2026-03-01" AND created < "2026-04-01 00:00" — Calendar dates: yyyy-MM-dd or yyyy/MM/dd, optionally with HH:mm.
created >= today() · created = yesterday() — Today and yesterday; now() is this moment.
created >= startOfMonth() — This month. Also startOfDay, startOfWeek, startOfQuarter, startOfYear and the endOf… versions.
created >= startOfMonth(-1) AND created < startOfMonth() — Last month: the number moves by whole periods (-1 = previous).

FROM leads WHERE created >= startOfWeek(-1) AND created < startOfWeek() SHOW count()

Last week, from Monday to Sunday.

History (WAS / CHANGED)

On leads you can ask about the past, not only the current value: which deals were ever in a stage, or who moved them. The fields with history are listed in the data reference.

FROM leads WHERE stage WAS won AND stage = lost SHOW name, owner.name, value

Was won at some point, is lost now.

FROM leads WHERE stage CHANGED TO won DURING (startOfMonth(), now()) SHOW name, value

Moved to won this month. Also FROM x, BY user, AFTER / BEFORE / ON a date.

FROM leads WHERE owner CHANGED AFTER -7d SHOW name, owner.name

Reassigned in the last 7 days.

Grouping (GROUP BY)

GROUP BY makes one row per value, up to 3 keys. Group a date by a period to get a timeline, or by hour of day / weekday to find patterns. Records with no value form their own “(none)” row.

GROUP BY owner.name · GROUP BY channel.type, source — By a field, or by two.

FROM orders WHERE created >= -90d GROUP BY week(created) SHOW count(), revenue()

Timeline: hour, day, week, month, quarter, year.

FROM orders WHERE created >= -90d GROUP BY hourOfDay(created) SHOW count()

Pattern: hourOfDay (00–23) or weekday (1 Monday … 7 Sunday).

GROUP BY channel.type AS channel — Rename a group column — needed to COMBINE two queries.

Columns and metrics (SHOW)

SHOW picks what appears: fields for a list, or metrics (up to 10) for a summary or groups. Every entity has ready-made metrics (revenue(), winRate() …) listed in the data reference; the general ones below work on any number field.

count() · count(phone) · countDistinct(customer) — Rows, rows with a value, distinct values.
sum(total) · avg(total) · min(total) · max(total) — Totals and averages of a number field.
median(total) · p90(daysToWin) — The typical value, and the value 90% stay under.
rate(cod = true) — Share of rows matching a condition, as a percentage.

FROM orders WHERE created >= -30d GROUP BY governorate SHOW count() AS orders, revenue() AS revenue

AS names a column; use the name in ORDER BY and CALC.

Sorting and limits (ORDER BY, LIMIT)

ORDER BY sorts by a column name, a metric or its AS name. A list shows 100 rows unless you say otherwise, up to 1000; exports take up to 5000.

FROM products WHERE created >= -30d GROUP BY name SHOW units(), revenue() ORDER BY revenue() DESC LIMIT 10

Top 10. DESC = biggest first, ASC = smallest first.

FROM leads WHERE owner = currentUser() SHOW name, value ORDER BY value DESC LIMIT 50

Sort a list by a field.

Combining queries (COMBINE, CALC)

COMBINE puts numbers from different entities side by side — orders next to chats, spend next to revenue. Write up to 4 grouped queries, give the shared key the same AS name in each, and join them with COMBINE BY that name. Each part keeps its own WHERE, so repeat the date range. Every metric needs its own AS name. CALC adds up to 10 calculated columns (+ − × ÷, brackets, ROUND); dividing by zero leaves the cell empty. ORDER BY and LIMIT go at the very end.

FROM orders WHERE created >= -30d GROUP BY channel.type AS channel SHOW count() AS orders COMBINE BY channel FROM conversations WHERE created >= -30d GROUP BY channel.type AS channel SHOW count() AS chats CALC orders_per_chat = ROUND(orders / chats, 2) ORDER BY orders DESC

Orders and chats per channel, and orders per chat.

FROM leads WHERE created >= startOfMonth() SHOW count() AS leads COMBINE FROM orders WHERE created >= startOfMonth() SHOW count() AS orders

Without GROUP BY: a bare COMBINE joins two summary rows.

FROM orders WHERE created >= startOfMonth() GROUP BY channel.type AS channel SHOW count() AS this_month COMBINE BY channel FROM orders WHERE created >= startOfMonth(-1) AND created < startOfMonth() GROUP BY channel.type AS channel SHOW count() AS last_month CALC change_pct = ROUND((this_month - last_month) * 100 / last_month, 1)

This month vs last month: the same query over two periods, and the % change.

FROM ads WHERE date >= -30d GROUP BY ad SHOW spend() AS spend COMBINE BY ad FROM orders WHERE created >= -30d AND ad IS NOT EMPTY GROUP BY ad SHOW revenue() AS revenue CALC roas = ROUND(revenue / spend, 2)

Return on ad spend per ad.

Clickable numbers (DRILL)

End a query with DRILL and every number in the result opens the records behind it, 50 to a page, newest first, with Open chat and Open lead links. DRILL SHOW picks the columns of that list. In a COMBINE, one DRILL at the end works for every part. Leave it out when the numbers should stay plain.

FROM conversations WHERE created >= -30d GROUP BY channel.name SHOW count() DRILL

Click a count to see those chats.

FROM orders WHERE created >= -30d GROUP BY governorate SHOW count(), revenue() DRILL SHOW number, customer, total, deliveryStatus

Choose the columns of the record list.

Team scorecard (FROM team)

FROM team gives one row per teammate with numbers gathered from several tables — messages, chats, deals, orders, calls. Set the period with WHERE period; no GROUP BY is needed (GROUP BY team.name rolls members up into teams).

FROM team WHERE period >= startOfMonth() SHOW name, messagesSent(), chats(), wonDeals(), orders(), revenue()

This month, per teammate.

FROM team WHERE period >= -90d GROUP BY team.name SHOW messagesSent(), wonDeals()

Per team instead of per person.

Asking the AI

In Ask AI, describe the report in your own words (English or Arabic) and the AI writes the query, checks that it runs, and fixes it if not. Once a query is on screen, type a change instead — “add revenue”, “only COD”, “split by month”, “compare with last month” — and only that part changes. Undo keeps your last versions. The ✨ button next to the editor does the same for a query you wrote.

Limits and safety

Reports can never slow down the CRM: each query is checked before it runs and refused if it would read too much — narrow the dates or filter on an indexed field. A run stops after 15 seconds. Each person can run 30 reports a minute. Results are cached for about a minute. You only ever see the records your role lets you see, and phone numbers stay masked for roles that cannot see them.

GROUP BY ≤ 3 keys · ≤ 10 metrics · ≤ 30 conditions · LIMIT ≤ 1000 · ≤ 1000 groups · 4000 characters — Size limits per query.

Data reference

What each entity counts, its fields (for WHERE, GROUP BY and SHOW) and its ready-made metrics. Group = can be used in GROUP BY. Fast = indexed, fastest to filter on. Phone and email fields are masked for roles without access to phone numbers and cannot be filtered on.

Leads / deals — `FROM leads`

Date field: created.
History (WAS / CHANGED) on: stage, owner, value, priority, source, probability, currency, customerType.
text ~ "…" searches: name, email, company, phone.

Field	Type	Group	Fast	Values	Description
`id`	text				Lead id
`chat`	text				The lead’s most recent chat (id)
`name`	text				Name
`email`	text (masked)				Email
`phone`	text (masked)				Phone
`company`	text	✓	✓		Company
`stage`	list value	✓	✓		The stage key, e.g. new, won, lost
`stage.name`	text	✓			Stage
`stage.won`	yes / no	✓			Stage is won
`stage.lost`	yes / no	✓			Stage is lost
`source`	list value	✓		`facebook` `instagram` `whatsapp` `website` `manual` `import`	Source
`priority`	list value	✓	✓	`urgent` `high` `medium` `low`	Priority
`probability`	number	✓	✓		Probability %
`value`	money	✓	✓		Deal value
`weightedValue`	money				Deal value × probability % (forecast: sum(weightedValue) by month(expectedClose))
`currency`	list value	✓			Currency
`customerType`	list value	✓		`individual` `business`	Customer type
`industry`	text	✓			Industry
`owner`	teammate	✓	✓		Owner
`owner.name`	text	✓			Owner name
`owner.email`	text	✓			Owner email
`channel.name`	text	✓			Channel
`channel.type`	list value	✓		`whatsapp` `messenger` `instagram` `telegram` `email` `sms`	Channel type
`tags`	tags		✓		Tags
`created`	date		✓		Created
`updated`	date				Updated
`wonAt`	date				When the deal last entered a won stage
`daysToWin`	number				Created → won, days; empty unless the deal is won (sales cycle: median(daysToWin))
`ageDays`	number				Days since the deal was created
`idleDays`	number				Days since the deal last changed (stale pipeline: idleDays > 14)
`expectedClose`	date				Expected close
`current`	yes / no	✓			false for an older deal cycle replaced by a newer one for the same person
`ad`	text	✓			The ad the lead came from: its first chat’s click-to-message ad, or its Meta lead form’s ad
`ad.name`	text	✓			Ad name from the Meta ad sync, else the ad headline (lead form name for form leads)
`campaign.name`	text	✓			Campaign of that ad (Meta ad sync; empty until an ad account is connected)
`hasOrder`	yes / no	✓			The lead’s customer (or one of its chats) has a store order that is not cancelled. Per-row lookup: combine with a date filter on large workspaces
`conversation.lastInbound`	date				Per-row lookup: combine with an indexed filter on large workspaces

Metric	Type	Better	Description
`wonCount()`	number	▲ higher	Deals in a won stage
`wonValue()`	money	▲ higher	Sum of deal value in won stages
`pipelineValue()`	money	▲ higher	Sum of deal value in stages that are neither won nor lost
`winRate()`	percentage	▲ higher	Won / (won + lost) — open deals are not counted
`orderCount()`	number	▲ higher	Store orders (not cancelled) the leads’ customers placed — see hasOrder. A person with several deal cycles counts in each; add current = true to count people once
`orderRevenue()`	money	▲ higher	Revenue of those orders, net of refunds (orders revenue()) — revenue by lead source: GROUP BY source SHOW orderRevenue()
`orderConversion()`	percentage	▲ higher	Share of leads whose customer placed a store order (hasOrder)

Store orders — `FROM orders`

Date field: created.
Without a date condition it looks at the last 90 days.
text ~ "…" searches: number, customer, email, phone.

Field	Type	Group	Fast	Values	Description
`id`	text				Order id
`number`	text				Order
`conversation`	text				The chat the order is linked to; empty for a store order nobody chatted about
`customer`	text				Customer
`phone`	text (masked)				Phone
`email`	text (masked)				Email
`created`	date		✓		Placed at
`total`	money	✓			Total
`subtotal`	money	✓			Subtotal
`shipping`	money	✓			Shipping
`discount`	money	✓			Discount
`refunded`	money	✓			Refunded
`net`	money	✓			Net (total − refunds)
`currency`	list value	✓			Currency
`cancelled`	yes / no	✓			Cancelled
`cancelReason`	list value	✓		`customer` `fraud` `inventory` `declined` `other`	Cancel reason
`financialStatus`	list value	✓		`pending` `paid` `partially_paid` `refunded` `partially_refunded` `voided` `authorized`	Payment status
`fulfillmentStatus`	list value	✓		`unfulfilled` `partial` `fulfilled` `restocked`	Fulfillment status
`cod`	yes / no	✓			Cash on delivery
`confirmation`	list value	✓		`pending` `sent` `read` `confirmed` `cancelled` `no_answer` `needs_call`	COD confirmation
`confirmationChannel`	list value	✓		`whatsapp` `sms` `voice` `agent` `thank_you_page`	Confirmed via
`governorate`	text	✓			Governorate
`source`	text	✓			Sales channel
`fromChat`	yes / no	✓			Created from chat
`tags`	list				Order tags
`discountCodes`	list				Discount codes
`owner`	teammate	✓			Chat owner
`owner.name`	text	✓			Chat owner name
`channel.name`	text	✓			Channel
`channel.type`	list value	✓			Channel type
`ad`	text	✓			The click-to-message ad the order’s chat started from; empty when none
`ad.name`	text	✓			Ad name from the Meta ad sync, else the ad headline
`campaign.name`	text	✓			Campaign of that ad (Meta ad sync)
`deliveryStatus`	list value	✓		`not_shipped` `shipped` `in_transit` `delivered` `returned`	Across all its shipments: returned (sent back to us, RTO) beats delivered beats in_transit (incl. out for delivery and a failed attempt); shipped = fulfilled with no courier status yet
`delivered`	yes / no	✓			A shipment reached the customer and none came back
`returned`	yes / no	✓			A shipment was returned to origin
`courier`	text	✓			Shipping company of the latest shipment
`repeatCustomer`	yes / no	✓			The customer had an earlier (not cancelled) order; false for a first order

Metric	Type	Better	Description
`customers()`	number	▲ higher	Distinct customers who ordered (orders per customer = count() / customers())
`repeatRate()`	percentage	▲ higher	Share of orders placed by a customer who had ordered before
`revenue()`	money	▲ higher	Net of refunds, cancelled orders excluded
`aov()`	money	▲ higher	Revenue / live orders
`cancelRate()`	percentage	▼ lower	Share of orders cancelled
`confirmRate()`	percentage	▲ higher	Confirmed / all cash-on-delivery orders
`deliveryRate()`	percentage	▲ higher	Delivered / all orders (Delivery & returns report definition)
`returnRate()`	percentage	▼ lower	Returned / (delivered + returned): the share of settled shipments that came back

Products sold (order lines) — `FROM products`

Date field: created.
Without a date condition it looks at the last 90 days.
text ~ "…" searches: name, sku.

Field	Type	Group	Fast	Values	Description
`id`	text				Line id
`conversation`	text				The chat the order came from (id); empty for a store order nobody chatted about
`name`	text	✓			Product
`productId`	text	✓			Product id
`variant`	text	✓			Variant
`sku`	text	✓			SKU
`quantity`	number	✓			Quantity
`price`	money	✓			Unit price
`lineTotal`	money				Line total
`orderNumber`	text				Order
`created`	date		✓		Order placed at
`cancelled`	yes / no	✓			Order cancelled
`cod`	yes / no	✓			Cash on delivery
`confirmation`	list value	✓		`pending` `sent` `read` `confirmed` `cancelled` `no_answer` `needs_call`	COD confirmation
`governorate`	text	✓			Governorate
`source`	text	✓			Sales channel
`fromChat`	yes / no	✓			Order created from chat
`currency`	list value	✓			Currency
`owner`	teammate	✓			Chat owner
`owner.name`	text	✓			Chat owner name
`channel.name`	text	✓			Channel
`channel.type`	list value	✓			Channel type
`ad`	text	✓			The click-to-message ad the order’s chat started from
`ad.name`	text	✓			Ad name from the Meta ad sync, else the ad headline
`campaign.name`	text	✓			Campaign of that ad (Meta ad sync)

Metric	Type	Better	Description
`units()`	number	▲ higher	Quantity on orders not cancelled
`revenue()`	money	▲ higher	Line value (price × quantity), cancelled orders excluded (the Products report definition)
`orders()`	number	▲ higher	Orders containing it
`cancelRate()`	percentage	▼ lower	Share of the orders containing it that were cancelled
`confirmRate()`	percentage	▲ higher	Confirmed / cash-on-delivery orders containing it

Conversations (chats) — `FROM conversations`

Date field: created.
Without a date condition it looks at the last 90 days.
text ~ "…" searches: name, phone.

Field	Type	Group	Fast	Values	Description
`id`	text				Conversation id
`customer`	text				Customer id
`name`	text				Customer name
`phone`	text (masked)				Phone
`status`	list value	✓	✓	`open` `pending` `snoozed` `closed`	Status
`open`	yes / no	✓			Not closed
`owner`	teammate	✓	✓		Assigned to
`owner.name`	text	✓			Assigned to (name)
`owner.email`	text	✓			Assigned to (email)
`team.name`	text	✓			Team
`channel.name`	text	✓			Channel
`channel.type`	list value	✓		`whatsapp` `facebook` `instagram` `tiktok` `telegram` `webchat` `sip`	Channel type
`awaitingReply`	yes / no	✓			The last message is from the customer
`lastDirection`	list value	✓		`inbound` `outbound`	Last message from
`hasInbound`	yes / no	✓			Customer wrote
`hasOutbound`	yes / no	✓			Any outbound message (agent, AI or automation)
`hasLead`	yes / no	✓			Linked to a lead
`lead`	text				The lead this chat belongs to (id)
`fromAd`	yes / no	✓			Started from a click-to-message ad
`ad`	text	✓			The click-to-message ad the chat started from; empty when none
`ad.name`	text	✓			Ad name from the Meta ad sync, else the ad headline
`campaign.name`	text	✓			Campaign of that ad (Meta ad sync)
`answered`	yes / no	✓			A teammate replied from the CRM after the customer wrote (AI / automations / phone app do not count)
`firstInbound`	date				The customer message the first response answers; set on answered chats
`firstResponseAt`	date		✓		First response at
`firstResponseMins`	number				First customer message → first teammate reply, minutes; empty when unanswered
`firstResponder`	teammate	✓			The teammate who sent the first reply
`firstResponder.name`	text	✓			First responder (name)
`aiHandled`	yes / no	✓			An AI agent sent at least one message in it
`waitingHours`	number				Open chats awaiting our reply: hours since the customer’s last message; empty otherwise
`hoursToAssign`	number				Chat started → assigned to a teammate, hours
`optedOut`	yes / no	✓			Opted out
`reachability`	list value	✓		`blocked` `unreachable`	Empty when reachable
`created`	date		✓		Started
`lastMessage`	date		✓		Last message
`lastInbound`	date				Last customer message
`assignedAt`	date				Assigned at
`updated`	date				Updated

Metric	Type	Better	Description
`openCount()`	number		Chats not closed
`waitingCount()`	number	▼ lower	Open chats whose last message is from the customer
`unassignedCount()`	number	▼ lower	Open chats with nobody assigned
`customers()`	number	▲ higher	Distinct customers who chatted (e.g. customers per channel)
`replyRate()`	percentage	▲ higher	Chats the customer wrote in that got any reply (agent, AI or automation)
`avgFirstResponseMins()`	number	▼ lower	Average minutes from the customer’s first message to the first teammate reply, answered chats
`medianFirstResponseMins()`	number	▼ lower	Median of the same (the typical wait; not pulled up by a few very slow chats)
`answeredWithin5m()`	percentage	▲ higher	Chats the customer wrote in that a teammate first answered within 5 minutes (unanswered = no)
`unansweredCount()`	number	▼ lower	Chats the customer wrote in with no teammate reply yet

Chat messages — `FROM messages`

Date field: created.
Without a date condition it looks at the last 30 days.

Field	Type	Group	Fast	Values	Description
`id`	text				Message id
`conversation`	text				Conversation id
`direction`	list value	✓		`inbound` `outbound`	Direction
`sentBy`	list value	✓		`customer` `agent` `ai` `automation` `phone` `external`	agent = a teammate in the CRM; phone = a teammate from the WhatsApp app; external = e.g. Meta inbox or away messages
`agent`	teammate	✓			The teammate who sent it from the CRM; empty for anything else
`agent.name`	text	✓			Agent name
`type`	list value	✓		`text` `image` `video` `audio` `document` `sticker` `template` `interactive` `location` `reaction`	Message type
`status`	list value	✓		`received` `processing` `sent` `delivered` `read` `played` `failed`	Delivery status
`failed`	yes / no	✓			Failed
`owner`	teammate	✓			Chat assigned to
`owner.name`	text	✓			Chat assigned to (name)
`channel.name`	text	✓			Channel
`channel.type`	list value	✓		`whatsapp` `facebook` `instagram` `tiktok` `telegram` `webchat` `sip`	Channel type
`created`	date		✓		Sent at

Metric	Type	Better	Description
`received()`	number		Messages from customers
`sent()`	number		Messages to customers, by anyone (agent, AI, automation…)
`agentReplies()`	number		Sent by a teammate from the CRM
`aiReplies()`	number		Sent by the AI agent
`aiShare()`	percentage		AI messages / all outbound messages
`chats()`	number		Distinct conversations with a message
`failRate()`	percentage	▼ lower	Outbound messages that failed to send

Customers — `FROM customers`

Date field: created.
text ~ "…" searches: name, company, email, phone.

Field	Type	Group	Fast	Values	Description
`id`	text				Customer id
`chat`	text				The customer’s most recent chat (id)
`name`	text				Name
`company`	text	✓	✓		Company
`email`	text (masked)				Email
`phone`	text (masked)				Phone
`status`	list value	✓	✓	`active` `inactive` `prospect` `archived`	Status
`type`	list value	✓		`individual` `business`	Customer type
`source`	list value	✓		`website` `referral` `import` `manual` `api`	How the record was created (api = from a chat or integration)
`industry`	text	✓			Industry
`owner`	teammate	✓	✓		Owner
`owner.name`	text	✓			Owner name
`owner.email`	text	✓			Owner email
`tags`	tags		✓		Tags
`channel.type`	list value	✓		`whatsapp` `facebook` `instagram` `tiktok` `telegram` `webchat` `sip`	Channel of their first chat. Per-row lookup: combine with a date or indexed filter on large workspaces
`conversation.lastInbound`	date				Per-row lookup: combine with a date or indexed filter on large workspaces
`created`	date		✓		Created
`updated`	date				Updated

Metric	Type	Better	Description
`unassignedCount()`	number	▼ lower	Customers nobody owns
`phoneRate()`	percentage	▲ higher	Share of customers with a phone number
`emailRate()`	percentage	▲ higher	Share of customers with an email address

Tasks — `FROM tasks`

Date field: created.
text ~ "…" searches: title.

Field	Type	Group	Fast	Values	Description
`id`	text				Task id
`title`	text				Title
`status`	list value	✓	✓	`pending` `in_progress` `on_hold` `completed` `cancelled`	Status
`open`	yes / no	✓			Pending, in progress or on hold
`overdue`	yes / no	✓			Open and past its due date
`priority`	list value	✓	✓	`urgent` `high` `medium` `low`	Priority
`assignee`	teammate	✓	✓		The first assignee
`assignee.name`	text	✓			Assignee name
`creator`	teammate	✓	✓		Created by
`creator.name`	text	✓			Created by (name)
`tags`	list				Tags
`due`	date		✓		Due
`completedAt`	date				Completed at
`hoursToComplete`	number				Created → completed, hours; empty unless completed
`created`	date		✓		Created
`updated`	date				Updated

Metric	Type	Better	Description
`openCount()`	number		Pending, in progress or on hold
`overdueCount()`	number	▼ lower	Open tasks past their due date
`completionRate()`	percentage	▲ higher	Completed / all tasks except cancelled
`avgHoursToComplete()`	number	▼ lower	Created → completed, completed tasks only

Phone calls — `FROM calls`

Date field: created.
Without a date condition it looks at the last 90 days.
text ~ "…" searches: phone, disposition.

Field	Type	Group	Fast	Values	Description
`id`	text				Call id
`direction`	list value	✓		`inbound` `outbound`	Direction
`status`	list value	✓		`ended` `missed` `failed` `voicemail` `queued` `ringing` `connected` `wrapup` `ivr` `created`	Status
`answered`	yes / no	✓			Answered
`missed`	yes / no	✓			Inbound missed call, as the Calls report counts it (short abandons excluded)
`missedReason`	list value	✓		`no_answer` `abandoned` `short_abandon` `no_agent` `outside_hours` `caller_choice` `ivr_abandoned` `ivr_hangup` `owner_offline` `outbound_abandoned`	Missed reason
`endReason`	list value	✓		`busy` `no_answer` `rejected` `carrier_error` `internal_error` `cancelled` `no_line` `not_entitled`	Failure reason
`provider`	list value	✓		`sip` `whatsapp`	Line
`agent`	teammate	✓	✓		Agent
`agent.name`	text	✓			Agent name
`phone`	text (masked)				Customer number
`channel.name`	text	✓			Line name
`queue.name`	text	✓			Queue
`duration`	number	✓			Talk time (s)
`waitSeconds`	number	✓			Wait (s)
`handleSeconds`	number	✓			Talk + wrap-up
`disposition`	text	✓			Disposition
`hangupBy`	list value	✓		`caller` `agent` `system`	Hung up by
`transfers`	number	✓			Transfers
`recorded`	yes / no	✓			Recorded
`created`	date		✓		Started at
`answeredAt`	date				Answered at

Metric	Type	Better	Description
`answerRate()`	percentage	▲ higher	Answered inbound / inbound calls offered to agents (Calls report definition)
`missedCount()`	number	▼ lower	Inbound calls missed (short abandons excluded)
`avgHandleSeconds()`	number		Talk + wrap-up of answered calls, seconds
`avgAnswerSeconds()`	number	▼ lower	Seconds an answered inbound caller waited

Broadcasts — `FROM broadcasts`

Date field: created.
text ~ "…" searches: name, template.

Field	Type	Group	Fast	Values	Description
`id`	text				Broadcast id
`name`	text				Name
`status`	list value	✓	✓	`draft` `scheduled` `sending` `paused` `completed` `cancelled` `failed`	Status
`type`	list value	✓		`whatsapp` `sms`	Sent over
`template`	text	✓			Template
`channel.name`	text	✓			Channel
`campaign.name`	text	✓			Campaign
`creator`	teammate	✓			Created by
`creator.name`	text	✓			Created by (name)
`fromAutomation`	yes / no	✓			From an automation
`recipients`	number	✓			Recipients
`sent`	number	✓			Sent
`delivered`	number	✓			Delivered
`read`	number	✓			Read
`failed`	number	✓			Failed
`created`	date		✓		Created
`scheduled`	date				Scheduled for
`started`	date				Started sending
`completedAt`	date				Finished

Metric	Type	Better	Description
`messagesSent()`	number		Sum of messages sent
`deliveryRate()`	percentage	▲ higher	Delivered / sent
`readRate()`	percentage	▲ higher	Read / delivered
`failRate()`	percentage	▼ lower	Failed / (sent + failed)

Ad spend (Meta ads, per ad per day) — `FROM ads`

Date field: date.
Without a date condition it looks at the last 90 days.
Needs the analytics:read permission as well.
text ~ "…" searches: ad.name, campaign.name.

Field	Type	Group	Fast	Description
`id`	text			Row id
`date`	date		✓	The ad account’s day
`ad`	text	✓		Matches the ad field of conversations / orders / leads
`ad.name`	text	✓		Ad
`campaign`	text	✓		Campaign id
`campaign.name`	text	✓		Campaign
`adset`	text	✓		Ad set id
`adset.name`	text	✓		Ad set
`account`	text	✓		Ad account
`currency`	list value	✓		Currency
`spend`	money	✓		Spend
`impressions`	number	✓		Impressions
`clicks`	number	✓		Clicks
`conversationsStarted`	number	✓		Meta’s own count of messaging conversations the ad started

Metric	Type	Better	Description
`spend()`	money		Amount spent (account currency)
`impressions()`	number	▲ higher	Times the ads were shown
`clicks()`	number	▲ higher	Clicks on the ads
`ctr()`	percentage	▲ higher	Clicks / impressions
`conversationsStarted()`	number	▲ higher	Messaging conversations the ads started (Meta’s count)
`costPerConversation()`	money	▼ lower	Spend / conversations started; empty with none
`cpc()`	money	▼ lower	Spend / clicks

Team (per teammate) — `FROM team`

One row per teammate. The report period is period (WHERE only).
Without a date condition it looks at the last 30 days.
text ~ "…" searches: name.

Field	Type	Group	Fast	Values	Description
`user`	teammate	✓	✓		user = currentUser(), user IN membersOf(“Sales”)
`name`	text	✓			Name
`email`	text (masked)				Email
`role`	text	✓			Role name(s), comma separated when a teammate has several (filter with role ~ “Sales”)
`team.name`	text	✓			Team name(s), comma separated when in several; for one team prefer user IN membersOf(“Team”)
`active`	yes / no	✓			Active
`status`	list value	✓		`active` `inactive` `suspended`	Account status
`joined`	date				Joined
`lastLogin`	date				Last login
`period`	date		✓		Not a column: the window every metric is counted in, e.g. period >= startOfMonth(). Only in the top-level AND chain

Metric	Type	Better	Description
`messagesSent()`	number		Messages the teammate sent from the CRM in the period (messages sentBy = agent)
`chats()`	number		Distinct chats the teammate sent a message in, in the period
`wonDeals()`	number	▲ higher	Deals they own that entered a won stage in the period (by wonAt; Sales-rep report definition)
`wonValue()`	money	▲ higher	Value of those won deals
`leadsAssigned()`	number		Leads created in the period that they own
`openDeals()`	number		Current deals they own in a stage neither won nor lost — right now, not limited by the period
`pipelineValue()`	money	▲ higher	Value of their open deals right now
`orders()`	number	▲ higher	Store orders placed in the period from chats they own
`revenue()`	money	▲ higher	Revenue of those orders, net of refunds, cancelled excluded (orders revenue())
`callsAnswered()`	number	▲ higher	Calls they answered in the period
`tasksCompleted()`	number	▲ higher	Tasks they are assigned to that were completed in the period
`firstResponses()`	number		Chats whose first teammate reply they sent, in the period (by when the reply was sent)
`avgFirstResponseMins()`	number	▼ lower	Their average first-response minutes over those chats
`medianFirstResponseMins()`	number	▼ lower	Their median first-response minutes (per teammate only; cannot be totalled over a group)
`wonPerChat()`	percentage	▲ higher	wonDeals / chats
`messagesPerWon()`	number		messagesSent / wonDeals; empty without a win

Functions and operators

Aggregates: count(), countDistinct(), sum(), avg(), min(), max(), median(), p90(), rate()
Date buckets (GROUP BY): hour(field), day(field), week(field), month(field), quarter(field), year(field), hourOfDay(field), weekday(field)
Values: now(), today(), yesterday(), startOfDay(), startOfWeek(), startOfMonth(), startOfQuarter(), startOfYear(), endOfDay(), endOfWeek(), endOfMonth(), endOfQuarter(), endOfYear(), currentUser(), membersOf()
Relative dates: -30m, -12h, -7d, -2w, -3M, -1y, "-4w 2d"
Date formats: yyyy/MM/dd, yyyy-MM-dd, yyyy-MM-dd HH:mm, ISO 8601 with zone
Operators: =, !=, >, >=, <, <=, ~, !~, IN, NOT IN, ANY, ALL, IS EMPTY, IS NOT EMPTY
COMBINE: up to 4 queries, up to 10 CALC columns
DRILL: the records behind a number, 50 to a page, with Open chat / Open lead links

More examples

FROM leads WHERE created >= startOfMonth() GROUP BY stage SHOW count(), sum(value)

FROM leads WHERE created >= -90d GROUP BY owner.name SHOW count(), wonValue(), winRate()

FROM orders WHERE created >= -30d GROUP BY governorate SHOW count(), revenue(), cancelRate()

FROM orders WHERE created >= -12w AND cod = true GROUP BY week(created) SHOW count(), confirmRate()

FROM conversations WHERE created >= -30d GROUP BY channel.type SHOW count(), waitingCount(), replyRate()

FROM messages WHERE created >= -7d GROUP BY day(created), sentBy SHOW count()

FROM customers WHERE created >= startOfYear() GROUP BY month(created) SHOW count(), unassignedCount()

FROM tasks WHERE overdue = true GROUP BY assignee.name SHOW count()

FROM calls WHERE created >= -30d GROUP BY agent.name SHOW count(), answerRate(), avgHandleSeconds()

FROM broadcasts WHERE created >= -90d GROUP BY campaign.name SHOW messagesSent(), deliveryRate(), readRate()

FROM products WHERE created >= -30d GROUP BY name SHOW units(), revenue() ORDER BY revenue() DESC LIMIT 10

FROM messages WHERE direction = inbound GROUP BY weekday(created), hourOfDay(created) SHOW count()

FROM leads WHERE stage.won = true GROUP BY source SHOW median(daysToWin), p90(daysToWin)

FROM orders WHERE created >= -90d GROUP BY repeatCustomer SHOW count(), revenue(), customers()

FROM orders WHERE created >= -30d GROUP BY channel.type AS channel SHOW count() AS orders COMBINE BY channel FROM conversations WHERE created >= -30d GROUP BY channel.type AS channel SHOW count() AS chats CALC orders_per_chat = orders / chats ORDER BY orders DESC

FROM leads WHERE created >= -2w GROUP BY day(created) AS day SHOW count() AS leads COMBINE BY day FROM orders WHERE created >= -2w GROUP BY day(created) AS day SHOW count() AS orders

FROM team SHOW name, wonDeals(), messagesSent(), chats() ORDER BY wonDeals DESC

FROM team WHERE period >= startOfMonth() SHOW name, messagesSent(), chats(), wonDeals(), orders(), revenue()

FROM team WHERE period >= -90d GROUP BY team.name SHOW count(), messagesSent(), wonDeals(), wonPerChat()

FROM orders WHERE created >= -30d GROUP BY governorate SHOW count(), deliveryRate(), returnRate()

FROM orders WHERE created >= -30d GROUP BY deliveryStatus SHOW count(), revenue()

FROM leads WHERE created >= -90d GROUP BY source SHOW count(), orderConversion(), orderRevenue()

FROM orders WHERE created >= startOfMonth() GROUP BY channel.type AS channel SHOW count() AS this_month COMBINE BY channel FROM orders WHERE created >= startOfMonth(-1) AND created < startOfMonth() GROUP BY channel.type AS channel SHOW count() AS last_month CALC change = this_month - last_month, change_pct = ROUND((this_month - last_month) * 100 / last_month, 1)

FROM orders WHERE created >= startOfMonth() SHOW revenue() AS this_month COMBINE FROM orders WHERE created >= startOfMonth(-1) AND created < startOfMonth() SHOW revenue() AS last_month CALC change_pct = ROUND((this_month - last_month) * 100 / last_month, 1)

FROM conversations WHERE created >= -30d AND fromAd = true GROUP BY ad.name SHOW count(), replyRate()

FROM ads WHERE date >= -30d GROUP BY campaign.name SHOW spend(), clicks(), ctr(), costPerConversation()

FROM ads WHERE date >= -30d GROUP BY ad SHOW spend() AS spend COMBINE BY ad FROM orders WHERE created >= -30d AND ad IS NOT EMPTY GROUP BY ad SHOW revenue() AS revenue CALC roas = ROUND(revenue / spend, 2)

FROM conversations WHERE created >= -30d GROUP BY firstResponder.name SHOW count(), avgFirstResponseMins(), medianFirstResponseMins(), answeredWithin5m()

FROM team WHERE period >= startOfMonth() SHOW name, firstResponses(), avgFirstResponseMins(), medianFirstResponseMins()

FROM orders WHERE created >= -30d GROUP BY governorate SHOW count(), revenue() DRILL SHOW number, customer, total, deliveryStatus

​Contents

​Getting started

​Conditions (WHERE)

​Values, people and saved filters

​Dates and periods

​History (WAS / CHANGED)

​Grouping (GROUP BY)

​Columns and metrics (SHOW)

​Sorting and limits (ORDER BY, LIMIT)

​Combining queries (COMBINE, CALC)

​Clickable numbers (DRILL)

​Team scorecard (FROM team)

​Asking the AI

​Limits and safety

​Data reference

​Leads / deals — FROM leads

​Store orders — FROM orders

​Products sold (order lines) — FROM products

​Conversations (chats) — FROM conversations

​Chat messages — FROM messages

​Customers — FROM customers

​Tasks — FROM tasks

​Phone calls — FROM calls

​Broadcasts — FROM broadcasts

​Ad spend (Meta ads, per ad per day) — FROM ads

​Team (per teammate) — FROM team

​Functions and operators

​More examples

Contents