These recipes assume a product_events collection with an event name, durable user and account IDs, and purpose-specific numeric properties. Adapt names and definitions to your tracking plan; a syntactically correct query can still answer the wrong question.
For complete product definitions—active users and accounts, activation, time to value, feature adoption, resurrection, and revenue retention—use the Product metrics cookbook.
GraphJSON collections expose:
timestamp: event time as a Unix secondjson: the serialized event payload
Start with Run SQL queries if those columns or ClickHouse JSON extraction are new to you.
Create a reusable event base#
Common table expressions keep extraction logic in one place:
WITH events AS (
SELECT
toDateTime(timestamp, 'UTC') AS occurred_at,
JSONExtractString(json, 'event') AS event,
JSONExtractString(json, 'event_id') AS event_id,
JSONExtractString(json, 'user_id') AS user_id,
JSONExtractString(json, 'account_id') AS account_id,
JSONExtractString(json, 'plan') AS plan
FROM product_events
WHERE toDateTime(timestamp) >= subtractDays(now(), 30)
)
SELECT event, count() AS events
FROM events
GROUP BY event
ORDER BY events DESC
Filter the source table’s time range before expensive grouping or joins. Use a named IANA time zone when local calendar boundaries matter.
Daily active accounts#
Count an account once per day when it performs one of the actions your team defines as meaningful:
WITH active_events AS (
SELECT
toDate(toDateTime(timestamp, 'America/Los_Angeles')) AS day,
JSONExtractString(json, 'account_id') AS account_id
FROM product_events
WHERE
JSONExtractString(json, 'event') IN (
'dashboard_created',
'report_exported',
'api_key_used'
)
AND toDateTime(timestamp) >= subtractDays(now(), 30)
)
SELECT
toUnixTimestamp(toDateTime(day, 'America/Los_Angeles')) AS timestamp,
uniqExact(account_id) AS active_accounts
FROM active_events
WHERE account_id != ''
GROUP BY day
ORDER BY day
Name the saved query after the definition, such as daily_active_accounts_key_actions. “Active” is not self-explanatory.
Rolling 7-day active users#
A rolling window is not the sum of seven daily unique counts because the same person can appear on several days. Build one row for each reporting day and count users in its trailing window:
WITH
toDate(now()) AS end_day,
activity AS (
SELECT
toDate(toDateTime(timestamp, 'UTC')) AS activity_day,
JSONExtractString(json, 'user_id') AS user_id
FROM product_events
WHERE
JSONExtractString(json, 'event') = 'product_used'
AND toDateTime(timestamp) >= subtractDays(now(), 37)
),
expanded AS (
SELECT
user_id,
addDays(activity_day, offset) AS day
FROM activity
ARRAY JOIN range(7) AS offset
)
SELECT
toUnixTimestamp(toDateTime(day, 'UTC')) AS timestamp,
uniqExact(user_id) AS rolling_7d_users
FROM expanded
WHERE
user_id != ''
AND day BETWEEN end_day - 29 AND end_day
GROUP BY day
ORDER BY day
Each activity row contributes its user to that day and the following six reporting days. The extra seven days in the source scan allow the earliest displayed day to have a complete trailing window.
Percentiles for latency#
Use percentiles for skewed measurements such as request duration:
SELECT
toUnixTimestamp(
toStartOfHour(toDateTime(timestamp, 'UTC'))
) AS hour,
quantileExact(0.50)(
JSONExtractFloat(json, 'duration_ms')
) AS p50_ms,
quantileExact(0.95)(
JSONExtractFloat(json, 'duration_ms')
) AS p95_ms,
quantileExact(0.99)(
JSONExtractFloat(json, 'duration_ms')
) AS p99_ms
FROM api_events
WHERE
JSONExtractString(json, 'event') = 'api_request_completed'
AND toDateTime(timestamp) >= subtractDays(now(), 7)
GROUP BY hour
ORDER BY hour
quantileExact favors exactness. At high volume, ClickHouse’s approximate quantile functions can reduce work. Do not average already-computed p95 values; calculate the percentile from the underlying measurements for the interval.
Latest known account state#
If you record state-change events, argMax selects the value attached to the latest event time:
SELECT
JSONExtractString(json, 'account_id') AS account_id,
argMax(
JSONExtractString(json, 'plan'),
timestamp
) AS current_plan,
max(timestamp) AS changed_at
FROM billing_events
WHERE
JSONExtractString(json, 'event') = 'subscription_status_changed'
AND JSONExtractString(json, 'account_id') != ''
GROUP BY account_id
ORDER BY changed_at DESC
LIMIT 500
If two state changes can share the same whole-second timestamp, add a source sequence or higher-resolution ordering property and use it in your state model. Analytics state should not replace your application database for authorization or billing decisions.
Deduplicate retried events#
Count one row per application-generated event_id:
WITH unique_events AS (
SELECT
JSONExtractString(json, 'event_id') AS event_id,
any(JSONExtractString(json, 'event')) AS event
FROM product_events
WHERE
toDateTime(timestamp) >= subtractDays(now(), 30)
AND JSONExtractString(json, 'event_id') != ''
GROUP BY event_id
)
SELECT event, count() AS events
FROM unique_events
GROUP BY event
ORDER BY events DESC
any is appropriate only when every retry with the same ID has the same immutable payload. For mutable updates, use the explicit latest-state pattern in Deduplicate mutable business events.
Conversion within a time window#
windowFunnel evaluates ordered conditions for each account:
WITH per_account AS (
SELECT
JSONExtractString(json, 'account_id') AS account_id,
windowFunnel(7 * 24 * 60 * 60)(
toDateTime(timestamp),
JSONExtractString(json, 'event') = 'account_created',
JSONExtractString(json, 'event') = 'data_source_connected',
JSONExtractString(json, 'event') = 'dashboard_shared'
) AS step_reached
FROM product_events
WHERE
toDateTime(timestamp) >= subtractDays(now(), 60)
AND JSONExtractString(json, 'account_id') != ''
GROUP BY account_id
)
SELECT
countIf(step_reached >= 1) AS created,
countIf(step_reached >= 2) AS connected_source,
countIf(step_reached >= 3) AS shared_dashboard,
round(100 * connected_source / nullIf(created, 0), 1)
AS activation_rate_pct
FROM per_account
The funnel window begins with the first matched condition for an account. Decide whether repeated attempts, out-of-order events, and reactivation should count before using the result as an activation definition.
Cohort retention matrix#
This example defines the cohort as the account’s first account_created week and activity as a later product_used event:
WITH
cohorts AS (
SELECT
JSONExtractString(json, 'account_id') AS account_id,
toStartOfWeek(min(toDateTime(timestamp, 'UTC'))) AS cohort_week
FROM product_events
WHERE
JSONExtractString(json, 'event') = 'account_created'
AND JSONExtractString(json, 'account_id') != ''
GROUP BY account_id
),
activity AS (
SELECT DISTINCT
JSONExtractString(json, 'account_id') AS account_id,
toStartOfWeek(toDateTime(timestamp, 'UTC')) AS activity_week
FROM product_events
WHERE
JSONExtractString(json, 'event') = 'product_used'
AND JSONExtractString(json, 'account_id') != ''
)
SELECT
cohorts.cohort_week,
dateDiff('week', cohorts.cohort_week, activity.activity_week)
AS week_number,
uniqExact(activity.account_id) AS retained_accounts
FROM cohorts
INNER JOIN activity USING account_id
WHERE
activity.activity_week >= cohorts.cohort_week
AND week_number BETWEEN 0 AND 12
GROUP BY cohorts.cohort_week, week_number
ORDER BY cohorts.cohort_week, week_number
To calculate a rate, join the result to each cohort’s account count. Decide whether “week 0” requires an activity event in addition to signup; that choice changes the denominator.
Sessionize with an inactivity gap#
The query below begins a new session after 30 minutes without activity:
WITH ordered AS (
SELECT
JSONExtractString(json, 'user_id') AS user_id,
timestamp,
lagInFrame(timestamp, 1, timestamp) OVER (
PARTITION BY user_id
ORDER BY timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS previous_timestamp
FROM product_events
WHERE
toDateTime(timestamp) >= subtractDays(now(), 7)
AND JSONExtractString(json, 'user_id') != ''
),
numbered AS (
SELECT
user_id,
timestamp,
sum(
if(timestamp - previous_timestamp > 1800, 1, 0)
) OVER (
PARTITION BY user_id
ORDER BY timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS session_number
FROM ordered
)
SELECT
user_id,
session_number,
min(timestamp) AS session_started_at,
max(timestamp) AS session_ended_at,
count() AS events
FROM numbered
GROUP BY user_id, session_number
ORDER BY session_started_at DESC
LIMIT 500
This scan only sees activity inside its seven-day boundary. Widen the source interval when sessions crossing the first boundary matter.
Revenue by account without currency errors#
Store money in minor units and group by currency:
SELECT
JSONExtractString(json, 'account_id') AS account_id,
JSONExtractString(json, 'currency') AS currency,
sum(JSONExtractInt(json, 'amount')) AS revenue_minor_units
FROM billing_events
WHERE
JSONExtractString(json, 'event') = 'payment_succeeded'
AND toDateTime(timestamp) >= subtractMonths(now(), 3)
GROUP BY account_id, currency
ORDER BY revenue_minor_units DESC
LIMIT 500
Never add USD cents and EUR cents. Convert in an upstream financial model when a reporting currency is required, and preserve the conversion rate and effective date.
Make recipes production-ready#
Before saving a query:
- define the entity, event set, time zone, and interval
- exclude empty identifiers
- choose exact or approximate functions intentionally
- test boundary dates and duplicated rows
- compare a small interval to hand-verified source records
- cap raw output and aggregate for visualization
- document schema versions or historical exceptions
GraphJSON caps the outer query result at 2,000 rows. A chart should usually return tens or hundreds of aggregate points, not an event dump.