Funnel & retention analysis

SQL templates for n-step funnels, day-N retention, and time-to-event.

The two queries every analytics dashboard needs eventually:

1. Funnel. Of users who did step 1, what % did step 2, then step 3, etc.?

2. Retention. Of users who first did X on day D, what % were still doing X on day D+N?

Millimetric doesn't ship a hosted "funnel builder" UI yet — but the queries are short, the data is in ClickHouse, and you can run them directly via your ClickHouse credentials or wrap them in a route in your own app.

A funnel template

WITH steps AS (
  SELECT
    anonymous_id,
    minIf(timestamp, event_name = '$pageview')         AS s1_landed,
    minIf(timestamp, event_name = 'clicked_cta')       AS s2_clicked,
    minIf(timestamp, event_name = 'viewed_signup_form') AS s3_form,
    minIf(timestamp, event_name = 'signup')            AS s4_signup,
    minIf(timestamp, event_name = 'activated')         AS s5_activated
  FROM events
  WHERE project_id = '...'
    AND timestamp BETWEEN '2026-05-01' AND '2026-05-17'
  GROUP BY anonymous_id
)
SELECT
  countIf(s1_landed   IS NOT NULL)                                            AS landed,
  countIf(s2_clicked  IS NOT NULL AND s2_clicked  >= s1_landed)               AS clicked,
  countIf(s3_form     IS NOT NULL AND s3_form     >= s2_clicked)              AS viewed_form,
  countIf(s4_signup   IS NOT NULL AND s4_signup   >= s3_form)                 AS signed_up,
  countIf(s5_activated IS NOT NULL AND s5_activated >= s4_signup)             AS activated,

  round(100.0 * countIf(s2_clicked  IS NOT NULL AND s2_clicked  >= s1_landed) / countIf(s1_landed IS NOT NULL), 2) AS pct_to_click,
  round(100.0 * countIf(s5_activated IS NOT NULL AND s5_activated >= s4_signup) / countIf(s1_landed IS NOT NULL), 2) AS pct_landed_to_activated
FROM steps;

Key bits:

• minIf(timestamp, event_name = …) returns the first time each step happened per user.

• s_n >= s_{n-1} enforces ordering — a user who signed up before viewing the form doesn't count.

• All steps within a single window (BETWEEN …) for clean cohort math.

Funnel by traffic source

Group the same logic by entry source:

WITH steps AS (
  SELECT
    anonymous_id,
    argMin(source, timestamp)  AS first_source,
    argMin(medium, timestamp)  AS first_medium,
    minIf(timestamp, event_name = '$pageview') AS s1,
    minIf(timestamp, event_name = 'signup')    AS s4
  FROM events
  WHERE project_id = '...'
    AND timestamp > now() - INTERVAL 30 DAY
  GROUP BY anonymous_id
)
SELECT
  first_source,
  first_medium,
  countIf(s1 IS NOT NULL)              AS landed,
  countIf(s4 IS NOT NULL AND s4 >= s1) AS signed_up,
  round(100.0 * countIf(s4 IS NOT NULL AND s4 >= s1) / countIf(s1 IS NOT NULL), 2) AS pct
FROM steps
GROUP BY first_source, first_medium
ORDER BY signed_up DESC;

Drops out as a per-channel conversion rate. Combine with marketing-attribution for revenue.

Day-N retention

For users who first did X on day D, what fraction did X on day D+N?

WITH cohort AS (
  SELECT
    user_id,
    toDate(min(timestamp)) AS cohort_day
  FROM events
  WHERE project_id = '...'
    AND event_name = 'signup'
  GROUP BY user_id
),
returns AS (
  SELECT
    user_id,
    toDate(timestamp) AS active_day
  FROM events
  WHERE project_id = '...'
    AND user_id IS NOT NULL
    AND event_name IN ('feature_used', '$pageview')   -- "active" definition
)
SELECT
  c.cohort_day,
  dateDiff('day', c.cohort_day, r.active_day) AS day_n,
  uniq(c.user_id) AS users
FROM cohort c
JOIN returns r USING user_id
WHERE r.active_day >= c.cohort_day
  AND r.active_day < c.cohort_day + INTERVAL 30 DAY
GROUP BY c.cohort_day, day_n
ORDER BY c.cohort_day, day_n;

→ pivots to:

cohort_day  | day0 | day1 | day7 | day14 | day30
2026-05-01  |  100 |   62 |   38 |    24 |    18
2026-05-02  |  118 |   71 |   44 |    27 |    19
...

Day-0 is your cohort size. Each subsequent day is the % of that cohort active that day.

"Stickiness" — DAU / MAU

SELECT
  toDate(timestamp) AS day,
  uniq(user_id)     AS dau,
  (
    SELECT uniq(user_id)
    FROM events
    WHERE project_id = '...'
      AND user_id IS NOT NULL
      AND timestamp BETWEEN day - INTERVAL 30 DAY AND day
  )                 AS mau,
  round(100.0 * uniq(user_id) / mau, 2) AS dau_mau_pct
FROM events
WHERE project_id = '...'
  AND user_id IS NOT NULL
  AND timestamp > now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day;

A DAU/MAU above 20% is good; above 50% is excellent.

Time-to-event

How long from signup to first purchase?

WITH events_per_user AS (
  SELECT
    user_id,
    minIf(timestamp, event_name = 'signup')              AS signed_up,
    minIf(timestamp, event_name = 'completed_checkout')  AS first_purchase
  FROM events
  WHERE project_id = '...'
    AND user_id IS NOT NULL
  GROUP BY user_id
)
SELECT
  user_id,
  signed_up,
  first_purchase,
  dateDiff('hour', signed_up, first_purchase) AS hours_to_first_purchase
FROM events_per_user
WHERE first_purchase IS NOT NULL
ORDER BY hours_to_first_purchase;

Distribution

SELECT
  if(hours <= 1,   '0–1h',
  if(hours <= 24,  '1–24h',
  if(hours <= 168, '1–7d',
  if(hours <= 720, '7–30d', '>30d')))) AS bucket,
  count() AS users
FROM (
  SELECT
    dateDiff('hour',
      minIf(timestamp, event_name = 'signup'),
      minIf(timestamp, event_name = 'completed_checkout')
    ) AS hours
  FROM events
  WHERE project_id = '...'
  GROUP BY user_id
  HAVING hours > 0
)
GROUP BY bucket
ORDER BY min(hours);

Power-user definition

Users in the top-decile of activity over 30 days:

WITH activity AS (
  SELECT
    user_id,
    count() AS event_count
  FROM events
  WHERE project_id = '...'
    AND user_id IS NOT NULL
    AND timestamp > now() - INTERVAL 30 DAY
  GROUP BY user_id
)
SELECT user_id, event_count
FROM activity
WHERE event_count >= (SELECT quantile(0.9)(event_count) FROM activity)
ORDER BY event_count DESC;

Use that cohort to drive A/B tests, surveys, or "which feature predicts retention" analyses.

Wrapping these in your app

You can hit ClickHouse directly with your CLICKHOUSE_* credentials, but most teams build a thin server route per saved query and read from the browser:

// app/api/analytics/funnel/route.ts
import { NextResponse } from "next/server";
import { ClickHouse } from "@clickhouse/client";

const ch = new ClickHouse({
  url: process.env.CLICKHOUSE_URL!,
  username: process.env.CLICKHOUSE_USER!,
  password: process.env.CLICKHOUSE_PASSWORD!
});

export async function GET() {
  const result = await ch.query({
    query: `WITH steps AS (...) SELECT ... FROM steps`,
    format: "JSONEachRow"
  });
  return NextResponse.json(await result.json());
}

Cache aggressively — these queries get expensive at scale, and the answers don't change second-to-second.

See also

• Events, Sessions.

• GET /v1/stats — for simple aggregations without writing SQL.

• Architecture — where the data physically lives.