Different conversion rates do not always mean that someone calculated incorrectly. Often, the denominator, event grain, time window, or event order differs. "June payment conversion" might mean eventual payment by the June order cohort, or the ratio of all orders and payments that happened during June. Define unit, stages, window, order, and cancellation/refund rules as a contract before comparing numbers.
One formula hides at least five decisions
The simplest conversion formula is:
conversion rate = people (or events) completing the goal ÷ people (or events) entering the start
Everything difficult is inside the parentheses. For an "order created → payment" funnel, answer:
- Unit: one user, one order, or one event?
- Stage: which event or field defines creation and payment?
- Window: a fixed calendar interval, or N days after each start?
- Order: must payment follow creation? Use the first repeated event or any event?
- Eligibility: how are test accounts, cancellations, refunds, and recreated orders treated?
If any answer differs, two rates may each be correct but not comparable.
User-based versus event-based: define the denominator unit
Suppose one user creates three orders in June and pays for one:
- User-based: the user converted because they paid at least once; the denominator is also unique users.
- Order-based: one paid order divided by three created orders equals 33.3%.
- Event-based: if payment retries produced two success events, naïvely dividing events might even yield 2 ÷ 3.
User-based metrics answer "how many people completed the journey." Order-based metrics answer "how many orders converted." Event-based metrics can describe pipeline health or action frequency, but retries and duplicates must be handled. Never combine unique users in the numerator with orders in the denominator.
Always show numerator, denominator, and rate:
420 paying users / 1,000 users who created an order = 42.0%
"Conversion was 42%" alone does not reveal whether the denominator shrank.
Cohort and calendar windows answer different questions
Suppose a user creates an order on June 30 and pays on July 1.
Calendar window
Both the denominator and numerator contain events in June, so the cross-month payment does not count. This describes how many events occurred during June, but users arriving at month end inherently have less time to convert.
Cohort window
First select the June creation cohort, then observe whether each user or order pays within seven days. The June 30 order paid on July 1 counts. This is better for comparing cohort quality, but you must wait for the observation window to mature; on July 3, the June 30 cohort is incomplete.
Neither is always right. An operations daily report may need a calendar view, while product-flow optimization may need a cohort view. Expose the definition in the name—"7-day payment rate for the June order cohort"—rather than calling both "June conversion."
Event order and stage deduplication determine whether the funnel is valid
Event streams may arrive out of order, be resent, or be backfilled. A strictly ordered funnel commonly requires:
first_order_created_at <= first_payment_at
first_payment_at <= first_fulfilled_at
But "first" also needs a definition: first per user, first successful payment per order, or something else? If a user cancels and creates a new order, is it one journey? Deduplicate resent payment webhooks with payment_id or another business key instead of treating event count as people.
Build a diagnostic table for every stage:
| Stage | Unit key | Event/field | Deduplication rule | Time field |
|---|---|---|---|---|
| Order created | order_id |
order_created |
First per order | created_at |
| Payment | order_id |
payment_succeeded |
First success per order | paid_at |
| Fulfillment | order_id |
fulfilled |
Earliest completion | fulfilled_at |
This exposes differences such as a one-to-many payment table or a status field that is a current snapshot rather than event history.
How should cancellations, refunds, and cross-day conversions work?
Cancellations
If an order was paid before cancellation, count it when asking whether the payment flow succeeded; perhaps exclude it when asking about final sales. The decision question determines the rule.
Refunds
Payment rate normally uses successful payment as the numerator. Net conversion or retained revenue may exclude refunds. Reporting both gross payment rate and post-refund rate prevents one number from carrying two meanings.
Cross-day behavior and time zones
When events are stored in UTC but reports use Asia/Taipei dates, convert time zones before assigning dates. Use half-open intervals [start, end) to avoid losing fractional times at month end. A cohort observation window, however, should begin at each starting event rather than reusing the calendar month end.
A reusable funnel Plan template
## Decision question
- What decision will this funnel support? Which periods or groups are being compared?
## Population and unit
- Population: which accounts or orders are included? Exclude test, internal, or bot traffic?
- Unit key: user_id / order_id / session_id
- Grain: what does one row represent in each intermediate and final table?
## Stages
- S1 name, event/field, time field, deduplication rule
- S2 name, event/field, time field, deduplication rule
- Is order enforced? Can units skip a stage?
## Time policy
- Cohort start: [start, end)
- Observation window: N days after start
- Report timezone: Asia/Taipei
- How are immature cohorts excluded or labeled?
## Eligibility and reversal
- Treatment of cancellations, refunds, recreations, retries, and NULL keys
## Outputs
- Count at each stage, step conversion, overall conversion
- Show numerator and denominator together
- Segment only by necessary dimensions defined in advance
## Checkpoints
- Distinct units and row counts at every stage
- Counts of order violations, duplicate events, NULL keys, and immature cohorts
- Largest absolute and relative stage drop
## Limitations
- Missing events, tracking changes, and areas where causality cannot be inferred
Do not examine drop-off only as a percentage
For each transition report:
absolute drop = stage_n count - stage_n+1 count
step conversion = stage_n+1 count / stage_n count
The largest absolute drop means the most units were lost; the lowest step conversion means the weakest proportion. They may occur at different stages. Also flag stage counts that increase unexpectedly. In a strictly ordered funnel, an increase usually indicates inconsistent deduplication, JOINs, or units.
How Lantide Data exposes definitions before Execute
In Lantide Data Project Analysis, an Agent can explore schema and event distributions, then record units, stages, denominators, time windows, and checkpoints in a Plan. A PM or business owner can comment directly without editing SQL. After the definition is agreed, the user presses Approve & Execute, and the Agent creates formal SQL evidence and a Report. See Plan → Execute → Report.
With a SQL-first workflow, denominators and event order do not exist only in a summary: grain, JOINs, filters, and aggregations can be reviewed in persistent SQL tabs, and the Report retains counts, rates, and limitations. The workflow surfaces disagreement earlier, but it cannot decide whether refunds count or repair missing events. The person accountable for the decision owns the final definition. See the SQL-first workflow.
Conclusion: give the metric a name that cannot be misunderstood
The next time someone asks for conversion, do not answer with a percentage alone. Say: "the conversion rate for the June order cohort, based on the first successful payment within seven days, excluding test orders but not later refunds." If the team cannot agree on that sentence, it is not ready to Execute.