> ## Documentation Index
> Fetch the complete documentation index at: https://docs.linkiasoft.com/llms.txt
> Use this file to discover all available pages before exploring further.

# LQL reference: every field, metric and function

> The full reference for the report language behind Your own reports: syntax, dates, grouping, COMBINE, DRILL, and every field and metric you can use.

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](https://docs.linkiasoft.com/help/analytics/custom-reports.md).

## Contents

* [Getting started](#start)
* [Conditions (WHERE)](#where)
* [Values, people and saved filters](#values)
* [Dates and periods](#dates)
* [History (WAS / CHANGED)](#history)
* [Grouping (GROUP BY)](#group)
* [Columns and metrics (SHOW)](#show)
* [Sorting and limits (ORDER BY, LIMIT)](#order)
* [Combining queries (COMBINE, CALC)](#combine)
* [Clickable numbers (DRILL)](#drill)
* [Team scorecard (FROM team)](#team)
* [Asking the AI](#ai)
* [Limits and safety](#limits)
* [Data reference](#data-reference)
* [Functions and operators](#functions)
* [More examples](#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.

```sql theme={null}
FROM orders WHERE created >= -7d SHOW number, customer, total
```

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

```sql theme={null}
FROM orders WHERE created >= -30d SHOW count(), revenue()
```

A summary: SHOW names only metrics, one row.

```sql theme={null}
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).

```sql theme={null}
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.

```sql theme={null}
FROM leads WHERE stage WAS won AND stage = lost SHOW name, owner.name, value
```

Was won at some point, is lost now.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
FROM orders WHERE created >= -90d GROUP BY week(created) SHOW count(), revenue()
```

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

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
FROM conversations WHERE created >= -30d GROUP BY channel.name SHOW count() DRILL
```

Click a count to see those chats.

```sql theme={null}
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).

```sql theme={null}
FROM team WHERE period >= startOfMonth() SHOW name, messagesSent(), chats(), wonDeals(), orders(), revenue()
```

This month, per teammate.

```sql theme={null}
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 | Values | 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

```sql theme={null}
FROM leads WHERE created >= startOfMonth() GROUP BY stage SHOW count(), sum(value)
```

```sql theme={null}
FROM leads WHERE created >= -90d GROUP BY owner.name SHOW count(), wonValue(), winRate()
```

```sql theme={null}
FROM orders WHERE created >= -30d GROUP BY governorate SHOW count(), revenue(), cancelRate()
```

```sql theme={null}
FROM orders WHERE created >= -12w AND cod = true GROUP BY week(created) SHOW count(), confirmRate()
```

```sql theme={null}
FROM conversations WHERE created >= -30d GROUP BY channel.type SHOW count(), waitingCount(), replyRate()
```

```sql theme={null}
FROM messages WHERE created >= -7d GROUP BY day(created), sentBy SHOW count()
```

```sql theme={null}
FROM customers WHERE created >= startOfYear() GROUP BY month(created) SHOW count(), unassignedCount()
```

```sql theme={null}
FROM tasks WHERE overdue = true GROUP BY assignee.name SHOW count()
```

```sql theme={null}
FROM calls WHERE created >= -30d GROUP BY agent.name SHOW count(), answerRate(), avgHandleSeconds()
```

```sql theme={null}
FROM broadcasts WHERE created >= -90d GROUP BY campaign.name SHOW messagesSent(), deliveryRate(), readRate()
```

```sql theme={null}
FROM products WHERE created >= -30d GROUP BY name SHOW units(), revenue() ORDER BY revenue() DESC LIMIT 10
```

```sql theme={null}
FROM messages WHERE direction = inbound GROUP BY weekday(created), hourOfDay(created) SHOW count()
```

```sql theme={null}
FROM leads WHERE stage.won = true GROUP BY source SHOW median(daysToWin), p90(daysToWin)
```

```sql theme={null}
FROM orders WHERE created >= -90d GROUP BY repeatCustomer SHOW count(), revenue(), customers()
```

```sql theme={null}
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
```

```sql theme={null}
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
```

```sql theme={null}
FROM team SHOW name, wonDeals(), messagesSent(), chats() ORDER BY wonDeals DESC
```

```sql theme={null}
FROM team WHERE period >= startOfMonth() SHOW name, messagesSent(), chats(), wonDeals(), orders(), revenue()
```

```sql theme={null}
FROM team WHERE period >= -90d GROUP BY team.name SHOW count(), messagesSent(), wonDeals(), wonPerChat()
```

```sql theme={null}
FROM orders WHERE created >= -30d GROUP BY governorate SHOW count(), deliveryRate(), returnRate()
```

```sql theme={null}
FROM orders WHERE created >= -30d GROUP BY deliveryStatus SHOW count(), revenue()
```

```sql theme={null}
FROM leads WHERE created >= -90d GROUP BY source SHOW count(), orderConversion(), orderRevenue()
```

```sql theme={null}
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)
```

```sql theme={null}
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)
```

```sql theme={null}
FROM conversations WHERE created >= -30d AND fromAd = true GROUP BY ad.name SHOW count(), replyRate()
```

```sql theme={null}
FROM ads WHERE date >= -30d GROUP BY campaign.name SHOW spend(), clicks(), ctr(), costPerConversation()
```

```sql theme={null}
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)
```

```sql theme={null}
FROM conversations WHERE created >= -30d GROUP BY firstResponder.name SHOW count(), avgFirstResponseMins(), medianFirstResponseMins(), answeredWithin5m()
```

```sql theme={null}
FROM team WHERE period >= startOfMonth() SHOW name, firstResponses(), avgFirstResponseMins(), medianFirstResponseMins()
```

```sql theme={null}
FROM orders WHERE created >= -30d GROUP BY governorate SHOW count(), revenue() DRILL SHOW number, customer, total, deliveryStatus
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.