Design Effective KPI Dashboards

Provides comprehensive patterns for designing effective KPI dashboards that drive business decisions - a three-tier KPI framework, SMART metric criteria, dashboard hierarchy and layout patterns, department-specific KPI catalogs, and working SQL/Streamlit implementation code.

Use this skill when designing executive dashboards, selecting meaningful KPIs, building real-time monitoring displays, creating department-specific metrics views, improving existing dashboard layouts, or establishing metric governance.

The KPI framework spans three levels: Strategic (long-term goals, monthly/quarterly updates, executive audience), Tactical (department goals, weekly/monthly, manager audience), and Operational (day-to-day, real-time/daily, team audience). Every KPI should be SMART: Specific, Measurable, Achievable, Relevant, Time-bound.

Dashboard hierarchy runs from a one-page Executive Summary (4-6 headline KPIs, trend indicators, key alerts) through department views (Sales, Marketing, Operations, Finance) down to detailed drilldowns for individual metrics and root cause analysis.

Department KPI catalogs are cataloged in detail: Sales (MRR, ARR, ARPU, pipeline value, win rate, deal size, sales cycle length); Marketing (CPA, CAC, lead volume, MQLs, conversion rate, marketing ROI, channel attribution); Product (DAU/MAU, session duration, feature adoption, NPS, CSAT, retention/churn rate); and Finance (gross margin, EBITDA, current/quick ratio, cash flow, revenue per employee, DSO).

Three ASCII-mocked layout patterns illustrate structure: an Executive Summary with headline KPI cards, a revenue trend chart, a revenue-by-product breakdown, and a color-coded alert bar; a SaaS Metrics dashboard combining MRR/ARR cards, an MRR growth trend, unit economics (CAC, LTV, LTV/CAC ratio, payback period), a cohort retention table, and churn breakdown (gross/net/logo/expansion); and a real-time Operations Center with live system health gauges, per-service status indicators, request throughput and error-rate sparklines, and a timestamped alert feed.

Implementation code includes SQL for core KPI calculations - a window-function MRR growth query, a cohort retention query joining signup cohorts against monthly activity, and a CAC calculation joining marketing spend against new customers - plus a Streamlit dashboard example:

def metric_card(label, value, delta, prefix="", suffix=""):
    delta_arrow = "▲" if delta >= 0 else "▼"
    st.metric(label=label, value=f"{prefix}{value:,.0f}{suffix}",
              delta=f"{delta_arrow} {abs(delta):.1f}%")

wiring KPI metric cards, Plotly line/pie charts, a cohort retention heatmap, and a color-coded alerts section (st.error/st.warning).

Best practices: limit to 5-7 KPIs and focus on what matters, show context via comparisons/trends/targets, use consistent red-bad/green-good coloring, enable drilldown from summary to detail, and match update frequency to the metric. Anti-patterns: don't show vanity metrics, don't overcrowd (white space aids comprehension), don't use distorting 3D charts, don't hide the calculation methodology, and don't ignore mobile responsiveness.

Implementation examples use SQL (window functions, cohort joins) and Python's Streamlit with Plotly for charts and heatmaps. References Stephen Few's and Edward Tufte's dashboard design principles and the Google Data Studio gallery for inspiration.

Analysts, product managers, and engineers building executive, department, or real-time operations dashboards who want a proven KPI framework, department-specific metric catalogs, and working SQL/Streamlit code rather than starting dashboard design from a blank canvas.