# Metabase KPI Cards - Ready to Deploy

## 🔑 10 Essential KPI Cards for $7000/month Automation

### Card 1: Revenue This Month
```sql
SELECT 
    COALESCE(SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END), 0) / 100.0 as revenue_usd,
    7000.0 as target
FROM stripe_transactions 
WHERE status IN ('paid', 'succeeded')
AND created_at >= DATE_TRUNC('month', CURRENT_DATE)
```
**Visualization:** Big number with target comparison  
**Refresh:** Every 15 minutes  
**Alert:** If revenue < $3500 (50% threshold)

---

### Card 2: Active Projects
```sql
SELECT 
    COUNT(*) as active_projects,
    2 as max_capacity
FROM projects 
WHERE status NOT IN ('completed', 'cancelled', 'on_hold')
AND start_date <= CURRENT_DATE
AND (completion_date IS NULL OR completion_date > CURRENT_DATE)
```
**Visualization:** Progress bar (current/max)  
**Refresh:** Every 5 minutes  
**Alert:** If > 2 projects (capacity warning)

---

### Card 3: Pipeline Value
```sql
SELECT 
    COALESCE(SUM(
        CASE 
            WHEN probability >= 0.8 THEN estimated_value * 0.8
            WHEN probability >= 0.5 THEN estimated_value * 0.5
            ELSE estimated_value * 0.2
        END
    ), 0) as pipeline_value
FROM leads l
JOIN deals d ON l.id = d.lead_id
WHERE l.status IN ('qualified', 'proposal_sent', 'negotiation')
AND d.created_at >= CURRENT_DATE - INTERVAL '30 days'
```
**Visualization:** Metric with trend arrow  
**Refresh:** Hourly  
**Alert:** If pipeline < $5000 (growth concern)

---

### Card 4: Maintenance MRR
```sql
SELECT 
    COUNT(*) as clients,
    SUM(monthly_fee) / 100.0 as mrr_active,
    1000.0 as target_mrr
FROM maintenance_clients 
WHERE status = 'active'
AND (next_bill_date <= CURRENT_DATE + INTERVAL '7 days' 
     OR next_bill_date IS NULL)
```
**Visualization:** Big number with progress to $1000  
**Refresh:** Daily at midnight  
**Alert:** If MRR < $300 (renewal needed)

---

### Card 5: Lead Conversion Rate
```sql
WITH funnel AS (
    SELECT 
        COUNT(*) FILTER (WHERE source IS NOT NULL) as visitors,
        COUNT(*) FILTER (WHERE qualified_at IS NOT NULL) as qualified,
        COUNT(*) FILTER (WHERE proposal_sent_at IS NOT NULL) as proposals,
        COUNT(*) FILTER (WHERE won_at IS NOT NULL) as won
    FROM leads
    WHERE created_at >= DATE_TRUNC('week', CURRENT_DATE)
)
SELECT 
    ROUND((qualified::DECIMAL / NULLIF(visitors, 0)) * 100, 1) as qualify_rate,
    ROUND((proposals::DECIMAL / NULLIF(qualified, 0)) * 100, 1) as proposal_rate,
    ROUND((won::DECIMAL / NULLIF(proposals, 0)) * 100, 1) as close_rate,
    ROUND((won::DECIMAL / NULLIF(visitors, 0)) * 100, 1) as conversion_rate
FROM funnel
```
**Visualization:** Funnel chart  
**Refresh:** Daily  
**Alert:** If conversion < 5%

---

### Card 6: Monthly Recurring Revenue Growth
```sql
SELECT 
    DATE_TRUNC('month', created_at) as month,
    SUM(amount) / 100.0 as mrr,
    LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', created_at)) as prev_mrr
FROM stripe_transactions
WHERE status = 'paid'
AND created_at >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC
```
**Visualization:** Line chart  
**Refresh:** Daily  
**Alert:** If MoM growth < 10%

---

### Card 7: Average Project Profit Margin
```sql
SELECT 
    AVG(ROUND((budget - actual_cost) * 100.0 / budget, 1)) as avg_margin,
    COUNT(*) as projects_analyzed,
    AVG(budget) / 100.0 as avg_budget
FROM projects 
WHERE status = 'completed'
AND completion_date >= CURRENT_DATE - INTERVAL '90 days'
AND budget > 0
```
**Visualization:** Gauge or metric  
**Refresh:** Weekly  
**Alert:** If margin < 50%

---

### Card 8: Hours Utilization
```sql
SELECT 
    ROUND(SUM(hours_billed) * 100.0 / NULLIF(SUM(hours_available), 0), 1) as utilization_percent,
    SUM(hours_billed) as billed_hours,
    SUM(hours_available) as total_hours_available
FROM time_tracking
WHERE date >= DATE_TRUNC('month', CURRENT_DATE)
```
**Visualization:** Progress bar with hours  
**Refresh:** Daily  
**Alert:** If utilization > 90% (overload risk)

---

### Card 9: New Leads This Week
```sql
SELECT 
    COUNT(*) as new_leads,
    COUNT(DISTINCT source) as sources,
    DATE_PART('day', NOW() - MIN(created_at)) as days_since_first
FROM leads 
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
```
**Visualization:** Single number with icon  
**Refresh:** Daily at midnight  
**Alert:** If leads = 0

---

### Card 10: Client Health Score
```sql
SELECT 
    ROUND(AVG(
        CASE 
            WHEN maintenance_active = true THEN 100
            WHEN last_contact >= CURRENT_DATE - INTERVAL '30 days' THEN 80
            WHEN last_contact >= CURRENT_DATE - INTERVAL '90 days' THEN 60
            ELSE 40
        END
    ), 1) as avg_client_health,
    COUNT(*) as total_clients
FROM (
    SELECT DISTINCT
        client_id,
        MAX(CASE WHEN type = 'maintenance' THEN true ELSE false END) as maintenance_active,
        MAX(contact_date) as last_contact
    FROM client_interactions
    WHERE interaction_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY client_id
) client_metrics
```
**Visualization:** Color-coded gauge  
**Refresh:** Daily  
**Alert:** If health < 70

---

## 🚀 Deployment Checklist

### Before Deployment:
- [ ] PostgreSQL database running
- [ ] Tables created from schema.sql
- [ ] Metabase container up (port 3001)
- [ ] Stripe webhooks configured
- [ ] n8n workflow active

### Deployment Steps:
1. Open Metabase at `http://localhost:3001`
2. Add PostgreSQL database connection
3. Click "Create Question" → "Custom"
4. Paste SQL for each card above
5. Save each as "Card" (not dashboard)
6. Create new dashboard "StartupLX KPIs"
7. Drag cards to dashboard in order
8. Set refresh times per card
9. Configure alerts per card
10. Set sharing link for mobile access

### Auto-Refresh Settings:
- Revenue cards: 15 minutes
- Pipeline cards: 30 minutes
- Health cards: 1 hour
- Historical cards: 1 day