How do you build a customer health score from CRM, billing and support data?

7 min read (+ optional SQL)

By Tim, founder of Seamline · Last updated 6 October 2026

Most health scores don't fail at the formula. They fail earlier: the signals live in different systems that don't agree on who the customer is.

A B2B customer health score is supposed to tell you which accounts are at risk before it's too late. In practice, many SaaS teams end up with a scorecard that's green for customers about to churn, because it's built from data that was easy to reach rather than the cross-system signals that matter.

This use case builds a health score the other way around: start with signals that plausibly indicate risk, link them to one customer across CRM, billing, support and product, score them simply, show the evidence behind every score, and then test which signals actually predict churn in your business. You can read it in about seven minutes. For anyone who wants to build it, the key steps have SQL tested on Postgres behind a toggle, and a technical appendix lets you run the whole example.

The result

A list where every colour comes with its reason:

Customer	Health	Confidence	Renews in	Evidence
Brightpath Learning	🔴 Red	High	47 days	3 open tickets, 1 high-priority · active users −45% · MRR down from €3,500 to €2,500
Northwind Analytics	🔴 Red	High	64 days	€1,950 overdue 17 days · high-priority ticket open · active users −35%
Atlas & Rowe	🔴 Red	High	41 days	€3,000 overdue 23 days · urgent ticket open (SSO login)
Fjord Studio	🟡 Yellow	Medium	88 days	Active users −25% · no CS conversation in 87 days · support data not linked
Oakline Legal	⚪ Unknown	Low	128 days	No billing, support or product data linked yet

All companies, people and numbers in this use case are fictional.

Look at Brightpath Learning. In the CRM it looks fine: CS spoke to them three days ago and the relationship notes are positive. A score built from CRM data alone would call it green. Billing and product tell a different story: they removed seats, and usage nearly halved.

Why most health scores go wrong

They're built from what's in the CRM. Last contact, sentiment, NPS. Easy to reach, and mostly about how the relationship feels, not what the customer does.
The strongest signals live elsewhere. Payment behaviour is in billing. Frustration is in support. Adoption is in the product. Each system uses its own customer record.
A single number hides the reason. "Health: 42" invites "says who?". Nobody knows what to do with it.
Missing data counts as healthy. A customer with no linked support or usage data has no bad signals, so it shows up green. That's the most dangerous green there is.

How it works

 CRM ──────┐
 Billing ──┤
 Support ──┤
 Product ──┤
           ▼
   Customer identity      one canonical ID, linked to every system's records
           │
           ▼
       Signals            each from the system that owns it
           │
           ▼
   Score + evidence       simple points, with the reason next to every point
           │
     ┌─────┴─────┐
     ▼           ▼
    CRM        Slack      health on the company record, weekly list to each owner

The formula is the easy part. The layer that links the signals to one customer is what makes the score worth trusting. This is how you build a reliable single customer view and health scorecard: not by replacing your CRM or data stack, but by linking each tool's records to one customer and applying the scoring rules consistently.

Step 1: Pick plausible churn signals before the formula

You don't know yet which signals predict churn in your business. Nobody does on day one: an overdue invoice can be an early warning in one company and routine in another, and login frequency matters for a daily tool but not for one used once a quarter. So start with a handful of signals that plausibly indicate risk and that you can explain to anyone, decide which system owns each one, and test them in step 5.

Signal	Source	Why it matters	Example threshold
Overdue invoice	Billing	Payment problems can come before cancellations	1 point after 1 day, 2 after 14
High-priority or piling-up tickets	Support	Unresolved problems wear down patience	2 points for an open high-priority ticket, 1 for 3+ open
Falling usage	Product	Fewer people logging in means less value	1 point at −15%, 2 at −30%
Contraction	Billing	Removing seats or downgrading is a vote with money	1 point if MRR fell 10%+ in 3 months, 2 if 25%+
Silence	CRM	Nobody has spoken to them in a while	1 point after 60 days

Two things deliberately aren't on the list:

Renewal date. It isn't a health signal; it's urgency. A red customer renewing in 40 days and one renewing in 8 months need the same diagnosis but a different timeline. Show it next to the score, and use it to sort.
Sentiment and NPS. Useful context, but they measure how the relationship feels. Start with what customers do; add sentiment later if step 5 shows it helps.

Step 2: Link every signal to the same customer

Each signal comes from a different system, and each system knows the customer under a different name: "Northwind Analytics" in the CRM, "NORTHWIND ANALYTICS B.V." in billing, an organisation called northwind.io in support, acc_1 in the product.

So give every business customer one canonical ID and link each system's records to it. Rules suggest the links, a person confirms them, and only confirmed links count. The renewal-risk use case shows how, step by step. The MRR used for the contraction signal is the same billing-based number as in the HubSpot ARR vs Stripe use case.

Then make one rule non-negotiable:

In the sample, Oakline Legal has no confirmed billing, support or product link. A naive score would show it green, because nothing looks wrong in data that isn't there. This one shows "unknown", which is the honest answer and a to-do for whoever maintains the links.

Show the SQL for step 2

One row per customer, with every signal from the system that owns it. NULL means "we don't know", not "fine":

-- One row per customer: every signal, from the system that owns it.
-- Only confirmed links are used. NULL means "we don't know", not "fine".
CREATE OR REPLACE VIEW customer_signals AS
-- Demo date: 2026-10-15, to match the sample data.
-- In production, replace it with CURRENT_DATE or your agreed reporting date.
WITH params AS (SELECT date '2026-10-15' AS today),
links AS (
  SELECT customer_id, source, source_id
  FROM customer_source_ids WHERE status = 'confirmed'
),
crm AS (
  SELECT l.customer_id, c.name, c.owner, c.renewal_date, c.last_cs_touch
  FROM links l JOIN crm_companies c ON l.source = 'crm' AND c.id = l.source_id
),
billing AS (
  SELECT l.customer_id,
         max(p.today - i.due_date) FILTER (WHERE i.status = 'open' AND i.due_date < p.today) AS days_overdue,
         sum(i.amount_eur)         FILTER (WHERE i.status = 'open' AND i.due_date < p.today) AS overdue_eur,
         string_agg(i.id, ', ')    FILTER (WHERE i.status = 'open' AND i.due_date < p.today) AS overdue_invoices
  FROM links l CROSS JOIN params p
  JOIN billing_invoices i ON l.source = 'billing' AND i.customer_id = l.source_id
  GROUP BY l.customer_id
),
mrr AS (
  SELECT l.customer_id,
         sum(m.mrr_eur) FILTER (WHERE m.month = date '2026-10-01') AS mrr_now,
         sum(m.mrr_eur) FILTER (WHERE m.month = date '2026-07-01') AS mrr_3m_ago
  FROM links l JOIN billing_mrr m ON l.source = 'billing' AND m.customer_id = l.source_id
  GROUP BY l.customer_id
),
support AS (
  SELECT l.customer_id,
         count(*) FILTER (WHERE t.status IN ('open','pending')) AS open_tickets,
         count(*) FILTER (WHERE t.status IN ('open','pending')
                            AND t.priority IN ('high','urgent'))  AS urgent_open,
         string_agg(t.id, ', ') FILTER (WHERE t.status IN ('open','pending')) AS open_ticket_ids
  FROM links l
  JOIN support_tickets t ON l.source = 'support' AND t.org_id = l.source_id
  GROUP BY l.customer_id
),
product AS (
  SELECT l.customer_id, u.active_last, u.active_prev
  FROM links l JOIN product_usage u ON l.source = 'product' AND u.account_id = l.source_id
)
SELECT
  crm.customer_id, crm.name, crm.owner,
  crm.renewal_date - p.today                        AS renews_in_days,
  p.today - crm.last_cs_touch                       AS days_since_cs_touch,
  -- billing (no linked billing record -> NULL)
  CASE WHEN EXISTS (SELECT 1 FROM links x WHERE x.customer_id = crm.customer_id AND x.source = 'billing')
       THEN coalesce(b.days_overdue, 0) END         AS days_overdue,
  b.overdue_eur, b.overdue_invoices,
  m.mrr_now, m.mrr_3m_ago,
  -- support (linked but no tickets -> 0)
  CASE WHEN EXISTS (SELECT 1 FROM links x WHERE x.customer_id = crm.customer_id AND x.source = 'support')
       THEN coalesce(s.open_tickets, 0) END         AS open_tickets,
  s.urgent_open, s.open_ticket_ids,
  -- product
  pr.active_last, pr.active_prev,
  round(100.0 * (pr.active_last - pr.active_prev) / nullif(pr.active_prev, 0)) AS usage_change_pct
FROM crm CROSS JOIN params p
LEFT JOIN billing b  USING (customer_id)
LEFT JOIN mrr m      USING (customer_id)
LEFT JOIN support s  USING (customer_id)
LEFT JOIN product pr USING (customer_id);

Step 3: A simple, explainable customer health score formula

Add up the points from step 1, and keep two things apart: how the customer looks, and how much of the customer you can actually see.

Points	Health
0–1	🟢 Green
2–3	🟡 Yellow
4 or more	🔴 Red

Sources linked	Confidence
Billing, support and product	High
One of them missing	Medium
Two or more missing	Low, and health shows ⚪ Unknown unless the known signals already add up to red

So Fjord Studio is yellow with medium confidence: the signals we can see are worrying, and support data is missing. That says more than "yellow" or "unknown" alone.

Why points and not weights, or a model? Because the first version has to be explainable. Anyone should be able to look at a red customer and see which two or three things put it there. Once you've run it for a few quarters and know which signals actually predict churn in your business, you can weigh them differently. Start simple and earn the complexity.

Show the SQL for step 3

The score, the colour and the evidence in one view:

-- A simple, explainable score: points per signal, with the reason written next to it.
-- Health and confidence are separate: one says how the customer looks,
-- the other how much of the customer we can actually see. Thresholds are examples.
CREATE OR REPLACE VIEW customer_health AS
WITH points AS (
  SELECT *,
    CASE WHEN days_overdue > 14 THEN 2 WHEN days_overdue > 0 THEN 1 ELSE 0 END             AS p_payment,
    CASE WHEN urgent_open > 0 THEN 2 WHEN open_tickets >= 3 THEN 1 ELSE 0 END               AS p_support,
    CASE WHEN usage_change_pct <= -30 THEN 2 WHEN usage_change_pct <= -15 THEN 1 ELSE 0 END AS p_usage,
    CASE WHEN mrr_now <= mrr_3m_ago * 0.75 THEN 2
         WHEN mrr_now <= mrr_3m_ago * 0.90 THEN 1 ELSE 0 END                                AS p_contraction,
    CASE WHEN days_since_cs_touch > 60 THEN 1 ELSE 0 END                                    AS p_silence,
    (days_overdue IS NULL)::int + (open_tickets IS NULL)::int + (usage_change_pct IS NULL)::int AS n_missing,
    concat_ws(', ',
      CASE WHEN days_overdue     IS NULL THEN 'billing' END,
      CASE WHEN open_tickets     IS NULL THEN 'support' END,
      CASE WHEN usage_change_pct IS NULL THEN 'product' END)                                AS missing
  FROM customer_signals
),
scored AS (
  SELECT *, p_payment + p_support + p_usage + p_contraction + p_silence AS risk_points
  FROM points
)
SELECT
  name AS customer, owner, renews_in_days, risk_points,
  CASE
    -- too little data to judge: say so instead of guessing (unless it's already clearly red)
    WHEN n_missing >= 2 AND risk_points < 4 THEN 'unknown'
    WHEN risk_points >= 4 THEN 'red'
    WHEN risk_points >= 2 THEN 'yellow'
    ELSE 'green'
  END AS health,
  CASE n_missing WHEN 0 THEN 'high' WHEN 1 THEN 'medium' ELSE 'low' END AS confidence,
  concat_ws(' · ',
    CASE WHEN p_payment > 0 THEN
      format('€%s overdue %s days (%s)', to_char(overdue_eur, 'FM999,999'), days_overdue, overdue_invoices) END,
    CASE WHEN p_support > 0 THEN
      format('%s open %s, %s high-priority (%s)', open_tickets,
             CASE WHEN open_tickets = 1 THEN 'ticket' ELSE 'tickets' END, urgent_open, open_ticket_ids) END,
    CASE WHEN p_usage > 0 THEN
      format('active users %s%% (%s → %s)', usage_change_pct, active_prev, active_last) END,
    CASE WHEN p_contraction > 0 THEN
      format('MRR down from €%s to €%s', to_char(mrr_3m_ago, 'FM999,999'), to_char(mrr_now, 'FM999,999')) END,
    CASE WHEN p_silence > 0 THEN
      format('no CS conversation in %s days', days_since_cs_touch) END,
    CASE WHEN missing <> '' THEN 'no data from: ' || missing END
  ) AS evidence
FROM scored;

-- The list: worst first; among equals, the renewal that comes up soonest
SELECT customer, owner, health, confidence, risk_points, renews_in_days, evidence
FROM customer_health
ORDER BY CASE health WHEN 'red' THEN 1 WHEN 'yellow' THEN 2 WHEN 'unknown' THEN 3 ELSE 4 END,
         risk_points DESC, renews_in_days;

On the sample data this returns the five customers in the table at the top, plus three green ones: Kestrel Logistics and Corvid Labs with high confidence, and Mosaic Retail with medium confidence. Mosaic's €800 invoice is only 7 days overdue, and its support data isn't linked, which the evidence column says.

Step 4: Show the evidence, not just the colour

The evidence column is the most important part of the result. Compare:

"Brightpath Learning: red."
"Brightpath Learning: 3 open tickets, one high-priority (users can't log in) · active users down 45% · MRR down from €3,500 to €2,500 after removing seats. Renews in 47 days."

The first starts a discussion about whether the score is right. The second starts a phone call. Every point in the score should trace back to a record someone can open: the invoice, the ticket, the usage chart.

Where the result goes

The CRM: health and evidence written to the company record, so the account owner sees them where they already work.
A weekly list per owner: red and yellow customers, sorted by renewal date, in Slack or email.
AI, optionally, on top: once the evidence is trustworthy, a model can turn it into a short brief and a draft message for the owner to review. Not before: AI reasoning over unlinked data produces confident, wrong summaries.

Step 5: Test the score against reality

A health score is a hypothesis, not a fact. Don't assume the formula is right; test it like a prediction. After a quarter, compare the colours with what customers did:

Did the reds churn or downgrade more often than the greens? If not, the signals or thresholds are wrong.
Which greens left anyway? Look at what they had in common. That's your missing signal.
How often did owners dismiss a red, and why? If a signal is dismissed most of the time, it's noise. Store the dismissals with a reason, so this check takes minutes.

Adjust one threshold at a time, and write down when you changed it, so last quarter's colours stay comparable.

What breaks in production

New customers without links. Every new signup needs a link in each system. Unlinked customers drift into "unknown"; someone has to review that list weekly.
Usage definitions. "Active user" means something different per product. Agree on it once, or the usage signal changes every time someone edits a dashboard.
Seasonality. Usage drops in August and December for many B2B products. Compare with the same period last year, or the score turns yellow every summer.
Ticket spikes from one event. An outage creates tickets for everyone. Exclude incident-related tickets, or every customer turns red at once.
Score fatigue. If half the customers are red, nobody looks. Tune the thresholds until red means "act this week".

Three ways to implement it

1. A spreadsheet, reviewed weekly. With fewer than about 50 customers, a sheet with one row per customer and five signal columns can be enough, if someone updates it every week.

2. A customer success platform. Tools built for this can calculate the score and give CS a place to work from. What they don't do automatically is resolve the identity, definitions and data ownership underneath. If the signals going into the platform are incomplete or inconsistent, the score will be too. That layer is worth getting right whichever tool sits on top.

3. A separate data layer, maintained by someone. The links, the signals and the score in a small database, written back to the CRM on a schedule. The most robust option, and the one that needs the most care. It's what we build and maintain at Seamline, but plenty of teams run it in-house.

Technical appendix: run this yourself

Run these in order in an empty Postgres database (a free Supabase project works). Tested on Postgres 16.

1. Sample data (fictional companies and numbers; "today" is 15 October 2026):

-- Sample data: fictional companies, people and numbers. "Today" is 2026-10-15.

-- CRM: companies with owner, renewal date and the last logged CS conversation
CREATE TABLE crm_companies (
  id            text PRIMARY KEY,
  name          text,
  owner         text,
  renewal_date  date,
  last_cs_touch date
);

-- Billing: invoices, and MRR now vs. three months ago
CREATE TABLE billing_invoices (
  id          text PRIMARY KEY,
  customer_id text,
  amount_eur  numeric,
  due_date    date,
  status      text            -- open, paid
);
CREATE TABLE billing_mrr (
  customer_id text,
  month       date,           -- first day of the month
  mrr_eur     numeric
);

-- Support: tickets per organisation
CREATE TABLE support_tickets (
  id         text PRIMARY KEY,
  org_id     text,
  priority   text,            -- low, normal, high, urgent
  status     text,            -- open, pending, solved
  created_at date,
  subject    text
);

-- Product: active users per account, last 30 days vs the 30 days before
CREATE TABLE product_usage (
  account_id   text PRIMARY KEY,
  active_last  int,
  active_prev  int
);

INSERT INTO crm_companies VALUES
  ('hs_101','Northwind Analytics','Sanne','2026-12-18','2026-09-30'),
  ('hs_102','Atlas & Rowe',       'Daan', '2026-11-25','2026-10-08'),
  ('hs_104','Kestrel Logistics',  'Sanne','2027-03-10','2026-09-22'),
  ('hs_105','Fjord Studio',       'Daan', '2027-01-11','2026-07-20'),
  ('hs_106','Mosaic Retail',      'Sanne','2027-04-02','2026-10-01'),
  ('hs_107','Brightpath Learning','Daan', '2026-12-01','2026-10-12'),
  ('hs_108','Corvid Labs',        'Sanne','2027-06-15','2026-09-15'),
  ('hs_110','Oakline Legal',      'Daan', '2027-02-20','2026-09-01');

INSERT INTO billing_invoices VALUES
  ('INV-2041','cus_A1', 1950,'2026-09-28','open'),
  ('INV-2042','cus_A2', 3000,'2026-09-22','open'),
  ('INV-2050','cus_A4', 2000,'2026-10-01','paid'),
  ('INV-2051','cus_A6',  800,'2026-10-08','open'),
  ('INV-2052','cus_A7', 2500,'2026-10-01','paid'),
  ('INV-2053','cus_A8',  920,'2026-10-01','paid'),
  ('INV-2054','cus_A5', 1000,'2026-10-01','paid');

INSERT INTO billing_mrr VALUES
  ('cus_A1','2026-07-01',2000), ('cus_A1','2026-10-01',2000),
  ('cus_A2','2026-07-01',2550), ('cus_A2','2026-10-01',2550),
  ('cus_A4','2026-07-01',1500), ('cus_A4','2026-10-01',2000),
  ('cus_A5','2026-07-01',1000), ('cus_A5','2026-10-01',1000),
  ('cus_A6','2026-07-01', 800), ('cus_A6','2026-10-01', 800),
  ('cus_A7','2026-07-01',3500), ('cus_A7','2026-10-01',2500),
  ('cus_A8','2026-07-01', 920), ('cus_A8','2026-10-01', 920);

INSERT INTO support_tickets VALUES
  ('T-4170','zd_9', 'high',  'open',   '2026-09-30','Export to BI fails'),
  ('T-4171','zd_9', 'normal','pending','2026-10-09','Question about invoice'),
  ('T-4180','zd_10','urgent','open',   '2026-10-08','SSO login broken'),
  ('T-4190','zd_12','normal','open',   '2026-10-10','Feature request: bulk edit'),
  ('T-4191','zd_13','normal','open',   '2026-10-02','Slow dashboard'),
  ('T-4192','zd_13','normal','open',   '2026-10-06','Slow dashboard (again)'),
  ('T-4193','zd_13','high',  'open',   '2026-10-12','Users can''t log in'),
  ('T-4195','zd_14','low',   'solved', '2026-09-20','Change billing email');

INSERT INTO product_usage VALUES
  ('acc_1', 26, 40),   -- Northwind: down 35%
  ('acc_2', 18, 19),   -- Atlas & Rowe
  ('acc_4', 64, 50),   -- Kestrel: up
  ('acc_5',  9, 12),   -- Fjord: down 25%
  ('acc_6', 14, 14),   -- Mosaic
  ('acc_7', 22, 40),   -- Brightpath: down 45%
  ('acc_8', 11, 11);   -- Corvid

2. The identity links. In a real setup, rules propose these and a person confirms them; the renewal-risk use case has the SQL for that.

-- One canonical ID per business customer, linked to each system's records.
-- In a real setup, rules suggest these links and a person confirms them.
CREATE TABLE customer_source_ids (
  customer_id text,
  source      text,           -- crm, billing, support, product
  source_id   text,
  status      text            -- only 'confirmed' links are used
);

INSERT INTO customer_source_ids VALUES
  ('CUST_000001','crm','hs_101','confirmed'),('CUST_000001','billing','cus_A1','confirmed'),
  ('CUST_000001','support','zd_9','confirmed'),('CUST_000001','product','acc_1','confirmed'),
  ('CUST_000002','crm','hs_102','confirmed'),('CUST_000002','billing','cus_A2','confirmed'),
  ('CUST_000002','support','zd_10','confirmed'),('CUST_000002','product','acc_2','confirmed'),
  ('CUST_000004','crm','hs_104','confirmed'),('CUST_000004','billing','cus_A4','confirmed'),
  ('CUST_000004','support','zd_12','confirmed'),('CUST_000004','product','acc_4','confirmed'),
  ('CUST_000005','crm','hs_105','confirmed'),('CUST_000005','billing','cus_A5','confirmed'),
  ('CUST_000005','product','acc_5','confirmed'),
  ('CUST_000006','crm','hs_106','confirmed'),('CUST_000006','billing','cus_A6','confirmed'),
  ('CUST_000006','product','acc_6','confirmed'),
  ('CUST_000007','crm','hs_107','confirmed'),('CUST_000007','billing','cus_A7','confirmed'),
  ('CUST_000007','support','zd_13','confirmed'),('CUST_000007','product','acc_7','confirmed'),
  ('CUST_000008','crm','hs_108','confirmed'),('CUST_000008','billing','cus_A8','confirmed'),
  ('CUST_000008','support','zd_14','confirmed'),('CUST_000008','product','acc_8','confirmed'),
  ('CUST_000010','crm','hs_110','confirmed');
-- Oakline Legal: no confirmed billing, support or product link yet

3. Then run the SQL from steps 2 and 3 above.

Want this done for your data? See how we work → /services/crm-data-cleansing

Want to see what this looks like on your own data?

Pick one question your team currently answers by opening three or four different systems. We'll map where the data lives, how the records connect, and what it would take to answer it reliably.

Book a revenue data diagnostic →

→ Which accounts approaching renewal have overdue invoices and open tickets?

7 min read (+ optional SQL)

→ Why doesn't your HubSpot ARR match Stripe?

7 min read (+ optional SQL)

How do you build a customer health score from CRM, billing and support data?

The result

Why most health scores go wrong

How it works

Step 1: Pick plausible churn signals before the formula

Step 2: Link every signal to the same customer

Step 3: A simple, explainable customer health score formula

Step 4: Show the evidence, not just the colour

Where the result goes

Step 5: Test the score against reality

What breaks in production

Three ways to implement it

Related use cases