Funnel Analysis: A Worked Example to Find Real Drop-Offs

Funnel analysis measures how many people or accounts complete a defined sequence of steps within a chosen time window. To find a real drop-off, count the same population at every step, enforce the required event order, and give each entrant enough time to finish before comparing conversion rates.

Suppose a product team asks why trial accounts are not becoming paying customers. One report counts eight accounts with a payment event among twelve signups. Another reports three conversions among eight eligible accounts. Neither number can be interpreted until the team agrees on what counts as a completed trial journey.

This guide works through that disagreement using synthetic account events. The goal is to decide what to investigate next without mistaking repeated events, incomplete observation, or skipped steps for customer behavior.

What question should the funnel answer?

Start with one decision: where should the team investigate friction in the path from a new account to a paid subscription?

For this example, the journey is account created → email verified → first successful project → first payment. A successful project is the activation event for this fictional product. Your activation definition needs evidence from your own product; it should not be copied from this example.

Use accounts because the subscription belongs to an account. If three colleagues verify their email, that does not create three trial accounts. Resolve user events to the correct account before counting. If the product supports several subscriptions per account, choose the subscription or trial attempt as the unit instead.

This is a closed funnel: an account must enter at the first step. Later stages count only after the earlier required stages. Google Analytics distinguishes open and closed funnels, and Amplitude supports several event-order choices. Choose the rule that matches the actual journey rather than assuming every analytics tool uses the same default.

Do not impose a required activation step on a purchase path that legitimately lets customers pay first. That would answer a different question and classify valid customers as drop-offs.

Fix the counting rules before drawing the chart

The synthetic report uses these rules:

• Count each account once, starting at its first account-created event. Exclude employee and test accounts before constructing the cohort.
• Include October 2026 entrants. Read events strictly before October 31, 2026, at 00:00 UTC; this is the data cutoff, not a claim that the whole month is complete.
• Allow seven days, or 168 hours, from account creation to complete the journey. An event exactly at the deadline is outside this example's window.
• Include only accounts whose complete seven-day window has elapsed by the cutoff. Show newer accounts separately as pending.
• Require verification after creation, activation after verification, and payment after activation. Other actions may happen between those steps.
• Count the earliest qualifying occurrence of each next step. Repeated events do not add accounts. Deduplicate repeated deliveries with the same event ID.

These choices make the result reproducible. A seven-day window is an example business rule, not a conversion benchmark. Use a window appropriate to your buying cycle and keep it consistent across comparison cohorts.

Distinguish event time from ingestion time. A customer may have completed a step yesterday even though the event arrived today. Record source freshness and rerun affected cohorts when late events arrive. For a reproducible historical report, retain the extract or an ingestion cutoff as well as the event-time cutoff.

Work through twelve accounts

The table is the complete synthetic account fixture. All creation times are 00:00 UTC on the date shown. Numbers are elapsed hours after creation; a dash means no recorded event before the cutoff. There are no other business events in the fixture.

For an event-log implementation, turn every creation and nonempty cell into an event with account ID, event name, event time, and a unique event ID. To exercise duplicate handling, deliver A's verification event twice with the same event ID. To exercise legitimate repetition, add another activation event for B at hour 25 with a new event ID. Neither should change the account counts.

If you count distinct accounts for each event independently, the four totals are 12 created, 10 verified, 8 activated, and 8 paid. Dividing eight payments by twelve creations gives 66.7%. That describes accounts with those event names in this extract; it does not establish completion of the required seven-day sequence.

Now apply the agreed rules. I through L have not had seven full days by the cutoff, so all four stay pending—even I and J, which already paid. Including early successes while excluding unfinished peers would bias the cohort upward.

D paid at hour 216, which is day nine. D reaches activation in this funnel but does not convert within seven days. G has no verification event, so its activation cannot advance this required sequence. H's payment likewise cannot skip verification and activation. Those records need investigation; they are not proof that the customers failed to act.

The completed-window cohort is A through H:

Overall conversion is 3 ÷ 8 = 37.5%. Step conversion is the next step's account count divided by the preceding step's count. Step drop-off is one minus that rate.

The difference between 66.7% and 37.5% is a difference in population and counting rules. It is not evidence of a recent performance decline. The fixture is deliberately tiny: one account moves the overall rate by 12.5 percentage points, so it cannot support a stable benchmark or a claim that one step is statistically worse.

How do you reproduce the ordered counts?

An analyst can implement the calculation in SQL, Python, or an existing funnel tool. Preserve the same logic across implementations:

• Deduplicate the event log by event ID, then identify the first creation time for each account.
• Keep October entrants whose creation time plus 168 hours is no later than the cutoff.
• For each eligible account, find the earliest verification strictly after creation and before its deadline.
• Find the earliest activation strictly after that qualifying verification and before the same deadline.
• Find the earliest payment strictly after that qualifying activation and before the same deadline.
• Count nonempty timestamps at each stage and retain the account-level result for inspection.

Choose each next timestamp after the previous qualifying timestamp. Taking the first activation anywhere in an account's history can incorrectly reject an account that activated before verification and then activated again afterward. Real data also needs a deterministic rule for equal timestamps; use a reliable sequence identifier when timestamp precision cannot establish order.

Use left joins or their equivalent to retain accounts that did not reach later stages. An inner join to payments can silently remove the very drop-offs you want to measure.

Check the expected counts of 8, 6, 4, and 3 before interpreting the chart. Check pending accounts separately. Also test a payment exactly at hour 168, a payment before activation, repeated creations, and an account with no later events. These cases expose a different set of errors from checking whether the chart looks plausible.

Which drop-off should you investigate first?

Verification to activation has the lowest observed transition rate in the example, but the first two transitions each lose two accounts. The chart alone does not choose the next product change.

First reconcile G and H against the underlying systems. A missing verification event could reflect broken tracking, an account merge, or a legitimate path that contradicts the funnel definition. Correct the measurement or the definition before calling that behavior a failure.

For E and F, investigate what happened after verification: did project creation fail, did the account lack a required connection, or did the team simply decide not to continue? Combine event evidence with permitted support records or a product analytics tool's session evidence. A funnel locates an observed break; it does not identify the cause by itself.

D suggests a timing question. Compare seven-day and longer-window conversion on equally mature cohorts to learn whether the journey is slow rather than abandoned. Report both windows explicitly. Do not change the window only for the cohort whose result looks disappointing.

On a larger dataset, split the same transition by one useful attribute, such as acquisition channel, account size, or onboarding path. Prefer the attribute recorded at entry. Assigning accounts by their later paid plan can select on the outcome and distort the comparison.

Keep the overall totals visible and avoid searching dozens of tiny segments for a dramatic percentage. Prioritize an investigation using the number of affected accounts, the evidence of a fixable problem, the value of the outcome, and the effort required. Estimated recoverable conversions are a scenario until an experiment or other credible evaluation establishes an effect.

Use AI to investigate, then inspect its work

Give an analytics assistant the counting rules, the approved source tables, the event definitions, and the expected fixture result. Ask it to return both the funnel and one row per account showing qualifying timestamps and exclusion reasons.

A useful request is: “Build the closed account-level funnel using the seven-day rules above. Separate pending accounts, preserve accounts that stop early, and show the query and account-level checks before explaining the result.”

Orion by Gravity's analysis documentation describes plain-language questions over connected data, returned code and results, and reusable analysis notebooks. That makes Orion worth evaluating when a data team wants to help business colleagues investigate an agreed warehouse funnel and inspect the analysis together. This synthetic example has not been run in Orion; use the expected counts as a validation case before relying on a production answer.

Orion Context pages can hold the business meaning and analytical instructions. Executable metric definitions belong in project metrics or the Metrics Catalog. Keep approved calculations in dbt or your existing governed metric system when that is their current home; writing a formula into a Context page does not change its execution.

Hex is also a credible option. Its SQL and Python funnel tutorial teaches the analysis through a simulated ecommerce dataset, and Threads supports conversational questions over warehouse tables and semantic models. Prefer evaluating Hex when maintaining analyst-authored SQL, Python, and interactive analytical work is central to the job. Evaluate Orion when the main need is shared question-led investigation and reusable analytical outputs. This guide establishes no comparative accuracy or speed result.

Keep your current product analytics tool when the central need is event instrumentation, journey exploration, or session replay. Orion can be an additional analysis path; this example does not establish that it replaces those capabilities. The Orion–Hex comparison covers the broader buying decision.

Turn the result into a decision record

The useful output is a short explanation someone can check: “Three of eight fully observed accounts completed the required sequence within seven days. Four newer accounts remain pending. Verification-to-activation merits investigation, but two records also need event reconciliation. No cause or improvement has been established.”

Attach the definition, cutoff, source freshness, query, account-level checks, and the next investigation owner. After a change, compare cohorts with the same rules and full observation windows. A before-and-after movement alone does not prove the change caused it.

For the broader investigation method, see how to examine why a metric changed. If the recurring question is how to deliver the checked result to stakeholders, see automating weekly executive reports.