Why Your SQL Conversion Rate Shows Zero When Customers Are Buying

The order count is correct, the session count is correct, and the dashboard says conversion is zero. Before investigating a sudden collapse in shopper intent, inspect the arithmetic. A query can discard the fractional part of a ratio before the reporting tool ever receives it. Changing the display to two decimal places cannot recover information already lost.

EcomToolkit recommends checking data types and units alongside metric definitions. This guide uses PostgreSQL examples and fictional ecommerce counts to show where a ratio becomes wrong, how to repair it, and which business questions remain after the arithmetic is fixed. SQL engines differ, so verify the behaviour in your own warehouse.

Table of Contents

• Reproduce the failure with small numbers
• Preserve one clear unit across the pipeline
• Handle zero denominators without inventing performance
• Repair the denominator before celebrating the number
• Aggregate counts before calculating the total rate
• Make regression checks meaningful
• The EcomToolkit view

Reproduce the failure with small numbers

Suppose a reporting period contains 37 converting sessions out of 1,000 eligible sessions. The proportion is 0.037 and the percentage is 3.7%. In PostgreSQL, dividing two integer values truncates the result towards zero. The mathematical-operator documentation explicitly describes this behaviour.

SELECT 37 / 1000 AS integer_result,
       37::numeric / 1000 AS proportion,
       100 * (37::numeric / 1000) AS percentage;

The outputs are zero, 0.037 and 3.7 respectively, allowing for the database client’s decimal formatting. The numerator is cast before division. Casting the result of 37 / 1000 afterwards would convert an already-truncated zero to a different type without restoring the missing fraction.

Similarly, multiplying the truncated result by 100 does not help. Reordering arithmetic may change a particular result, but an explicit type decision is easier to review than relying on a literal or implicit conversion. Make the intended decimal calculation visible to the next analyst reading the query.

Preserve one clear unit across the pipeline

Choose whether the query exports a proportion or a percentage value. A proportion of 0.037 commonly belongs in a field that the dashboard formats as 3.7%. A query that already returns 3.7 should not then be multiplied by another percentage formatter. Otherwise the display can become 370%.

Keep that unit in the field name or semantic definition. For example, session_conversion_proportion makes the range and formatting expectation clearer than cr. It also gives downstream exports and spreadsheet users a better chance of preserving the meaning when the original dashboard settings are no longer visible.

Avoid rounding the proportion too early. Rounding 0.037 to two decimal places yields 0.04, which displays as 4% rather than 3.7%. Store enough precision for downstream comparisons and round at the presentation boundary according to the reporting need.

Handle zero denominators without inventing performance

A segment with no eligible sessions does not have an observed conversion rate of zero. It has no denominator from which to calculate the rate. Preserve that difference so the chart does not make a newly launched market look like a fully observed failure.

The following self-contained example uses converting sessions, not raw order counts. It assumes both counts are complete and refer to the same eligibility rules. PostgreSQL’s conditional-expression reference documents NULLIF, which allows the zero denominator to become null instead of causing a division-by-zero error.

WITH counts(segment, converting_sessions, eligible_sessions) AS (
  VALUES ('mobile', 37, 1000),
         ('new-market', 0, 0),
         ('campaign', 0, 250)
)
SELECT segment,
       converting_sessions,
       eligible_sessions,
       converting_sessions::numeric
         / NULLIF(eligible_sessions, 0) AS conversion_proportion
FROM counts;

Mobile has a proportion of 0.037. The new market has an undefined rate, represented by null. The campaign has a genuine observed zero because 250 eligible sessions produced no converting sessions. A dashboard should distinguish the latter two states with labels or explicit data-status fields.

Do not wrap the final ratio in a blanket COALESCE to zero simply to remove blanks. That would collapse unavailable and observed-zero states again. If the organisation requires a particular export convention, include a separate status column so the downstream reader can reconstruct the distinction.

Repair the denominator before celebrating the number

Correct decimal arithmetic does not guarantee a useful conversion metric. Orders per session, purchasing users per user and converting sessions per session answer different questions. An order-per-session ratio can exceed one in some datasets; a properly defined share of converting sessions cannot exceed one when the numerator is a subset of the denominator.

Confirm bot filtering, consent scope, timezone and channel attribution rules. If the numerator covers all backend orders while the denominator covers only a consented subset of browser sessions, the resulting ratio may be unsuitable as a session conversion rate. The type cast fixes arithmetic, not mismatched coverage.

Also inspect the grain before counting. Joining session records to multiple product lines can repeat a converting session. Deduplicate or aggregate at the intended level before computing the ratio. Our SQL join fanout guide explains why correct-looking counts can become inflated after an innocent join.

Keep numerator and denominator columns available during review. A percentage by itself hides whether a sharp movement came from more conversions, fewer measured sessions or a tracking gap. Those explanations lead to different actions, and the ratio alone cannot choose between them.

Aggregate counts before calculating the total rate

Consider two fictional channels. Channel A has 10 converting sessions out of 100 eligible sessions, giving 10%. Channel B has 9 out of 900, giving 1%. Their simple average is 5.5%, but the combined rate is 19 out of 1,000, or 1.9%.

The difference comes from denominator size. For a combined proportion across disjoint groups using the same definition, sum the numerators and denominators and then divide. A denominator-weighted average of the exact group proportions is equivalent; a simple average generally is not.

Do not weight already-rounded displayed percentages if you can use the underlying counts. Small display differences can accumulate, especially when many segments have tiny rates. Preserve counts and full calculation precision in the reporting layer, even if the executive table shows only one decimal place.

Overlapping groups need separate treatment. A shopper may appear in more than one audience, so adding their counts can double count the population. Our customer-segment membership guide helps define inclusion and exclusion rules before a segment report is rolled into a total.

Make regression checks meaningful

A compact test set should expose the actual failure modes: a fractional rate, a zero numerator with a positive denominator, a zero denominator, and unequal segment sizes. These examples are more useful than testing only a 100% conversion case, which integer division happens to represent correctly.

Add a check at the dashboard boundary as well as in SQL. A correct warehouse value can still be displayed incorrectly by a formatter, spreadsheet import or calculated field. Compare the raw value, the declared unit and the rendered percentage for one known fixture.

For financial amounts elsewhere in the model, choose types deliberately rather than copying a conversion-rate expression. PostgreSQL’s numeric-type documentation distinguishes exact numeric values from inexact floating-point types. The appropriate precision and scale depend on the measure and required operations.

The EcomToolkit view

A useful conversion rate has a complete chain of meaning: eligible population, converting subset, correct division, declared unit and honest display. A type-casting fix is small, but it should trigger a review of that chain rather than a quick visual correction that leaves the denominator unexplained.

Bring the query and one dashboard screenshot to an EcomToolkit analytics review. Start with a handful of known counts, prove the expected result, and then decide whether the commercial movement is real enough to justify changing campaigns or the storefront.