Business Intelligence Fundamentals

Metric Definition & Calculation

Business Metrics

-- Core business metrics

-- Revenue metrics SELECT DATE_TRUNC('month', order_date)::DATE as month, ROUND(SUM(amount), 2) as total_revenue, COUNT(DISTINCT order_id) as order_count, ROUND(SUM(amount) / COUNT(DISTINCT order_id), 2) as avg_order_value, COUNT(DISTINCT customer_id) as unique_customers, ROUND(SUM(amount) / COUNT(DISTINCT customer_id), 2) as revenue_per_customer FROM orders GROUP BY DATE_TRUNC('month', order_date) ORDER BY month DESC;

-- Customer metrics SELECT customer_id, COUNT(DISTINCT order_id) as lifetime_orders, ROUND(SUM(amount), 2) as lifetime_value, MIN(order_date) as first_order_date, MAX(order_date) as last_order_date, ROUND(DATEDIFF(DAY, MIN(order_date), MAX(order_date)) / NULLIF(COUNT(DISTINCT order_id) - 1, 0), 2) as avg_days_between_orders, ROUND(SUM(amount) / DATEDIFF(DAY, MIN(order_date), CURRENT_DATE), 4) as revenue_per_day FROM orders GROUP BY customer_id;

-- Product performance SELECT product_id, product_name, category, COUNT(DISTINCT order_id) as order_count, SUM(quantity) as units_sold, ROUND(SUM(revenue), 2) as total_revenue, ROUND(AVG(revenue), 2) as avg_order_value, ROUND(SUM(profit), 2) as total_profit, ROUND(100.0 * SUM(profit) / NULLIF(SUM(revenue), 0), 2) as profit_margin_pct FROM order_items oi JOIN products p ON oi.product_id = p.id GROUP BY product_id, product_name, category ORDER BY total_revenue DESC;

KPI Definitions

-- Key Performance Indicator calculations

-- Monthly Active Users (MAU) SELECT DATE_TRUNC('month', activity_date)::DATE as month, COUNT(DISTINCT user_id) as mau FROM user_activity GROUP BY DATE_TRUNC('month', activity_date);

-- Customer Acquisition Cost (CAC) SELECT DATE_TRUNC('month', acquired_date)::DATE as month, COUNT(DISTINCT customer_id) as new_customers, ROUND(SUM(marketing_spend) / COUNT(DISTINCT customer_id), 2) as cac FROM customers c JOIN marketing_spend m ON EXTRACT(YEAR FROM c.acquired_date) = EXTRACT(YEAR FROM m.spend_date) AND EXTRACT(MONTH FROM c.acquired_date) = EXTRACT(MONTH FROM m.spend_date) GROUP BY DATE_TRUNC('month', acquired_date);

-- Customer Retention Rate WITH monthly_activity AS ( SELECT DATE_TRUNC('month', activity_date)::DATE as month, customer_id FROM orders GROUP BY DATE_TRUNC('month', activity_date), customer_id ) SELECT current_month.month, COUNT(DISTINCT current_month.customer_id) as current_month_customers, COUNT(DISTINCT previous_month.customer_id) as retained_customers, ROUND(100.0 * COUNT(DISTINCT previous_month.customer_id) / COUNT(DISTINCT current_month.customer_id), 2) as retention_rate_pct FROM monthly_activity current_month LEFT JOIN monthly_activity previous_month ON current_month.customer_id = previous_month.customer_id AND current_month.month = previous_month.month + INTERVAL '1 month' GROUP BY current_month.month ORDER BY current_month.month;

-- Net Promoter Score (NPS) calculation SELECT department, COUNT(CASE WHEN nps_score >= 9 THEN 1 END) as promoters, COUNT(CASE WHEN nps_score >= 7 AND nps_score <= 8 THEN 1 END) as passives, COUNT(CASE WHEN nps_score <= 6 THEN 1 END) as detractors, COUNT() as total_responses, ROUND(100.0 * (COUNT(CASE WHEN nps_score >= 9 THEN 1 END) - COUNT(CASE WHEN nps_score <= 6 THEN 1 END)) / COUNT(), 1) as nps_score FROM customer_surveys GROUP BY department;

Dimensional Modeling for BI

Fact Table Grain Selection

-- Atomic grain (transaction-level) CREATE TABLE fact_sales_atomic ( transaction_id BIGINT PRIMARY KEY, date_id INT, customer_id INT, product_id INT, store_id INT, quantity INT, unit_price DECIMAL(10, 2), net_sales DECIMAL(12, 2), FOREIGN KEY (date_id) REFERENCES dim_date(date_id) );

-- Summary grain (aggregated for performance) CREATE TABLE fact_sales_summary ( summary_id BIGINT PRIMARY KEY, date_id INT, customer_segment VARCHAR(50), product_category VARCHAR(50), store_region VARCHAR(50), transaction_count INT, total_quantity INT, total_sales DECIMAL(15, 2), FOREIGN KEY (date_id) REFERENCES dim_date(date_id) );

Dashboard Query Optimization

-- Optimized for dashboard performance using pre-aggregations SELECT d.month_name, d.quarter, d.year, dpc.product_category, dcs.customer_segment, COUNT() as transaction_count, SUM(fss.total_quantity) as units_sold, ROUND(SUM(fss.total_sales), 2) as revenue, ROUND(SUM(fss.total_sales) / COUNT(), 2) as avg_transaction_value, ROUND(SUM(fss.total_sales) / NULLIF(COUNT(DISTINCT dcs.customer_id), 0), 2) as revenue_per_customer FROM fact_sales_summary fss JOIN dim_date d ON fss.date_id = d.date_id JOIN dim_product_category dpc ON fss.product_category = dpc.category_id JOIN dim_customer_segment dcs ON fss.customer_segment = dcs.segment_id WHERE d.year = EXTRACT(YEAR FROM CURRENT_DATE) GROUP BY d.month_name, d.quarter, d.year, dpc.product_category, dcs.customer_segment ORDER BY d.year DESC, d.quarter DESC, d.month_name DESC;

Trend & Variance Analysis

-- Year-over-year comparison SELECT EXTRACT(MONTH FROM order_date) as month, EXTRACT(YEAR FROM order_date) as year, ROUND(SUM(amount), 2) as monthly_revenue FROM orders GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date) ORDER BY year DESC, month;

-- Budget vs Actual variance SELECT department, EXTRACT(MONTH FROM report_date) as month, SUM(budgeted_amount) as budget, SUM(actual_amount) as actual, SUM(actual_amount) - SUM(budgeted_amount) as variance, ROUND(100.0 * (SUM(actual_amount) - SUM(budgeted_amount)) / NULLIF(SUM(budgeted_amount), 0), 2) as variance_pct FROM budget_actuals GROUP BY department, EXTRACT(MONTH FROM report_date) ORDER BY department, month DESC;

-- Cumulative variance analysis WITH monthly_budget AS ( SELECT department, EXTRACT(MONTH FROM report_date) as month, SUM(budgeted_amount) as budget, SUM(actual_amount) as actual FROM budget_actuals GROUP BY department, EXTRACT(MONTH FROM report_date) ) SELECT department, month, budget, actual, SUM(actual) OVER (PARTITION BY department ORDER BY month) as ytd_actual, SUM(budget) OVER (PARTITION BY department ORDER BY month) as ytd_budget, SUM(actual) OVER (PARTITION BY department ORDER BY month) - SUM(budget) OVER (PARTITION BY department ORDER BY month) as ytd_variance FROM monthly_budget ORDER BY department, month;

Advanced Analytics Calculations

-- Cohort lifetime value WITH user_cohorts AS ( SELECT DATE_TRUNC('month', customer_acquired_date)::DATE as cohort_month, customer_id, DATE_TRUNC('month', order_date)::DATE as order_month, amount FROM orders o JOIN customers c ON o.customer_id = c.id ) SELECT cohort_month, DATE_PART('month', order_month::timestamp - cohort_month::timestamp) / 1 as months_since_acquisition, COUNT(DISTINCT customer_id) as cohort_size, ROUND(SUM(amount), 2) as cohort_revenue FROM user_cohorts WHERE order_month >= cohort_month GROUP BY cohort_month, months_since_acquisition ORDER BY cohort_month, months_since_acquisition;

-- Customer segmentation with RFM analysis WITH rfm AS ( SELECT customer_id, MAX(order_date) as last_order_date, DATEDIFF(DAY, MAX(order_date), CURRENT_DATE) as recency, COUNT(DISTINCT order_id) as frequency, ROUND(SUM(amount), 2) as monetary, NTILE(4) OVER (ORDER BY DATEDIFF(DAY, MAX(order_date), CURRENT_DATE) DESC) as r_score, NTILE(4) OVER (ORDER BY COUNT(DISTINCT order_id)) as f_score, NTILE(4) OVER (ORDER BY SUM(amount)) as m_score FROM orders GROUP BY customer_id ) SELECT customer_id, CASE WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'Champions' WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal Customers' WHEN f_score >= 3 THEN 'At Risk' WHEN r_score = 4 THEN 'Lost' ELSE 'Other' END as segment, frequency, monetary, recency FROM rfm ORDER BY monetary DESC;

Best Practices for BI

✅ Use conformed dimensions across all fact tables ✅ Pre-aggregate data for dashboard performance ✅ Implement slowly changing dimensions appropriately ✅ Create metrics at atomic grain level ✅ Use views for metric consistency ✅ Document metric definitions and calculations ✅ Implement data quality checks ✅ Monitor query performance with EXPLAIN PLAN ✅ Use appropriate indexes for BI queries ✅ Implement incremental loads for fact tables