Skip to main content

Knowledge > Runbooks > Business Ops > Weekly Metrics Review

Weekly Metrics Review

A 30-minute weekly review of product, operational, and business metrics across all ChurchWiseAI properties. Run this once per week to track trajectory and catch trends before they become problems.

Prerequisites​

  • Access to Supabase SQL editor or MCP tool
  • Stripe CLI (test and live mode access)
  • Access to Vercel dashboard for infrastructure metrics

Metrics to Gather​

1. Subscription Activity This Week​

-- New subscriptions
SELECT count(*) as new_subscriptions
FROM premium_churches
WHERE created_at > now() - interval '7 days'
  AND stripe_subscription_id IS NOT NULL;

-- Cancellations (subscriptions that lost their stripe_subscription_id)
-- Note: adjust query based on actual schema for tracking cancellations
SELECT count(*) FROM premium_churches
WHERE updated_at > now() - interval '7 days'
  AND stripe_subscription_id IS NULL
  AND plan != 'free';

Via Stripe CLI (live mode when customers exist):

stripe subscriptions list --status canceled --limit 20 --api-key $STRIPE_LIVE_SECRET_KEY

2. Voice Call Volume​

SELECT
  date_trunc('week', created_at) as week,
  count(*) as total_calls,
  count(DISTINCT church_id) as churches_with_calls,
  round(avg(extract(epoch from (ended_at - started_at))/60)::numeric, 1) as avg_duration_min
FROM voice_call_logs
WHERE created_at > now() - interval '28 days'
GROUP BY week ORDER BY week DESC;

3. Engagement: Prayer Requests and Callbacks​

SELECT
  date_trunc('week', created_at) as week,
  'prayer_request' as type,
  count(*) as count
FROM voice_prayer_requests
WHERE created_at > now() - interval '28 days'
GROUP BY week
UNION ALL
SELECT
  date_trunc('week', created_at),
  'callback',
  count(*)
FROM voice_callback_requests
WHERE created_at > now() - interval '28 days'
GROUP BY week
ORDER BY week DESC, type;

4. Visitor Contact Captures​

SELECT date_trunc('week', created_at) as week, count(*) as visitor_captures
FROM voice_visitor_contacts
WHERE created_at > now() - interval '28 days'
GROUP BY week ORDER BY week DESC;

5. Content Growth (ITW Illustrations)​

SELECT
  date_trunc('week', created_at) as week,
  count(*) as new_illustrations
FROM unified_rag_content
WHERE content_type = 'illustration'
  AND created_at > now() - interval '28 days'
GROUP BY week ORDER BY week DESC;

6. Error Rate Trend​

SELECT
  date_trunc('week', created_at) as week,
  count(*) FILTER (WHERE severity = 'P0') as p0_errors,
  count(*) FILTER (WHERE severity = 'P1') as p1_errors,
  count(*) FILTER (WHERE severity = 'P2') as p2_errors
FROM ops_error_reports
WHERE created_at > now() - interval '28 days'
GROUP BY week ORDER BY week DESC;

Flag any week where P0 errors increased vs. prior week.

7. PewSearch Directory Health​

-- Visible church count (should be stable ~218K)
SELECT count(*) FROM churches WHERE directory_visible = true;

-- New premium pages this week
SELECT count(*) FROM premium_churches
WHERE plan = 'pro_website'
  AND created_at > now() - interval '7 days';

8. Compile into a Weekly Status Update​

Format for the record (add to a running notes doc or DECISION_LOG.md if significant):

Week of [date]:
- New subscriptions: X
- Cancellations: X
- MRR: $X (change: +/-$X)
- Voice calls: X total, X churches active
- Prayer requests: X | Callbacks: X | Visitor captures: X
- New illustrations: X
- P0 errors: X (vs X last week)
- Notable events: [anything unusual]

Verification​

  • All queries return results without errors
  • Numbers are plausible (sanity check against prior week)
  • Any anomalies (spike in errors, drop in calls, unexpected churn) are documented

See Also​