Reusable behavioral cohorts and segments

A cohort is a named rule that decides which entities belong to a population. Examples include accounts that activated within seven days, users who adopted a feature but later lapsed, and customers exposed to a particular rollout.

GraphJSON does not currently provide a native saved-cohort object that can be attached to every chart. Treat a cohort as governed analytical code: keep its contract and canonical SQL in version control, reuse that SQL in saved queries, and publish membership snapshots when several consumers need the same result.

This guide extends retention curves from a single query into reusable, auditable populations.

Write the cohort contract#

Before writing SQL, specify:

The evaluation time is essential. “Accounts that have not exported” produces a different answer today than it did last week.

Choose dynamic or frozen membership#

Use a dynamic cohort when membership should change as behavior changes:

• active in the last 28 days
• currently on a paid plan
• had two failures in the last hour
• has not completed a core workflow in 14 days

Use a frozen cohort when the original population must remain stable:

• experiment assignment
• customers eligible for a launch
• accounts in a migration wave
• users acquired during a campaign

Do not reconstruct a frozen cohort from current properties. Emit the assignment or eligibility decision when it happens:

{
  "event": "cohort_assigned",
  "cohort_key": "onboarding_email_2026_07",
  "cohort_version": 1,
  "entity_type": "account",
  "account_id": "acct_7",
  "variant": "treatment",
  "assigned_at": "2026-07-15T18:20:00Z",
  "reason": "eligible_at_launch"
}

A property such as plan: "pro" is not a cohort assignment unless the plan itself is the complete, time-aware definition.

Use one entity grain#

Decide whether membership applies to a user, account, workspace, device, order, or another durable entity. Do not count users in the numerator and accounts in the denominator.

For B2B activation, aggregate member behavior to the account grain:

WITH accounts AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    min(toDateTime(timestamp)) AS created_at
  FROM product_events
  WHERE JSONExtractString(json, 'event') = 'account_created'
  GROUP BY account_id
),
first_value AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    min(toDateTime(timestamp)) AS first_value_at
  FROM product_events
  WHERE JSONExtractString(json, 'event') = 'report_exported'
  GROUP BY account_id
)
SELECT
  accounts.account_id,
  accounts.created_at,
  first_value.first_value_at,
  dateDiff('hour', accounts.created_at, first_value.first_value_at) AS hours_to_value
FROM accounts
INNER JOIN first_value USING account_id
WHERE
  first_value.first_value_at >= accounts.created_at
  AND first_value.first_value_at < accounts.created_at + INTERVAL 7 DAY

Any member may qualify the account unless the contract says that an owner, administrator, or minimum number of distinct members must act.

Define inclusion and exclusion separately#

Positive behavior and eligibility are different concerns:

eligible population
  ∩ qualifying behavior
  − explicit exclusions
  = cohort membership

Keep exclusions reviewable. Common exclusions are employees, synthetic traffic, test environments, fraudulent accounts, deleted entities, unsupported plans, and entities created before reliable instrumentation began.

Avoid a long hidden list of IDs inside one query. Maintain governed reference data, or emit a versioned eligibility snapshot.

Respect event-time properties#

Current account state can rewrite history. An account that upgraded today was not necessarily on the new plan when it activated.

Choose the intended temporal join:

• as-of behavior: plan, region, or lifecycle stage at the qualifying event
• as-of evaluation: state when membership is calculated
• current: latest known state, explicitly labeled as current

For as-of behavior, prefer properties captured at the authoritative transition or join against a versioned state history. Never use argMax(plan, timestamp) without acknowledging that it applies today’s latest state to older behavior.

Handle negative behavior and maturity#

“Did not return within 14 days” cannot be evaluated for an account created yesterday. Require a complete observation window:

WITH signups AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    min(toDateTime(timestamp)) AS created_at
  FROM product_events
  WHERE JSONExtractString(json, 'event') = 'account_created'
  GROUP BY account_id
),
returns AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    min(toDateTime(timestamp)) AS returned_at
  FROM product_events
  WHERE JSONExtractString(json, 'event') = 'core_workflow_completed'
  GROUP BY account_id
)
SELECT signups.account_id
FROM signups
LEFT JOIN returns USING account_id
WHERE
  signups.created_at < now() - INTERVAL 14 DAY
  AND (
    returns.returned_at IS NULL
    OR returns.returned_at >= signups.created_at + INTERVAL 14 DAY
  )

Record allowed lateness as well. If events may arrive 24 hours late, evaluate a 14-day rule only after 15 days or label the newest membership provisional.

Publish reusable membership snapshots#

When dashboards, jobs, and customer workflows must use exactly the same membership, calculate it externally and emit a bounded snapshot:

{
  "event": "cohort_membership_evaluated",
  "membership_id": "activated_accounts:2:acct_7:2026-07-25",
  "cohort_key": "activated_accounts",
  "cohort_version": 2,
  "entity_type": "account",
  "account_id": "acct_7",
  "is_member": true,
  "evaluation_at": "2026-07-26T00:00:00Z",
  "data_complete_through": "2026-07-25T23:00:00Z",
  "reason_code": "report_exported_within_7d"
}

GraphJSON does not schedule or materialize this calculation. Use the pattern in Derived events and scheduled rollups and select the latest accepted result for each logical membership identity. Include negative results when consumers need to distinguish “not a member” from “not evaluated.”

Version definition changes#

Create a new version when entry rules, exclusions, entity grain, lookback, property semantics, or maturity rules change. During a transition:

1. calculate old and new definitions over the same settled interval
2. compare population size and changed entity IDs
3. explain expected additions and removals
4. update dependent queries and workflows together
5. retain the effective date

Do not overwrite an experiment audience or compliance review population with a new interpretation.

Validate membership#

For each cohort, keep a small fixture containing:

• clear members
• clear non-members
• boundary timestamps
• duplicate and late events
• missing identities
• excluded internal entities
• property changes before and after qualification
• immature observation windows

Test the canonical SQL with metric tests in CI. In production, monitor membership size, entry and exit counts, missing entity IDs, freshness, and sudden distribution changes.

Launch checklist#

• One durable entity defines membership.
• Inclusion and exclusion rules are separate and reviewable.
• Dynamic or frozen semantics are explicit.
• Evaluation time, lookback, and allowed lateness are documented.
• Time-varying properties have an as-of policy.
• Negative behavior waits for a mature observation window.
• The canonical SQL and fixtures are versioned.
• Shared snapshots include definition and completeness metadata.
• Dependent dashboards and workflows name the cohort version.