What is a project health score, and how do you measure it in Metabase?
A project health score is a transparent risk score that combines signals like overdue work, blocked work, stale work, and missing metadata. It should point leaders toward projects that need attention, not pretend to be a perfect forecast.
TL;DR — Keep the formula visible. A useful score is explainable, adjustable, and backed by drill-through tables.
Definition
Project health score is a weighted combination of risk signals. Many teams invert it so 100 is healthy; the SQL below returns a 0-100 risk score where higher means more attention needed.
Data needed
- Project or board
- Open/completed state
- Due date, blocked status, and last-updated date
- Owner and priority fields for drilldowns
SQL pattern
Project health risk scorePostgreSQL
WITH project_rollup AS (
SELECT
project_name,
COUNT(*) FILTER (WHERE completed_at IS NULL) AS open_items,
COUNT(*) FILTER (WHERE completed_at IS NULL AND due_date < CURRENT_DATE) AS overdue_items,
COUNT(*) FILTER (WHERE completed_at IS NULL AND status_group = 'blocked') AS blocked_items,
COUNT(*) FILTER (WHERE completed_at IS NULL AND updated_at < CURRENT_DATE - INTERVAL '14 days') AS stale_items
FROM work_items
WHERE archived_at IS NULL
AND canceled_at IS NULL
GROUP BY 1
)
SELECT
project_name,
open_items,
overdue_items,
blocked_items,
stale_items,
LEAST(
100,
ROUND(
40.0 * overdue_items / NULLIF(open_items, 0)
+ 35.0 * blocked_items / NULLIF(open_items, 0)
+ 25.0 * stale_items / NULLIF(open_items, 0),
0
)
) AS project_health_risk_score
FROM project_rollup
ORDER BY project_health_risk_score DESC NULLS LAST;Pitfalls
Hiding the formula.→ If people cannot explain the score, they will not trust it.
Using one formula for every workflow forever.→ Tune weights by process, and review them after teams learn from the dashboard.
No drill-through.→ Every score needs a table of the actual overdue, blocked, and stale items behind it.
Related
FAQ
Should the score be health or risk?
Either is fine, but label it clearly. Risk score is often easier to debug because every point comes from an explicit problem signal.