Kpi Dashboard Design

1---
2name: kpi-dashboard-design
3description: Design effective KPI dashboards with metrics selection, visualization best practices, and real-time monitoring patterns. Use when building business dashboards, selecting metrics, or designing data visualization layouts.
4---
5 
6# KPI Dashboard Design
7 
8Comprehensive patterns for designing effective Key Performance Indicator (KPI) dashboards that drive business decisions.
9 
10## Do not use this skill when
11 
12- The task is unrelated to kpi dashboard design
13- You need a different domain or tool outside this scope
14 
15## Instructions
16 
17- Clarify goals, constraints, and required inputs.
18- Apply relevant best practices and validate outcomes.
19- Provide actionable steps and verification.
20- If detailed examples are required, open `resources/implementation-playbook.md`.
21 
22## Use this skill when
23 
24- Designing executive dashboards
25- Selecting meaningful KPIs
26- Building real-time monitoring displays
27- Creating department-specific metrics views
28- Improving existing dashboard layouts
29- Establishing metric governance
30 
31## Core Concepts
32 
33### 1. KPI Framework
34 
35| Level           | Focus            | Update Frequency  | Audience   |
36| --------------- | ---------------- | ----------------- | ---------- |
37| **Strategic**   | Long-term goals  | Monthly/Quarterly | Executives |
38| **Tactical**    | Department goals | Weekly/Monthly    | Managers   |
39| **Operational** | Day-to-day       | Real-time/Daily   | Teams      |
40 
41### 2. SMART KPIs
42 
43```
44Specific: Clear definition
45Measurable: Quantifiable
46Achievable: Realistic targets
47Relevant: Aligned to goals
48Time-bound: Defined period
49```
50 
51### 3. Dashboard Hierarchy
52 
53```
54├── Executive Summary (1 page)
55│   ├── 4-6 headline KPIs
56│   ├── Trend indicators
57│   └── Key alerts
58├── Department Views
59│   ├── Sales Dashboard
60│   ├── Marketing Dashboard
61│   ├── Operations Dashboard
62│   └── Finance Dashboard
63└── Detailed Drilldowns
64    ├── Individual metrics
65    └── Root cause analysis
66```
67 
68## Common KPIs by Department
69 
70### Sales KPIs
71 
72```yaml
73Revenue Metrics:
74  - Monthly Recurring Revenue (MRR)
75  - Annual Recurring Revenue (ARR)
76  - Average Revenue Per User (ARPU)
77  - Revenue Growth Rate
78 
79Pipeline Metrics:
80  - Sales Pipeline Value
81  - Win Rate
82  - Average Deal Size
83  - Sales Cycle Length
84 
85Activity Metrics:
86  - Calls/Emails per Rep
87  - Demos Scheduled
88  - Proposals Sent
89  - Close Rate
90```
91 
92### Marketing KPIs
93 
94```yaml
95Acquisition:
96  - Cost Per Acquisition (CPA)
97  - Customer Acquisition Cost (CAC)
98  - Lead Volume
99  - Marketing Qualified Leads (MQL)
100 
101Engagement:
102  - Website Traffic
103  - Conversion Rate
104  - Email Open/Click Rate
105  - Social Engagement
106 
107ROI:
108  - Marketing ROI
109  - Campaign Performance
110  - Channel Attribution
111  - CAC Payback Period
112```
113 
114### Product KPIs
115 
116```yaml
117Usage:
118  - Daily/Monthly Active Users (DAU/MAU)
119  - Session Duration
120  - Feature Adoption Rate
121  - Stickiness (DAU/MAU)
122 
123Quality:
124  - Net Promoter Score (NPS)
125  - Customer Satisfaction (CSAT)
126  - Bug/Issue Count
127  - Time to Resolution
128 
129Growth:
130  - User Growth Rate
131  - Activation Rate
132  - Retention Rate
133  - Churn Rate
134```
135 
136### Finance KPIs
137 
138```yaml
139Profitability:
140  - Gross Margin
141  - Net Profit Margin
142  - EBITDA
143  - Operating Margin
144 
145Liquidity:
146  - Current Ratio
147  - Quick Ratio
148  - Cash Flow
149  - Working Capital
150 
151Efficiency:
152  - Revenue per Employee
153  - Operating Expense Ratio
154  - Days Sales Outstanding
155  - Inventory Turnover
156```
157 
158## Dashboard Layout Patterns
159 
160### Pattern 1: Executive Summary
161 
162```
163┌─────────────────────────────────────────────────────────────┐
164│  EXECUTIVE DASHBOARD                        [Date Range ▼]  │
165├─────────────┬─────────────┬─────────────┬─────────────────┤
166│   REVENUE   │   PROFIT    │  CUSTOMERS  │    NPS SCORE    │
167│   $2.4M     │    $450K    │    12,450   │       72        │
168│   ▲ 12%     │    ▲ 8%     │    ▲ 15%    │     ▲ 5pts     │
169├─────────────┴─────────────┴─────────────┴─────────────────┤
170│                                                             │
171│  Revenue Trend                    │  Revenue by Product     │
172│  ┌───────────────────────┐       │  ┌──────────────────┐   │
173│  │    /\    /\          │       │  │ ████████ 45%     │   │
174│  │   /  \  /  \    /\   │       │  │ ██████   32%     │   │
175│  │  /    \/    \  /  \  │       │  │ ████     18%     │   │
176│  │ /            \/    \ │       │  │ ██        5%     │   │
177│  └───────────────────────┘       │  └──────────────────┘   │
178│                                                             │
179├─────────────────────────────────────────────────────────────┤
180│  🔴 Alert: Churn rate exceeded threshold (>5%)              │
181│  🟡 Warning: Support ticket volume 20% above average        │
182└─────────────────────────────────────────────────────────────┘
183```
184 
185### Pattern 2: SaaS Metrics Dashboard
186 
187```
188┌─────────────────────────────────────────────────────────────┐
189│  SAAS METRICS                     Jan 2024  [Monthly ▼]     │
190├──────────────────────┬──────────────────────────────────────┤
191│  ┌────────────────┐  │  MRR GROWTH                          │
192│  │      MRR       │  │  ┌────────────────────────────────┐  │
193│  │    $125,000    │  │  │                          /──   │  │
194│  │     ▲ 8%       │  │  │                    /────/      │  │
195│  └────────────────┘  │  │              /────/            │  │
196│  ┌────────────────┐  │  │        /────/                  │  │
197│  │      ARR       │  │  │   /────/                       │  │
198│  │   $1,500,000   │  │  └────────────────────────────────┘  │
199│  │     ▲ 15%      │  │  J  F  M  A  M  J  J  A  S  O  N  D  │
200│  └────────────────┘  │                                      │
201├──────────────────────┼──────────────────────────────────────┤
202│  UNIT ECONOMICS      │  COHORT RETENTION                    │
203│                      │                                      │
204│  CAC:     $450       │  Month 1: ████████████████████ 100%  │
205│  LTV:     $2,700     │  Month 3: █████████████████    85%   │
206│  LTV/CAC: 6.0x       │  Month 6: ████████████████     80%   │
207│                      │  Month 12: ██████████████      72%   │
208│  Payback: 4 months   │                                      │
209├──────────────────────┴──────────────────────────────────────┤
210│  CHURN ANALYSIS                                             │
211│  ┌──────────┬──────────┬──────────┬──────────────────────┐ │
212│  │ Gross    │ Net      │ Logo     │ Expansion            │ │
213│  │ 4.2%     │ 1.8%     │ 3.1%     │ 2.4%                 │ │
214│  └──────────┴──────────┴──────────┴──────────────────────┘ │
215└─────────────────────────────────────────────────────────────┘
216```
217 
218### Pattern 3: Real-time Operations
219 
220```
221┌─────────────────────────────────────────────────────────────┐
222│  OPERATIONS CENTER                    Live ● Last: 10:42:15 │
223├────────────────────────────┬────────────────────────────────┤
224│  SYSTEM HEALTH             │  SERVICE STATUS                │
225│  ┌──────────────────────┐  │                                │
226│  │   CPU    MEM    DISK │  │  ● API Gateway      Healthy    │
227│  │   45%    72%    58%  │  │  ● User Service     Healthy    │
228│  │   ███    ████   ███  │  │  ● Payment Service  Degraded   │
229│  │   ███    ████   ███  │  │  ● Database         Healthy    │
230│  │   ███    ████   ███  │  │  ● Cache            Healthy    │
231│  └──────────────────────┘  │                                │
232├────────────────────────────┼────────────────────────────────┤
233│  REQUEST THROUGHPUT        │  ERROR RATE                    │
234│  ┌──────────────────────┐  │  ┌──────────────────────────┐  │
235│  │ ▁▂▃▄▅▆▇█▇▆▅▄▃▂▁▂▃▄▅ │  │  │ ▁▁▁▁▁▂▁▁▁▁▁▁▁▁▁▁▁▁▁▁  │  │
236│  └──────────────────────┘  │  └──────────────────────────┘  │
237│  Current: 12,450 req/s     │  Current: 0.02%                │
238│  Peak: 18,200 req/s        │  Threshold: 1.0%               │
239├────────────────────────────┴────────────────────────────────┤
240│  RECENT ALERTS                                              │
241│  10:40  🟡 High latency on payment-service (p99 > 500ms)    │
242│  10:35  🟢 Resolved: Database connection pool recovered     │
243│  10:22  🔴 Payment service circuit breaker tripped          │
244└─────────────────────────────────────────────────────────────┘
245```
246 
247## Implementation Patterns
248 
249### SQL for KPI Calculations
250 
251```sql
252-- Monthly Recurring Revenue (MRR)
253WITH mrr_calculation AS (
254    SELECT
255        DATE_TRUNC('month', billing_date) AS month,
256        SUM(
257            CASE subscription_interval
258                WHEN 'monthly' THEN amount
259                WHEN 'yearly' THEN amount / 12
260                WHEN 'quarterly' THEN amount / 3
261            END
262        ) AS mrr
263    FROM subscriptions
264    WHERE status = 'active'
265    GROUP BY DATE_TRUNC('month', billing_date)
266)
267SELECT
268    month,
269    mrr,
270    LAG(mrr) OVER (ORDER BY month) AS prev_mrr,
271    (mrr - LAG(mrr) OVER (ORDER BY month)) / LAG(mrr) OVER (ORDER BY month) * 100 AS growth_pct
272FROM mrr_calculation;
273 
274-- Cohort Retention
275WITH cohorts AS (
276    SELECT
277        user_id,
278        DATE_TRUNC('month', created_at) AS cohort_month
279    FROM users
280),
281activity AS (
282    SELECT
283        user_id,
284        DATE_TRUNC('month', event_date) AS activity_month
285    FROM user_events
286    WHERE event_type = 'active_session'
287)
288SELECT
289    c.cohort_month,
290    EXTRACT(MONTH FROM age(a.activity_month, c.cohort_month)) AS months_since_signup,
291    COUNT(DISTINCT a.user_id) AS active_users,
292    COUNT(DISTINCT a.user_id)::FLOAT / COUNT(DISTINCT c.user_id) * 100 AS retention_rate
293FROM cohorts c
294LEFT JOIN activity a ON c.user_id = a.user_id
295    AND a.activity_month >= c.cohort_month
296GROUP BY c.cohort_month, EXTRACT(MONTH FROM age(a.activity_month, c.cohort_month))
297ORDER BY c.cohort_month, months_since_signup;
298 
299-- Customer Acquisition Cost (CAC)
300SELECT
301    DATE_TRUNC('month', acquired_date) AS month,
302    SUM(marketing_spend) / NULLIF(COUNT(new_customers), 0) AS cac,
303    SUM(marketing_spend) AS total_spend,
304    COUNT(new_customers) AS customers_acquired
305FROM (
306    SELECT
307        DATE_TRUNC('month', u.created_at) AS acquired_date,
308        u.id AS new_customers,
309        m.spend AS marketing_spend
310    FROM users u
311    JOIN marketing_spend m ON DATE_TRUNC('month', u.created_at) = m.month
312    WHERE u.source = 'marketing'
313) acquisition
314GROUP BY DATE_TRUNC('month', acquired_date);
315```
316 
317### Python Dashboard Code (Streamlit)
318 
319```python
320import streamlit as st
321import pandas as pd
322import plotly.express as px
323import plotly.graph_objects as go
324 
325st.set_page_config(page_title="KPI Dashboard", layout="wide")
326 
327# Header with date filter
328col1, col2 = st.columns([3, 1])
329with col1:
330    st.title("Executive Dashboard")
331with col2:
332    date_range = st.selectbox(
333        "Period",
334        ["Last 7 Days", "Last 30 Days", "Last Quarter", "YTD"]
335    )
336 
337# KPI Cards
338def metric_card(label, value, delta, prefix="", suffix=""):
339    delta_color = "green" if delta >= 0 else "red"
340    delta_arrow = "▲" if delta >= 0 else "▼"
341    st.metric(
342        label=label,
343        value=f"{prefix}{value:,.0f}{suffix}",
344        delta=f"{delta_arrow} {abs(delta):.1f}%"
345    )
346 
347col1, col2, col3, col4 = st.columns(4)
348with col1:
349    metric_card("Revenue", 2400000, 12.5, prefix="$")
350with col2:
351    metric_card("Customers", 12450, 15.2)
352with col3:
353    metric_card("NPS Score", 72, 5.0)
354with col4:
355    metric_card("Churn Rate", 4.2, -0.8, suffix="%")
356 
357# Charts
358col1, col2 = st.columns(2)
359 
360with col1:
361    st.subheader("Revenue Trend")
362    revenue_data = pd.DataFrame({
363        'Month': pd.date_range('2024-01-01', periods=12, freq='M'),
364        'Revenue': [180000, 195000, 210000, 225000, 240000, 255000,
365                    270000, 285000, 300000, 315000, 330000, 345000]
366    })
367    fig = px.line(revenue_data, x='Month', y='Revenue',
368                  line_shape='spline', markers=True)
369    fig.update_layout(height=300)
370    st.plotly_chart(fig, use_container_width=True)
371 
372with col2:
373    st.subheader("Revenue by Product")
374    product_data = pd.DataFrame({
375        'Product': ['Enterprise', 'Professional', 'Starter', 'Other'],
376        'Revenue': [45, 32, 18, 5]
377    })
378    fig = px.pie(product_data, values='Revenue', names='Product',
379                 hole=0.4)
380    fig.update_layout(height=300)
381    st.plotly_chart(fig, use_container_width=True)
382 
383# Cohort Heatmap
384st.subheader("Cohort Retention")
385cohort_data = pd.DataFrame({
386    'Cohort': ['Jan', 'Feb', 'Mar', 'Apr', 'May'],
387    'M0': [100, 100, 100, 100, 100],
388    'M1': [85, 87, 84, 86, 88],
389    'M2': [78, 80, 76, 79, None],
390    'M3': [72, 74, 70, None, None],
391    'M4': [68, 70, None, None, None],
392})
393fig = go.Figure(data=go.Heatmap(
394    z=cohort_data.iloc[:, 1:].values,
395    x=['M0', 'M1', 'M2', 'M3', 'M4'],
396    y=cohort_data['Cohort'],
397    colorscale='Blues',
398    text=cohort_data.iloc[:, 1:].values,
399    texttemplate='%{text}%',
400    textfont={"size": 12},
401))
402fig.update_layout(height=250)
403st.plotly_chart(fig, use_container_width=True)
404 
405# Alerts Section
406st.subheader("Alerts")
407alerts = [
408    {"level": "error", "message": "Churn rate exceeded threshold (>5%)"},
409    {"level": "warning", "message": "Support ticket volume 20% above average"},
410]
411for alert in alerts:
412    if alert["level"] == "error":
413        st.error(f"🔴 {alert['message']}")
414    elif alert["level"] == "warning":
415        st.warning(f"🟡 {alert['message']}")
416```
417 
418## Best Practices
419 
420### Do's
421 
422- **Limit to 5-7 KPIs** - Focus on what matters
423- **Show context** - Comparisons, trends, targets
424- **Use consistent colors** - Red=bad, green=good
425- **Enable drilldown** - From summary to detail
426- **Update appropriately** - Match metric frequency
427 
428### Don'ts
429 
430- **Don't show vanity metrics** - Focus on actionable data
431- **Don't overcrowd** - White space aids comprehension
432- **Don't use 3D charts** - They distort perception
433- **Don't hide methodology** - Document calculations
434- **Don't ignore mobile** - Ensure responsive design
435 
436## Resources
437 
438- [Stephen Few's Dashboard Design](https://www.perceptualedge.com/articles/visual_business_intelligence/rules_for_using_color.pdf)
439- [Edward Tufte's Principles](https://www.edwardtufte.com/tufte/)
440- [Google Data Studio Gallery](https://datastudio.google.com/gallery)
441