Loading…
This lesson covers business reporting frameworks and performance analysis techniques for data-driven decision making.
Executive Summary
Detailed Analysis
Recommendations
Appendices
Daily Reports
Weekly Reports
Monthly Reports
Quarterly Reports
-- Generate comprehensive performance report data
WITH performance_metrics AS (
-- Calculate key performance indicators
SELECT
report_period,
business_unit,
-- Revenue metrics
SUM(revenue) as total_revenue,
SUM(revenue) - LAG(SUM(revenue)) OVER (PARTITION BY business_unit ORDER BY report_period) as revenue_change,
ROUND((SUM(revenue) - LAG(SUM(revenue)) OVER (PARTITION BY business_unit ORDER BY report_period)) /
LAG(SUM(revenue)) OVER (PARTITION BY business_unit ORDER BY report_period) * 100, 2) as revenue_growth_pct,
-- Customer metrics
COUNT(DISTINCT customer_id) as unique_customers,
AVG(customer_satisfaction_score) as avg_satisfaction,
-- Operational metrics
SUM(orders_processed) as total_orders,
AVG(processing_time_hours) as avg_processing_time,
-- Efficiency metrics
ROUND(SUM(revenue) / COUNT(DISTINCT employee_id), 2) as revenue_per_employee,
ROUND(SUM(orders_processed) / COUNT(DISTINCT employee_id), 2) as orders_per_employee
FROM daily_performance
WHERE report_period >= DATEADD(month, -3, CURRENT_DATE)
GROUP BY report_period, business_unit
),
performance_targets AS (
-- Compare against targets
SELECT
pm.report_period,
pm.business_unit,
pm.total_revenue,
pm.revenue_growth_pct,
pm.avg_satisfaction,
pm.avg_processing_time,
t.revenue_target,
t.satisfaction_target,
t.processing_time_target,
-- Performance vs target
ROUND((pm.total_revenue / t.revenue_target - 1) * 100, 2) as revenue_vs_target_pct,
pm.avg_satisfaction - t.satisfaction_target as satisfaction_vs_target,
pm.avg_processing_time - t.processing_time_target as processing_time_vs_target,
-- Performance rating
CASE
WHEN pm.total_revenue >= t.revenue_target * 1.1 AND pm.avg_satisfaction >= t.satisfaction_target THEN 'Excellent'
WHEN pm.total_revenue >= t.revenue_target AND pm.avg_satisfaction >= t.satisfaction_target * 0.95 THEN 'Good'
WHEN pm.total_revenue >= t.revenue_target * 0.9 AND pm.avg_satisfaction >= t.satisfaction_target * 0.9 THEN 'Meets Expectations'
ELSE 'Needs Improvement'
END as performance_rating
FROM performance_metrics pm
JOIN performance_targets t ON pm.business_unit = t.business_unit
AND EXTRACT(MONTH FROM pm.report_period) = EXTRACT(MONTH FROM t.target_month)
AND EXTRACT(YEAR FROM pm.report_period) = EXTRACT(YEAR FROM t.target_month)
)
SELECT
'Performance Report' as report_type,
report_period,
business_unit,
total_revenue,
revenue_growth_pct,
revenue_vs_target_pct,
avg_satisfaction,
satisfaction_vs_target,
avg_processing_time,
processing_time_vs_target,
performance_rating,
-- Trend analysis
CASE
WHEN revenue_growth_pct > 10 THEN 'Strong Growth'
WHEN revenue_growth_pct > 5 THEN 'Moderate Growth'
WHEN revenue_growth_pct > 0 THEN 'Slow Growth'
WHEN revenue_growth_pct > -5 THEN 'Stable'
ELSE 'Declining'
END as growth_trend,
-- Action recommendations
CASE
WHEN performance_rating = 'Excellent' THEN 'Maintain Current Strategy'
WHEN performance_rating = 'Good' THEN 'Optimize for Growth'
WHEN performance_rating = 'Meets Expectations' THEN 'Focus on Improvement Areas'
ELSE 'Immediate Action Required'
END as recommended_action
FROM performance_targets
ORDER BY report_period DESC, business_unit;
Data Quality Assurance
Visual Design Principles
Narrative Structure
Characteristics
Best Use Cases
Dashboard Components
Characteristics
Best Use Cases
Scorecard Components
-- Dashboard data (real-time operational metrics)
WITH dashboard_data AS (
SELECT
'Dashboard Metrics' as report_type,
CURRENT_TIMESTAMP as last_updated,
business_unit,
-- Real-time operational metrics
COUNT(CASE WHEN status = 'Active' THEN 1 END) as active_users,
COUNT(CASE WHEN status = 'Processing' THEN 1 END) as pending_orders,
ROUND(AVG(CASE WHEN response_time_ms IS NOT NULL THEN response_time_ms END), 2) as avg_response_time,
ROUND(SUM(CASE WHEN revenue_today IS NOT NULL THEN revenue_today END), 2) as today_revenue,
-- System health indicators
CASE
WHEN AVG(CASE WHEN response_time_ms IS NOT NULL THEN response_time_ms END) < 1000 THEN 'Healthy'
WHEN AVG(CASE WHEN response_time_ms IS NOT NULL THEN response_time_ms END) < 2000 THEN 'Warning'
ELSE 'Critical'
END as system_status,
-- Real-time alerts
COUNT(CASE WHEN alert_level = 'Critical' THEN 1 END) as critical_alerts,
COUNT(CASE WHEN alert_level = 'Warning' THEN 1 END) as warning_alerts
FROM operational_metrics
WHERE timestamp >= CURRENT_TIMESTAMP - INTERVAL '1 hour'
GROUP BY business_unit
),
-- Scorecard data (strategic performance evaluation)
scorecard_data AS (
SELECT
'Scorecard Metrics' as report_type,
evaluation_period,
business_unit,
-- Strategic objectives and measures
objective_category,
measure_name,
target_value,
actual_value,
ROUND((actual_value / target_value - 1) * 100, 2) as performance_variance_pct,
-- Performance rating
CASE
WHEN actual_value >= target_value * 1.1 THEN 'Exceeds'
WHEN actual_value >= target_value THEN 'Achieves'
WHEN actual_value >= target_value * 0.9 THEN 'Approaches'
ELSE 'Below'
END as performance_rating,
-- Weight for overall score
objective_weight,
measure_weight
FROM strategic_performance
WHERE evaluation_period = DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
)
SELECT
report_type,
business_unit,
last_updated,
evaluation_period,
-- Dashboard specific metrics
active_users,
pending_orders,
avg_response_time,
today_revenue,
system_status,
critical_alerts,
warning_alerts,
-- Scorecard specific metrics
objective_category,
measure_name,
target_value,
actual_value,
performance_variance_pct,
performance_rating,
-- Usage context
CASE
WHEN report_type = 'Dashboard Metrics' THEN 'Operational Monitoring'
WHEN report_type = 'Scorecard Metrics' THEN 'Strategic Evaluation'
END as usage_context,
-- Update frequency
CASE
WHEN report_type = 'Dashboard Metrics' THEN 'Real-time'
WHEN report_type = 'Scorecard Metrics' THEN 'Monthly'
END as update_frequency
FROM dashboard_data
UNION ALL
SELECT
report_type,
business_unit,
last_updated,
evaluation_period,
active_users,
pending_orders,
avg_response_time,
today_revenue,
system_status,
critical_alerts,
warning_alerts,
objective_category,
measure_name,
target_value,
actual_value,
performance_variance_pct,
performance_rating,
usage_context,
update_frequency
FROM scorecard_data
ORDER BY report_type, business_unit;
Use Dashboards When
Use Scorecards When
Hybrid Approaches
Time Series Analysis
Statistical Methods
Visual Trend Presentation
-- Comprehensive trend analysis for business metrics
WITH trend_data AS (
-- Calculate monthly trends with multiple analysis methods
SELECT
DATE_TRUNC('month', metric_date) as analysis_month,
business_unit,
metric_category,
metric_name,
metric_value,
-- Basic trend calculations
LAG(metric_value, 1) OVER (PARTITION BY business_unit, metric_name ORDER BY analysis_month) as previous_month,
LAG(metric_value, 12) OVER (PARTITION BY business_unit, metric_name ORDER BY analysis_month) as same_month_last_year,
-- Growth rates
ROUND((metric_value - LAG(metric_value, 1) OVER (PARTITION BY business_unit, metric_name ORDER BY analysis_month)) /
LAG(metric_value, 1) OVER (PARTITION BY business_unit, metric_name ORDER BY analysis_month) * 100, 2) as month_over_month_growth,
ROUND((metric_value - LAG(metric_value, 12) OVER (PARTITION BY business_unit, metric_name ORDER BY analysis_month)) /
LAG(metric_value, 12) OVER (PARTITION BY business_unit, metric_name ORDER BY analysis_month) * 100, 2) as year_over_year_growth,
-- Moving averages
AVG(metric_value) OVER (PARTITION BY business_unit, metric_name ORDER BY analysis_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as three_month_avg,
AVG(metric_value) OVER (PARTITION BY business_unit, metric_name ORDER BY analysis_month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) as twelve_month_avg
FROM business_metrics
WHERE metric_date >= DATEADD(year, -2, CURRENT_DATE)
),
trend_analysis AS (
-- Analyze trend patterns and significance
SELECT
analysis_month,
business_unit,
metric_category,
metric_name,
metric_value,
month_over_month_growth,
year_over_year_growth,
three_month_avg,
twelve_month_avg,
-- Trend classification
CASE
WHEN month_over_month_growth > 10 THEN 'Strong Growth'
WHEN month_over_month_growth > 5 THEN 'Moderate Growth'
WHEN month_over_month_growth > 0 THEN 'Slow Growth'
WHEN month_over_month_growth > -5 THEN 'Stable'
WHEN month_over_month_growth > -10 THEN 'Slow Decline'
ELSE 'Rapid Decline'
END as trend_direction,
-- Volatility assessment
ROUND(STDDEV(metric_value) OVER (PARTITION BY business_unit, metric_name ORDER BY analysis_month ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 2) as volatility_6month,
-- Performance vs moving average
ROUND((metric_value / three_month_avg - 1) * 100, 2) as vs_three_month_avg_pct,
ROUND((metric_value / twelve_month_avg - 1) * 100, 2) as vs_twelve_month_avg_pct,
-- Trend strength
CASE
WHEN ABS(month_over_month_growth) > 10 AND ABS(vs_three_month_avg_pct) > 5 THEN 'Strong'
WHEN ABS(month_over_month_growth) > 5 AND ABS(vs_three_month_avg_pct) > 2 THEN 'Moderate'
ELSE 'Weak'
END as trend_strength
FROM trend_data
WHERE previous_month IS NOT NULL
)
SELECT
'Trend Analysis' as analysis_type,
analysis_month,
business_unit,
metric_category,
metric_name,
metric_value,
month_over_month_growth,
year_over_year_growth,
trend_direction,
trend_strength,
volatility_6month,
-- Interpretation guidance
CASE
WHEN trend_direction = 'Strong Growth' AND trend_strength = 'Strong' THEN 'Excellent Performance - Accelerating Growth'
WHEN trend_direction = 'Moderate Growth' AND trend_strength = 'Moderate' THEN 'Good Performance - Steady Growth'
WHEN trend_direction = 'Stable' AND volatility_6month < 10 THEN 'Stable Performance - Low Volatility'
WHEN trend_direction = 'Slow Decline' AND trend_strength = 'Weak' THEN 'Monitor Closely - Minor Decline'
WHEN trend_direction = 'Rapid Decline' AND trend_strength = 'Strong' THEN 'Critical - Immediate Action Required'
ELSE 'Normal Variation - Continue Monitoring'
END as trend_interpretation,
-- Recommended actions
CASE
WHEN trend_direction = 'Strong Growth' AND trend_strength = 'Strong' THEN 'Scale Up - Invest in Growth Drivers'
WHEN trend_direction = 'Moderate Growth' AND trend_strength = 'Moderate' THEN 'Optimize - Fine-tune Strategy'
WHEN trend_direction = 'Stable' AND volatility_6month < 10 THEN 'Maintain - Current Strategy Working'
WHEN trend_direction = 'Slow Decline' AND trend_strength = 'Weak' THEN 'Investigate - Root Cause Analysis'
WHEN trend_direction = 'Rapid Decline' AND trend_strength = 'Strong' THEN 'Emergency - Immediate Intervention'
ELSE 'Monitor - Continue Observation'
END as recommended_action
FROM trend_analysis
ORDER BY analysis_month DESC, business_unit, metric_category;
1. Identify the Pattern
2. Assess Significance
3. Determine Causes
4. Predict Future Trends
Executive Audience
Operational Audience
Cross-Functional Audience
Simplify Complex Concepts
Tell a Story with Data
Visual Communication
-- Generate executive summary data for non-technical presentation
WITH executive_summary AS (
-- Calculate high-level business performance
SELECT
'Executive Summary' as report_type,
CURRENT_DATE as report_date,
business_unit,
-- Key business metrics (simplified for executives)
ROUND(SUM(revenue), 0) as total_revenue,
ROUND(SUM(revenue) / 1000000, 1) as revenue_millions,
ROUND(SUM(profit), 0) as total_profit,
ROUND(SUM(profit) / SUM(revenue) * 100, 1) as profit_margin_pct,
COUNT(DISTINCT customer_id) as total_customers,
AVG(customer_satisfaction) as satisfaction_score,
-- Performance indicators
CASE
WHEN SUM(revenue) >= revenue_target * 1.1 THEN 'Exceeding Target'
WHEN SUM(revenue) >= revenue_target THEN 'Meeting Target'
ELSE 'Below Target'
END as revenue_performance,
-- Trend indicators
CASE
WHEN revenue_growth_pct > 10 THEN 'Strong Growth'
WHEN revenue_growth_pct > 5 THEN 'Good Growth'
WHEN revenue_growth_pct > 0 THEN 'Modest Growth'
ELSE 'Declining'
END as growth_trend,
-- Risk assessment
CASE
WHEN satisfaction_score < 3.5 OR profit_margin_pct < 5 THEN 'High Risk'
WHEN satisfaction_score < 4.0 OR profit_margin_pct < 10 THEN 'Medium Risk'
ELSE 'Low Risk'
END as risk_level
FROM business_performance_summary
WHERE period = 'Current Quarter'
GROUP BY business_unit, revenue_target, revenue_growth_pct
),
key_insights AS (
-- Generate actionable insights
SELECT
business_unit,
-- Top 3 insights
'Revenue Performance' as insight_category,
CASE
WHEN revenue_performance = 'Exceeding Target' THEN 'Outstanding revenue growth exceeding expectations'
WHEN revenue_performance = 'Meeting Target' THEN 'Solid performance meeting revenue goals'
ELSE 'Revenue performance requires immediate attention'
END as key_insight,
CASE
WHEN revenue_performance = 'Exceeding Target' THEN 'Scale successful initiatives'
WHEN revenue_performance = 'Meeting Target' THEN 'Optimize for efficiency'
ELSE 'Implement recovery plan'
END as recommended_action,
1 as priority_rank
UNION ALL
SELECT
business_unit,
'Customer Satisfaction' as insight_category,
CASE
WHEN satisfaction_score >= 4.5 THEN 'Excellent customer satisfaction levels'
WHEN satisfaction_score >= 4.0 THEN 'Good customer satisfaction'
WHEN satisfaction_score >= 3.5 THEN 'Acceptable satisfaction with room for improvement'
ELSE 'Customer satisfaction requires urgent attention'
END as key_insight,
CASE
WHEN satisfaction_score >= 4.5 THEN 'Maintain current service levels'
WHEN satisfaction_score >= 4.0 THEN 'Enhance customer experience'
WHEN satisfaction_score >= 3.5 THEN 'Address customer pain points'
ELSE 'Immediate service improvement required'
END as recommended_action,
2 as priority_rank
UNION ALL
SELECT
business_unit,
'Profitability' as insight_category,
CASE
WHEN profit_margin_pct >= 15 THEN 'Excellent profitability'
WHEN profit_margin_pct >= 10 THEN 'Good profitability'
WHEN profit_margin_pct >= 5 THEN 'Acceptable profitability'
ELSE 'Profitability needs improvement'
END as key_insight,
CASE
WHEN profit_margin_pct >= 15 THEN 'Explore growth opportunities'
WHEN profit_margin_pct >= 10 THEN 'Optimize cost structure'
WHEN profit_margin_pct >= 5 THEN 'Improve operational efficiency'
ELSE 'Comprehensive cost review required'
END as recommended_action,
3 as priority_rank
FROM executive_summary
)
SELECT
es.report_type,
es.report_date,
es.business_unit,
es.revenue_millions,
es.profit_margin_pct,
es.satisfaction_score,
es.revenue_performance,
es.growth_trend,
es.risk_level,
ki.insight_category,
ki.key_insight,
ki.recommended_action,
-- Executive-friendly summary
CASE
WHEN es.revenue_performance = 'Exceeding Target' AND es.satisfaction_score >= 4.0 THEN 'Excellent Performance'
WHEN es.revenue_performance = 'Meeting Target' AND es.satisfaction_score >= 3.5 THEN 'Good Performance'
WHEN es.revenue_performance = 'Below Target' OR es.satisfaction_score < 3.5 THEN 'Needs Attention'
ELSE 'Mixed Performance'
END as overall_assessment,
-- Communication priority
CASE
WHEN es.risk_level = 'High Risk' THEN 'Immediate Discussion Required'
WHEN es.risk_level = 'Medium Risk' THEN 'Schedule Review Meeting'
ELSE 'Regular Update Sufficient'
END as communication_priority
FROM executive_summary es
JOIN key_insights ki ON es.business_unit = ki.business_unit
WHERE ki.priority_rank <= 3
ORDER BY es.business_unit, ki.priority_rank;
Structure Your Presentation
Use Business Language
Visual Design for Executives
Anticipate Common Questions
Prepare Supporting Details
Follow-Up Communication
In the final lesson, we'll work on a capstone project that integrates all the concepts learned throughout this course.