Resolve Inconsistent Rates and Time Windows

Understand the likely causes

• Common causes include equal averaging of unequal-exposure rates, COUNT(*) versus non-null counts, duplicate events after joins, partial periods, inclusive endpoints counted twice, and mixing local dates with UTC. Filtering the numerator and denominator differently can silently turn one metric into another. Rounding is a separate final presentation issue.

1. Write the formula and eligible unit

Express the rate as named numerator divided by named exposure, with explicit entity/event grain and inclusion rules. Ask whether a unique account, an opportunity, or an observed measurement is the denominator. Recover underlying counts instead of reverse-engineering a rounded percentage. Compare the two reports’ definitions line by line before changing numerical tolerances.

2. Normalize time before deriving the reporting date

Choose event time or another justified timestamp role and an explicit reporting zone. Parse aware instants and convert before applying the window. Use start-inclusive/end-exclusive boundaries for adjacent periods unless the metric specifies another reviewed convention. A naive timestamp needs a source-time interpretation; attaching UTC without evidence does not convert an unknown local instant correctly.

3. Compute numerator and denominator from one eligible set

The complete program selects exactly one UTC day from four synthetic boundary events, then computes success among those eligible events. The event at the next midnight is excluded rather than counted in two days. The numerator is a subset of the same eligible records, and the output exposes counts beside the rate. No wall-clock current date or external source is used.

from datetime import datetime, timezone

raw = [("2026-10-24T23:59:00+00:00", True), ("2026-10-25T00:00:00+00:00", True), ("2026-10-25T23:59:00+00:00", False), ("2026-10-26T00:00:00+00:00", True)]
start = datetime(2026, 10, 25, tzinfo=timezone.utc)
end = datetime(2026, 10, 26, tzinfo=timezone.utc)
eligible = [success for token, success in raw if start <= datetime.fromisoformat(token).astimezone(timezone.utc) < end]
if not eligible:
    raise ValueError("no eligible exposure")
successes = sum(eligible)
print(f"eligible={len(eligible)} successes={successes} rate={successes / len(eligible):.0%}")

eligible=2 successes=1 rate=50%

4. Recombine subgroup rates using exposure

For an overall opportunity rate, sum subgroup numerators and denominators before division. An unweighted mean of group rates gives each group equal weight and answers a different question. Retain counts for missing-key groups and inspect whether a tool dropped them. A zero denominator needs an undefined/not-applicable policy, not an invented zero rate.

5. Reconcile coverage before explaining the movement

Compare source extraction time, completed versus provisional periods, accepted/rejected counts, unique event keys, and join cardinality. A lower latest-period total can reflect delayed arrival or incomplete exposure. Keep a small reconciliation table showing each filter’s effect and round only at presentation. Do not call a remaining difference noise until its structural causes have been checked.

Keep it from recurring

• Version metric definitions with population, grain, window, zone, numerator, denominator, and missingness policy. Keep underlying counts, extraction versions, and group reconciliation beside every published rate. Use automated boundary tests and avoid hidden current-time filters in reproducible reports.