If two channels convert at 90% and 10%, is overall conversion 50%? The answer depends on how many eligible objects each channel contains and whether the groups overlap. Ratio errors often arise before division: the system selects the wrong denominator, treats percentages as additive measures or mistakes incomplete data for zero. This article develops calculation and acceptance rules for conversion within a defined eligible population. All examples are constructed, not customer or production results.
Define a traceable population before defining its conversion rate
An executable ratio contract should identify the metric and version, the unique key of the measured object, eligibility rules, the qualifying numerator event, the observation window, attribution dimensions, the data cutoff and the treatment of missing values and zero denominators. “Conversions divided by leads” is insufficient: deals closed this month may come from leads created last month, so the two counts may describe different populations.
Use a precise example: the denominator consists of eligible leads created within a defined interval that have completed 30 days of observation. The numerator counts those same leads that converted at least once within 30 days of creation. Multiple deals from one lead still contribute one converted lead. Define the exact window boundaries and business time zone. An unfinished observation window differs from incomplete source delivery, and neither establishes that a lead failed to convert.
For this definition, numerator objects are a subset of denominator objects, so converted leads must be between zero and eligible leads. This constraint does not apply to every percentage: sales-target attainment can exceed 100%, and orders per customer is not the share of customers who converted. A percentage symbol does not establish shared business semantics. Store these measures as separate definitions.
Before calculating a ratio, apply the fact-uniqueness checks described in cardinality and amount conservation in multi-table joins. If both numerator and denominator are duplicated twice, the displayed percentage can remain unchanged. Inspecting the rate alone will miss the error. The rest of this article addresses aggregation and interpretation once the intended fact population has been established.
An overall rate and an average department rate answer different questions
In the first constructed dataset, channel A has 10 eligible leads and 9 converted leads, giving 90%. Channel B has 100 eligible leads and 10 conversions, giving 10%. Averaging the two percentages gives 50%. Summing numerators and denominators separately gives 19 divided by 110, or approximately 17.27%. Both calculations are possible, but their questions differ.
The 17.27% figure describes the converted share of these 110 distinct leads. The 50% figure is the equal-weight average of two channel rates. The latter may support an explicitly defined department comparison, but it must not be labeled overall lead conversion. PostgreSQL avg calculates an arithmetic mean over non-null inputs; it does not infer that the business question requires weighting by lead count.[1]
When group denominators are positive, groups form a complete non-overlapping partition and their definitions agree, the overall ratio equals group rates weighted by their shares of the total denominator. Here the weights are 10/110 and 100/110. This follows algebraically from the numerator and denominator definitions. It is not a rule to weight every percentage by headcount: a valid rollup of target attainment would use compatible target amounts, not employee counts.
Retain unformatted numerators and denominators and divide again at the required reporting level. Keeping only “90%” and “10%,” or only rounded daily rates, discards the weights needed for reliable aggregation. If the source provides group rates without denominators, state that the overall ratio cannot be reconstructed. A model must not invent the missing population sizes.
Check three conditions before adding numerator and denominator counts
First, groups must be mutually exclusive and collectively cover the population. A lead attributed to both search and an event is counted twice if the two channel-level distinct counts are added. Establish one attribution rule or return to object sets and deduplicate the overall population. Overlapping multi-touch views can be useful, but their group counts are not an additive overall total. Repairing only the denominator leaves duplicated numerator objects unresolved.
Second, measurement units and definitions must agree. Eligible leads cannot be combined with visits, and 30-day conversion cannot be combined with 7-day conversion. Two teams may use the same label while one excludes invalid leads and the other includes all registrations. They do not yet have a meaningful shared denominator. Align the rules or present separate results rather than concealing differences under one column name.
Third, cover the intended population completely. A missing channel value must not silently remove an otherwise eligible lead. pandas groupby defaults to dropna=True, which excludes rows with missing group keys; dropna=False retains a missing-key group.[3] For eligible objects, an explicitly labeled “unassigned” group plus a quality measure is usually more transparent than an unexplained difference between visible groups and the overall count.
These conditions also constrain caching and pre-aggregation. Daily distinct-customer counts generally cannot be added into a monthly distinct-customer count because the same customer can appear on several days. Lead cohorts partitioned by a unique creation date, with each object included once, may be additive under a common observation rule. When choosing compact summary storage, document the dimensions and time grains that the summary actually supports.
Separate zero denominators, real zero conversion and missing data
For a complete group with eligible objects, 0/100 is 0%: no object converted within the window. In contrast, 0/0 has no defined conversion rate and should be labeled “no eligible objects.” Under this article’s definition, 3/0 indicates a data or definition error, not 0% or infinity. An unknown denominator means incomplete data. Return a status separately from the numeric value.
PostgreSQL sum skips null inputs and returns null for no input rows. NULLIF(x,0) returns null when x equals zero.[1][2] Consequently, SUM(converted)::numeric / NULLIF(SUM(eligible), 0) can be the numerical core after quality checks pass. It is not a complete acceptance rule, especially because it cannot detect partial missingness that sum has silently skipped.
Suppose A is still 9/10, while B has 100 eligible leads but its converted count has not arrived. Summing the two columns independently with a null numerator for B produces 9/110, approximately 8.18%. An unknown outcome has become an apparently known low rate. Keeping only complete A gives 90%, but that is a result for the complete subset and must disclose its coverage; it is not the overall result.
For certified answers, a useful policy is to return an incomplete status and anomaly count whenever unresolved numerator or denominator gaps remain inside the target scope. If exploration is permitted, label the complete subset and return its numerator, denominator and coverage information. No records do not automatically establish no business activity: inspect delivery-complete signals, the expected scope and the cutoff. A universal zero-fill rule destroys these distinctions.
Produce a controlled result that carries both numbers and status
First, build one eligibility and conversion record per unique lead in the approved population, retaining observation maturity, source completeness and exclusive attribution. If upstream systems already supply group counts, validate unique group grain, a common metric version and which groups are expected. Two non-null numbers in a row do not prove that upstream delivery is complete.
Second, before aggregation, count missing fields, invalid negative values, conversions outside the eligible population, duplicate objects and unfinished observations. A numerator larger than its denominator is invalid for this object-conversion definition; other measures need their own checks. Quality failure and a valid zero value must follow different result paths.
Third, aggregate numerator and denominator under identical filters, authorization scope and data cutoff. Calculate the rate only after the quality gate passes and the denominator is positive; otherwise return the appropriate reason state. The PostgreSQL expression above supplies only the numerical core. A full implementation must also carry quality counts and readiness signals. This article checks constructed arithmetic, not a target database deployment or production SQL implementation.
Fourth, return an interpretable result package to the analytics assistant: metric version, numerator 19, denominator 110, ratio 19/110, displayed value 17.27%, population interval, observation window, cutoff, grouping rule and status. Presentation can round according to policy, while further aggregation uses original counts. A follow-up request for a regional breakdown must revalidate whether region is an additive dimension instead of simply averaging an existing percentage column.
The approach adds metadata maintenance and validation costs. Frequent stable metrics may justify summaries containing counts and quality states; infrequent new questions may need object-level computation. A change to dimensions, attribution or observation rules requires a version change and an explicit recomputation scope. Do not suppress unknown states or silently switch metric definitions merely to return a number every time.
Every group can improve while the overall rate declines
A second constructed dataset tests trend interpretation. In period one, A converts 90/100, or 90%, and B converts 9/100, or 9%; the overall rate is 99/200, or 49.5%. In period two, A is 19/20, or 95%, and B is 18/180, or 10%; overall conversion is 37/200, or 18.5%. The groups improve by 5 and 1 percentage points respectively, yet the overall rate drops by 31 percentage points because lower-converting B rises from 50% to 90% of the denominator.
Neither “the calculation is wrong” nor “performance deteriorated in every group” follows from these figures. Report actual overall conversion, group rates and denominator-share changes together. As an additional comparison, applying period one’s 50/50 weights to period two gives 52.5%, three percentage points above period one. Label that a fixed-mix comparison: it is neither the actual period-two rate nor causal evidence of an intervention’s effect.
A fixed-weight comparison needs comparable groups and observation rules. New channels, disappearing groups, changed eligibility or unfinished observation windows can break that comparability. Do not force a trend calculation using old weights and nonexistent group rates. Disclose the compared scope, retain unmatched groups and withhold attribution when necessary. The model’s narrative should respect these outputs rather than generating business causes automatically whenever a line falls.
Use counterexamples to test calculation, rollups and explanation
The baseline fixture is 9/10 and 10/100. Require an overall ratio of 19/110 and label the equal-weight department average separately as 50%. Split B’s 100 objects into two non-overlapping subgroups and aggregate again: the original overall rate must remain unchanged. A system that averages percentages can change its answer when the grouping changes. This test reveals more than inspecting a single summary screenshot.
Boundary fixtures distinguish 0/100, 0/0, 3/0, a missing numerator, a missing denominator and no input records, checking both values and statuses. Eligibility and attribution fixtures add cross-group duplicates and an unknown channel. Independent object counts must reconcile with group counts wherever addition is permitted. Deliberately doubling both numerator and denominator must trigger a count failure even though the ratio is unchanged.
The trend fixture uses the mix change from 49.5% to 18.5%. Require the explanation to mention both within-group improvement and changed weights; it must not say every channel’s conversion declined. Test that the 52.5% fixed-weight comparison is not mislabeled as an actual overall rate. Also distinguish “up five percentage points” from “up five percent,” so language does not alter a numerically correct answer.
Retain source objects or auditable summaries, the contract version, expected counts, statuses, ratios and interpretation limits. The examples were checked with Python rational arithmetic. That establishes their arithmetic, not platform performance or statistical significance. Release acceptance should include independent numerator and denominator reconciliation, missing-data handling and grouping invariance. A correct percentage alone does not prove that the data pipeline is complete.