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

Every chart in Datahop 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 |
| [`web_overview`, `web_stats`, `web_trend`](#web-analytics) | Traffic (needs Web analytics) | 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 `-24h`, `-7d`, `-12w`, `-6m`, `-1y`, `today`, `yesterday`, `mtd`, `ytd`, `all`, or an ISO date such as `2026-10-01`. `to` is optional and defaults to now. A date in `to` includes that whole day. Ranges follow the project timezone. The default is the last 30 days (7 days for events and web analytics).

### Intervals [#intervals]

`hour`, `day` (default), `week` or `month`. Weeks start on Monday. Hourly intervals work for up to 31 days.

### Filters [#filters]

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

| `type` | Filters on |
| --- | --- |
| `event` (default) | A property of the event, such as `utm_source` or `$browser` |
| `person` | The person's current property, such as `plan` |
| `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": "plan", "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": "order_completed", "math": "sum", "mathProperty": "amount" }],
  "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"
order_completed · sum(amount): 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": "signed_up" }, { "event": "project_created" }, { "event": "invite_sent" }],
  "windowDays": 14,
  "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 companies (or another group type) instead of people |

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

```text title="summary"
signed_up 1,759 → project_created 625 (35.5%) → invite_sent 194 (31.0%)
Overall conversion 11.0%. Biggest drop-off: project_created → invite_sent (69.0% lost).
```

## retention [#retention]

```json
{
  "kind": "retention",
  "startEvent": { "event": "signed_up" },
  "returnEvent": { "event": "project_opened" },
  "period": "week",
  "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": "pageviews", "startPoint": "/pricing", "steps": 5, "per": "session", "dateRange": { "from": "-7d" } }
```

`include` is `pageviews`, `events` or `both`. `startPoint` and `endPoint` take a page path or an event name, 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": "board_opened" }, "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": "board_opened" }], "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": "order_completed", "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.plan AS plan, count() AS signups FROM events WHERE event = 'signed_up' GROUP BY plan" }
```

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`, `pathname`, `referrer_domain`, `utm_*`, `browser`, `os`, `device_type`, `country`, `group_0`…`group_4` |
| `persons` | Person. `id`, `properties`, `created_at` |
| `sessions` | Visit. `started_at`, `duration_seconds`, `pageviews`, `entry_pathname`, `exit_pathname`, `referrer_domain`, `utm_source`, `is_bounce` and more |
| `groups` | Company or other group. `group_type_index`, `group_key`, `properties` |
| Any saved query | Whatever it returns: `SELECT * FROM paying_users` |

| Shorthand | Means |
| --- | --- |
| `properties.plan`, `properties['my key']` | A property as text |
| `toFloat64OrNull(properties.amount)` | A property as a number |
| `person.properties.email` (on `events`) | The person'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`).

## Web analytics [#web-analytics]

| Kind | Fields | Returns |
| --- | --- | --- |
| `web_overview` | `dateRange`, `filters`, `compare` (default `true`) | Visitors, pageviews, sessions, bounce rate (0–1), average session duration (seconds) and views per session, each with the previous period |
| `web_stats` | `by`, `dateRange`, `filters`, `limit` (default 20) | Top rows with visitors, views and bounce rate |
| `web_trend` | `dateRange`, `interval`, `filters` | Visitors, pageviews and sessions per interval |

`by` is one of `page`, `entry_page`, `exit_page`, `referrer`, `channel`, `utm_source`, `utm_medium`, `utm_campaign`, `country`, `device_type`, `browser`, `os` or `host`. See [Web analytics](/docs/web-analytics) for what the numbers mean.

## 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": "order_completed", "withinDays": 90, "minCount": 2 },
    { "type": "person_property", "key": "country", "operator": "in", "value": ["DE", "AT"] },
    { "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 `amount` ≥ 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\"" }
```