references/details.md GitHub Copy Downloadkpi-dashboard-design — detailed worked examples
Common KPIs by Department
Sales KPIs
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
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
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
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.4 M │ $450 K │ 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
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;
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;
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)
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" )
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" ]
)
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:,.0 f} {suffix} " ,
delta=f"{delta_arrow} {abs (delta):.1 f} %"
)
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="%" )
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 )
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 )
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' ]} " )