What is a KPI

Technical Explanation

A Key Performance Indicator (KPI) is a measurable value that demonstrates how effectively a company is achieving key business objectives. Unlike general metrics, KPIs are specifically tied to strategic goals and drive actionable decisions.

KPI vs General Metric

Aspect	KPI	General Metric
Strategic Alignment	Tied to key objectives	May exist in isolation
Actionability	Drives decisions	Informational only
Quantity	Few (5-10 per area)	Many (can be dozens)
Monitoring Frequency	Daily/weekly	As needed
Thresholds	Has targets/goals	Just observed

What Makes a Good KPI

1. Specific — Clearly defined what is being measured
2. Measurable — Can be quantified with precision
3. Aligned — Tied to strategic business objectives
4. Actionable — When it changes, you know what to do
5. Time-bound — Measured over a defined period

Types of KPIs

Type	Description	Example
Lagging	Measures outcome of past actions	Revenue, Churn rate
Leading	Predicts future outcomes	Sign-ups, Demo requests
Input	Measures activities/effort	Calls made, Emails sent
Output	Measures results	Deals closed, Revenue

---

Code Examples

Using the CatCafe dataset:

-- KPIs for CatCafe business

-- 1. Revenue KPI (Lagging - outcome)
SELECT
    DATE_TRUNC('month', order_date) as month,
    SUM(total_amount) as monthly_revenue,
    SUM(total_amount) - LAG(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) as mom_change
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- 2. Customer Acquisition Cost (Input KPI)
WITH costs AS (
    SELECT
        DATE_TRUNC('month', campaign_date) as month,
        SUM(cost) as marketing_cost
    FROM marketing_spend
    GROUP BY DATE_TRUNC('month', campaign_date)
),
acquisitions AS (
    SELECT
        DATE_TRUNC('month', registration_date) as month,
        COUNT(*) as new_customers
    FROM customers
    GROUP BY DATE_TRUNC('month', registration_date)
)
SELECT
    c.month,
    costs.marketing_cost,
    acquisitions.new_customers,
    costs.marketing_cost / NULLIF(acquisitions.new_customers, 0) as cac
FROM costs c
JOIN acquisitions a ON c.month = a.month;

-- 3. Customer Lifetime Value (CLV) KPI
SELECT
    customer_id,
    COUNT(*) as total_orders,
    SUM(total_amount) as lifetime_value,
    AVG(total_amount) as avg_order_value,
    MAX(order_date) as last_order_date,
    DATE_PART('day', MAX(order_date) - MIN(order_date)) as customer_tenure_days
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 3
ORDER BY lifetime_value DESC;

-- 4. KPI Dashboard Query (for executives)
SELECT
    (
        SELECT COUNT(DISTINCT customer_id)
        FROM orders
        WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
    ) as active_customers_30d,
    (
        SELECT SUM(total_amount)
        FROM orders
        WHERE status = 'completed'
        AND order_date >= CURRENT_DATE - INTERVAL '30 days'
    ) as revenue_30d,
    (
        SELECT COUNT(DISTINCT customer_id)
        FROM customers
        WHERE registration_date >= CURRENT_DATE - INTERVAL '30 days'
    ) as new_customers_30d;

---

The Cat Analogy

Think of KPIs like cat health vitals:

General metrics = Random observations:

"The cat's whiskers are 6 inches long"
"The cat has 4 legs"
"Weight: 10 pounds"

Interesting facts, but do they tell you if the cat is healthy?

KPIs = The vitals that matter:

"Body temperature: 101.5°F (normal range: 100-102.5)"
"Heart rate: 140 bpm (elevated - possible stress)"
"Weight change: -0.5 lbs this week (concerning)"

The temp tells you "action needed: monitor or treat." The whisker length tells you nothing actionable.

Good Cat KPI: Vitals that, when they change, tell you exactly what to do. Bad Cat KPI: "Number of whiskers touched today" — not tied to health outcomes.

---

Common Pitfalls

Vanity KPIs

-- BAD KPI: "Total page views"
SELECT
    page_views,
    page_views as kpi_value  -- This doesn't drive action
FROM website_stats;

-- GOOD KPI: "Conversion rate from page to signup"
SELECT
    COUNT(DISTINCT user_id) FILTER (WHERE action = 'signup') * 100.0
        / NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE action = 'page_view'), 0)
    as conversion_rate
FROM user_actions;
-- Tied to business outcome, actionable

Too Many KPIs

-- WRONG: Tracking everything
SELECT
    total_orders,
    total_revenue,
    avg_order_value,
    unique_customers,
    returning_customers,
    cart_abandonment_rate,
    email_open_rate,
    email_click_rate,
    -- ... 50 more
FROM daily_metrics;
-- Too much noise! Which KPI actually matters today?

-- RIGHT: 5 KPIs max, clearly prioritized
SELECT
    daily_revenue,
    new_customer_count,
    returning_customer_rate,
    avg_order_value,
    customer_acquisition_cost
FROM executive_dashboard;

---

Exercises

Exercise 1

For CatCafe, identify 3 KPIs that would be "leading" indicators (predict future revenue) vs 3 "lagging" indicators (outcome of past actions).

Exercise 2

Write a query to calculate the "Average Order Value" KPI per customer segment (new vs returning).

Exercise 3

What makes a KPI "actionable"? Write an example of a non-actionable metric turned into an actionable KPI.

Exercise 4

Calculate CatCafe's month-over-month revenue growth as a KPI with trend direction.

Exercise 5

Design a "Cat Happiness Score" KPI for a cat cafe. What data would you need? What would trigger action?

---

Key Takeaways

• KPIs are metrics tied to strategic objectives that drive decisions
• Good KPIs are: Specific, Measurable, Aligned, Actionable, Time-bound
• KPIs should be few (5-10 per area), not dozens
• Leading KPIs predict future outcomes; Lagging KPIs measure past results
• Track KPIs over time to detect trends, not just point-in-time values
• Avoid vanity KPIs that look good but don't drive action