Build customer health and account scoring

A customer-health score is a prioritization aid, not an objective fact about a customer. It should help an accountable team decide where to investigate, educate, or offer support.

Start with the decision:

When the score changes, who reviews it, what evidence do they see,
and which actions are allowed?

If there is no owner or action, build an account dashboard before compressing several signals into one number.

Define health for the product#

Useful dimensions can include:

Billing and support context may be relevant, but use them carefully. A support ticket can mean deep engagement, a product defect, or neither. A failed payment is not proof a user dislikes the product.

Exclude protected or sensitive attributes and undocumented proxies. Get legal, privacy, and policy review before using a score for pricing, access, eligibility, or other consequential treatment.

Choose the account grain#

For B2B products, calculate one row per stable account_id:

account_id × scoring_as_of × score_version

User activity rolls up into the account. Do not compare an account with 500 licensed users directly to one with two without normalizing the intended opportunity.

Define:

• account creation and activation time
• current lifecycle state
• licensed, invited, and active member denominators
• parent/child account behavior
• merges, splits, and deleted accounts
• scoring time zone and settling delay

Use event-time state when plan, entitlement, or lifecycle state can change.

Create a feature table#

Build interpretable account-level features before a score:

WITH
  eligible_accounts AS (
    SELECT
      JSONExtractString(json, 'account_id') AS account_id,
      min(timestamp) AS created_at
    FROM account_state_events
    WHERE
      JSONExtractString(json, 'event') = 'account_created'
      AND JSONExtractString(json, 'account_id') != ''
    GROUP BY account_id
  ),
  activity AS (
    SELECT
      JSONExtractString(json, 'account_id') AS account_id,
      maxIf(
        toDateTime(timestamp),
        JSONExtractString(json, 'event') = 'core_workflow_completed'
      ) AS last_core_value_at,
      uniqExactIf(
        JSONExtractString(json, 'feature'),
        JSONExtractString(json, 'event') = 'feature_used'
        AND timestamp >= toUnixTimestamp(now() - INTERVAL 28 DAY)
      ) AS core_features_used,
      uniqExactIf(
        JSONExtractString(json, 'user_id'),
        JSONExtractString(json, 'event') = 'core_workflow_completed'
        AND timestamp >= toUnixTimestamp(now() - INTERVAL 28 DAY)
      ) AS active_users,
      countIf(
        JSONExtractString(json, 'event') = 'core_workflow_completed'
        AND timestamp >= toUnixTimestamp(now() - INTERVAL 28 DAY)
      ) AS completed_workflows
    FROM product_events
    WHERE
      timestamp >= toUnixTimestamp(now() - INTERVAL 90 DAY)
      AND JSONExtractString(json, 'account_id') != ''
    GROUP BY account_id
  )
SELECT
  eligible_accounts.account_id,
  eligible_accounts.created_at,
  if(
    activity.last_core_value_at = toDateTime(0),
    NULL,
    dateDiff('day', activity.last_core_value_at, now())
  ) AS days_since_core_value,
  activity.core_features_used,
  activity.active_users,
  activity.completed_workflows
FROM eligible_accounts
LEFT JOIN activity
  ON eligible_accounts.account_id = activity.account_id
ORDER BY account_id

The account registry keeps eligible zero-activity accounts present. Mirror it from the authoritative lifecycle source and use an explicit missing value for accounts with no core outcome in retained history. Test each feature independently; a score cannot be trusted when its input table silently omits inactive accounts.

Normalize to product expectations#

Prefer bounded functions with plain-language meaning:

recency points:
  20 if value occurred in the last 7 days
  12 if 8–14 days
   5 if 15–28 days
   0 if never or older

breadth points:
   5 per distinct core capability, capped at 20

seat adoption points:
  20 × min(active_members / eligible_members, 1)

Normalize against the intended product cadence. Daily inactivity is meaningful for an operational inbox and irrelevant for quarterly planning software.

Cap each component so a noisy event cannot dominate the total. Keep currency, duration, and ratio types explicit.

Start with a transparent score#

Example, not a universal formula:

Store the component values, not just the sum:

{
  "event": "account_health_snapshot",
  "snapshot_id": "health:acct_7:2026-07-26:v3",
  "account_id": "acct_7",
  "scored_at": 1785085200,
  "data_complete_through": 1784998800,
  "score_version": 3,
  "score": 72,
  "band": "watch",
  "components": {
    "activation": 20,
    "recency": 12,
    "breadth": 15,
    "seat_adoption": 15,
    "trend": 4,
    "reliability": 6
  },
  "reason_codes": [
    "core_value_8_to_14_days",
    "seat_adoption_below_target"
  ]
}

If nested fields are inconvenient for repeated filtering, also expose bounded top-level component fields. Keep reason codes from a controlled vocabulary.

Separate score, band, and action#

The score is a calculation. The band is an interpretation:

80–100 healthy
60–79  watch
 0–59  investigate

The action remains a human-owned workflow. Do not automatically email a customer, remove access, change pricing, or escalate an account solely because one derived score crossed a threshold.

Use hysteresis to prevent churn around a boundary:

enter investigate below 55
leave investigate above 65

Alternatively, require the band to persist for two settled snapshots.

Handle cold starts#

New accounts have little history and should not be labeled unhealthy by default. Create an explicit state:

insufficient_history

Apply an onboarding-specific definition until:

• a minimum account age is reached
• the activation journey is complete
• enough eligible opportunities exist
• the first natural usage cycle has elapsed

Similarly, paused, seasonal, internal, sandbox, churned, or delinquent accounts may require exclusion or a separately labeled lifecycle state.

Measure trend on comparable windows#

Compare two settled periods of equal length:

recent = days 8–21 ago
baseline = days 22–35 ago

The delay avoids scoring incomplete recent data. Use absolute differences for low-volume accounts and bounded relative change for larger ones.

Never divide by zero and call a new account “infinite growth.” Return a documented new_activity, no_activity, or insufficient_baseline state.

Validate against outcomes#

If the score is intended to predict renewal, expansion, or churn:

1. define the outcome and prediction horizon before analysis
2. freeze features as they were available at scoring time
3. exclude information learned after the prediction point
4. backtest on multiple time periods and account segments
5. inspect precision, recall, calibration, and false positives
6. compare against simple baselines such as recency alone
7. review drift as product behavior changes

Do not train on a current account table and attach those values to historical events; that leaks the future into the model.

A heuristic score can be useful without predictive claims. Label it “engagement health v3,” not “churn probability,” unless it has been calibrated as a probability.

Build the operator view#

For each account, show:

• current score, band, version, and data-through time
• component contributions
• change since the prior settled snapshot
• reason codes
• lifecycle and cohort context
• recent value-producing events
• reliability or delivery issues
• link to the authorized account timeline

Let operators sort by material change as well as raw score. A drop from 91 to 70 may deserve attention before a stable 58 already under review.

Do not expose raw customer payloads or unrestricted GraphJSON queries through the view.

Close the learning loop carefully#

Track operator review separately:

{
  "event": "account_health_reviewed",
  "review_id": "review_91",
  "account_id": "acct_7",
  "score_version": 3,
  "reviewed_band": "watch",
  "disposition": "expected_seasonality",
  "action": "no_contact",
  "reviewed_at": 1785092400
}

These outcomes can reveal false alarms and missing segments. They should not silently become training labels; human workflows contain inconsistency and bias.

Version and roll out#

For every score version:

• source-control the feature and scoring SQL
• keep fixture accounts with exact expected components
• backfill into an isolated collection
• compare distributions and band movement
• sample accounts with the largest changes
• obtain product, customer, data, and policy review
• run old and new versions in parallel
• record the cutover and rollback window

Never overwrite historical score snapshots with today’s formula. Store score_version and regenerate into a new series if you need restated history.

Production checklist#

• Score has a decision, owner, allowed actions, and non-goals.
• Account grain, lifecycle, opportunity denominators, and event-time state are defined.
• Features are interpretable, bounded, and independently tested.
• Zero activity and cold start remain visible.
• Components, version, freshness, and reason codes accompany the total.
• Bands use persistence or hysteresis where needed.
• Protected traits and sensitive proxies are excluded and reviewed.
• Predictive claims are backtested without future leakage.
• Operators can see evidence and override an inappropriate interpretation.
• New versions run in parallel with a rollback path.

Continue with B2B SaaS product analytics, metric governance, and customer support account timelines.