> For the complete documentation index, see [llms.txt](https://docs.millimetric.ai/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.millimetric.ai/recipes/funnels.md).

# Funnel & retention analysis

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

```sql
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:

```sql
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](/recipes/marketing-attribution.md) for revenue.

## Day-N retention

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

```sql
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

```sql
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`?

```sql
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

```sql
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:

```sql
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:

```ts
// 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](/core-concepts/events.md), [Sessions](/core-concepts/sessions.md).
* [GET /v1/stats](/api-reference/stats.md) — for simple aggregations without writing SQL.
* [Architecture](/architecture.md) — where the data physically lives.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.millimetric.ai/recipes/funnels.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.