Explore queries

What it is

POST /client/v1/data/query is the self-serve half of the Data API. The fixed reports (/events/summary, /events/breakdown, /sessions/breakdown) answer the questions we anticipated. This route answers the ones you compose: any event, any property, filters, several series, a breakdown, a previous-period compare, an ordered funnel with a conversion window, or the journeys people took before a conversion, with first-touch and last-touch counts.

You send a spec, not SQL. The spec is validated (unknown keys are rejected), compiled on the server to ClickHouse SQL over a wide, typed events table, and scoped to your location by the compiler itself. There is no field in the spec that can name a tenant, a table, or raw SQL.

A first trend

One series[] entry per spec series × breakdown value. data[] is aligned to buckets[] and zero-filled; previous[] is aligned by index to previous_buckets[]. Omit interval and each entry carries a single value (and previous_value) instead.

The spec

Filters

{ "property": "...", "op": "...", "value": ... }

Properties

Typed names resolve to indexed columns. Anything else is looked up as a custom event property.

Discover keys with GET /properties?queryable=true, which lists only the event properties this endpoint can read, and values with GET /query/values.

A funnel

Step k counts users who reached steps 1 through k in order within the window. By default users are distinct_ids, so an anonymous visitor who identifies between two steps is two ids and the funnel breaks there. Add "grain": "person" to resolve ids to people first and keep the journey whole — meta.grain echoes what was counted.

That matters most when a step is not a browser event. A CRM or server-side conversion arrives under a contact id, never the visitor's distinct_id, so at device grain the funnel structurally cannot link it: on a live web+CRM tenant a $pageview → opportunity_created funnel found 637 conversions by device and 914 by person, a rate of 3.4% against 5.2%. On a web-only funnel the two agree to within 0.1%, and person grain only costs you time.

Paths to a goal

A paths query answers "what did people do before they converted?" It finds everyone who did goal in date_range. For each person it takes the touchpoints in the lookback_days before their first goal in the range, merges consecutive repeats, and counts identical journeys.

• Persons, not devices. By default every id behind one person (browsers, devices, CRM contact ids) is joined before the journey is built, because earlier visits are usually on another device or arrive before the person was identified. "grain": "device" builds journeys per distinct_id instead.
• Session touchpoints start at or before the goal, so the goal's own session is the last touch. Event touchpoints are strictly before the goal.
• First touch is the first touchpoint in the lookback. Last touch is the one closest to the goal. Both count every goal person once, so each list sums to goal_persons - no_touchpoint.
• share is over goal_persons, including people whose journey is outside limit.
• no_touchpoint counts goal persons with nothing tracked in the lookback, typically a CRM conversion whose contact was never linked to a browser visit.
• truncated marks journeys that had more than depth touchpoints. Only the last depth are shown.
• Days to convert. median_days and avg_days run from a person's first touchpoint in the lookback to their goal, on the response and on every journey, first-touch and last-touch row (null for people with no touchpoint). They cannot exceed lookback_days, so a long cycle reads short; at_lookback counts people whose first touch sits at the edge of the lookback, and a high count means a longer lookback would change the answer.
• linear_touch[] splits each person evenly across their touchpoints (a 3-session journey gives each session 1/3). credit is the people credited (it sums to goal_persons - no_touchpoint), persons the people who had that touchpoint at all, share is credit / goal_persons, and value the goal value credited the same way. Repeats count: two Paid Social sessions in a 3-session journey give Paid Social 2/3.
• Revenue by channel. With value_property, first-touch rows answer "which channel starts the journeys that bring the money", which often differs from who brings the buyers: on one tenant Paid Social started 86% of buyers' journeys but 43% of revenue, and Direct 5% of buyers but 36%.
• avg_touchpoints is the mean number of touchpoints behind that journey before repeats were merged. For session touchpoints, that is sessions.
• Sampling. Above 20,000 goal ids, a fixed hash-based sample of 20,000 is used and sampled is true. Shares stay representative. Absolute counts cover only the sample.

Channel groups

channel_group classifies each session from its source and medium. When the session had no UTMs, those come from the referrer (google.com / organic, x.com / referral, none for direct). An ad click id overrides a referrer-derived value, never an explicit UTM: gclid/gbraid/wbraid → google / cpc, fbclid → facebook / paid, msclkid → microsoft / cpc, twclid, ttclid, li_fat_id → twitter, tiktok, linkedin / paid. The first matching rule wins:

Matching ignores case. Use "dimension": "source_medium" to see the raw pairs behind a group.

Self-referrals are not touchpoints. A session whose referrer is the landing page's own site (same registrable domain, so go2.example.com referring to example.com counts) means the visitor was already there: a new session after the timeout, or a hop between subdomains. Such sessions are dropped from the journey. If every session in a journey is one, the journey reads as Direct (direct/none, direct, (none) for the other dimensions). A referral from a different domain you also own still counts as Referral.

Journeys by event

{
  "kind": "paths",
  "date_range": { "preset": "30d" },
  "goal": { "event": "form_submitted" },
  "touchpoint": "event",
  "exclude": ["input_captured"]
}

Each step is an event name, or the pathname for a $pageview, e.g. ["/pricing", "/demo", "input_captured"].

Paths queries cannot be saved as insights yet.

Ad attribution and ROAS

An attribution query answers "which campaigns, ad sets or ads produced this revenue, and what did it cost?" Every conversion in the range is credited to the person's sessions in the lookback_days before it (default 30, across devices and CRM contact ids, as paths). A session from Meta or Google lands on its campaign; any other session lands on its channel group. Spend for the same dates is joined in, so a campaign that spent and converted nothing still has a row.

{
  "kind": "attribution",
  "date_range": { "preset": "30d" },
  "deals": {},
  "model": "last",
  "level": "campaign"
}

Conversions are either a goal event, valued by value_property ({ "goal": { "event": "order_completed" }, "value_property": "activity_value" } for GHL orders), or deals: won GHL opportunities. A deal converts once, at the time it was first marked won, if its latest status is still won, and is valued at its latest monetary value. deals.pipeline narrows to one pipeline by exact name.

How a session is matched to a campaign:

• Platform. Meta when the session has an fbclid or is Paid Social from a Meta source. Google when it has a gclid, gbraid, wbraid or gad_source, or is Paid Search from Google.
• Ids from the landing URL. Meta: campaign_id, adset_id, ad_id, then the utm_id / utm_campaign, utm_term and utm_content id template. Google: gad_campaignid (Google adds it), campaignid, adgroupid, creative. Only numeric values count, so a name or an unexpanded {{campaign.id}} never matches.
• By name. A Meta or Google session with no campaign id whose utm_campaign equals a campaign name in the synced spend (case-insensitive, Mythic's ad-tagging bid suffix removed, unique names only) gets that campaign.
• Anything else is the platform's (unmatched) row. A conversion with no session in the lookback is the (no touchpoint) channel row, so credit always adds up to the conversion count.

The response has totals over every row (conversions, revenue, spend, blended roas and cpa, the ad-credited share, unmatched_ad_conversions, no_touchpoint, over_cap, mixed_currency), filtered with the same figures after platform and account_id, and rows[] sorted by revenue then spend. Revenue is assumed to be in the ad account's currency. When spend spans more than one currency, that level's spend, roas and cpa are null rather than a sum of different currencies. Above 20,000 converting ids, attribution runs on a deterministic sample (sampled: true): the remaining conversions are counted on the (over attribution cap) row so totals stay complete, and row cpa / roas are null. A row is type: "ad" (platform, account, campaign, and ad set / ad at those levels, with names from the ad sync) or type: "channel". cpa is spend per conversion and roas revenue per unit of spend, null when the denominator is 0. Conversions under linear are fractional.

Attribution queries cannot be saved as insights yet.

Semantics worth knowing

• Unique counts are approximate. dau and unique_sessions use ClickHouse uniq(), accurate to about 1–2%.
• Buckets are UTC and every bucket in the range is present, zero where nothing happened.
• Compare aligns by index. previous[i] is the bucket i of the previous window, so week-over-week lines overlay directly.
• $$other is a real value in breakdown_values and series[], never a separate field.
• Caching. A range that ends before today is served from cache for six hours; one touching today for two minutes. meta.cached tells you which.
• Rate limit. 30 requests per 10 seconds per location, separate from the rest of the Data API.

Errors

Property values

GET /query/values?property=utm_source&days=30&limit=20 returns { success, data: { property, days, values: [{ value, count }], cached } } — the picker behind filter and breakdown inputs. Accepts the same property names as /query, an optional event to narrow, and a case-insensitive search.

Saving one as an insight

An insight is exactly this spec stored as config.query through the Builder API (POST /builder/insights) plus an insight_type that picks the visualization; run it with POST /builder/insights/:id/query. It is validated at write time, executes on this engine, and returns this response shape. See Insight config.

From MCP

The query_events and list_property_values tools are thin proxies over these two routes with the same spec as their input schema, attribution included.