Query analytics with VoidQL

VoidQL is a read-only dialect of SQL over a project's Voidhash data: events, people, subscriptions, transactions, the catalog, paywalls, experiments, push and ads. Use it from your backend, scripts or an AI agent when a built-in report doesn't answer your question. Studio's Analytics → Query page runs the same language; see Query with VoidQL in the Help center.

The API operations and MCP tools need no early access, and they work on a self-hosted server too.

Every operation needs read access to Analytics (project:analytics:read). See API keys.

Run a query

curl https://api.voidhash.com/api/v1/projects/$VOIDHASH_PROJECT_ID/analytics/voidql/query \
  -H "Authorization: Bearer $VOIDHASH_SECRET_KEY" \
  -H "Content-Type: application/json" \
  -d '{ "query": "SELECT store, count() AS active FROM subscriptions WHERE is_active GROUP BY store", "limit": 100 }'

The response has columns (each with name and type), rows (one object per row) and isTruncated, which is true when the query had more rows than limit. Times come back as UTC text. A query changes nothing, so it is safe to repeat. From a terminal, run voidhash api call voidql.query --project my-app --body '{"query": "…"}'.

Two more operations help you write queries:

• GET …/analytics/voidql/schema lists every table and its columns, with types, descriptions and which columns are personal data, plus the functions a query may call. The voidql_schema MCP tool returns the same, with the rules and example queries.
• POST …/analytics/voidql/explain and the voidql_explain MCP tool check a query without running it: the columns it returns, the tables and projects it reads, whether it reads personal data, and an estimate of the rows it would scan.

Syntax

VoidQL is a subset of ClickHouse SELECT. One statement per query, with an optional trailing ;.

• Clauses: WITH, SELECT [DISTINCT], FROM, JOIN (INNER, LEFT, RIGHT, FULL, CROSS, with ON or USING), WHERE, PREWHERE, GROUP BY (with ROLLUP, CUBE or TOTALS), HAVING, QUALIFY, ORDER BY, LIMIT … OFFSET, LIMIT … BY.
• Set operators: UNION ALL, UNION DISTINCT, INTERSECT, EXCEPT. A bare UNION is an error.
• Subqueries in FROM (with an alias), IN (SELECT …), EXISTS (SELECT …) and as scalars.
• Window functions: fn(…) OVER (PARTITION BY … ORDER BY … ROWS BETWEEN … AND …).
• Literals: strings in single quotes ('' escapes a quote), numbers, TRUE, FALSE, NULL.
• Case: keywords, table names and function names are case-insensitive; column names are not.

Only listed functions are allowed. They include:

count(DISTINCT x) works as countDistinct(x). The schema operation returns the full list.

Times are UTC. Date functions take a time zone as their last argument: toStartOfDay(event_ts, 'Europe/Prague'). JSON keys read as text with a dot: properties.$app_version on events, traits.plan on people. There are no query parameters, so build values into the query text and escape quotes.

Tables

Every table is scoped to the project automatically; a query can't set or widen the scope.

Know these before you count money or subscriptions:

• Environments are rows. Add environment = 'production' for real revenue and subscriptions.
• Money is in minor units. *_amount columns are in the row's currency and *_usd columns in US cents. revenue.amount_usd is in US dollars, and ad_spend in millionths of the ad account's currency. VoidQL doesn't convert to your reporting currency.
• Balances add up over time. MRR at a moment is sum(mrr_change_usd) of every movement up to it, so filter the range after summing.
• A LEFT JOIN without a match reads '', 0 or 1970-01-01, not NULL, except for columns the schema marks as nullable.

MRR by month, from its movements:

WITH monthly AS (
  SELECT toStartOfMonth(event_ts) AS month, sum(mrr_change_usd) AS net_new
  FROM mrr_movements WHERE environment = 'production' GROUP BY month
)
SELECT month, net_new / 100 AS net_new_usd,
  sum(net_new) OVER (ORDER BY month) / 100 AS mrr_usd
FROM monthly ORDER BY month

Personal data

People's email, name and traits, and event keys that identify a person, such as properties.$email or context.$ip, also need read access to Customers (project:persons:read). Without it, the whole query is rejected with personal_data. people.id identifies a person without it.

Limits

A self-hosted server applies the quota only once you create its read-only analytics users; see Analytics users.

Statements other than SELECT, such as INSERT, DROP or SETTINGS, are rejected before they run.

Errors

A query that can't run fails with an error code and a message that says what to change:

Next steps

• MCP tools for analytics: let an AI agent write and run queries.
• Events: what events holds and how it's captured.
• Metric definitions: how Studio computes MRR, churn and the other metrics.
• Query with VoidQL in Studio.