I’m calculating average purchase values, but my dataset is full of NULLs where customers didn’t buy anything. Should I use COALESCE(price, 0) or just let the AVG() function ignore them? I’m worried that treating them as zero will artificially deflate my true average.
3 answers
This is a classic "business logic" vs "math" problem. If you want the average of completed purchases, let AVG() ignore the NULLs. If you want the average revenue per visitor, use COALESCE(price, 0). Treating NULLs as zero will definitely skew your median and mean if the "non-purchase" group is large. In my work with e-commerce data in 2023, we always created two metrics: "AOV" (Average Order Value) which ignores NULLs, and "ARPU" (Average Revenue Per User) which converts NULLs to zero. Transparency is key.
Are you also looking at the count of NULLs as a separate "churn" metric, or are you purely focused on the central tendency of the spending data?
Never use COALESCE(0) for averages unless you specifically want to include the "zeros" in the denominator. It completely changes the meaning of your data.
Exactly. I always tell my juniors: NULL means "we don't know," and 0 means "we know it's nothing." They aren't the same!
I'm mainly focused on the spending, but now that you mention it, the NULL count is a huge signal for our marketing team. I think I'll use a CASE statement to bucket users into "Buyers" and "Browsers" first. Is there a way to calculate both the average of the buyers and the percentage of browsers in a single pass using window functions or grouping?