SOP-CHS-01: Customer Health Scoring System v1.0¶
Document ID: SOP-CHS-01
Version: 1.0
Effective Date: January 2026
Owner: Operations
Status: ✅ Production Ready
1. Purpose¶
This SOP defines the Customer Health Scoring system that proactively identifies at-risk customers and triggers contextual interventions before churn occurs.
Core principle: Generic "is everything okay?" messages feel hollow. Component-based scoring enables interventions that reference the actual problem, building trust through demonstrated awareness.
2. System Overview¶
2.1 What It Does¶
- Calculates a 0-100 health score for every active customer daily
- Breaks score into six weighted components (delivery, engagement, skip, payment, support, tenure)
- Detects when components cross intervention thresholds
- Triggers contextual emails or internal alerts based on the specific issue
- Enforces cooldowns to prevent spam
2.2 Architecture¶
┌─────────────────────────────────────────────────────────────────â”
│ DAILY SCHEDULE (UTC) │
├─────────────────────────────────────────────────────────────────┤
│ 5:00 AM │ Cron: fn_calculate_all_customer_health() │
│ │ → Calculates scores for all active customers │
├────────────┼────────────────────────────────────────────────────┤
│ 5:15 AM │ Make.com: CHS-01 Daily Health Score Sync │
│ │ → Syncs all scores to Customer.io attributes │
├────────────┼────────────────────────────────────────────────────┤
│ 5:30 AM │ Cron: fn_detect_interventions() │
│ │ → Checks thresholds, creates intervention events │
├────────────┼────────────────────────────────────────────────────┤
│ Every │ Make.com: CHS-02 Support Volume Alerts │
│ 15 min │ → Routes support alerts to Slack #ops-urgent │
├────────────┼────────────────────────────────────────────────────┤
│ Real-time │ Event Bridge scenario │
│ │ → Routes intervention events to Customer.io │
└────────────┴────────────────────────────────────────────────────┘
3. Health Score Components¶
3.1 Component Weights¶
| Component | Weight | What It Measures |
|---|---|---|
| Delivery | 25% | Courier performance (on-time, no exceptions) |
| Engagement | 15% | Portal logins, email opens, QR scans |
| Skip | 20% | Frequency of skipped deliveries |
| Payment | 20% | Payment success rate |
| Support | 10% | Support ticket volume (inverse) |
| Tenure | 10% | Subscription age (loyalty bonus) |
Total: 100%
3.2 Component Calculations¶
Delivery Score (25%)¶
Base: 100
- Each delivery exception in last 28 days: -20
- Each late delivery (>1 day) in last 28 days: -10
Minimum: 0
Engagement Score (15%)¶
Base: 20 (baseline for passive healthy customers)
+ Portal login in last 14 days: +30
+ Email opened in last 14 days: +25
+ QR code scanned in last 30 days: +25
Maximum: 100
Note: Low engagement after Box 2-3 is healthy behaviour. The proof system's value is that it's available if needed. We do NOT intervene on low engagement.
Skip Score (20%)¶
Payment Score (20%)¶
Support Score (10%)¶
Tenure Score (10%)¶
Subscription age < 30 days: 50
Subscription age 30-89 days: 70
Subscription age 90-179 days: 85
Subscription age 180+ days: 100
3.3 Risk Tiers¶
| Tier | Score Range | Meaning |
|---|---|---|
| Healthy | 80-100 | No intervention needed |
| Monitor | 60-79 | Watch for decline |
| At Risk | 40-59 | Proactive outreach recommended |
| Critical | 0-39 | Immediate attention required |
4. Intervention Logic¶
4.1 Intervention Triggers¶
| Component | Threshold | Intervention Type |
|---|---|---|
| Payment | < 80 | Email: Payment failed |
| Delivery | < 60 | Email: Delivery issues |
| Skip | < 60 | Email: Schedule check |
| Support | < 60 | Slack alert only (manual follow-up) |
| Engagement | — | No intervention (low engagement is healthy) |
| Tenure | — | No intervention (not actionable) |
4.2 Priority Logic¶
If multiple components are low, only ONE intervention is sent based on this priority:
- Payment (most urgent - subscription at risk)
- Delivery (operational failure we caused)
- Skip (behavioural signal of disengagement)
- Support (Slack alert only, no email)
4.3 Cooldowns¶
| Intervention Type | Cooldown Period |
|---|---|
| Email interventions | 14 days |
| Support Slack alerts | 7 days |
A customer will not receive the same intervention type within the cooldown period.
5. Customer.io Campaigns¶
5.1 Campaign: Health: Payment Failed¶
| Setting | Value |
|---|---|
| Trigger event | health_intervention_payment |
| Subject | Quick note about your payment |
| Preview text | Easy to sort |
| CTA | Update payment method → my.protocolraw.co.uk |
Copy:
Hi {{customer.first_name}},
Your last payment didn't go through. This happens - cards expire, banks flag things.
Update your payment method here:
[Update payment method]
Protocol Raw
5.2 Campaign: Health: Delivery Issues¶
| Setting | Value |
|---|---|
| Trigger event | health_intervention_delivery |
| Subject | Your recent deliveries |
| Preview text | Let us know if we can help |
| CTA | None (invites reply) |
Copy:
Hi {{customer.first_name}},
Your last couple of deliveries haven't gone smoothly.
If there's something that might help - a safe place, a neighbour,
specific timing - reply and let us know. We'll see what we can do
with the courier.
Protocol Raw
5.3 Campaign: Health: Schedule Check¶
| Setting | Value |
|---|---|
| Trigger event | health_intervention_skip |
| Subject | Is your schedule working? |
| Preview text | We can adjust it |
| CTA | Adjust your schedule → my.protocolraw.co.uk |
Copy:
Hi {{customer.first_name}},
You've skipped a couple of recent boxes. That's fine, but your
current frequency might not be quite right.
You can change it anytime in your portal. Options are every 2, 3,
4, 5, or 6 weeks.
[Adjust your schedule]
Protocol Raw
6. Make.com Scenarios¶
6.1 CHS-01: Daily Health Score Sync¶
Schedule: 5:15 AM UTC daily
Flow:
1. HTTP GET → v_customer_health_for_sync view
2. Iterator → Loop through customers
3. Customer.io Identify → Update 9 attributes per customer
Attributes synced:
- health_score
- health_tier
- delivery_score
- engagement_score
- skip_score
- payment_score
- support_score
- tenure_score
- health_calculated_at
6.2 CHS-02: Support Volume Alerts¶
Schedule: Every 15 minutes
Flow:
1. HTTP GET → Unprocessed ALERT events from ops_events
2. Iterator → Loop through alerts
3. Router → Filter by alert_type = 'high_support_volume'
4. Slack → Post to #ops-urgent
5. HTTP PATCH → Mark event as processed
Slack message format:
âš ï¸ *High Support Volume*
*Customer:* {{email}}
*Name:* {{first_name}}
*Support Score:* {{support_score}}/100
*Health Score:* {{health_score}}/100
Recent support tickets may need manual follow-up.
<View Customer link>
7. Database Objects¶
7.1 Tables¶
customer_health_scores
- customer_id (UUID, PK, FK)
- health_score (INTEGER)
- risk_tier (TEXT)
- delivery_score, engagement_score, skip_score,
payment_score, support_score, tenure_score (INTEGER)
- subscription_age_days (INTEGER)
- calculated_at (TIMESTAMPTZ)
customer_interventions
- id (UUID, PK)
- customer_id (UUID, FK)
- intervention_type (TEXT)
- trigger_component (TEXT)
- trigger_value (INTEGER)
- created_at (TIMESTAMPTZ)
7.2 Functions¶
| Function | Purpose |
|---|---|
fn_calculate_customer_health(customer_id) |
Calculate single customer |
fn_calculate_all_customer_health() |
Calculate all active customers |
fn_detect_interventions() |
Check thresholds, create events |
7.3 Views¶
| View | Purpose |
|---|---|
v_customer_health_summary |
Health scores with customer details |
v_customer_health_for_sync |
Optimised for Customer.io sync |
7.4 Cron Jobs¶
| Job | Schedule | Function |
|---|---|---|
| daily-health-score-calculation | 0 5 * * * | fn_calculate_all_customer_health() |
| daily-intervention-detection | 30 5 * * * | fn_detect_interventions() |
8. Monitoring¶
8.1 Daily Checks¶
Metabase Dashboard: Customer Health - Health score distribution (pie chart by tier) - Score trends over time - Intervention volume by type - Component score averages
8.2 Alerts¶
| Condition | Channel | Threshold |
|---|---|---|
| High support volume | #ops-urgent | support_score < 60 |
| Cron job failure | #ops-alerts | Job doesn't complete |
8.3 Key Metrics¶
| Metric | Target | Query |
|---|---|---|
| % Healthy customers | > 70% | WHERE risk_tier = 'healthy' |
| % Critical customers | < 5% | WHERE risk_tier = 'critical' |
| Avg health score | > 75 | AVG(health_score) |
9. Troubleshooting¶
9.1 Health scores not calculating¶
Symptoms: customer_health_scores table empty or stale
Check:
SELECT jobid, jobname, schedule, active
FROM cron.job
WHERE jobname = 'daily-health-score-calculation';
Fix: Re-enable cron job if disabled
9.2 Interventions not triggering¶
Symptoms: Low scores but no events in ops_events
Check:
-- Check for recent interventions (cooldown may apply)
SELECT * FROM raw_ops.customer_interventions
WHERE customer_id = '[uuid]'
ORDER BY created_at DESC;
Fix: Wait for cooldown to expire, or manually reset for testing
9.3 Customer.io not receiving events¶
Symptoms: Events created but no emails sent
Check: 1. Event Bridge scenario running? 2. Customer.io campaign active? 3. Event type matches campaign trigger?
Fix: Check Make.com execution history for errors
9.4 Slack alerts not arriving¶
Symptoms: Support alerts created but not in Slack
Check:
1. CHS-02 scenario running every 15 min?
2. Events have processed_at = NULL?
3. Slack connection valid?
Test:
-- Create test alert
INSERT INTO raw_ops.ops_events (
kind, entity_type, entity_id, message, event_payload
) VALUES (
'ALERT', 'customer',
(SELECT id FROM raw_ops.customers LIMIT 1),
'Test alert',
'{"alert_type": "high_support_volume", "email": "test@test.com",
"first_name": "Test", "support_score": 45, "health_score": 72}'::jsonb
);
10. Future Enhancements¶
| Enhancement | Priority | Notes |
|---|---|---|
| Metabase dashboard | High | Visualise health distribution |
| Predictive churn model | Medium | ML-based Box-2 propensity |
| Real-time score updates | Low | Trigger recalc on key events |
| Direct Seal payment link | Medium | Include edit_url in payment email |
11. Version History¶
| Version | Date | Changes |
|---|---|---|
| 1.0 | January 2026 | Initial release |
12. Related Documentation¶
- SOP-SUB-00: Subscription State Management (payment events)
- SOP-02: Courier Watchdog (delivery events)
- Protocol_Raw_Email_Design_System_v1_1.md (email templates)
- Protocol_Raw_Customer_Portal_Documentation_v2_5.md (portal links)
End of SOP-CHS-01 v1.0
Last reviewed: January 2026
Next review: Monthly via automated monitoring
System status: ✅ Production Ready