Product metrics cookbook | GraphJSON Docs

This cookbook turns common product questions into explicit metric contracts and GraphJSON SQL.

Copy the structure, not the nouns. Your activation event, meaningful activity, eligible population, time zone, and account model must come from your product.

Start with a metric contract#

Every saved metric should state:

Name:
Decision:
Entity:
Event set:
Denominator:
Window:
Time zone:
Exclusions:
Identity policy:
Freshness:
Owner:
Version:

“MAU” is not a complete definition. “Unique production accounts that completed one of three key actions in the trailing 30 days, UTC” is.

The examples assume:

{
  "event": "report_exported",
  "event_id": "evt_01J...",
  "user_id": "usr_42",
  "account_id": "acct_7",
  "plan": "pro",
  "environment": "production",
  "schema_version": 1
}

Exclude empty IDs before distinct counts.

Define meaningful activity#

Use completed customer-value actions:

data_source_connected
dashboard_published
report_exported
api_request_completed
workflow_completed

Avoid defining active usage as:

any page view
login alone
background polling
notification delivery
an internal admin action

The selected activity set is a product decision. Version it when meaning changes.

Rolling DAU, WAU, and MAU#

This snapshot uses trailing 1-, 7-, and 30-day windows:

WITH activity AS (
  SELECT
    timestamp,
    JSONExtractString(json, 'account_id') AS account_id
  FROM product_events
  WHERE
    JSONExtractString(json, 'event') IN (
      'data_source_connected',
      'dashboard_published',
      'report_exported'
    )
    AND JSONExtractString(json, 'environment') = 'production'
    AND toDateTime(timestamp) >= subtractDays(now(), 30)
)
SELECT
  uniqExactIf(
    account_id,
    account_id != ''
      AND toDateTime(timestamp) >= subtractDays(now(), 1)
  ) AS rolling_1d_active_accounts,
  uniqExactIf(
    account_id,
    account_id != ''
      AND toDateTime(timestamp) >= subtractDays(now(), 7)
  ) AS rolling_7d_active_accounts,
  uniqExactIf(
    account_id,
    account_id != ''
  ) AS rolling_30d_active_accounts
FROM activity

These are trailing windows, not calendar-day, calendar-week, or calendar-month values. Put “rolling” in the title.

For a trend, use the rolling active-user query and adapt the entity and window.

Stickiness#

A common descriptive ratio is rolling one-day active entities divided by rolling 30-day active entities:

WITH activity AS (
  SELECT
    timestamp,
    JSONExtractString(json, 'account_id') AS account_id
  FROM product_events
  WHERE
    JSONExtractString(json, 'event') IN (
      'dashboard_published',
      'report_exported'
    )
    AND toDateTime(timestamp) >= subtractDays(now(), 30)
),
totals AS (
  SELECT
    uniqExactIf(
      account_id,
      account_id != ''
        AND toDateTime(timestamp) >= subtractDays(now(), 1)
    ) AS active_1d,
    uniqExactIf(account_id, account_id != '') AS active_30d
  FROM activity
)
SELECT
  active_1d,
  active_30d,
  round(100 * active_1d / nullIf(active_30d, 0), 1)
    AS stickiness_pct
FROM totals

Stickiness is not automatically product quality. A payroll product can be valuable with monthly usage; an incident tool can be valuable when rarely opened. Choose a frequency aligned with expected behavior.

Activation rate#

Prefer one application-emitted account_activated event after the approved rule is completed.

WITH accounts AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    minIf(
      timestamp,
      JSONExtractString(json, 'event') = 'account_created'
    ) AS created_at,
    minIf(
      timestamp,
      JSONExtractString(json, 'event') = 'account_activated'
    ) AS activated_at
  FROM product_events
  WHERE
    JSONExtractString(json, 'event') IN (
      'account_created',
      'account_activated'
    )
    AND JSONExtractString(json, 'account_id') != ''
    AND toDateTime(timestamp) >= subtractDays(now(), 90)
  GROUP BY account_id
)
SELECT
  countIf(created_at > 0) AS created_accounts,
  countIf(
    activated_at >= created_at
      AND activated_at <= created_at + 7 * 24 * 60 * 60
  ) AS activated_within_7d,
  round(
    100 * activated_within_7d / nullIf(created_accounts, 0),
    1
  ) AS activation_rate_pct
FROM accounts

This denominator includes recent accounts that may not have had a full seven-day opportunity. For a settled cohort, exclude account creations newer than seven days.

Time to value#

WITH accounts AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    minIf(
      timestamp,
      JSONExtractString(json, 'event') = 'account_created'
    ) AS created_at,
    minIf(
      timestamp,
      JSONExtractString(json, 'event') = 'account_activated'
    ) AS activated_at
  FROM product_events
  WHERE
    JSONExtractString(json, 'event') IN (
      'account_created',
      'account_activated'
    )
    AND JSONExtractString(json, 'account_id') != ''
  GROUP BY account_id
)
SELECT
  round(quantileExact(0.50)(
    (activated_at - created_at) / 3600
  ), 1) AS p50_hours,
  round(quantileExact(0.90)(
    (activated_at - created_at) / 3600
  ), 1) AS p90_hours
FROM accounts
WHERE activated_at >= created_at AND created_at > 0

Use both median and a tail percentile. An average can hide a long group of accounts stuck in onboarding.

Do not calculate time to value only among activated accounts without also showing activation rate. The slowest outcome can be “never activated.”

Feature adoption#

Define:

eligible population
adoption action
observation window
entity grain

Example: active accounts that used a feature in the last 30 days:

WITH
  active AS (
    SELECT DISTINCT
      JSONExtractString(json, 'account_id') AS account_id
    FROM product_events
    WHERE
      JSONExtractString(json, 'event') IN (
        'dashboard_published',
        'report_exported'
      )
      AND JSONExtractString(json, 'account_id') != ''
      AND toDateTime(timestamp) >= subtractDays(now(), 30)
  ),
  adopted AS (
    SELECT DISTINCT
      JSONExtractString(json, 'account_id') AS account_id
    FROM product_events
    WHERE
      JSONExtractString(json, 'event') = 'scheduled_report_created'
      AND JSONExtractString(json, 'account_id') != ''
      AND toDateTime(timestamp) >= subtractDays(now(), 30)
  )
SELECT
  count() AS eligible_active_accounts,
  countIf(adopted.account_id != '') AS adopted_accounts,
  round(
    100 * adopted_accounts / nullIf(eligible_active_accounts, 0),
    1
  ) AS adoption_rate_pct
FROM active
LEFT JOIN adopted USING account_id

If only some plans can use the feature, add plan eligibility from an authoritative event-time source. Do not blame ineligible accounts for non-adoption.

Breadth, depth, and frequency#

One adoption percentage cannot describe engagement.

Dimension	Question
Breadth	How many eligible accounts use it?
Depth	How many users or workflows use it within each account?
Frequency	How often does the account return to it?

Frequency distribution:

WITH per_account AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    count() AS uses
  FROM product_events
  WHERE
    JSONExtractString(json, 'event') = 'report_exported'
    AND JSONExtractString(json, 'account_id') != ''
    AND toDateTime(timestamp) >= subtractDays(now(), 30)
  GROUP BY account_id
)
SELECT
  multiIf(
    uses = 1, '1',
    uses BETWEEN 2 AND 4, '2-4',
    uses BETWEEN 5 AND 9, '5-9',
    '10+'
  ) AS frequency_bucket,
  count() AS accounts
FROM per_account
GROUP BY frequency_bucket
ORDER BY min(uses)

Pick buckets from the product’s expected cadence.

Churn and resurrection#

This activity metric compares two settled 30-day windows:

WITH per_account AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    countIf(
      toDateTime(timestamp) >= subtractDays(now(), 30)
    ) > 0 AS active_current,
    countIf(
      toDateTime(timestamp) >= subtractDays(now(), 60)
      AND toDateTime(timestamp) < subtractDays(now(), 30)
    ) > 0 AS active_previous
  FROM product_events
  WHERE
    JSONExtractString(json, 'event') IN (
      'dashboard_published',
      'report_exported'
    )
    AND JSONExtractString(json, 'account_id') != ''
    AND toDateTime(timestamp) >= subtractDays(now(), 60)
  GROUP BY account_id
)
SELECT
  countIf(active_previous AND active_current) AS retained,
  countIf(active_previous AND NOT active_current) AS churned,
  countIf(NOT active_previous AND active_current) AS resurrected
FROM per_account

This is behavioral inactivity, not subscription churn. Use explicit billing state for commercial churn.

The query cannot identify accounts with no activity in either window because they do not appear in the scanned events. Join to an authoritative eligible account population when that denominator matters.

Cohort retention#

Retention requires:

cohort-entry event
returning activity definition
entity
interval
denominator policy

Use the retention curve guide and cohort SQL recipe.

Do not compare an immature cohort’s week 8 with an older cohort that had a full eight weeks of observation.

Revenue retention#

Net and gross revenue retention need an authoritative recurring-revenue snapshot or event-time state.

Recommended monthly fact:

{
  "event": "account_mrr_snapshot",
  "snapshot_month": "2026-07-01",
  "account_id": "acct_7",
  "mrr_minor": 4900,
  "currency": "usd",
  "pricing_model_version": "mrr-v3"
}

Generate it from the billing authority. Do not reconstruct contractual revenue from page views or present-day plan labels.

For one currency:

WITH per_account AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    maxIf(
      JSONExtractInt(json, 'mrr_minor'),
      JSONExtractString(json, 'snapshot_month') = '2026-06-01'
    ) AS starting_mrr,
    maxIf(
      JSONExtractInt(json, 'mrr_minor'),
      JSONExtractString(json, 'snapshot_month') = '2026-07-01'
    ) AS ending_mrr
  FROM billing_events
  WHERE
    JSONExtractString(json, 'event') = 'account_mrr_snapshot'
    AND JSONExtractString(json, 'currency') = 'usd'
  GROUP BY account_id
)
SELECT
  sum(starting_mrr) AS starting_mrr,
  sum(ending_mrr) AS ending_mrr,
  round(
    100 * ending_mrr / nullIf(starting_mrr, 0),
    1
  ) AS net_revenue_retention_pct,
  round(
    100 * sum(least(ending_mrr, starting_mrr))
      / nullIf(starting_mrr, 0),
    1
  ) AS gross_revenue_retention_pct
FROM per_account
WHERE starting_mrr > 0

This excludes new accounts from the starting cohort. NRR allows expansion; GRR caps each account at its starting amount.

Document policies for:

annual normalization
discounts and credits
pauses
usage charges
multiple subscriptions
currency conversion

Segment responsibly#

Useful segments:

plan at event time
acquisition source
company size band
region
product version
activated versus not activated

Avoid slicing until every group tells a story. Small segments produce volatile rates and can expose individual customer behavior.

Always show numerator and denominator alongside a percentage.

Build the dashboard#

One practical order:

active accounts
activation rate
p50 and p90 time to value
feature adoption
breadth/depth/frequency
cohort retention
behavioral churn and resurrection
revenue retention from the billing authority

Put definition, entity, window, and time zone in the title or description.

Validate every metric#

For a small known interval:

choose several accounts manually
list their source facts
calculate the expected result
run the GraphJSON query
investigate differences
record exclusions

Reconcile:

event IDs
occurrence time
identity and account mapping
schema versions
duplicates
source eligibility

Metric checklist#

Decision and owner are named.
Counting entity is explicit.
Meaningful activity is versioned.
Window is calendar or rolling by design.
Time zone is explicit.
Empty identifiers are excluded.
Percentages show numerator and denominator.
Eligibility uses event-time state.
Revenue comes from an authoritative billing model.
Small segments are protected.
A hand-verified interval reconciles.

Continue with Metric governance, ClickHouse SQL cookbook, and B2B SaaS product analytics.