Thinking in Metrics & Grain
Expert · 20 min read · ▶ live playground · ✦ checkpoint
Metrics go wrong when you count the wrong thing. In analytics SQL, grain means what one row represents, and it decides whether your metric is useful or quietly wrong.
A café app gives you two honest tables: one row per user, and one row per event. Both are true. They answer different questions.
Start With The Grain
Before you write COUNT, write the sentence: "one row means ___." A receipt line, a whole receipt, and a customer are all real café facts; mixing them is how a dashboard lies without throwing an error.
Here is the size of this lesson's analytics seed, computed by the database:
SELECT
(SELECT count(*) FROM cafe_users) AS users,
(SELECT count(*) FROM cafe_app_events) AS events,
(SELECT count(*) FROM cafe_app_events WHERE event_type = 'purchase') AS purchases; users | events | purchases
-------+--------+-----------
12 | 97 | 16
(1 row)Now look at the event table's first rows:
SELECT event_id,
user_id,
event_type,
event_ts,
branch,
order_id,
purchase_amount
FROM cafe_app_events
ORDER BY event_id
LIMIT 8; event_id | user_id | event_type | event_ts | branch | order_id | purchase_amount
----------+---------+-------------+---------------------+--------+----------+-----------------
1 | 101 | signup | 2026-01-03 09:12:00 | center | NULL | NULL
2 | 101 | app_open | 2026-01-03 09:20:00 | center | NULL | NULL
3 | 101 | menu_view | 2026-01-03 09:21:00 | center | NULL | NULL
4 | 101 | add_to_cart | 2026-01-03 09:24:00 | center | NULL | NULL
5 | 101 | purchase | 2026-01-03 09:27:00 | center | 9101 | 14.50
6 | 101 | app_open | 2026-01-17 08:35:00 | center | NULL | NULL
7 | 101 | menu_view | 2026-01-17 08:36:00 | center | NULL | NULL
8 | 101 | purchase | 2026-01-17 08:42:00 | center | 9102 | 8.75
(8 rows)The grain of cafe_app_events is one app event. User 101 appears many times because Ava opened the app, viewed the menu, added an item, and bought more than once. Count event rows when the question is about events. Do not count them when the sentence on the report says "users."
The Wrong Denominator Looks Plausible
Here is a classic analytics bug. The query joins users to events, groups by branch, and calls the result a purchase rate:
SELECT u.home_branch,
count(*) AS joined_rows,
count(*) FILTER (WHERE e.event_type = 'purchase') AS purchase_events,
round(
100.0 * count(*) FILTER (WHERE e.event_type = 'purchase') / count(*),
1
) AS purchase_rate_pct
FROM cafe_users u
JOIN cafe_app_events e ON e.user_id = u.user_id
GROUP BY u.home_branch
ORDER BY u.home_branch; home_branch | joined_rows | purchase_events | purchase_rate_pct
-------------+-------------+-----------------+-------------------
campus | 27 | 5 | 18.5
center | 41 | 8 | 19.5
harbor | 29 | 3 | 10.3
(3 rows)Nothing errored. The footer looks tidy. But joined_rows is event rows, not signed-up users. The denominator is "all app events after the join," so the percent answers "what share of event rows are purchases?" That might be a metric, but it is not a user purchase rate.
Metrics Need Dimensions
A metric is a numeric answer you compute, such as users, revenue, or purchase rate. A dimension is a category or time value you slice by, such as branch, channel, tier, or month. A metric is the coffee total; a dimension is the label on the pile you put it in.
Fix the rate by first rolling every user to one row:
WITH user_rollup AS (
SELECT u.user_id,
u.home_branch,
count(*) FILTER (WHERE e.event_type = 'purchase') > 0 AS made_purchase,
count(*) FILTER (WHERE e.event_type = 'purchase') AS purchase_events,
sum(e.purchase_amount) AS revenue
FROM cafe_users u
LEFT JOIN cafe_app_events e ON e.user_id = u.user_id
GROUP BY u.user_id, u.home_branch
)
SELECT ur.home_branch,
count(*) AS signed_up_users,
count(*) FILTER (WHERE ur.made_purchase) AS purchasing_users,
round(
100.0 * count(*) FILTER (WHERE ur.made_purchase) / NULLIF(count(*), 0),
1
) AS purchase_rate_pct,
COALESCE(sum(ur.revenue), 0) AS revenue
FROM user_rollup ur
GROUP BY ur.home_branch
ORDER BY ur.home_branch; home_branch | signed_up_users | purchasing_users | purchase_rate_pct | revenue
-------------+-----------------+------------------+-------------------+---------
campus | 4 | 3 | 75.0 | 65.40
center | 4 | 3 | 75.0 | 114.60
harbor | 4 | 2 | 50.0 | 38.40
(3 rows)Read the CTE name out loud: user_rollup. It changes event grain into user grain. Then GROUP BY folds those user rows into branch rows. The final output grain is one branch.
Ratios Have Denominators
A rate is a ratio with a story: numerator divided by denominator. The numerator is the thing that happened; the denominator is the set of chances it had to happen.
For March signups, compute purchase events per app open by acquisition channel:
WITH march_channel_events AS (
SELECT u.acquisition_channel,
count(*) FILTER (WHERE e.event_type = 'app_open') AS app_opens,
count(*) FILTER (WHERE e.event_type = 'purchase') AS purchase_events
FROM cafe_users u
LEFT JOIN cafe_app_events e ON e.user_id = u.user_id
WHERE u.signup_ts >= TIMESTAMP '2026-03-01'
AND u.signup_ts < TIMESTAMP '2026-04-01'
GROUP BY u.acquisition_channel
)
SELECT mce.acquisition_channel,
mce.app_opens,
mce.purchase_events,
round(
100.0 * mce.purchase_events / NULLIF(mce.app_opens, 0),
1
) AS purchase_events_per_open_pct
FROM march_channel_events mce
ORDER BY mce.acquisition_channel; acquisition_channel | app_opens | purchase_events | purchase_events_per_open_pct
---------------------+-----------+-----------------+------------------------------
email | 1 | 0 | 0.0
organic | 0 | 0 | NULL
paid_search | 2 | 1 | 50.0
referral | 1 | 0 | 0.0
(4 rows)NULL-safe division means using NULLIF(denominator, 0) so a zero denominator becomes NULL instead of crashing the query. Organic had zero app opens, so the rate is not zero. It is undefined. Display it as "n/a" later if the dashboard wants that, but keep the SQL honest.
Reconcile Before You Trust
Reconciliation is proving that two ways of totaling the same facts agree. It is the cash-drawer count before you believe the register tape.
Here, the branch report should add back to the raw purchase events:
WITH user_rollup AS (
SELECT u.user_id,
u.home_branch,
count(*) FILTER (WHERE e.event_type = 'purchase') AS purchase_events,
sum(e.purchase_amount) AS revenue
FROM cafe_users u
LEFT JOIN cafe_app_events e ON e.user_id = u.user_id
GROUP BY u.user_id, u.home_branch
),
branch_report AS (
SELECT ur.home_branch,
sum(ur.purchase_events) AS purchase_events,
sum(ur.revenue) AS revenue
FROM user_rollup ur
GROUP BY ur.home_branch
),
checks AS (
SELECT 1 AS sort_order,
'raw purchase events' AS check_name,
count(*) AS purchase_events,
sum(e.purchase_amount) AS revenue
FROM cafe_app_events e
WHERE e.event_type = 'purchase'
UNION ALL
SELECT 2 AS sort_order,
'summed branch report' AS check_name,
sum(br.purchase_events) AS purchase_events,
sum(br.revenue) AS revenue
FROM branch_report br
)
SELECT c.check_name,
c.purchase_events,
c.revenue
FROM checks c
ORDER BY c.sort_order; check_name | purchase_events | revenue
----------------------+-----------------+---------
raw purchase events | 16 | 218.40
summed branch report | 16 | 218.40
(2 rows)This check does not prove every metric is perfect. It proves one dangerous class of mistakes is absent: your grouped report did not lose or double-count purchase facts on the way to the dashboard.
The next lesson uses this same dataset for funnels and cohorts. The first move will stay the same: name the grain before you trust the metric.
Checkpoint
Answer all three to mark this lesson complete