Build a product funnel with an explicit event contract

Define entry selection, step order, conversion window, counting unit and observation maturity before calculating a funnel. Counting users who ever fired two event names does not establish that they completed a valid sequence.

A useful funnel contract also explains repeated attempts and boundary timestamps, where two otherwise reasonable implementations can disagree.

Define the reporting-product funnel

The product analytics lab uses each external nonbot user's first valid report_created event as entry. A valid report_published event at or after entry and strictly before entry plus 48 hours completes the funnel.

The unit is a user at their first entry, not every attempt or session. Only entries whose full 48-hour window has elapsed by the observation cutoff enter the rate's denominator.

Execute the sequence and maturity rules

WITH entries AS (
 SELECT u.user_id, MIN(e.event_time) AS entered_at
 FROM users u JOIN events e USING(user_id)
 WHERE u.user_kind='external' AND u.is_bot=0
   AND e.valid=1 AND e.event_name='report_created'
   AND e.event_time<'2026-01-16T00:00:00Z'
 GROUP BY u.user_id
)
SELECT f.user_id,
 CASE WHEN EXISTS (
   SELECT 1 FROM events e
   WHERE e.user_id=f.user_id AND e.event_name='report_published' AND e.valid=1
     AND julianday(e.event_time)>=julianday(f.entered_at)
     AND julianday(e.event_time)<julianday(f.entered_at)+2
 ) THEN 1 ELSE 0 END AS converted
FROM entries f
WHERE julianday(f.entered_at)+2<=julianday('2026-01-16T00:00:00Z')
ORDER BY f.user_id;

U1 and U3 convert among six mature entrants, giving 2/6 or approximately 33.3%. The lab executes this SQL in SQLite and separately checks the expected user-level outcomes.

from build_and_verify import database, FUNNEL_SQL

db = database()
rows = db.execute(FUNNEL_SQL).fetchall()
db.close()
assert rows == [('U1',1),('U2',0),('U3',1),('U4',0),('U5',0),('U6',0)]
assert sum(converted for _, converted in rows) == 2
print(rows)

Explain the nonconverters

U2 and U6 publish too late. U4's publish event is marked invalid because it does not represent a qualifying output. U5 publishes exactly at the exclusive 48-hour boundary and therefore does not count under this contract.

Those are different reasons, even though each produces converted=0. A diagnostic funnel can retain the reason categories to guide investigation.

Avoid a maturity-biased denominator

U9 enters late enough that its full conversion window has not elapsed by the cutoff. It already publishes successfully, but the mature-cohort rate excludes it just as it would exclude a recent nonconverter.

Including successful immature users while excluding unsuccessful immature users biases the rate upward. An alternative real-time estimator needs its own censoring or time-to-event treatment, not selective inclusion.

Reconcile platform semantics explicitly

Analytics tools can select conversion paths differently. Amplitude's funnel conversion documentation describes unique-user path selection and how holding a property constant can change the counting unit.

This lab intentionally uses a first-entry contract. Do not expect its count to match a platform's earliest-longest path, repeated-attempt or session-level funnel without matching those settings.

For a multi-step funnel, require an ordered chain rather than independently checking whether every event exists somewhere in the window. A later step occurring before its prerequisite should not qualify.

Validate the event evidence

Deduplicate delivery replays by event ID, reject conflicting payloads and exclude internal/bot identities consistently. Preserve event time and ingestion time in a real pipeline so late arrival can be distinguished from late user behavior.

Exercise: change the upper boundary to inclusive in a copied query and identify the exact added converter. Then design an attempt-level funnel and explain why its denominator differs from this first-entry user funnel.

NeuraPath's Data Analytics with Generative AI course connects SQL sequences with product interpretation. A defensible funnel makes the path and observation rules visible before presenting a conversion percentage.

Continue learning

This article is part of the Customer and product analytics sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Define active users before calculating DAU and MAU.
• Continue with Activation metrics: connect first value to observable behaviour.