Level 4 · Cost per conversion without dividing by zero

Rates are divisions, and a denominator can be zero: a channel with no conversions, a day with no orders, a new product with no sales. What happens next depends on the database. Postgres, SQL Server and Snowflake stop the whole query with a “division by zero” error. DuckDB returns Infinity for decimal division, which flows straight into a chart.

NULLIF in one line

NULLIF(a, b) returns NULL when a = b, and a otherwise. Wrap the denominator:

spend / NULLIF(conversions, 0)

When conversions is 0 the division becomes spend / NULL, which is NULL: no error, no Infinity, and a blank on the chart, which is the honest answer.

The real zeros in this data

Social is Harbor & Pine’s weakest converting source. In May 2025 the store spent $10,170.19 on social and those sessions brought 0 conversions out of 204 social sessions. September and October 2025 also had 0. Paid, with far more spend, never hit zero in 2025, but its lowest month (February) had a single conversion for $20,740.71. NULLIF handles the zeros; a cost of $20,000 per conversion is a business conversation, not a data problem.

NULLIF for sentinel values

The second use of NULLIF is cleaning. Older systems often store “unknown” as a sentinel such as 0, -1 or an empty string. Turn the sentinel into a real NULL so aggregates skip it:

SELECT AVG(NULLIF(quantity, 0)) AS avg_quantity
FROM order_items;

Eight order lines have quantity 0 (data-entry errors). AVG(quantity) is 1.9954 and AVG(NULLIF(quantity, 0)) is 1.9959. The difference is tiny here, but with a sentinel like -1 on thousands of rows it decides the answer. For text: NULLIF(TRIM(coupon_code), '').

Common mistakes

• Wrapping the whole ratio. NULLIF(spend / conversions, 0) still divides by zero first.
• Inventing a value. COALESCE(conversions, 1) or a ratio of 0 makes up a number. If the chart must show something, show NULL as “no conversions” in the label.
• Filtering out the zero rows. It removes the error and also the evidence that money was spent with no result.

In SQL Server

NULLIF is identical and is the standard T-SQL guard against error 8134 (divide by zero). Beware of integer division too: conversions / sessions between two INT columns returns 0, so multiply by 1.0 first.

SELECT channel, spend / NULLIF(conversions, 0) AS cost_per_conversion FROM monthly_marketing;