Skip to main content

Knowledge > Runbooks > Business Ops > Customer Account Health Check

Customer Account Health Check

Proactively review active customer accounts for signs of low engagement or risk of churn. Catch at-risk accounts before they cancel.

Prerequisites​

  • Access to Supabase SQL editor
  • Active customer list (from premium_churches — currently all founder test accounts)

Note: As of 2026-04-10, there are 3 Stripe subscriptions (1 customer + 2 founder tests). 13 premium_churches rows total. This runbook is fully operational for when customers exist. Run it monthly once the first paying customer signs up.

Health Signals​

SignalGreen (Healthy)Yellow (Watch)Red (At Risk)
Last admin login< 14 days14–30 days> 30 days
Knowledge base content> 5 entries1–5 entries0 entries
Chatbot conversations> 10/month1–10/month0/month
Voice calls (if voice plan)> 5/month1–5/month0/month
Payment statusCurrent—Past due

Steps​

1. Get All Active Paying Customers​

SELECT
  pc.id,
  pc.church_id,
  pc.plan,
  pc.tier,
  pc.stripe_subscription_id,
  pc.created_at as subscribed_at,
  c.name as church_name,
  c.city,
  c.state
FROM premium_churches pc
JOIN churches c ON pc.church_id = c.id
WHERE pc.stripe_subscription_id IS NOT NULL
  AND pc.plan != 'free'
ORDER BY pc.created_at ASC;

2. Check Last Admin Login for Each Church​

SELECT
  cas.church_id,
  max(cas.last_active) as last_login,
  now() - max(cas.last_active) as days_since_login
FROM church_admin_sessions cas
WHERE cas.church_id IN (
  SELECT church_id FROM premium_churches WHERE stripe_subscription_id IS NOT NULL
)
GROUP BY cas.church_id
ORDER BY last_login ASC;

Flag any church with days_since_login > 30 days — they may have forgotten about the product or are unhappy.

3. Check Knowledge Base Content​

SELECT
  church_id,
  count(*) as kb_entries,
  max(created_at) as last_entry_added
FROM unified_rag_content
WHERE church_id IN (
  SELECT church_id FROM premium_churches WHERE stripe_subscription_id IS NOT NULL
)
GROUP BY church_id;

A church with 0 KB entries cannot benefit from the chatbot. Proactively offer onboarding help.

4. Check Chatbot and Voice Activity​

-- Voice call activity per church (last 30 days)
SELECT
  church_id,
  count(*) as calls_last_30_days
FROM voice_call_logs
WHERE created_at > now() - interval '30 days'
  AND church_id IN (
    SELECT church_id FROM premium_churches
    WHERE stripe_subscription_id IS NOT NULL
    AND tier IN ('voice', 'both')
  )
GROUP BY church_id;

For chatbot activity: call the analytics API for each church:

GET /api/admin/analytics-proxy?churchId=[id]&days=30

5. Check Payment Status​

# Check for any past_due subscriptions
stripe subscriptions list --status past_due --limit 20 --api-key $STRIPE_LIVE_SECRET_KEY

A past_due subscription is the most urgent health issue — it will cancel automatically if not resolved.

6. Build the At-Risk List​

Score each customer across the health signals. Classify:

Critical (Red):

  • No admin login in > 30 days AND no KB content AND no activity → likely inactive customer, churn imminent

Watch (Yellow):

  • No admin login in 14–30 days OR no KB content but some activity
  • Payment past due

Healthy (Green):

  • Regular logins, KB content present, activity in last 30 days

7. Proactive Outreach for At-Risk Accounts​

For Red customers, draft a personal outreach email:

  • Reference their church by name
  • Offer a free onboarding call
  • Ask what's not working
  • Do not send without founder review

For Yellow customers:

  • Send an automated "tip of the week" via the lifecycle email system (automatic via daily cron)
  • For payment failures: send a personal payment failure notice

8. Log the Health Check​

Add a brief note to business ops records:

- 2026-03-25: Customer health check. X customers total. X green, X yellow, X red. Actions: [list]

Verification​

  • Every paying customer has been assessed across all health signals
  • At-risk customers are documented and have an action plan
  • Payment failures are being actively followed up

See Also​