Dashboard KPI designer for any business type
Views
5.6K
Copies
2.0K
Likes
1.1K
Comments
0
Copy rate
35.0%
Prompt
You are a BI consultant. Design a KPI dashboard for a [business_type] with [business_size]. The dashboard should be for the [audience_role] persona.
Include:
1. Top-level KPIs (5-7 metrics) with target thresholds
2. Layout wireframe (where each element goes)
3. Chart type recommendations for each metric (with justification)
4. Drill-down paths (what happens when you click a metric)
5. Alert/anomaly thresholds
6. Refresh frequency per metric
7. Data sources and SQL queries for each KPICustomise this prompt
Fill in 3 variables to personalise this prompt
Preview
You are a BI consultant. Design a KPI dashboard for a [business_type] with [business_size]. The dashboard should be for the [audience_role] persona.
Include:
1. Top-level KPIs (5-7 metrics) with target thresholds
2. Layout wireframe (where each element goes)
3. Chart type recommendations for each metric (with justification)
4. Drill-down paths (what happens when you click a metric)
5. Alert/anomaly thresholds
6. Refresh frequency per metric
7. Data sources and SQL queries for each KPI
Example output
# Executive KPI Dashboard: B2B SaaS Company
**Business:** CloudBoard — project management SaaS
**Size:** $5M ARR, 2,400 customers, 45 employees
**Audience:** CEO / co-founder (daily check, 60 seconds max)
---
## 1. Top-Level KPIs
| # | KPI | Current | Target | Alert Threshold |
|---|-----|---------|--------|-----------------|
| 1 | **MRR** (Monthly Recurring Revenue) | $417K | $500K by Q4 | <$400K or >5% drop MoM |
| 2 | **Net Revenue Retention (NRR)** | 118% | >120% | <110% |
| 3 | **New Customers (this month)** | 47 | 60/mo | <30 |
| 4 | **Churn Rate** | 2.1% | <2.0% | >3.0% |
| 5 | **CAC Payback (months)** | 8.2 | <9 | >12 |
| 6 | **Runway (months)** | 18 | >12 | <9 |
| 7 | **NPS Score** | 52 | >50 | <40 |
---
## 2. Layout Wireframe
```
┌─────────────────────────────────────────────────────┐
│ CloudBoard Dashboard April 2026 [🔔 2] │
├──────────┬──────────┬──────────┬──────────┬─────────┤
│ MRR │ NRR │ New Cust │ Churn │ NPS │
│ $417K │ 118% │ 47 │ 2.1% │ 52 │
│ ↑ 4.2% │ ↑ 2pp │ ↓ 12% │ ↓ 0.3pp │ ↑ 3 │
│ ● green │ ● green │ ● red │ ● green │ ● grn │
├──────────┴──────────┴──────────┴──────────┴─────────┤
│ │
│ [MRR Trend — Area Chart] [Revenue Breakdown] │
│ 12-month line with target Pie: new/expansion/ │
│ band overlay contraction/churn │
│ │
├─────────────────────────┬────────────────────────────┤
│ │ │
│ [Cohort Retention │ [Pipeline & Forecast] │
│ Heatmap] │ Bar chart: pipeline │
│ Monthly cohorts │ stages + forecast line │
│ color-coded by │ │
│ retention % │ │
│ │ │
├─────────────────────────┴────────────────────────────┤
│ [CAC & Payback] │ [Runway] │ [Alerts] │
│ Dual-axis: CAC bar + │ Cash balance │ Recent │
│ payback line over time │ burn rate │ anomalies │
└─────────────────────────┴───────────────┴────────────┘
```
---
## 3. Chart Type Recommendations
| KPI | Chart Type | Why This Chart |
|-----|-----------|----------------|
| MRR trend | Area chart with target band | Area shows magnitude; the shaded target band makes "on track / off track" instantly visible |
| Revenue breakdown | Waterfall chart | Shows new + expansion - contraction - churn = net change. Tells the full revenue story in one view |
| Cohort retention | Heatmap (month × month) | Heat colors reveal retention patterns at a glance; darker = better. Immediately shows if recent cohorts are better or worse |
| Pipeline forecast | Stacked bar + line overlay | Bars show pipeline volume by stage; line shows weighted forecast. Dual encoding for two related metrics |
| CAC payback | Dual-axis (bar + line) | Bar = CAC amount (left axis), Line = payback months (right axis). Shows if spend efficiency is improving |
| Runway | Gauge or single number | CEO needs one number: "how many months until we need to raise?" Gauge with red/yellow/green zones |
| NPS | Trend line with distribution | Line shows NPS over time; small bar chart beneath shows promoter/passive/detractor split |
---
## 4. Drill-Down Paths
**MRR → click → MRR Detail View:**
- Breakdown by plan tier (Free → Starter → Pro → Enterprise)
- New MRR vs. expansion vs. contraction vs. churned
- Top 10 accounts by MRR
- MRR movements table (upgrades, downgrades, churns this month)
**New Customers → click → Acquisition Detail:**
- By source (organic, paid, referral, sales)
- By plan tier
- Time-to-close distribution (histogram)
- Conversion funnel (visitor → trial → paid)
**Churn Rate → click → Churn Analysis:**
- Churned customers list with: name, plan, tenure, last login date, stated reason
- Churn by plan tier (are free users churning more than paid?)
- Churn by tenure (3-month vs 6-month vs 12-month churn curves)
- Churn risk: customers at risk based on usage decline signals
**NPS → click → NPS Detail:**
- Score distribution (0-10 histogram)
- Verbatim feedback from promoters and detractors
- NPS by plan tier and tenure
- Trend over last 12 months
---
## 5. Alert Thresholds
| Alert | Condition | Severity | Notification |
|-------|-----------|----------|-------------|
| MRR drop | >5% decline MoM | 🔴 Critical | Email CEO + Slack #leadership |
| Churn spike | >3% in any month | 🔴 Critical | Email CEO + CS lead |
| NPS drop | Below 40 | 🟡 Warning | Slack #product |
| Runway low | <9 months | 🔴 Critical | Email CEO + CFO |
| CAC rising | >20% increase QoQ | 🟡 Warning | Slack #marketing |
| New customers | <30 in a month | 🟡 Warning | Slack #growth |
| Big account churn | Any account >$5K MRR churns | 🔴 Critical | Email CEO + AE immediately |
---
## 6. Refresh Frequency
| Metric | Refresh | Why |
|--------|---------|-----|
| MRR | Daily (6am UTC) | Billing events happen overnight; morning refresh catches them |
| New customers | Real-time | CEO wants to see every new signup as it happens |
| Churn | Daily | Captures cancellations from previous day |
| NRR | Weekly (Monday) | Metric is noisy daily; weekly smooths it |
| CAC / Payback | Monthly | Requires full-month ad spend data to be accurate |
| NPS | After each survey batch (bi-weekly) | NPS is collected in waves, not continuously |
| Runway | Monthly | Depends on bank balance reconciliation |
---
## 7. SQL Queries for Each KPI
### MRR
```sql
SELECT
DATE_TRUNC('month', s.current_period_start) AS month,
SUM(s.amount / 100.0) AS mrr -- Stripe stores cents
FROM subscriptions s
WHERE s.status = 'active'
AND s.current_period_start >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY month
ORDER BY month;
```
### Net Revenue Retention
```sql
WITH cohort AS (
SELECT customer_id, SUM(amount) AS starting_mrr
FROM subscriptions
WHERE status = 'active'
AND current_period_start >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 months')
AND current_period_start < DATE_TRUNC('month', CURRENT_DATE - INTERVAL '11 months')
GROUP BY customer_id
),
current AS (
SELECT customer_id, SUM(amount) AS current_mrr
FROM subscriptions
WHERE status = 'active'
AND current_period_start >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY customer_id
)
SELECT
ROUND(
SUM(COALESCE(c.current_mrr, 0)) * 100.0 / SUM(co.starting_mrr),
1
) AS nrr_pct
FROM cohort co
LEFT JOIN current c ON c.customer_id = co.customer_id;
```
### Churn Rate
```sql
SELECT
DATE_TRUNC('month', cancelled_at) AS month,
COUNT(*) AS churned,
(SELECT COUNT(*) FROM subscriptions WHERE status = 'active') AS total_active,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM subscriptions WHERE status = 'active'), 2) AS churn_pct
FROM subscriptions
WHERE cancelled_at >= DATE_TRUNC('month', CURRENT_DATE)
AND cancelled_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
GROUP BY month;
```