SaaS Customer Health & Success data model
Third-normal-form replica of the customer-success operational system. Each conceptual table becomes its own entity with a warehouse surrogate key plus the real source business key, and child snapshot / signal / task tables reference their parent entities via foreign keys. The composite health-score snapshot is separated from its contributing signals so trend and red-flag analysis can run at either grain. User and CSM contact details are PII-masked under SOC2 and GDPR.
Normalized replica of a SaaS customer-success operational stack (Gainsight / ChurnZero / Catalyst / Vitally): accounts, end users and CSM owners; composite health scores composed of usage, support, billing and NPS signals; risks, engagements, playbook runs and tasks; plus voice-of-customer (NPS, support summaries) — keyed by real source business identifiers and anchored to plan, product, signal-type, risk category, playbook template and CSM role reference data. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 19
- tables
- 163
- columns
- 21
- relationships
- 5
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Composite health score split into snapshot header and signal detail
Customer-success platforms compute an overall health score from many weighted input signals (usage, support, billing, NPS). Modeling the snapshot header and per-signal detail as separate entities lets trend reports run off the snapshot grain while red-flag and signal-drilldown reports run off the signal grain without exploding the snapshot row.
CSM caseload via a time-bounded account_assignment table with is_current
Account ownership changes — primary CSM, onboarding specialist and renewal manager rotate over an account's life. A dedicated account_assignment entity with assigned_at / ended_at / is_current preserves history for tenure analysis while allowing a simple is_current=true filter to drive today's caseload reports.
Risk records separated from engagements so portfolio risk is queryable
At-risk accounts need a first-class entity with severity, ARR-at-risk and owner so portfolio dashboards can roll up exposure without scanning engagement notes. Keeping risk_record distinct from engagement_log lets each grain stay clean and reportable.
Playbook runs and tasks split for cadence execution tracking
Playbooks (onboarding-90day, renewal-prep, at-risk-rescue) are multi-step cadences. A playbook_run header records start/completion and outcome while playbook_task children record each step's status — supporting both run-level completion reporting and task-level SLA tracking.
NPS captured at user grain rolled to account
NPS surveys are answered by individual users, but customer-success reporting needs per-account NPS. Storing nps_response at the user grain with both user_key and account_key preserves the source grain for verbatim analysis while allowing simple per-account rollups.
Tables in the SaaS Customer Health & Success model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (12)
account
Grain: one row per customer account
Customer account master (one row per company). parent_account_key is a self-reference.
| Column | Type | Keys |
|---|---|---|
| account_key | bigint | PK |
| plan_key | bigint | |
| account_id | varchar | BK |
| account_name | varchar | |
| parent_account_key | bigint | |
| industry | varchar | |
| employee_size_band | varchar | |
| country | varchar | |
| region | varchar | |
| lifecycle_stage | varchar | |
| signup_date | date | |
| contract_start_date | date | |
| contract_end_date | date | |
| arr_amount | decimal | |
| currency | varchar | |
| is_strategic | boolean |
user
Grain: one row per end user of an account
End-user identities within a customer account.
| Column | Type | Keys |
|---|---|---|
| user_key | bigint | PK |
| account_key | bigint | |
| user_id | varchar | BK |
| full_name | varchar | PII |
| varchar | PII | |
| user_role | varchar | |
| signup_date | date | |
| last_login_date | date | |
| is_executive_sponsor | boolean | |
| is_decision_maker | boolean | |
| is_active | boolean |
csm_user
Grain: one row per customer-success team member
Customer-success team member directory.
| Column | Type | Keys |
|---|---|---|
| csm_user_key | bigint | PK |
| csm_role_key | bigint | |
| csm_user_id | varchar | BK |
| full_name | varchar | PII |
| varchar | PII | |
| caseload_size | integer | |
| region_covered | varchar | |
| hire_date | date | |
| is_active | boolean |
account_assignment
Grain: one row per (account, csm_user, assignment_type, period)
Time-bounded mapping of accounts to CSM owners.
| Column | Type | Keys |
|---|---|---|
| account_assignment_key | bigint | PK |
| account_key | bigint | |
| csm_user_key | bigint | |
| assignment_id | varchar | BK |
| assignment_type | varchar | |
| assigned_at | timestamp | |
| ended_at | timestamp | |
| is_current | boolean |
health_score_snapshot
Grain: one row per account per health-score snapshot
Composite health-score header — one per account per scoring run.
| Column | Type | Keys |
|---|---|---|
| health_score_snapshot_key | bigint | PK |
| account_key | bigint | |
| health_snapshot_id | varchar | BK |
| snapshot_date | date | |
| overall_health_score | decimal | |
| health_grade | varchar | |
| trend | varchar | |
| churn_risk_probability_pct | decimal | |
| expansion_propensity_pct | decimal | |
| computed_by_version | varchar |
health_signal
Grain: one row per signal contributing to a health snapshot
Per-signal detail behind each composite health snapshot.
| Column | Type | Keys |
|---|---|---|
| health_signal_key | bigint | PK |
| health_score_snapshot_key | bigint | |
| health_signal_type_key | bigint | |
| signal_id | varchar | BK |
| signal_value | decimal | |
| normalized_score | decimal | |
| signal_threshold | decimal | |
| is_red_flag | boolean | |
| observed_at | timestamp |
risk_record
Grain: one row per logged risk against an account
Risks logged against accounts, owned by a CSM.
| Column | Type | Keys |
|---|---|---|
| risk_record_key | bigint | PK |
| account_key | bigint | |
| risk_category_key | bigint | |
| csm_user_key | bigint | |
| risk_id | varchar | BK |
| opened_at | timestamp | |
| target_resolution_date | date | |
| resolved_at | timestamp | |
| risk_severity | varchar | |
| risk_status | varchar | |
| arr_at_risk | decimal | |
| currency | varchar | |
| mitigation_plan_summary | varchar |
engagement_log
Grain: one row per CSM engagement with an account
Calls, meetings, QBRs and other touchpoints between CSM and customer.
| Column | Type | Keys |
|---|---|---|
| engagement_log_key | bigint | PK |
| account_key | bigint | |
| csm_user_key | bigint | |
| engagement_type_key | bigint | |
| engagement_id | varchar | BK |
| engagement_date | timestamp | |
| duration_minutes | integer | |
| summary | varchar | |
| attendee_count | integer | |
| sentiment | varchar | |
| was_executive_attended | boolean | |
| next_steps | varchar |
playbook_run
Grain: one row per playbook execution
A single execution of a playbook template against an account.
| Column | Type | Keys |
|---|---|---|
| playbook_run_key | bigint | PK |
| account_key | bigint | |
| playbook_template_key | bigint | |
| csm_user_key | bigint | |
| playbook_run_id | varchar | BK |
| started_at | timestamp | |
| completed_at | timestamp | |
| run_status | varchar | |
| outcome | varchar |
playbook_task
Grain: one row per task within a playbook run
Individual tasks/checklist items within a playbook run.
| Column | Type | Keys |
|---|---|---|
| playbook_task_key | bigint | PK |
| playbook_run_key | bigint | |
| playbook_task_id | varchar | BK |
| task_sequence | smallint | |
| task_title | varchar | |
| due_date | date | |
| completed_at | timestamp | |
| task_status | varchar | |
| assigned_to_user | varchar |
nps_response
Grain: one row per NPS survey response
Individual NPS survey responses captured at the user grain.
| Column | Type | Keys |
|---|---|---|
| nps_response_key | bigint | PK |
| user_key | bigint | |
| account_key | bigint | |
| nps_response_id | varchar | BK |
| survey_date | date | |
| nps_score | smallint | |
| category | varchar | |
| verbatim | varchar | PII |
| csat_score | decimal | |
| follow_up_required | boolean |
support_ticket_summary
Grain: one row per account per support summary period
Per-account rollup of support activity over a period.
| Column | Type | Keys |
|---|---|---|
| support_ticket_summary_key | bigint | PK |
| account_key | bigint | |
| summary_id | varchar | BK |
| period_start | date | |
| period_end | date | |
| tickets_opened | integer | |
| tickets_closed | integer | |
| critical_count | integer | |
| average_resolution_hours | decimal | |
| csat_average | decimal |
Lookup tables (7)
product
Grain: one row per product offering
Reference list of SaaS products tracked for adoption.
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| product_id | varchar | BK |
| product_name | varchar | |
| product_family | varchar | |
| is_core | boolean | |
| is_addon | boolean |
plan
Grain: one row per subscription plan
Subscription plan catalog (tier + price book).
| Column | Type | Keys |
|---|---|---|
| plan_key | bigint | PK |
| plan_id | varchar | BK |
| plan_name | varchar | |
| plan_tier | varchar | |
| billing_frequency | varchar | |
| list_mrr | decimal | |
| currency | varchar |
health_signal_type
Grain: one row per health signal type
Catalog of input signals that feed the composite health score.
| Column | Type | Keys |
|---|---|---|
| health_signal_type_key | bigint | PK |
| signal_type_id | varchar | BK |
| signal_name | varchar | |
| signal_category | varchar | |
| weight | decimal | |
| is_leading_indicator | boolean |
risk_category
Grain: one row per risk category
Taxonomy of churn / at-risk reasons.
| Column | Type | Keys |
|---|---|---|
| risk_category_key | bigint | PK |
| risk_category_id | varchar | BK |
| risk_name | varchar | |
| is_actionable | boolean |
playbook_template
Grain: one row per playbook template
Reusable customer-success playbook definitions.
| Column | Type | Keys |
|---|---|---|
| playbook_template_key | bigint | PK |
| playbook_id | varchar | BK |
| playbook_name | varchar | |
| playbook_type | varchar | |
| target_stage | varchar | |
| expected_duration_days | integer |
csm_role
Grain: one row per CSM role definition
Roles available to customer-success team members.
| Column | Type | Keys |
|---|---|---|
| csm_role_key | bigint | PK |
| csm_role_id | varchar | BK |
| role_name | varchar | |
| tier_coverage | varchar |
engagement_type
Grain: one row per engagement type
Classification of CSM-to-customer engagement modalities.
| Column | Type | Keys |
|---|---|---|
| engagement_type_key | bigint | PK |
| engagement_type_id | varchar | BK |
| description | varchar | |
| is_high_touch | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- account.plan_key → plan (many-to-one)
- user.account_key → account (many-to-one)
- csm_user.csm_role_key → csm_role (many-to-one)
- account_assignment.account_key → account (many-to-one)
- account_assignment.csm_user_key → csm_user (many-to-one)
- health_score_snapshot.account_key → account (many-to-one)
- health_signal.health_score_snapshot_key → health_score_snapshot (many-to-one)
- health_signal.health_signal_type_key → health_signal_type (many-to-one)
- risk_record.account_key → account (many-to-one)
- risk_record.risk_category_key → risk_category (many-to-one)
- risk_record.csm_user_key → csm_user (many-to-one)
- engagement_log.account_key → account (many-to-one)
- engagement_log.csm_user_key → csm_user (many-to-one)
- engagement_log.engagement_type_key → engagement_type (many-to-one)
- playbook_run.account_key → account (many-to-one)
- playbook_run.playbook_template_key → playbook_template (many-to-one)
- playbook_run.csm_user_key → csm_user (many-to-one)
- playbook_task.playbook_run_key → playbook_run (many-to-one)
- nps_response.user_key → user (many-to-one)
- nps_response.account_key → account (many-to-one)
- support_ticket_summary.account_key → account (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.