Skip to main content

Knowledge > Runbooks > Technical Ops > Query Product Analytics for Reporting

Query Product Analytics for Reporting

Gather product and operational metrics from across the ChurchWiseAI portfolio for reporting, health checks, or strategic review.

Prerequisites​

  • Access to Supabase SQL editor or mcp__plugin_supabase_supabase__execute_sql MCP tool
  • Stripe CLI for revenue metrics
  • Access to Vercel dashboard for infrastructure metrics

Analytics Sources by Category​

Chatbot Analytics​

Use the Supabase RPC function directly:

SELECT * FROM get_chatbot_analytics(
  p_church_id := '00000000-0000-4000-a000-000000000001',
  p_days := 30
);

Or call the API route (requires admin token):

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

Key chatbot metrics:

-- Conversation volume by church (last 30 days)
SELECT church_id, count(*) as conversations
FROM chatbot_conversations  -- check actual table name in schema
WHERE created_at > now() - interval '30 days'
GROUP BY church_id ORDER BY conversations DESC;

-- Prayer requests received
SELECT count(*), date_trunc('week', created_at) as week
FROM voice_prayer_requests
WHERE created_at > now() - interval '90 days'
GROUP BY week ORDER BY week DESC;

-- Callback requests
SELECT count(*), date_trunc('week', created_at) as week
FROM voice_callback_requests
WHERE created_at > now() - interval '90 days'
GROUP BY week ORDER BY week DESC;

Voice Analytics​

-- Call volume by church (last 30 days)
SELECT church_id, count(*) as calls,
       avg(extract(epoch from (ended_at - started_at))/60) as avg_duration_minutes
FROM voice_call_logs
WHERE created_at > now() - interval '30 days'
GROUP BY church_id ORDER BY calls DESC;

-- Call volume by week
SELECT date_trunc('week', created_at) as week, count(*) as calls
FROM voice_call_logs
WHERE created_at > now() - interval '90 days'
GROUP BY week ORDER BY week DESC;

-- Calls by classification (if Gemini classification is stored)
SELECT classification, count(*)
FROM voice_call_logs
WHERE created_at > now() - interval '30 days'
GROUP BY classification;

Subscription and Revenue Metrics​

-- Active subscriptions by plan
SELECT plan, tier, count(*) as count
FROM premium_churches
WHERE stripe_subscription_id IS NOT NULL
GROUP BY plan, tier ORDER BY count DESC;

-- New subscriptions this month
SELECT count(*) FROM premium_churches
WHERE created_at > date_trunc('month', now());

Via Stripe CLI (test mode):

stripe subscriptions list --status active --limit 100

Live mode:

stripe subscriptions list --status active --limit 100 --api-key $STRIPE_LIVE_SECRET_KEY

Operations Health​

-- Recent P0 errors
SELECT route, message, count(*) as occurrences, max(created_at) as last_seen
FROM ops_error_reports
WHERE severity = 'P0' AND created_at > now() - interval '7 days'
GROUP BY route, message ORDER BY occurrences DESC;

-- Service quota snapshots (Twilio balance, Resend usage)
SELECT service, metric_name, metric_value, recorded_at
FROM ops_quota_snapshots
ORDER BY recorded_at DESC LIMIT 20;

-- Error rate by week
SELECT date_trunc('week', created_at) as week,
       count(*) FILTER (WHERE severity = 'P0') as p0_count,
       count(*) FILTER (WHERE severity = 'P1') as p1_count
FROM ops_error_reports
WHERE created_at > now() - interval '90 days'
GROUP BY week ORDER BY week DESC;

Church Directory Metrics (PewSearch)​

-- Total visible churches
SELECT count(*) FROM churches WHERE directory_visible = true;

-- Churches by denomination (top 10)
SELECT d.name, count(c.id) as church_count
FROM churches c
JOIN denominations d ON c.denomination_id = d.id
WHERE c.directory_visible = true
GROUP BY d.name ORDER BY church_count DESC LIMIT 10;

-- Premium pages (pro website subscriptions)
SELECT count(*) FROM premium_churches WHERE plan = 'pro_website';

Content Metrics (IllustrateTheWord)​

-- Illustration count by theological lens
SELECT theological_lens, count(*) as illustrations
FROM unified_rag_content
WHERE content_type = 'illustration' AND is_active = true
GROUP BY theological_lens ORDER BY illustrations DESC;

-- New illustrations this month
SELECT count(*) FROM unified_rag_content
WHERE content_type = 'illustration'
AND created_at > date_trunc('month', now());

Verification​

  • Query results match expected magnitude (churches ~218K, unified_rag_content ~327K)
  • Aggregate counts are consistent across related queries
  • No query times out (add LIMIT if needed, paginate large result sets)

See Also​