SQL Business Metrics

A trustworthy business metric makes its grain, numerator, denominator, and zero handling explicit.

What & Why

Business metrics often compare two aggregates from the same population. The aggregates do not have to be row counts: campaign email rates divide SUM(opens) or SUM(clicks) by SUM(recipients) within each campaign.

The calculation is only meaningful when the result grain and denominator are documented, and NULLIF protects groups whose denominator is zero.

See How It Works

BUSINESS QUESTION

Calculate campaign email open and click rates from total sends.

id	campaign_id	sent_at	recipients	opens	clicks	unsubscribes	bounces
201	1	2024-01-20 15:00:00+00	1200	540	180	9	24
202	2	2024-02-15 16:30:00+00	800	420	96	5	12
203	3	2024-03-18 13:00:00+00	1500	610	225	14	31
204	4	2024-04-08 14:00:00+00	0	0	0	0	0

EXAMPLE QUERY

SELECT
  campaign_id,
  SUM(recipients) AS recipients,
  SUM(opens) AS opens,
  SUM(clicks) AS clicks,
  ROUND(SUM(opens)::numeric / NULLIF(SUM(recipients), 0) * 100, 2) AS open_rate_pct,
  ROUND(SUM(clicks)::numeric / NULLIF(SUM(recipients), 0) * 100, 2) AS click_rate_pct
FROM marketing.email_sends
GROUP BY campaign_id
ORDER BY click_rate_pct DESC NULLS LAST, campaign_id;

RESULT — email metrics by campaign

campaign_id	recipients	opens	clicks	open_rate_pct	click_rate_pct
1	1200	540	180	45.00	15.00
3	1500	610	225	40.67	15.00
2	800	420	96	52.50	12.00
4	0	0	0	NULL	NULL

The rates use recipients from the same campaign group as their numerator.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice