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.

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

Building blocksCopy Anchor Link

Date rangesCopy Anchor Link

{ "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).

IntervalsCopy Anchor Link

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

FiltersCopy Anchor Link

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

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

BreakdownsCopy Anchor Link

{ "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 fieldsCopy Anchor Link

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

trendsCopy Anchor Link

{
  "kind": "trends",
  "series": [{ "event": "order_completed", "math": "sum", "mathProperty": "amount" }],
  "interval": "week",
  "dateRange": { "from": "-12w" },
  "compare": true
}

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.

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.

funnelCopy Anchor Link

{
  "kind": "funnel",
  "steps": [{ "event": "signed_up" }, { "event": "project_created" }, { "event": "invite_sent" }],
  "windowDays": 14,
  "dateRange": { "from": "-30d" }
}

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

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).

retentionCopy Anchor Link

{
  "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.

pathsCopy Anchor Link

{ "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.

lifecycleCopy Anchor Link

{ "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.

stickinessCopy Anchor Link

{ "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.

eventsCopy Anchor Link

{ "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.

sqlCopy Anchor Link

{ "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 lists every table, column and property.

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 analyticsCopy Anchor Link

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 for what the numbers mean.

Cohort definitionsCopy Anchor Link

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

{
  "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 }
  ]
}

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

ErrorsCopy Anchor Link

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

{ "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\"" }