How to Build a Customer Health Score with SQL

Every CS platform sells you a health score. Traffic-light dashboard, red/yellow/green badges, maybe some ML sprinkled on top. The problem is, when you dig into how those scores are computed, it's almost always an equally-weighted average of four or five signals. Login frequency counts the same as NPS. Support ticket volume counts the same as payment failures.

That produces scores that don't predict churn.

I've seen teams run health scores for months, confident in their green accounts, then lose three "healthy" customers in a single quarter. The post-mortem always reveals the same thing: those accounts had stopped logging in weeks ago, but their NPS was high and they paid on time, so the equal-weight average stayed green. Login frequency is a leading indicator. NPS is a lagging one. Treating them the same defeats the purpose of having a composite score.

The four signal categories

You need data from four tables (plus a customers table with id, contract_start, renewal_date):

Usage — logins(user_id, created_at) and events(user_id, event_type, created_at). Login frequency and feature adoption depth. Strongest churn predictor by far. A customer who stops logging in is telling you something, regardless of what they said in their last survey.

Financial — invoices(customer_id, status, amount, created_at). Payment history, revenue expansion or contraction. Late payments and downgrades are obvious risk signals, but expansion (seat additions, plan upgrades) is an equally strong positive signal.

Engagement — support_tickets(customer_id, priority, status, created_at). High-priority tickets aren't always bad (engaged customers file tickets), but a spike in open P1s is.

Relationship — contract age and renewal proximity from the customers table. A customer 30 days from renewal with declining usage is a completely different risk profile than one who just signed a 12-month contract.

Why percentile rank, not min-max

Raw signal values aren't comparable. 15 login days is great; 15 open P1 tickets is terrible. You need each signal on a 0-100 scale before combining them.

Min-max normalization is the obvious choice but it's distorted by outliers. One power user who logs in 30 days out of 30 compresses everyone else into the bottom of the range. PERCENT_RANK() distributes scores evenly across your customer base:

-- Higher login count = healthier, so rank ascending
ROUND(100.0 * PERCENT_RANK() OVER (ORDER BY login_days_30d), 1)
  AS n_logins,
 
-- More failures = less healthy, so reverse the rank
ROUND(100.0 * (1 - PERCENT_RANK() OVER (ORDER BY failed_invoices_90d)), 1)
  AS n_failures

The reversed rank for negative signals is easy to forget. A customer with the most failures should get the lowest health score, so 1 - PERCENT_RANK() flips the direction. Miss it, and your healthiest-looking customers are actually your sickest.

One gotcha: if all customers have the same value for a signal (say, zero failed invoices), PERCENT_RANK() returns 0.0 for everyone, not 100.0. Mathematically correct, but counterintuitive. Handle it with a CASE WHEN MAX() OVER () = MIN() OVER () THEN 100 if that matters to you.

Choosing weights

Here's where most implementations go sideways. The default move is equal weights, but that produces a score that doesn't correlate with actual churn.

Start with these weights, then calibrate with your own data:

• Usage: 40% (login frequency 25%, feature adoption 15%)
• Financial: 30% (payment history 10%, failure count 10%, expansion trend 10%)
• Engagement: 15% (open P1 tickets)
• Relationship: 15% (tenure 5%, renewal proximity 10%)

Usage gets the heaviest weight because it moves first. A customer who stops logging in will eventually stop paying, file fewer tickets, and churn at renewal. The financial and engagement signals confirm what usage already told you, but they confirm it later. If you weight them equally, your health score becomes a lagging indicator that turns red the same week the customer cancels.

The full health score query

The complete CTE chain — raw signal extraction, percentile normalization, weighted composite, and traffic-light classification:

WITH usage_signals AS (
  SELECT
    c.id AS customer_id,
    COUNT(DISTINCT l.created_at::date) FILTER (
      WHERE l.created_at >= NOW() - INTERVAL '30 days'
    ) AS login_days_30d,
    COUNT(DISTINCT e.event_type) FILTER (
      WHERE e.created_at >= NOW() - INTERVAL '30 days'
    ) AS distinct_features_30d
  FROM customers c
  LEFT JOIN logins l ON l.user_id = c.id
  LEFT JOIN events e ON e.user_id = c.id
  GROUP BY c.id
),
financial_signals AS (
  SELECT
    customer_id,
    COUNT(*) FILTER (WHERE status = 'paid' AND created_at >= NOW() - INTERVAL '90 days')
      AS paid_invoices_90d,
    COUNT(*) FILTER (WHERE status IN ('failed','past_due') AND created_at >= NOW() - INTERVAL '90 days')
      AS failed_invoices_90d,
    COALESCE(SUM(amount) FILTER (WHERE created_at >= NOW() - INTERVAL '30 days'), 0)
      - COALESCE(SUM(amount) FILTER (
          WHERE created_at BETWEEN NOW() - INTERVAL '60 days' AND NOW() - INTERVAL '30 days'
        ), 0) AS revenue_delta
  FROM invoices
  GROUP BY customer_id
),
engagement_signals AS (
  SELECT customer_id,
    COUNT(*) FILTER (WHERE priority = 'high' AND status = 'open'
      AND created_at >= NOW() - INTERVAL '30 days') AS open_p1_tickets_30d
  FROM support_tickets
  GROUP BY customer_id
),
relationship_signals AS (
  SELECT id AS customer_id,
    EXTRACT(DAYS FROM NOW() - contract_start) AS contract_age_days,
    EXTRACT(DAYS FROM renewal_date - NOW()) AS days_to_renewal
  FROM customers
),
raw AS (
  SELECT c.id AS customer_id,
    COALESCE(u.login_days_30d, 0) AS login_days_30d,
    COALESCE(u.distinct_features_30d, 0) AS distinct_features_30d,
    COALESCE(f.paid_invoices_90d, 0) AS paid_invoices_90d,
    COALESCE(f.failed_invoices_90d, 0) AS failed_invoices_90d,
    COALESCE(f.revenue_delta, 0) AS revenue_delta,
    COALESCE(e.open_p1_tickets_30d, 0) AS open_p1_tickets_30d,
    COALESCE(r.contract_age_days, 0) AS contract_age_days,
    COALESCE(r.days_to_renewal, 365) AS days_to_renewal
  FROM customers c
  LEFT JOIN usage_signals u ON u.customer_id = c.id
  LEFT JOIN financial_signals f ON f.customer_id = c.id
  LEFT JOIN engagement_signals e ON e.customer_id = c.id
  LEFT JOIN relationship_signals r ON r.customer_id = c.id
),
normalized AS (
  SELECT customer_id,
    ROUND(100.0 * PERCENT_RANK() OVER (ORDER BY login_days_30d), 1) AS n_logins,
    ROUND(100.0 * PERCENT_RANK() OVER (ORDER BY distinct_features_30d), 1) AS n_features,
    ROUND(100.0 * PERCENT_RANK() OVER (ORDER BY paid_invoices_90d), 1) AS n_payments,
    ROUND(100.0 * (1 - PERCENT_RANK() OVER (ORDER BY failed_invoices_90d)), 1) AS n_failures,
    ROUND(100.0 * PERCENT_RANK() OVER (ORDER BY revenue_delta), 1) AS n_expansion,
    ROUND(100.0 * (1 - PERCENT_RANK() OVER (ORDER BY open_p1_tickets_30d)), 1) AS n_tickets,
    ROUND(100.0 * PERCENT_RANK() OVER (ORDER BY contract_age_days), 1) AS n_tenure,
    ROUND(100.0 * PERCENT_RANK() OVER (ORDER BY days_to_renewal), 1) AS n_renewal
  FROM raw
),
scored AS (
  SELECT customer_id,
    ROUND(0.625 * n_logins + 0.375 * n_features, 1) AS usage_score,
    ROUND(0.333 * n_payments + 0.333 * n_failures + 0.334 * n_expansion, 1) AS financial_score,
    n_tickets AS engagement_score,
    ROUND(0.333 * n_tenure + 0.667 * n_renewal, 1) AS relationship_score,
    ROUND(
      0.40 * (0.625 * n_logins + 0.375 * n_features)
      + 0.30 * (0.333 * n_payments + 0.333 * n_failures + 0.334 * n_expansion)
      + 0.15 * n_tickets
      + 0.15 * (0.333 * n_tenure + 0.667 * n_renewal)
    , 1) AS health_score
  FROM normalized
)
SELECT
  customer_id,
  usage_score, financial_score, engagement_score, relationship_score,
  health_score,
  CASE
    WHEN health_score >= 70 THEN 'Green'
    WHEN health_score >= 40 THEN 'Yellow'
    ELSE 'Red'
  END AS health_status
FROM scored
ORDER BY health_score ASC

A few things worth calling out.

The LEFT JOIN chain in the raw CTE matters. If a customer has zero logins, zero invoices, or zero tickets in the window, they should show up with zeros, not disappear from the result set. A missing row is the worst kind of bug in a health score because it makes unhealthy customers invisible instead of red.

days_to_renewal defaults to 365 for customers with no renewal date (month-to-month or missing data). That's deliberate. Don't penalize a customer on renewal proximity just because their contract type doesn't have a fixed renewal date.

Ordering by health_score ASC puts your riskiest accounts at the top. That's what your CS team needs to see first.

Accounts to call this week

The most actionable cut of the health score isn't "all Red accounts." It's Yellow and Red accounts with a renewal coming up. Those are the ones where a single conversation might change the outcome:

SELECT
  customer_id,
  health_score,
  health_status,
  usage_score,
  financial_score,
  days_to_renewal
FROM customer_health_scores  -- materialized view or table from the query above
WHERE health_status IN ('Yellow', 'Red')
  AND days_to_renewal <= 60
ORDER BY days_to_renewal ASC, health_score ASC

Notice the output includes the category sub-scores. When your CS rep calls, they need to know why the account is Yellow. A usage problem ("you haven't logged in for two weeks, what's blocking you?") is a different conversation than a financial problem ("your last two invoices failed").

Detecting deterioration

A snapshot tells you who's at risk right now. But the slope matters more than the level. A customer at 55 who was at 75 last week is a much bigger problem than one who's been steady at 50 for months.

Materialize the health score daily into a health_score_history table (add a scored_at DATE column), then query for status transitions:

SELECT
  h.customer_id,
  h.health_score AS current_score,
  h.health_status AS current_status,
  p.health_score AS prev_score,
  p.health_status AS prev_status,
  h.health_score - p.health_score AS score_change
FROM health_score_history h
JOIN health_score_history p
  ON p.customer_id = h.customer_id
  AND p.scored_at = h.scored_at - INTERVAL '7 days'
WHERE h.scored_at = CURRENT_DATE
  AND p.health_status = 'Green'
  AND h.health_status IN ('Yellow', 'Red')
ORDER BY score_change ASC

This filters to accounts that were Green last week but aren't anymore. Those are your churn risk alerts. A customer who's been Red for months is probably already gone. One who just dropped from Green is reachable.

Set this up as a scheduled Slack alert that fires daily. Your CS team gets a list of accounts to call before the customer has time to quietly evaluate competitors.

Calibrating weights with real churn data

The weights above are reasonable defaults, not gospel. Once you have 6-12 months of health score history alongside actual churn outcomes, calibrate empirically.

Export your normalized signal values and a churned boolean:

SELECT
  h.customer_id,
  h.n_logins, h.n_features,
  h.n_payments, h.n_failures, h.n_expansion,
  h.n_tickets, h.n_tenure, h.n_renewal,
  CASE WHEN c.canceled_at IS NOT NULL THEN 1 ELSE 0 END AS churned
FROM health_score_history h
JOIN customers c ON c.id = h.customer_id
WHERE h.scored_at = '2026-01-01'  -- snapshot date, 6+ months ago

Run a logistic regression on that (scikit-learn, R, even a spreadsheet) and look at the coefficients. In every B2B SaaS dataset I've seen, login frequency dominates — usually 2-3x the coefficient of the next strongest signal. NPS and support tickets tend to be the weakest predictors, partly because they're noisy, partly because the customers who churn silently never file tickets or respond to surveys.

The calibration loop: compute scores, wait for churn outcomes, regress, adjust weights, repeat. Don't adjust more than once a quarter. Overfitting to recent churn patterns makes the score reactive rather than predictive.

Getting all the signals into one place

The hard part isn't the SQL. It's getting the data together. Usage lives in your product database. Billing lives in Stripe. CRM data lives in HubSpot. Support tickets are in Zendesk or Intercom.

With Fastero, you connect each source and pull the relevant tables into DuckDB. The health score query runs across all of them in a single statement. PERCENT_RANK() works identically in DuckDB, and you get sub-second performance on datasets up to tens of millions of rows. No ETL pipeline, no warehouse, no waiting for a nightly dbt run.

Pair the health score with revenue leak detection to catch financial signals (failed payments, invoice aging) automatically. You can also compute customer lifetime value and net revenue retention from the same underlying data. Once usage, billing, and CRM sit in one store, every SaaS metric becomes a query instead of a spreadsheet project.

Try Fastero free — connect Postgres, Stripe, and HubSpot, then compute customer health scores across all your data in one SQL query. No credit card required.