# Query language (/docs/reference/query-language)

Every chart in RoChart is a JSON object with a `kind`. The insight editor builds it, saved insights store it, and agents send it to the MCP `query` tool or `POST /api/projects/:projectId/query`. That means an agent can rebuild any chart you see, and you can open any insight an agent saved. You only send what differs from the defaults.

| `kind` | Answers | Can be saved as an insight |
| --- | --- | --- |
| [`trends`](#trends) | Counts or totals over time | Yes |
| [`funnel`](#funnel) | Conversion through ordered steps | Yes |
| [`retention`](#retention) | Do people come back? | Yes |
| [`paths`](#paths) | Common sequences of pages or events | Yes |
| [`lifecycle`](#lifecycle) | New, returning, resurrecting and dormant people | Yes |
| [`stickiness`](#stickiness) | How many days people did it | Yes |
| [`events`](#events) | Raw events, newest first | No |
| [`sql`](#sql) | Anything else, in read-only SQL | No |
| [`game_engagement`, `game_acquisition`, `game_retention`, `game_monetization`, `game_live`](#game-analytics) | The Game analytics page | No |

Every result includes a `summary`: a few lines of plain text with the numbers that matter.

## Building blocks [#building-blocks]

### Date ranges [#date-ranges]

```json
{ "dateRange": { "from": "-30d" } }
```

`from` takes `-30min`, `-1h`, `-3h`, `-24h`, `-7d`, `-12w`, `-6m`, `-1y`, `today`, `yesterday`, `mtd`, `ytd`, `all`, or an ISO date or date and time such as `2026-10-01` or `2026-10-01T09:30`. `min` is minutes and `m` is months. `to` is optional and defaults to now. A date in `to` includes that whole day; a date and time ends exactly then. Ranges and times without an offset follow the project timezone. The default is the last 30 days (7 days for events and game analytics).

```json
{ "dateRange": { "from": "2026-11-02T09:00", "to": "2026-11-02T17:30" } }
```

### Intervals [#intervals]

`minute`, `5min`, `10min`, `hour`, `day` (default), `week` or `month`. Weeks start on Monday. Minute intervals work for up to about 1 day (`minute`), 1 week (`5min`) and 2 weeks (`10min`); hourly intervals for up to 31 days. With a minute interval, `-1h` starts on a minute boundary and `-3h` on a 5-minute one, so every bucket is whole except the current one.

### Filters [#filters]

```json
{ "type": "event", "key": "$robux", "operator": "gte", "value": 100 }
```

| `type` | Filters on |
| --- | --- |
| `event` (default) | A property of the event, such as `mode` or `$device_type` |
| `person` | The player's current property, such as `premium` or `level` |
| `group` | A property of the event's group. Add `groupTypeIndex`. |
| `cohort` | Membership: `operator` `in` or `not_in`, `value` the cohort id |

| Operator | Meaning |
| --- | --- |
| `eq` / `neq` | Equals / doesn't equal. An array works like `in`. |
| `in` / `not_in` | Is (not) one of the values in an array |
| `contains` / `not_contains` | Contains the text (not case-sensitive) |
| `regex` | Matches a regular expression |
| `gt` / `gte` / `lt` / `lte` | Number comparisons |
| `is_set` / `is_not_set` | Has a value / doesn't. Leave out `value`. |

All filters in a list must match. For "either/or", use `in` with several values, or a cohort that matches **any** rule.

### Breakdowns [#breakdowns]

```json
{ "breakdown": { "type": "person", "key": "platform", "limit": 5 } }
```

`type` is `event`, `person`, `group` (add `groupTypeIndex`) or `cohort` (with `cohortIds`, up to 10). `limit` keeps the most common values (10 by default, up to 50). Empty values show as `(none)`.

### Shared fields [#shared-fields]

`trends`, `funnel`, `retention` and `events` also take `filters` (applied everywhere) and `cohortId` (only people in that cohort).

## trends [#trends]

```json
{
  "kind": "trends",
  "series": [{ "event": "$purchase", "math": "sum", "mathProperty": "$robux" }],
  "interval": "week",
  "dateRange": { "from": "-12w" },
  "compare": true
}
```

| Field | |
| --- | --- |
| `series` | 1–10 series: `{ event, math?, mathProperty?, groupTypeIndex?, filters?, name? }`. `event: null` means all events. |
| `math` | `total` (default), `unique_persons`, `unique_sessions`, `unique_groups` (with `groupTypeIndex`), or `sum`, `avg`, `min`, `max`, `median`, `p90`, `p99` (with `mathProperty`) |
| `interval` | See above |
| `breakdown` | Optional |
| `compare` | `true` adds the previous period |
| `display` | `line`, `area`, `bar`, `number` or `table`. Only changes how the app draws it. |

The result has `labels` (bucket starts) and, per series, `data` (one value per label) and `total` (over the whole range). `incompleteFrom` is the index of a bucket that's still in progress.

```text title="summary"
$purchase · sum($robux): total 1,811,500; last complete week 427,391 (+0.6% vs previous week); peak 427,391 on 2026-10-26.
The last week is still in progress.
```

## funnel [#funnel]

```json
{
  "kind": "funnel",
  "steps": [{ "event": "$session_start" }, { "event": "tutorial_completed" }, { "event": "$purchase" }],
  "windowDays": 7,
  "dateRange": { "from": "-30d" }
}
```

| Field | |
| --- | --- |
| `steps` | 2–12 steps: `{ event, name?, filters? }` |
| `windowDays` | Days to convert from the first step, 1–365 (default 14) |
| `order` | `ordered` (default) or `strict` |
| `breakdown` | Optional. The value comes from the first step. |
| `aggregationGroupTypeIndex` | Count groups (such as clans) instead of people |

Each step in the result has `count`, `conversionFromStart`, `conversionFromPrevious` and `dropoff`.

```text title="summary"
$session_start 1,759 → tutorial_completed 625 (35.5%) → $purchase 194 (31.0%)
Overall conversion 11.0%. Biggest drop-off: $session_start → tutorial_completed (64.5% lost).
```

## retention [#retention]

```json
{
  "kind": "retention",
  "startEvent": { "event": "tutorial_completed" },
  "returnEvent": { "event": "$session_start" },
  "period": "day",
  "periods": 8
}
```

`period` is `day`, `week` (default) or `month`. `periods` is the number of columns, 2–30 (default 8). Each cohort in the result has a `start`, a `size` and `values`, the number of people who returned in each period. `average` is the weighted retention per period.

## paths [#paths]

```json
{ "kind": "paths", "include": "events", "startPoint": "$session_start", "steps": 5, "per": "session", "dateRange": { "from": "-7d" } }
```

`include` is `events`, `pageviews` (for website data) or `both`. `startPoint` and `endPoint` take an event name or a page path, and `endPoint` returns the steps leading up to it. `steps` is 2–8, and `per` is `session` or `person`. The result has `nodes` (`id` is `"<step>:<name>"`), `links` between them, and `total`.

## lifecycle [#lifecycle]

```json
{ "kind": "lifecycle", "event": { "event": "$session_start" }, "interval": "week", "dateRange": { "from": "-8w" } }
```

Returns `labels` and, per period, `new`, `returning`, `resurrecting` and `dormant` counts. The current period's `dormant` is `0` until it ends.

## stickiness [#stickiness]

```json
{ "kind": "stickiness", "series": [{ "event": "match_ended" }], "interval": "day", "dateRange": { "from": "-30d" } }
```

For each event, `counts[i]` is how many people were active on exactly `i + 1` days (or weeks, with `"interval": "week"`). `periods` is the number of days or weeks in the range.

## events [#events]

```json
{ "kind": "events", "event": "$purchase", "limit": 50 }
```

Optional fields are `event`, `personId`, `distinctId`, `groupTypeIndex` + `groupKey`, `limit` (up to 1,000, default 100) and `before`. When `truncated` is `true`, pass the last row's timestamp as `before` to get the next page.

## sql [#sql]

```json
{ "kind": "sql", "sql": "SELECT properties.$device_type AS platform, uniq(person_id) AS players FROM events WHERE event = '$session_start' GROUP BY platform" }
```

One read-only `SELECT` (or `WITH … SELECT`) over your project's data, in ClickHouse SQL. Times are in your project's timezone, and `person_id` already accounts for merged identities, so `uniq(person_id)` counts real people. The [SQL Editor](/docs/product-analytics/sql) lists every table, column and property.

| Table | One row per |
| --- | --- |
| `events` | Event. `event`, `distinct_id`, `person_id`, `session_id`, `timestamp`, `properties`, `device_type`, `country`, `group_0`…`group_4`, plus web columns (`pathname`, `referrer_domain`, `utm_*`, `browser`, `os`) for website events |
| `persons` | Person. `id`, `properties`, `created_at` |
| `sessions` | Session, by `$session_id`: `started_at`, `duration_seconds` and more |
| `groups` | Clan or other group. `group_type_index`, `group_key`, `properties` |
| Any saved query | Whatever it returns: `SELECT * FROM paying_players` |

| Shorthand | Means |
| --- | --- |
| `properties.mode`, `properties['my key']` | A property as text |
| `toFloat64OrNull(properties.$robux)` | A property as a number |
| `person.properties.name` (on `events`) | The player's property |
| `inCohort(person_id, 3)` | Whether the person is in cohort 3 |

A query runs for at most 20 seconds and returns at most 10,000 rows. Add `"display": { "type": "line", "x": "day", "y": ["users"] }` to say how the result should be charted (`table`, `line`, `bar` or `number`).

## Game analytics [#game-analytics]

The [Game analytics](/docs/game-analytics) page runs on five kinds, one per tab plus the live strip, computed from the [Roblox events](/docs/game-analytics#where-the-numbers-come-from) with the definitions on that page.

```json
{ "kind": "game_engagement", "dateRange": { "from": "-28d" }, "breakdown": "platform", "filters": [{ "dimension": "join_source", "values": ["friend", "referral"] }] }
```

| Kind | Fields | Returns |
| --- | --- | --- |
| `game_engagement` | `dateRange`, `interval` (`hour` or `day`), `breakdown`, `filters`, `compare` | DAU, new vs returning, playtime, session time, sessions, MAU and peak concurrent players |
| `game_acquisition` | `dateRange`, `by`, `filters` | New players by first join source, campaign, country, platform or locale, with each source's D1/D7 retention, playtime and revenue per new player |
| `game_retention` | `dateRange` (cohort days), `breakdown`, `filters`, `days` | D1, D7 and D30 per cohort day, stickiness and the cohort table |
| `game_monetization` | `dateRange`, `interval`, `filters`, `compare` | Robux revenue, purchases, payers, payer conversion, ARPDAU, ARPPU and top products |
| `game_live` | `filters` | Players in game now, servers, concurrent players over the last hour, and today so far vs yesterday at the same time |

Filters and breakdowns here use player dimensions instead of event properties: `platform`, `country`, `locale`, `join_source`, `campaign`, `new_vs_returning`, `premium` or `server_type`, taken from the session's `$session_start`. These kinds are new and their fields may still change; the MCP `query` tool always describes the current ones.

## Cohort definitions [#cohort-definitions]

Cohorts aren't a query kind, but their rules use the same building blocks:

```json
{
  "match": "all",
  "criteria": [
    { "type": "behavior", "event": "$purchase", "withinDays": 90, "minCount": 2 },
    { "type": "person_property", "key": "platform", "operator": "in", "value": ["Phone", "Tablet"] },
    { "type": "cohort", "cohortId": 3, "negate": true }
  ]
}
```

| Rule | Fields |
| --- | --- |
| `person_property` | `key`, `operator`, `value` |
| `behavior` | `event`, `withinDays` (default 30), `minCount` (default 1), `performed` (`false` = did *not* do it), `sumProperty` + `minSum` (a total, such as `$robux` ≥ 500), `filters` |
| `cohort` | `cohortId`, `negate` |

`match` is `all` (default) or `any`, with 1–20 rules.

## Errors [#errors]

An invalid query returns `400 invalid_query` with a message that names the field, and usually a hint:

```json
{ "error": "invalid_query", "message": "Invalid date \"last week\"", "hint": "Use relative values like \"-7d\", \"-24h\", \"-12w\", \"-6m\", \"mtd\", \"ytd\", \"today\", or an ISO date \"2026-01-31\"" }
```