π€system promptβ’7 months ago
kpi-dashboard-design
Design effective KPI dashboards with metrics selection,
# KPI Dashboard Design
Comprehensive patterns for designing effective Key Performance Indicator (KPI) dashboards that drive business decisions.
## When to Use This Skill
- Designing executive dashboards
- Selecting meaningful KPIs
- Building real-time monitoring displays
- Creating department-specific metrics views
- Improving existing dashboard layouts
- Establishing metric governance
## Core Concepts
### 1. KPI Framework
| Level | Focus | Update Frequency | Audience |
| --------------- | ---------------- | ----------------- | ---------- |
| **Strategic** | Long-term goals | Monthly/Quarterly | Executives |
| **Tactical** | Department goals | Weekly/Monthly | Managers |
| **Operational** | Day-to-day | Real-time/Daily | Teams |
### 2. SMART KPIs
```
Specific: Clear definition
Measurable: Quantifiable
Achievable: Realistic targets
Relevant: Aligned to goals
Time-bound: Defined period
```
### 3. Dashboard Hierarchy
```
βββ Executive Summary (1 page)
β βββ 4-6 headline KPIs
β βββ Trend indicators
β βββ Key alerts
βββ Department Views
β βββ Sales Dashboard
β βββ Marketing Dashboard
β βββ Operations Dashboard
β βββ Finance Dashboard
βββ Detailed Drilldowns
βββ Individual metrics
βββ Root cause analysis
```
## Common KPIs by Department
### Sales KPIs
```yaml
Revenue Metrics:
- Monthly Recurring Revenue (MRR)
- Annual Recurring Revenue (ARR)
- Average Revenue Per User (ARPU)
- Revenue Growth Rate
Pipeline Metrics:
- Sales Pipeline Value
- Win Rate
- Average Deal Size
- Sales Cycle Length
Activity Metrics:
- Calls/Emails per Rep
- Demos Scheduled
- Proposals Sent
- Close Rate
```
### Marketing KPIs
```yaml
Acquisition:
- Cost Per Acquisition (CPA)
- Customer Acquisition Cost (CAC)
- Lead Volume
- Marketing Qualified Leads (MQL)
Engagement:
- Website Traffic
- Conversion Rate
- Email Open/Click Rate
- Social Engagement
ROI:
- Marketing ROI
- Campaign Performance
- Channel Attribution
- CAC Payback Period
```
### Product KPIs
```yaml
Usage:
- Daily/Monthly Active Users (DAU/MAU)
- Session Duration
- Feature Adoption Rate
- Stickiness (DAU/MAU)
Quality:
- Net Promoter Score (NPS)
- Customer Satisfaction (CSAT)
- Bug/Issue Count
- Time to Resolution
Growth:
- User Growth Rate
- Activation Rate
- Retention Rate
- Churn Rate
```
### Finance KPIs
```yaml
Profitability:
- Gross Margin
- Net Profit Margin
- EBITDA
- Operating Margin
Liquidity:
- Current Ratio
- Quick Ratio
- Cash Flow
- Working Capital
Efficiency:
- Revenue per Employee
- Operating Expense Ratio
- Days Sales Outstanding
- Inventory Turnover
```
## Dashboard Layout Patterns
### Pattern 1: Executive Summary
```
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β EXECUTIVE DASHBOARD [Date Range βΌ] β
βββββββββββββββ¬ββββββββββββββ¬ββββββββββββββ¬ββββββββββββββββββ€
β REVENUE β PROFIT β CUSTOMERS β NPS SCORE β
β $2.4M β $450K β 12,450 β 72 β
β β² 12% β β² 8% β β² 15% β β² 5pts β
βββββββββββββββ΄ββββββββββββββ΄ββββββββββββββ΄ββββββββββββββββββ€
β β
β Revenue Trend β Revenue by Product β
β βββββββββββββββββββββββββ β ββββββββββββββββββββ β
β β /\ /\ β β β ββββββββ 45% β β
β β / \ / \ /\ β β β ββββββ 32% β β
β β / \/ \ / \ β β β ββββ 18% β β
β β / \/ \ β β β ββ 5% β β
β βββββββββββββββββββββββββ β ββββββββββββββββββββ β
β β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ€
β π΄ Alert: Churn rate exceeded threshold (>5%) β
β π‘ Warning: Support ticket volume 20% above average β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
```
### Pattern 2: SaaS Metrics Dashboard
```
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β SAAS METRICS Jan 2024 [Monthly βΌ] β
ββββββββββββββββββββββββ¬βββββββββββββββββββββββββββββββββββββββ€
β ββββββββββββββββββ β MRR GROWTH β
β β MRR β β ββββββββββββββββββββββββββββββββββ β
β β $125,000 β β β /ββ β β
β β β² 8% β β β /ββββ/ β β
β ββββββββββββββββββ β β /ββββ/ β β
β ββββββββββββββββββ β β /ββββ/ β β
β β ARR β β β /ββββ/ β β
β β $1,500,000 β β ββββββββββββββββββββββββββββββββββ β
β β β² 15% β β J F M A M J J A S O N D β
β ββββββββββββββββββ β β
ββββββββββββββββββββββββΌβββββββββββββββββββββββββββββββββββββββ€
β UNIT ECONOMICS β COHORT RETENTION β
β β β
β CAC: $450 β Month 1: ββββββββββββββββββββ 100% β
β LTV: $2,700 β Month 3: βββββββββββββββββ 85% β
β LTV/CAC: 6.0x β Month 6: ββββββββββββββββ 80% β
β β Month 12: ββββββββββββββ 72% β
β Payback: 4 months β β
ββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββββββββββ€
β CHURN ANALYSIS β
β ββββββββββββ¬βββββββββββ¬βββββββββββ¬βββββββββββββββββββββββ β
β β Gross β Net β Logo β Expansion β β
β β 4.2% β 1.8% β 3.1% β 2.4% β β
β ββββββββββββ΄βββββββββββ΄βββββββββββ΄βββββββββββββββββββββββ β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
```
### Pattern 3: Real-time Operations
```
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β OPERATIONS CENTER Live β Last: 10:42:15 β
ββββββββββββββββββββββββββββββ¬βββββββββββββββββββββββββββββββββ€
β SYSTEM HEALTH β SERVICE STATUS β
β ββββββββββββββββββββββββ β β
β β CPU MEM DISK β β β API Gateway Healthy β
β β 45% 72% 58% β β β User Service Healthy β
β β βββ ββββ βββ β β β Payment Service Degraded β
β β βββ ββββ βββ β β β Database Healthy β
β β βββ ββββ βββ β β β Cache Healthy β
β ββββββββββββββββββββββββ β β
ββββββββββββββββββββββββββββββΌβββββββββββββββββββββββββββββββββ€
β REQUEST THROUGHPUT β ERROR RATE β
β ββββββββββββββββββββββββ β ββββββββββββββββββββββββββββ β
β β βββββ
ββββββ
ββββββββ
β β β ββββββββββββββββββββ β β
β ββββββββββββββββββββββββ β ββββββββββββββββββββββββββββ β
β Current: 12,450 req/s β Current: 0.02% β
β Peak: 18,200 req/s β Threshold: 1.0% β
ββββββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββββ€
β RECENT ALERTS β
β 10:40 π‘ High latency on payment-service (p99 > 500ms) β
β 10:35 π’ Resolved: Database connection pool recovered β
β 10:22 π΄ Payment service circuit breaker tripped β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
```
## Implementation Patterns
### SQL for KPI Calculations
```sql
-- Monthly Recurring Revenue (MRR)
WITH mrr_calculation AS (
SELECT
DATE_TRUNC('month', billing_date) AS month,
SUM(
CASE subscription_interval
WHEN 'monthly' THEN amount
WHEN 'yearly' THEN amount / 12
WHEN 'quarterly' THEN amount / 3
END
) AS mrr
FROM subscriptions
WHERE status = 'active'
GROUP BY DATE_TRUNC('month', billing_date)
)
SELECT
month,
mrr,
LAG(mrr) OVER (ORDER BY month) AS prev_mrr,
(mrr - LAG(mrr) OVER (ORDER BY month)) / LAG(mrr) OVER (ORDER BY month) * 100 AS growth_pct
FROM mrr_calculation;
-- Cohort Retention
WITH cohorts AS (
SELECT
user_id,
DATE_TRUNC('month', created_at) AS cohort_month
FROM users
),
activity AS (
SELECT
user_id,
DATE_TRUNC('month', event_date) AS activity_month
FROM user_events
WHERE event_type = 'active_session'
)
SELECT
c.cohort_month,
EXTRACT(MONTH FROM age(a.activity_month, c.cohort_month)) AS months_since_signup,
COUNT(DISTINCT a.user_id) AS active_users,
COUNT(DISTINCT a.user_id)::FLOAT / COUNT(DISTINCT c.user_id) * 100 AS retention_rate
FROM cohorts c
LEFT JOIN activity a ON c.user_id = a.user_id
AND a.activity_month >= c.cohort_month
GROUP BY c.cohort_month, EXTRACT(MONTH FROM age(a.activity_month, c.cohort_month))
ORDER BY c.cohort_month, months_since_signup;
-- Customer Acquisition Cost (CAC)
SELECT
DATE_TRUNC('month', acquired_date) AS month,
SUM(marketing_spend) / NULLIF(COUNT(new_customers), 0) AS cac,
SUM(marketing_spend) AS total_spend,
COUNT(new_customers) AS customers_acquired
FROM (
SELECT
DATE_TRUNC('month', u.created_at) AS acquired_date,
u.id AS new_customers,
m.spend AS marketing_spend
FROM users u
JOIN marketing_spend m ON DATE_TRUNC('month', u.created_at) = m.month
WHERE u.source = 'marketing'
) acquisition
GROUP BY DATE_TRUNC('month', acquired_date);
```
### Python Dashboard Code (Streamlit)
```python
import streamlit as st
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
st.set_page_config(page_title="KPI Dashboard", layout="wide")
# Header with date filter
col1, col2 = st.columns([3, 1])
with col1:
st.title("Executive Dashboard")
with col2:
date_range = st.selectbox(
"Period",
["Last 7 Days", "Last 30 Days", "Last Quarter", "YTD"]
)
# KPI Cards
def metric_card(label, value, delta, prefix="", suffix=""):
delta_color = "green" if delta >= 0 else "red"
delta_arrow = "β²" if delta >= 0 else "βΌ"
st.metric(
label=label,
value=f"{prefix}{value:,.0f}{suffix}",
delta=f"{delta_arrow} {abs(delta):.1f}%"
)
col1, col2, col3, col4 = st.columns(4)
with col1:
metric_card("Revenue", 2400000, 12.5, prefix="$")
with col2:
metric_card("Customers", 12450, 15.2)
with col3:
metric_card("NPS Score", 72, 5.0)
with col4:
metric_card("Churn Rate", 4.2, -0.8, suffix="%")
# Charts
col1, col2 = st.columns(2)
with col1:
st.subheader("Revenue Trend")
revenue_data = pd.DataFrame({
'Month': pd.date_range('2024-01-01', periods=12, freq='M'),
'Revenue': [180000, 195000, 210000, 225000, 240000, 255000,
270000, 285000, 300000, 315000, 330000, 345000]
})
fig = px.line(revenue_data, x='Month', y='Revenue',
line_shape='spline', markers=True)
fig.update_layout(height=300)
st.plotly_chart(fig, use_container_width=True)
with col2:
st.subheader("Revenue by Product")
product_data = pd.DataFrame({
'Product': ['Enterprise', 'Professional', 'Starter', 'Other'],
'Revenue': [45, 32, 18, 5]
})
fig = px.pie(product_data, values='Revenue', names='Product',
hole=0.4)
fig.update_layout(height=300)
st.plotly_chart(fig, use_container_width=True)
# Cohort Heatmap
st.subheader("Cohort Retention")
cohort_data = pd.DataFrame({
'Cohort': ['Jan', 'Feb', 'Mar', 'Apr', 'May'],
'M0': [100, 100, 100, 100, 100],
'M1': [85, 87, 84, 86, 88],
'M2': [78, 80, 76, 79, None],
'M3': [72, 74, 70, None, None],
'M4': [68, 70, None, None, None],
})
fig = go.Figure(data=go.Heatmap(
z=cohort_data.iloc[:, 1:].values,
x=['M0', 'M1', 'M2', 'M3', 'M4'],
y=cohort_data['Cohort'],
colorscale='Blues',
text=cohort_data.iloc[:, 1:].values,
texttemplate='%{text}%',
textfont={"size": 12},
))
fig.update_layout(height=250)
st.plotly_chart(fig, use_container_width=True)
# Alerts Section
st.subheader("Alerts")
alerts = [
{"level": "error", "message": "Churn rate exceeded threshold (>5%)"},
{"level": "warning", "message": "Support ticket volume 20% above average"},
]
for alert in alerts:
if alert["level"] == "error":
st.error(f"π΄ {alert['message']}")
elif alert["level"] == "warning":
st.warning(f"π‘ {alert['message']}")
```
## Best Practices
### Do's
- **Limit to 5-7 KPIs** - Focus on what matters
- **Show context** - Comparisons, trends, targets
- **Use consistent colors** - Red=bad, green=good
- **Enable drilldown** - From summary to detail
- **Update appropriately** - Match metric frequency
### Don'ts
- **Don't show vanity metrics** - Focus on actionable data
- **Don't overcrowd** - White space aids comprehension
- **Don't use 3D charts** - They distort perception
- **Don't hide methodology** - Document calculations
- **Don't ignore mobile** - Ensure responsive design
## Resources
- [Stephen Few's Dashboard Design](https://www.perceptualedge.com/articles/visual_business_intelligence/rules_for_using_color.pdf)
- [Edward Tufte's Principles](https://www.edwardtufte.com/tufte/)
- [Google Data Studio Gallery](https://datastudio.google.com/gallery)
π0
ποΈ0
π€ Auto-discovered