Business KPIs in SQL: Real Metrics

The Strategy Meeting

I was invited to a product meeting. The team was discussing metrics: *"What's our conversion rate? What's our ARPU?"* I realized that the raw data I was pulling wasn't "Intelligence" yet. It needed to be transformed into Key Performance Indicators (KPIs).

The Quest: The Ratio

A KPI is often a **ratio** or **percentage**. You can't just `SUM()` or `COUNT()` to get it—you have to combine them.

The Implementation: Calculating Real Value

Conversion Rate (Signups that became Customers)

SELECT

COUNT(CASE WHEN status = 'purchased' THEN 1 END) * 100.0 / COUNT(*) AS conversion_rate

FROM users;

ARPU (Average Revenue Per User)

SELECT

SUM(order_amount) / COUNT(DISTINCT customer_id) AS arpu

FROM orders;

The Power of `CASE WHEN`

The `CASE` statement lets شما create "Conditional Counting." It's how we calculate success rates, failure rates, and other complex metrics.

-- How many orders were delivered late?

SELECT

COUNT(CASE WHEN actual_delivery > expected_delivery THEN 1 END) AS late_deliveries,

COUNT(*) AS total_deliveries

FROM shipments;

The "Oops" Moment

I once tried to divide two integers and got `0` as the result. SQL was doing integer division and rounding down.

**Pro Tip**: When calculating percentages, multiply one of the numbers by `100.0` (a decimal) to force SQL to use decimal math. Otherwise, `10 / 100` might return `0` instead of `0.1`.

The Victory

The product team saw a clear "Conversion Rate: 4.5%" on my single slide. No charts, no graphs—just one number that told them the story of user behavior. Calculating KPIs is what makes a basic data analyst into a strategic partner.

Your Task for Today

Calculate a simple percentage metric using `CASE WHEN`. For example, what percentage of orders are in 'Shipped' status?

*Day 22: GROUP BY Performance Secrets.*