Key Metrics Every Data Analyst Must Know

SELECT SUM(order_amount) AS total_revenue
FROM orders
WHERE order_date BETWEEN '2026-08-01' AND '2026-08-31'
  AND status != 'cancelled';

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month)          AS prev_month_revenue,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
          / LAG(revenue) OVER (ORDER BY month), 1) AS mom_growth_pct
FROM monthly_revenue;

SELECT
    YEAR(order_date)  AS yr,
    MONTH(order_date) AS mth,
    SUM(order_amount) AS revenue
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY yr, mth;
-- Compare rows at same month across consecutive years

SELECT
    ROUND(SUM(order_amount) / COUNT(order_id), 2) AS aov
FROM orders
WHERE order_date BETWEEN '2026-08-01' AND '2026-08-31';

-- Monthly churn: customers active last month but not this month
SELECT
    ROUND(100.0 * COUNT(DISTINCT last_month.customer_id)
          / NULLIF(COUNT(DISTINCT this_month.customer_id), 0), 1) AS churn_rate_pct
FROM (SELECT DISTINCT customer_id FROM orders
      WHERE DATE_FORMAT(order_date, '%Y-%m') = '2026-07') this_month
LEFT JOIN (SELECT DISTINCT customer_id FROM orders
           WHERE DATE_FORMAT(order_date, '%Y-%m') = '2026-08') next_month
    ON this_month.customer_id = next_month.customer_id
LEFT JOIN (SELECT DISTINCT customer_id FROM orders
           WHERE DATE_FORMAT(order_date, '%Y-%m') = '2026-07') last_month
    ON this_month.customer_id != next_month.customer_id;

SELECT
    customer_id,
    COUNT(order_id)                                       AS total_orders,
    ROUND(SUM(order_amount), 0)                           AS total_revenue,
    ROUND(AVG(order_amount), 0)                           AS avg_order_value,
    DATEDIFF(MAX(order_date), MIN(order_date)) / 30       AS active_months
FROM orders
GROUP BY customer_id
HAVING total_orders > 1
ORDER BY total_revenue DESC;

SELECT
    SUM(CASE WHEN score >= 9 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS promoter_pct,
    SUM(CASE WHEN score <= 6 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS detractor_pct,
    ROUND(
        SUM(CASE WHEN score >= 9 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) -
        SUM(CASE WHEN score <= 6 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1
    )                                                                AS nps
FROM nps_survey
WHERE survey_date BETWEEN '2026-08-01' AND '2026-08-31';

SELECT
    DATE(session_date)                               AS date,
    COUNT(DISTINCT session_id)                       AS total_sessions,
    COUNT(DISTINCT order_id)                         AS orders,
    ROUND(100.0 * COUNT(DISTINCT order_id)
          / COUNT(DISTINCT session_id), 2)           AS conversion_rate_pct
FROM web_sessions
LEFT JOIN orders USING (session_id)
GROUP BY DATE(session_date)
ORDER BY date;

-- CAC by marketing channel
SELECT
    channel,
    SUM(spend_inr)                                      AS total_spend,
    COUNT(DISTINCT c.customer_id)                       AS new_customers,
    ROUND(SUM(spend_inr) / NULLIF(COUNT(DISTINCT c.customer_id), 0), 0)
                                                        AS cac_inr
FROM marketing_spend ms
LEFT JOIN customers c
    ON ms.channel = c.acquisition_channel
    AND MONTH(c.signup_date) = MONTH(ms.spend_date)
GROUP BY channel
ORDER BY cac_inr ASC;

SELECT
    campaign_name,
    spend_inr,
    incremental_revenue,
    ROUND(100.0 * (incremental_revenue - spend_inr) / spend_inr, 1) AS roi_pct
FROM campaign_performance
ORDER BY roi_pct DESC;

SELECT
    carrier,
    COUNT(*)                                                          AS total_orders,
    SUM(CASE WHEN on_time = 1 AND qty_correct = 1 THEN 1 ELSE 0 END) AS otif_count,
    ROUND(100.0 *
          SUM(CASE WHEN on_time = 1 AND qty_correct = 1 THEN 1 ELSE 0 END)
          / COUNT(*), 1)                                              AS otif_pct
FROM deliveries
WHERE delivery_date BETWEEN '2026-08-01' AND '2026-08-31'
GROUP BY carrier
ORDER BY otif_pct ASC;

SELECT
    category,
    ROUND(AVG(stock_value_inr), 0)                       AS avg_inventory_value,
    ROUND(SUM(cogs_inr) / 30, 0)                         AS daily_cogs,
    ROUND(AVG(stock_value_inr) / (SUM(cogs_inr) / 30), 1) AS dio_days
FROM inventory_snapshot
WHERE snapshot_date BETWEEN '2026-08-01' AND '2026-08-31'
GROUP BY category
ORDER BY dio_days DESC;

What is a KPI in data analytics?

A KPI (Key Performance Indicator) is a quantifiable measure used to evaluate how effectively a business is achieving its objectives. In data analytics, KPIs are the specific metrics that appear on dashboards and reports — because they directly reflect business health. A good KPI is specific (measures one thing), timely (updated frequently enough to act on), actionable (if the number changes, someone can do something about it), and business-relevant (tied to a goal the organisation cares about). Examples: monthly revenue, customer churn rate, on-time delivery rate, conversion rate. Not everything is a KPI — a metric becomes a KPI when a team actively monitors it and makes decisions based on it.

What is the difference between a metric and a KPI?

A metric is any quantitative measurement — total website visits, total orders placed, average delivery time. A KPI is a metric that has been designated as critical to a specific business goal. All KPIs are metrics, but not all metrics are KPIs. For example, "number of pages viewed per session" is a metric. For a content site whose goal is engagement, it becomes a KPI. For an e-commerce site whose goal is transactions, it is just a supporting metric. The distinction matters in analytics because dashboards should highlight KPIs — the numbers stakeholders care about — not every available metric. Cluttered dashboards that show 40 metrics with no hierarchy are a common analyst mistake.

What is customer churn rate and how do you calculate it?

Customer churn rate is the percentage of customers who stop using a product or service in a given period. Formula: Churn Rate = (Customers Lost in Period / Customers at Start of Period) × 100. Example: if you had 10,000 customers at the start of July and 9,200 at the end of July (without counting new additions), churn = (800 / 10,000) × 100 = 8%. In Indian subscription businesses (OTT platforms, SaaS, telecom), churn is a primary KPI because acquiring a new customer costs 5–7x more than retaining an existing one. A churn rate above 5% monthly is typically a red flag that something is wrong with product value or customer experience.

What is Month-over-Month (MoM) growth and Year-over-Year (YoY) growth?

Month-over-Month (MoM) growth compares a metric in one month to the previous month. Formula: MoM Growth = ((This Month - Last Month) / Last Month) × 100. It shows short-term momentum but is sensitive to seasonality. Year-over-Year (YoY) growth compares the same month (or period) across two consecutive years. Formula: YoY Growth = ((This Year - Last Year) / Last Year) × 100. YoY removes seasonality — comparing August 2026 to August 2025 is more meaningful than comparing August 2026 to July 2026 for categories like electronics (where July has a different demand pattern than August). Indian financial analysis typically reports both — MoM for operational monitoring, YoY for strategic reviews.