[hypequery](https://hypequery.com/) › [Recipes](https://hypequery.com/recipes) › Funnel analysis in ClickHouse with windowFunnel and a typed dataset Index for agents: https://hypequery.com/llms.txt # Funnel analysis in ClickHouse with windowFunnel and a typed dataset (https://hypequery.com/recipes/funnel-windowfunnel) Count users through login, query and export within 24 hours per tenant with windowFunnel, get conversion per step, and see what each strict mode changes. ## Run it ```bash npx @hypequery/cli create funnel-windowfunnel -y cd funnel-windowfunnel pnpm install pnpm dev ``` - Packages: @hypequery/datasets, @hypequery/clickhouse (tested from hypequery 2.14.0) - Stack: node, clickhouse - Source: https://github.com/hypequery/hypequery/tree/main/recipes/funnel-windowfunnel - Test: `pnpm test` - Verify: `node dist/recipe.js --json` | Env var | Required | Description | | --- | --- | --- | | CLICKHOUSE_URL | no | ClickHouse HTTP endpoint (default http://localhost:8123) | | CLICKHOUSE_USERNAME | no | ClickHouse user (default: default) | | CLICKHOUSE_PASSWORD | no | ClickHouse password (default: hypequery, the seed's password) | | CLICKHOUSE_DATABASE | no | Database holding the seed tables (default hypequery_examples) | `windowFunnel(window)(timestamp, cond1, cond2, ...)` returns, per group, the deepest step a user reached in order with every step inside `window` seconds of the first. Group by user, then count users per level: that is the funnel. Across all tenants in October 2026, 1,537 users logged in, 863 (56.1%) ran a query within 24 hours of a login, and 337 (39.0% of those, 21.9% overall) went on to export. windowFunnel cannot be a hypequery measure, because it has to be aggregated per user before you count users, so the recipe puts it in a view and the dataset counts levels over that view. The mode comparison at the end is a typed raw query. ## Demo ```text login -> query_run -> export within 24h, top tenants, 2026-10 tenant logged in ran query exported login->query query->export login->export 153 141 106 50 75.2% 47.2% 35.5% 14 99 61 20 61.6% 32.8% 20.2% 52 98 62 27 63.3% 43.5% 27.6% 9 80 54 21 67.5% 38.9% 26.3% 47 73 59 34 80.8% 57.6% 46.6% 18 71 47 26 66.2% 55.3% 36.6% 134 57 41 17 71.9% 41.5% 29.8% 36 51 39 23 76.5% 59.0% 45.1% 66 39 29 20 74.4% 69.0% 51.3% 170 33 14 0 42.4% 0.0% 0.0% all tenants 1537 863 337 56.1% 39.0% 21.9% ``` ## Install ```bash npx @hypequery/cli create funnel-windowfunnel -y cd funnel-windowfunnel docker compose -f seed/docker-compose.yml up -d --wait && (cd seed && pnpm install && pnpm seed) # or set CLICKHOUSE_* for your own ClickHouse pnpm install pnpm dev ``` `pnpm dev` creates the `funnel_windowfunnel_user_levels` view, runs the query, and prints the per-tenant table, the overall row and the mode comparison. ## What it shows - **windowFunnel once, in a view** (`sql/user-funnel.sql`): one row per tenant, user and month with `windowFunnel(86400)(occurred_at, event_type = 'login', event_type = 'query_run', event_type = 'export')`, the deepest step (0 to 3) reached within 24 hours. - **Steps as measures** (`src/dataset.ts`): "reached step N" is `countDistinct(userId)` filtered on `funnelLevel >= N`, and the three conversion rates are derived measures, so a dashboard asks for `conversion` by name. - **The generated SQL is asserted** (`expected.sql`): hypequery renders each step as `COUNT(DISTINCT if(funnel_level >= N, user_id, NULL))` in one pass over the view. - **Checked against a funnel without windowFunnel** (`src/smoke.test.ts`): the test rebuilds every tenant's funnel from sorted timestamp arrays and `arrayExists`, and the counts must match exactly. - **The strict modes, pinned** (`compareModes()` in `src/recipe.ts`): the same October funnel under every mode, with and without pre-filtering to the funnel events. ## Key file ```ts export const query = { dimensions: ['tenantId'], measures: ['loggedIn', 'ranQuery', 'exported', 'queryRate', 'exportRate', 'conversion'], filters: [between('month', '2026-10-01 00:00:00', '2026-10-31 23:59:59')], orderBy: [{ field: 'loggedIn', direction: 'desc' }, { field: 'tenantId', direction: 'asc' }], limit: 10, } satisfies DatasetQueryFor; const result = await analytics.execute(Funnel, query); ``` ## Strict modes The mode is the second parameter: `windowFunnel(86400, 'strict_order')(...)`. Users reaching each step, all tenants, October 2026: | Mode | Input | Step 1 | Step 2 | Step 3 | | --- | --- | ---: | ---: | ---: | | default | all events | 1537 | 863 | 337 | | `strict_order` | all events | 1537 | 178 | 6 | | `strict_order` | funnel events only | 1537 | 635 | 244 | | `strict_deduplication` | either | 1537 | 641 | 138 | | `strict_increase` | either | 1537 | 863 | 337 | | `strict_once` | either | 1537 | 863 | 337 | - **Default**: other events between steps are ignored, and the result does not depend on which non-funnel rows you feed it. - **`strict_order`**: any event between two steps stops the chain. On the full event stream almost nothing survives, because a page view or API call usually sits between a login and a query. What it measures depends on the rows you pass in: pre-filter to the funnel events and it means "no other funnel step in between"; leave everything in and it means "the next thing the user did". - **`strict_deduplication`**: a repeat of the step just reached ends that chain: login, query_run, query_run, export scores 2, not 3. A repeated login does not, because it can start a fresh chain. - **`strict_increase`**: steps need strictly increasing timestamps. Same answer here because the seed has almost no same-second events per user. - **`strict_once`**: each event counts towards one step only. By default one event can satisfy two consecutive conditions (with steps `e IN ('a', 'b')` then `e IN ('b', 'x')`, a single `b` passes both); our three conditions are disjoint, so nothing changes. On ClickHouse 25.8, `strict_deduplication` can also report a step that never happened inside the window: login, query_run 5 minutes later, export a day later, then another query_run returns 3 with a one-hour window, where the default mode returns 2. With the 24-hour window, 51 October users get a higher level from `strict_deduplication` than from the default mode, which should be impossible for a stricter mode. The smoke test pins this repro; check your version before trusting that mode. ## Architecture ```text events ── VIEW funnel_windowfunnel_user_levels (tenant_id, user_id, month, funnel_level = windowFunnel(86400)(...)) │ └─ dataset loginToExportFunnel ── loggedIn / ranQuery / exported (filtered countDistinct) │ queryRate / exportRate / conversion (derived) analytics.execute(Funnel, query) ──▶ output.json (asserted in CI, exact) db.rawQuery(...) ──▶ the mode comparison (typed raw SQL) ``` ## Adapt it - **Your steps.** Each condition is any boolean expression: `event_type = 'page_view' AND path = '/pricing'` works as a step. windowFunnel takes up to 32 (33 is `Too many event arguments`). - **Your window.** The first parameter is seconds when the timestamp is a `DateTime`. Change `86400` in the view; the dataset does not change. - **Funnels that cross months.** The view groups by month, so a login on the 31st and an export on the 1st do not connect. Group by `toStartOfWeek` or drop the month for a rolling funnel over a filtered range. - **Per tenant only.** Set `tenantKey: 'tenant_id'` on the dataset and every query is scoped to one tenant, which is how the embedded example serves it. - **Drop-off per step.** Add `funnelLevel` to `dimensions` and `users: measure.countDistinct('userId')` to get the count of users whose deepest step was each level. ## Related recipes - [retention-cohorts](https://hypequery.com/recipes/retention-cohorts): the same "shape it in a view, count it in a dataset" pattern. - [sessionization](https://hypequery.com/recipes/sessionization): split the same event stream into sessions instead of funnels. ## Related examples - [multi-tenant-embedded-analytics](../../examples/multi-tenant-embedded-analytics): serve a per-tenant funnel to each customer. Tested on ClickHouse 25.8 with `@hypequery/datasets` 0.29 and `@hypequery/clickhouse` 2.14. ## Key file: src/recipe.ts ```typescript import fs from 'node:fs'; import path from 'node:path'; import { fileURLToPath } from 'node:url'; import { between, type DatasetQueryFor } from '@hypequery/datasets'; import { analytics, db } from './client.js'; import { Funnel } from './dataset.js'; /** October 2026, the seed's last full month; fixed so the output is reproducible. */ export const MONTH: [string, string] = ['2026-10-01 00:00:00', '2026-10-31 23:59:59']; /** * The question: for the ten tenants with the most logged-in users in * October, how many went login -> query_run -> export within 24 hours, and * what is the conversion at each step? */ export const query = { dimensions: ['tenantId'], measures: ['loggedIn', 'ranQuery', 'exported', 'queryRate', 'exportRate', 'conversion'], filters: [between('month', MONTH[0], MONTH[1])], orderBy: [ { field: 'loggedIn', direction: 'desc' }, { field: 'tenantId', direction: 'asc' }, ], limit: 10, } satisfies DatasetQueryFor; export function toSQL(): string { return analytics.toSQL(Funnel, query); } export async function run() { const result = await analytics.execute(Funnel, query); return result.data; } /** The same funnel over every tenant: one row, the headline numbers. */ export async function overall() { const result = await analytics.execute(Funnel, { ...query, dimensions: [], orderBy: [], limit: undefined }); return result.data[0]; } export interface ModeRow { mode: string; input: 'all events' | 'funnel events only'; loggedIn: string; ranQuery: string; exported: string; } /** * The escape hatch: compare windowFunnel's modes with a typed raw query. The * mode is a parameter of the aggregate itself, which a dataset measure cannot * express, so this part is plain SQL through the same client. */ export async function compareModes(): Promise { const modes = ['', 'strict_order', 'strict_deduplication', 'strict_increase', 'strict_once']; const inputs = [ ['all events', ''], ['funnel events only', "AND event_type IN ('login', 'query_run', 'export')"], ] as const; const rows: ModeRow[] = []; for (const [input, filter] of inputs) { for (const mode of modes) { const params = mode ? `86400, '${mode}'` : '86400'; const [row] = await db.rawQuery>( `SELECT countIf(level >= 1) AS loggedIn, countIf(level >= 2) AS ranQuery, countIf(level >= 3) AS exported FROM ( SELECT windowFunnel(${params})(occurred_at, event_type = 'login', event_type = 'query_run', event_type = 'export') AS level FROM events WHERE occurred_at BETWEEN ? AND ? ${filter} GROUP BY tenant_id, user_id )`, [MONTH[0], MONTH[1]], ); rows.push({ mode: mode || 'default', input, ...row }); } } return rows; } const root = path.resolve(path.dirname(fileURLToPath(import.meta.url)), '..'); const isMain = process.argv[1] && import.meta.url === new URL(`file://${process.argv[1]}`).href; if (isMain) { run() .then(async (rows) => { if (process.argv.includes('--write')) { // Capture the artifacts shown on the recipe page and asserted in CI. fs.writeFileSync(path.join(root, 'expected.sql'), `${toSQL()}\n`); fs.writeFileSync(path.join(root, 'output.json'), `${JSON.stringify(rows, null, 2)}\n`); console.log('wrote expected.sql and output.json'); } else if (process.argv.includes('--json')) { console.log(JSON.stringify(rows, null, 2)); } else { console.log(`login -> query_run -> export within 24h, top tenants, ${MONTH[0].slice(0, 7)}\n`); console.table(rows); console.log('\nAll tenants:'); console.table([await overall()]); console.log('\nwindowFunnel modes, all tenants:'); console.table(await compareModes()); console.log('\nGenerated SQL:\n' + toSQL()); } await db.close(); }) .catch(async (error) => { console.error(error instanceof Error ? error.message : error); await db.close(); process.exit(1); }); } ``` ## Dataset: src/dataset.ts ```typescript import { dataset, dimension, divide, gte, measure, nullIfZero } from '@hypequery/datasets'; /** * The funnel as a dataset. The view (sql/user-funnel.sql) stores each user's * windowFunnel level per month: 0 = no login, 1 = logged in, 2 = then ran a * query, 3 = then exported, all inside 24 hours. "Reached step N" is then an * ordinary filtered distinct count, and conversion is a derived measure. */ export const Funnel = dataset('loginToExportFunnel', { source: 'funnel_windowfunnel_user_levels', timeKey: 'month', timeGrains: ['month', 'quarter', 'year'], dimensions: { tenantId: dimension.number({ column: 'tenant_id', label: 'Tenant' }), month: dimension.timestamp({ column: 'month', label: 'Month' }), userId: dimension.number({ column: 'user_id' }), funnelLevel: dimension.number({ column: 'funnel_level', label: 'Deepest step reached' }), }, measures: { loggedIn: measure.countDistinct('userId', { label: 'Step 1: logged in', filters: [gte('funnelLevel', 1)] }), ranQuery: measure.countDistinct('userId', { label: 'Step 2: ran a query', filters: [gte('funnelLevel', 2)] }), exported: measure.countDistinct('userId', { label: 'Step 3: exported', filters: [gte('funnelLevel', 3)] }), queryRate: measure.derived({ label: 'Login to query', uses: { from: 'loggedIn', to: 'ranQuery' }, formula: ({ from, to }) => divide(to, nullIfZero(from)), }), exportRate: measure.derived({ label: 'Query to export', uses: { from: 'ranQuery', to: 'exported' }, formula: ({ from, to }) => divide(to, nullIfZero(from)), }), conversion: measure.derived({ label: 'Login to export', description: 'Share of users who logged in and went on to export within 24 hours', uses: { from: 'loggedIn', to: 'exported' }, formula: ({ from, to }) => divide(to, nullIfZero(from)), }), }, }); ``` ## Generated SQL ```sql WITH base AS (SELECT funnel_windowfunnel_user_levels.tenant_id AS `tenantId`, COUNT(DISTINCT if((funnel_windowfunnel_user_levels.funnel_level >= 1), funnel_windowfunnel_user_levels.user_id, NULL)) AS `loggedIn`, COUNT(DISTINCT if((funnel_windowfunnel_user_levels.funnel_level >= 2), funnel_windowfunnel_user_levels.user_id, NULL)) AS `ranQuery`, COUNT(DISTINCT if((funnel_windowfunnel_user_levels.funnel_level >= 3), funnel_windowfunnel_user_levels.user_id, NULL)) AS `exported` FROM funnel_windowfunnel_user_levels WHERE toDateTime64(funnel_windowfunnel_user_levels.month, 9, 'UTC') BETWEEN ? AND ? GROUP BY `tenantId`) SELECT `tenantId`, `loggedIn`, `ranQuery`, `exported`, (toFloat64(`ranQuery`) / NULLIF(`loggedIn`, 0)) AS `queryRate`, (toFloat64(`exported`) / NULLIF(`ranQuery`, 0)) AS `exportRate`, (toFloat64(`exported`) / NULLIF(`loggedIn`, 0)) AS `conversion` FROM base ORDER BY `loggedIn` DESC, `tenantId` ASC LIMIT 10 ``` ## Output on the seed dataset ```json [ { "tenantId": 153, "loggedIn": "141", "ranQuery": "106", "exported": "50", "queryRate": "0.75177304964539", "exportRate": "0.4716981132075472", "conversion": "0.3546099290780142" }, { "tenantId": 14, "loggedIn": "99", "ranQuery": "61", "exported": "20", "queryRate": "0.6161616161616161", "exportRate": "0.32786885245901637", "conversion": "0.20202020202020202" }, { "tenantId": 52, "loggedIn": "98", "ranQuery": "62", "exported": "27", "queryRate": "0.6326530612244898", "exportRate": "0.43548387096774194", "conversion": "0.2755102040816326" }, { "tenantId": 9, "loggedIn": "80", "ranQuery": "54", "exported": "21", "queryRate": "0.675", "exportRate": "0.3888888888888889", "conversion": "0.2625" }, { "tenantId": 47, "loggedIn": "73", "ranQuery": "59", "exported": "34", "queryRate": "0.8082191780821918", "exportRate": "0.576271186440678", "conversion": "0.4657534246575342" }, { "tenantId": 18, "loggedIn": "71", "ranQuery": "47", "exported": "26", "queryRate": "0.6619718309859155", "exportRate": "0.5531914893617021", "conversion": "0.36619718309859156" }, { "tenantId": 134, "loggedIn": "57", "ranQuery": "41", "exported": "17", "queryRate": "0.7192982456140351", "exportRate": "0.4146341463414634", "conversion": "0.2982456140350877" }, { "tenantId": 36, "loggedIn": "51", "ranQuery": "39", "exported": "23", "queryRate": "0.7647058823529411", "exportRate": "0.5897435897435898", "conversion": "0.45098039215686275" }, { "tenantId": 66, "loggedIn": "39", "ranQuery": "29", "exported": "20", "queryRate": "0.7435897435897436", "exportRate": "0.6896551724137931", "conversion": "0.5128205128205128" }, { "tenantId": 170, "loggedIn": "33", "ranQuery": "14", "exported": "0", "queryRate": "0.42424242424242425", "exportRate": "0", "conversion": "0" } ] ``` ## Related - [Weekly retention cohorts in ClickHouse with a typed dataset](https://hypequery.com/recipes/retention-cohorts): Turn signup cohorts and weekly activity into a retention dataset: cohort size, active users and retention ratio, sliceable by cohort and week offset. - [Sessionization in ClickHouse with lagInFrame and a typed dataset](https://hypequery.com/recipes/sessionization): Split events into 30-minute inactivity sessions per user with lagInFrame and a running sum, then count sessions and average their length with a dataset. - [Multi-tenant embedded analytics on ClickHouse with signed tokens](https://hypequery.com/examples/multi-tenant-embedded-analytics): Embed tenant-scoped ClickHouse analytics in your product: your server mints a short-lived token, hypequery enforces the tenant on every query. - [All recipes](https://hypequery.com/recipes) - [/clickhouse-product-analytics](https://hypequery.com/clickhouse-product-analytics) - [/clickhouse-saas-analytics](https://hypequery.com/clickhouse-saas-analytics) - [/clickhouse-typescript](https://hypequery.com/clickhouse-typescript)