SaaS Customer Health Score: Usage Signals, Churn Prediction, and At-Risk Dashboards

Customer health scores translate dozens of behavioral signals into a single number that tells your customer success team where to focus. A workspace at 85 is thriving. A workspace that dropped from 70 to 30 in two weeks is about to churn. Without a score, your team is flying blind — relying on gut feeling and whoever shouts loudest.

The score doesn't need to be a perfect churn predictor. It needs to be directionally correct and actionable.

Scoring Model Design

// lib/health/model.ts

export interface HealthSignal {
  name:        string;
  weight:      number;   // 0–1; all weights should sum to 1
  compute:     (data: WorkspaceSnapshot) => number;  // Returns 0–100
  description: string;
}

export interface WorkspaceSnapshot {
  workspaceId:        string;
  plan:               string;
  // Usage in last 14 days
  dau14d:             number[];   // Daily active user counts
  eventCount14d:      number;
  uniqueFeatures14d:  number;     // Distinct feature types used
  // Usage in previous 14 days (for trend)
  eventCountPrev14d:  number;
  // Lifecycle
  daysSinceSignup:    number;
  teamMemberCount:    number;
  // Billing
  daysSinceLastPayment: number | null;
  hasPaymentFailed:   boolean;
  // Support
  openTicketCount:    number;
}

// Individual signal scorers — each returns 0–100
const SIGNALS: HealthSignal[] = [
  {
    name:    "engagement",
    weight:  0.30,
    description: "Daily active users over last 14 days",
    compute(data) {
      if (data.dau14d.length === 0) return 0;
      const activeDays = data.dau14d.filter((d) => d > 0).length;
      // 14/14 active days = 100; 0/14 = 0
      return Math.round((activeDays / 14) * 100);
    },
  },
  {
    name:    "feature_adoption",
    weight:  0.25,
    description: "Breadth of features used",
    compute(data) {
      // Assume 10 core features — using 8+ = 100
      return Math.min(100, Math.round((data.uniqueFeatures14d / 10) * 100));
    },
  },
  {
    name:    "trend",
    weight:  0.20,
    description: "Event volume trend vs prior period",
    compute(data) {
      if (data.eventCountPrev14d === 0) return 50;  // Neutral for new accounts
      const ratio = data.eventCount14d / data.eventCountPrev14d;
      if (ratio >= 1.1) return 100;  // Growing
      if (ratio >= 0.9) return 70;   // Stable
      if (ratio >= 0.7) return 40;   // Declining
      return 10;                      // Sharp decline
    },
  },
  {
    name:    "team_size",
    weight:  0.10,
    description: "Team adoption",
    compute(data) {
      if (data.teamMemberCount >= 5) return 100;
      if (data.teamMemberCount >= 3) return 70;
      if (data.teamMemberCount >= 2) return 40;
      return 20;  // Solo user = high churn risk
    },
  },
  {
    name:    "billing_health",
    weight:  0.15,
    description: "Payment status and plan tier",
    compute(data) {
      if (data.hasPaymentFailed) return 0;
      if (data.plan === "free") return 30;
      if (data.plan === "starter") return 60;
      if (data.plan === "growth") return 85;
      if (data.plan === "enterprise") return 100;
      return 50;
    },
  },
];

export function computeHealthScore(data: WorkspaceSnapshot): number {
  const weightedSum = SIGNALS.reduce((sum, signal) => {
    const signalScore = signal.compute(data);
    return sum + signalScore * signal.weight;
  }, 0);

  return Math.round(Math.max(0, Math.min(100, weightedSum)));
}

export type HealthRisk = "healthy" | "at_risk" | "critical";

export function classifyRisk(score: number): HealthRisk {
  if (score >= 70) return "healthy";
  if (score >= 40) return "at_risk";
  return "critical";
}

Database Schema

CREATE TABLE workspace_health_scores (
  workspace_id  UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  scored_at     DATE NOT NULL,
  score         INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100),
  risk          TEXT NOT NULL CHECK (risk IN ('healthy', 'at_risk', 'critical')),
  -- Store signal breakdown for debugging
  signals       JSONB NOT NULL DEFAULT '{}',
  PRIMARY KEY (workspace_id, scored_at)
);

CREATE INDEX idx_health_scores_date ON workspace_health_scores(scored_at DESC, risk);
CREATE INDEX idx_health_scores_workspace ON workspace_health_scores(workspace_id, scored_at DESC);

Daily Score Computation Job

// workers/compute-health-scores.ts — run nightly
import { prisma } from "@/lib/prisma";
import { computeHealthScore, classifyRisk, type WorkspaceSnapshot } from "@/lib/health/model";

async function buildSnapshot(workspaceId: string): Promise<WorkspaceSnapshot> {
  const now     = new Date();
  const days14  = new Date(now.getTime() - 14 * 86400_000);
  const days28  = new Date(now.getTime() - 28 * 86400_000);

  const [workspace, events14d, eventsPrev14d, members, recentPayment] = await Promise.all([
    prisma.workspace.findUniqueOrThrow({
      where:  { id: workspaceId },
      select: { plan: true, createdAt: true },
    }),
    prisma.event.groupBy({
      by:     ["occurredAt"],
      where:  { workspaceId, occurredAt: { gte: days14 } },
      _count: { userId: true },
    }),
    prisma.event.count({
      where: { workspaceId, occurredAt: { gte: days28, lt: days14 } },
    }),
    prisma.workspaceMember.count({ where: { workspaceId } }),
    prisma.payment.findFirst({
      where:   { workspaceId },
      orderBy: { createdAt: "desc" },
      select:  { createdAt: true, status: true },
    }),
  ]);

  // Unique features used in last 14 days
  const featureResult = await prisma.$queryRaw<[{ unique_features: bigint }]>`
    SELECT COUNT(DISTINCT name) AS unique_features
    FROM events
    WHERE workspace_id = ${workspaceId}::uuid
      AND occurred_at >= ${days14}
  `;

  // Build DAU array (one entry per day for last 14 days)
  const dauByDay = new Map<string, number>();
  events14d.forEach((row) => {
    const day = row.occurredAt.toISOString().split("T")[0];
    dauByDay.set(day, (dauByDay.get(day) ?? 0) + row._count.userId);
  });
  const dau14d = Array.from({ length: 14 }, (_, i) => {
    const d = new Date(now.getTime() - (13 - i) * 86400_000);
    return dauByDay.get(d.toISOString().split("T")[0]) ?? 0;
  });

  const daysSinceSignup = Math.floor(
    (now.getTime() - workspace.createdAt.getTime()) / 86400_000
  );

  return {
    workspaceId,
    plan:               workspace.plan,
    dau14d,
    eventCount14d:      events14d.reduce((s, r) => s + r._count.userId, 0),
    uniqueFeatures14d:  Number(featureResult[0].unique_features),
    eventCountPrev14d:  eventsPrev14d,
    daysSinceSignup,
    teamMemberCount:    members,
    daysSinceLastPayment: recentPayment
      ? Math.floor((now.getTime() - recentPayment.createdAt.getTime()) / 86400_000)
      : null,
    hasPaymentFailed: recentPayment?.status === "failed",
    openTicketCount:  0,
  };
}

export async function computeAllHealthScores(): Promise<void> {
  const today      = new Date().toISOString().split("T")[0];
  const workspaces = await prisma.workspace.findMany({
    where:  { deletedAt: null },
    select: { id: true },
  });

  console.log(`[health] Computing scores for ${workspaces.length} workspaces`);

  let computed = 0;
  for (const ws of workspaces) {
    try {
      const snapshot = await buildSnapshot(ws.id);
      const score    = computeHealthScore(snapshot);
      const risk     = classifyRisk(score);

      await prisma.workspaceHealthScore.upsert({
        where:  { workspaceId_scoredAt: { workspaceId: ws.id, scoredAt: new Date(today) } },
        create: { workspaceId: ws.id, scoredAt: new Date(today), score, risk, signals: snapshot as any },
        update: { score, risk, signals: snapshot as any },
      });

      computed++;
    } catch (err) {
      console.error(`[health] Failed to score workspace ${ws.id}:`, err);
    }
  }

  console.log(`[health] Scored ${computed}/${workspaces.length} workspaces`);
}

At-Risk Dashboard Query

-- Workspaces that were healthy last week and are now at-risk or critical
WITH current_scores AS (
  SELECT workspace_id, score, risk
  FROM workspace_health_scores
  WHERE scored_at = CURRENT_DATE
),
week_ago_scores AS (
  SELECT workspace_id, score AS prev_score
  FROM workspace_health_scores
  WHERE scored_at = CURRENT_DATE - INTERVAL '7 days'
)
SELECT
  w.id,
  w.name,
  w.plan,
  cs.score,
  cs.risk,
  ws.prev_score,
  cs.score - ws.prev_score AS score_delta,
  -- Flag accounts that dropped significantly
  CASE
    WHEN cs.score - ws.prev_score < -20 THEN 'sharp_decline'
    WHEN cs.score - ws.prev_score < -10 THEN 'declining'
    ELSE 'stable'
  END AS trend
FROM current_scores cs
JOIN workspaces w           ON w.id = cs.workspace_id
LEFT JOIN week_ago_scores ws ON ws.workspace_id = cs.workspace_id
WHERE cs.risk IN ('at_risk', 'critical')
ORDER BY cs.score ASC, score_delta ASC
LIMIT 50;

Cost and Timeline Estimates

See Also

• SaaS Usage Analytics
• SaaS Dunning Management
• SaaS Email Sequences
• PostgreSQL Window Functions
• SaaS Activity Feed

---

Working With Viprasol

Customer health scores only work if they're computed consistently and surfaced where your team can act on them. Our team builds the full pipeline: weighted signal model with configurable weights, daily PostgreSQL snapshot, risk classification (healthy/at_risk/critical), sharp-decline detection query, and a customer success dashboard sorted by urgency.

What we deliver:

• SIGNALS array: engagement (DAU 14d), feature_adoption (unique features), trend (vs prior 14d), team_size, billing_health — all return 0–100
• computeHealthScore: weighted sum clamped to 0–100
• classifyRisk: healthy ≥70, at_risk 40–69, critical < 40
• workspace_health_scores table: workspace_id + scored_at PK, score, risk, signals JSONB
• buildSnapshot: parallel Promise.all for DAU array, feature count, member count, payment status
• computeAllHealthScores: nightly upsert loop with error isolation per workspace
• At-risk SQL: CTE current vs week-ago scores, score_delta, sharp_decline flag

Talk to our team about your customer success analytics →

Or explore our SaaS development services.