Good KPI Selection

Technical Explanation

Selecting the right KPIs is critical—good KPIs drive actions, while bad KPIs create noise. A well-selected KPI connects directly to business outcomes and provides clear guidance for decisions.

KPI Selection Criteria

Criteria	Question	Good KPI	Bad KPI
Actionable	Can you act on it?	Conversion rate	Total signups
Measurable	Can you quantify it?	Revenue	Customer "happiness"
Aligns with goals	Does it matter?	CAC	Server uptime
Time-bound	Measured over period?	Monthly churn	Cumulative signups
Specific	Is it clear?	Email click rate	"Engagement"

The SMART KPI Framework

• Specific: Clear, unambiguous
• Measurable: Quantifiable
• Actionable: Change triggers response
• Relevant: Tied to goals
• Time-bound: Over a defined period

---

Code Examples

Using the CatCafe dataset:

-- GOOD KPIs: Actionable and measurable

-- KPI 1: Customer Acquisition Cost
WITH marketing_costs AS (
    SELECT
        DATE_TRUNC('month', campaign_date) as month,
        SUM(cost) as spend
    FROM marketing_spend
    GROUP BY DATE_TRUNC('month', campaign_date)
),
new_customers AS (
    SELECT
        DATE_TRUNC('month', registration_date) as month,
        COUNT(*) as new_customers
    FROM customers
    GROUP BY DATE_TRUNC('month', registration_date)
)
SELECT
    mc.month,
    mc.spend,
    nc.new_customers,
    mc.spend / NULLIF(nc.new_customers, 0) as cac
FROM marketing_costs mc
LEFT JOIN new_customers nc ON mc.month = nc.month;

-- KPI 2: Customer Lifetime Value
SELECT
    customer_id,
    COUNT(*) as orders,
    SUM(total_amount) as ltv,
    MAX(order_date) as last_order,
    DATE_PART('day', MAX(order_date) - MIN(order_date)) as customer_days
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 3
ORDER BY ltv DESC;

-- KPI 3: Conversion Rate by Channel
SELECT
    u.acquisition_channel,
    COUNT(DISTINCT u.id) as visitors,
    COUNT(DISTINCT CASE WHEN o.id IS NOT NULL THEN u.id END) as converters,
    COUNT(DISTINCT CASE WHEN o.id IS NOT NULL THEN u.id END) * 100.0
        / NULLIF(COUNT(DISTINCT u.id), 0) as conversion_rate
FROM users u
LEFT JOIN orders o ON u.id = o.customer_id
GROUP BY u.acquisition_channel;

-- BAD KPIs to Avoid
-- ❌ "Total users ever registered" (no time period)
SELECT COUNT(*) FROM users;  -- Vanity metric

-- ✅ "Monthly Active Users" (time-bound, actionable)
SELECT
    DATE_TRUNC('month', activity_date) as month,
    COUNT(DISTINCT user_id) as mau
FROM user_activity
GROUP BY DATE_TRUNC('month', activity_date);

-- ❌ "Number of page views" (vanity)
SELECT SUM(page_views) FROM daily_stats;  -- Doesn't drive action

-- ✅ "Pages per session" (actionable)
SELECT
    AVG(pages_per_session) as avg_pages,
    COUNT(DISTINCT session_id) as sessions
FROM session_data;

---

The Cat Analogy

Selecting KPIs for cat cafe success:

Bad KPIs:

• "Number of cat photos taken" — not tied to revenue
• "Hours the cafe was open" — doesn't measure success
• "Total cats in residence" — no target, no action

Good KPIs:

• "Weekly revenue per square foot" — ties space to money
• "Customer wait time" — directly affects satisfaction
• "Cat adoption rate" — ties to mission and revenue

Why it matters:

Bad KPI: "We had 500 visitors this month!"
→ So what? Did they buy anything? Were they happy?

Good KPI: "Conversion rate is 8%, up from 5% last month"
→ Time to invest more in whatever's working

---

Exercises

Exercise 1

Evaluate these as potential KPIs:

• Total revenue
• Customer satisfaction score
• Revenue per customer
• Number of emails sent
• Website uptime percentage

Exercise 2

Write a query to calculate the ratio of customer acquisition cost to customer lifetime value.

Exercise 3

What makes a KPI "actionable" vs just informative?

Exercise 4

Design 5 KPIs for a cat grooming service.

---

Key Takeaways

• Good KPIs are: Specific, Measurable, Actionable, Relevant, Time-bound
• KPIs should drive decisions, not just look impressive
• Connect KPIs to business outcomes (revenue, costs, retention)
• Limit to 5-10 KPIs maximum per dashboard
• Review and update KPIs as business goals evolve