Conversion Funnel Optimization Questions

Direct answer

Customer acquisition cost (CAC) by channel is total spend on that channel divided by the number of users it acquired, over the same reporting window, but the two tables rarely line up on time cleanly: spend on a given day influences acquisitions for some days afterward, not just that same day, so the query needs an explicit spend-attribution window (spend from day minus some lag through the acquisition day) rather than naively matching spend and acquisitions on the same calendar date. Every choice made about that window, and about which acquisition dates count as "the last 30 days," is an assumption that changes the resulting number and needs to be stated, not left implicit.

Structured elaboration

The core computation. For each channel, sum spend over the eligible spend window, sum acquisitions over the reporting window, and divide. Two separate windows are doing different jobs here: the acquisition window defines which users count as "acquired in the period being reported," and the spend-attribution window defines which spend is considered to have caused those acquisitions, and the two are not the same date range.

Why a lag is needed at all. Ad spend on a given day rarely converts to an acquisition that same day; a user might see an ad, research for a few days, and sign up a week later. A spend-attribution window (for example, spend from acquisition-day-minus-7 through acquisition-day) approximates this lag without needing a full touch-level attribution model, treating any spend within that lookback as having plausibly contributed to acquisitions in the reporting window. The lag length itself is an assumption that should be grounded in the actual typical consideration period for the product (a checked, not guessed, number), and stated explicitly whenever a CAC figure is reported.

Stating the alignment assumption plainly. Two assumptions specifically need to be named alongside any CAC number: what "last 30 days" means for the acquisition side (a trailing 30-day window ending on the report date, which excludes the report date's own start-of-window boundary by construction, worth checking against off-by-one expectations) and how far back the spend-attribution lag reaches, since that lag can pull spend from BEFORE the 30-day acquisition window into the denominator, which is intentional (spend from late in the prior month can genuinely be driving acquisitions early in the current window) but easy to get wrong if not made explicit.

Worked example

Schemas: ad_spend(date DATE, channel VARCHAR, spend NUMERIC), acquisitions(user_id BIGINT, acquired_at TIMESTAMP, channel VARCHAR).

-- Report date fixed at 2024-03-31; a 30-day trailing acquisition window; a 7-day spend-attribution lag
WITH windowed_acquisitions AS (
    SELECT user_id, channel, date(acquired_at) AS acquired_date
    FROM acquisitions
    WHERE date(acquired_at) BETWEEN date('2024-03-31', '-29 days') AND date('2024-03-31')
),
acquisition_counts AS (
    SELECT channel, COUNT(*) AS acquisitions_count
    FROM windowed_acquisitions
    GROUP BY channel
),
eligible_spend AS (
    -- any spend date that could fall inside SOME acquisition's [acquired_date - 7, acquired_date] lag
    SELECT date, channel, spend
    FROM ad_spend
    WHERE date BETWEEN date('2024-03-31', '-29 days', '-7 days') AND date('2024-03-31')
),
spend_totals AS (
    SELECT channel, SUM(spend) AS total_spend
    FROM eligible_spend
    GROUP BY channel
)
SELECT
    s.channel,
    s.total_spend,
    COALESCE(a.acquisitions_count, 0) AS acquisitions_count,
    ROUND(s.total_spend / NULLIF(a.acquisitions_count, 0), 2) AS cac
FROM spend_totals s
LEFT JOIN acquisition_counts a ON a.channel = s.channel
ORDER BY s.channel;

Pinned data: paid_search has spend spread from 2024-02-25 through 2024-03-31 (deliberately including spend that predates the 30-day acquisition window, to exercise the 7-day lag reaching back into February) and three acquisitions, one of which (2024-03-01) falls exactly one day before the 30-day acquisition window's start boundary; email has spend and acquisitions entirely inside March.

CREATE TABLE ad_spend (date DATE, channel VARCHAR, spend NUMERIC);
CREATE TABLE acquisitions (user_id BIGINT, acquired_at TIMESTAMP, channel VARCHAR);

INSERT INTO ad_spend VALUES
 ('2024-02-25','paid_search',200),
 ('2024-03-15','paid_search',500),
 ('2024-03-31','paid_search',500),
 ('2024-03-10','email',30);

INSERT INTO acquisitions VALUES
 (1,'2024-03-01 09:00:00','paid_search'),  -- one day before the 30-day window start: must be excluded
 (2,'2024-03-10 09:00:00','paid_search'),
 (3,'2024-03-20 09:00:00','paid_search'),
 (4,'2024-03-05 09:00:00','email'),
 (5,'2024-03-25 09:00:00','email');

Output (actually executed, SQLite):

channel | total_spend | acquisitions_count | cac
email | 30 | 2 | 15.0
paid_search | 1200 | 2 | 600.0

Only 2 of paid_search's 3 pinned acquisitions count: the acquisition dated exactly 2024-03-01 falls one day before the acquisition window's start (2024-03-31 minus 29 days is 2024-03-02), which is the correct, intended behavior of a 30-day TRAILING window (29 days back plus the report date itself equals 30 calendar days total) and not a bug, but it is precisely the kind of boundary detail that must be stated alongside the number: a different, equally reasonable convention (30 days back, not 29) would have included that acquisition and produced a different CAC. The eligible spend total for paid_search, 1200, correctly includes the late-February spend rows because the 7-day lag pulls the eligible-spend window's start back to 2024-02-24.

Complexity

Both common table expression (CTE) branches are straightforward aggregations, $O(A + S)$ where $A$ is the number of acquisition rows and $S$ is the number of spend rows in their respective date-bounded scans, assuming indexes on acquisitions(acquired_at) and ad_spend(date) so the WHERE filters can range-seek rather than scan the full history. The final join between two small, channel-grouped aggregates is negligible in cost regardless of the underlying table sizes.

Edge cases

• A channel with spend but zero acquisitions in the window: the LEFT JOIN from spend_totals preserves it with acquisitions_count = 0 and cac = NULL (via NULLIF), which correctly signals "this channel spent money and acquired nobody in this window" rather than silently dividing by zero or dropping the channel from the report.
• A channel with acquisitions but no eligible spend rows (a free/organic channel, for instance): would be absent from spend_totals and therefore absent from this query's output entirely, since the query is spend-anchored (LEFT JOIN from spend to acquisitions, not the reverse); if organic/zero-spend channels need to appear with an explicit cac = 0 or NULL, the join direction needs to be reversed or a FULL OUTER JOIN used instead, a deliberate design choice worth stating.
• The acquisition at the exact boundary of the 30-day window (2024-03-01 in the pinned data, above): demonstrates the trailing-window convention concretely rather than abstractly, and is exactly the kind of edge that a stated, written-down window definition prevents two people from silently disagreeing about.

Trade-offs and pitfalls

• The spend-attribution lag is a modeling assumption, not a measured fact, unless it is derived from an actual attribution or path-length analysis on the product's real conversion timelines; picking a round number like 7 days without checking it against the typical consideration period risks systematically over- or under-crediting a channel whose real lag is materially different (a high-consideration B2B sale with a multi-week research phase would need a much longer lag than a 7-day default suggests).
• A common mistake is computing CAC per channel using each channel's own acquisitions and spend in isolation without checking whether a user shows multiple channels across their journey (in a single-touch acquisitions table, this table's own channel column has presumably already made that assignment, likely via a first-touch or last-touch rule); if so, that assignment rule directly determines this CAC number and should be stated alongside it, since a different assignment rule would shift spend and users between channels and produce different CAC figures for the exact same underlying data.
• Reporting a channel's CAC without also reporting its acquisition volume invites a wrong read: a channel with very low CAC but negligible volume is not automatically the channel to scale, since the marginal CAC of scaling it further is not the same as its current average CAC (diminishing returns on a channel's available audience are common), a distinction worth naming explicitly whenever a CAC comparison is used to argue for reallocating budget.

Direct answer

A click-up-but-purchase-flat pattern means the quick-buy button is successfully generating INTENT that the flow immediately after the click fails to convert, so the investigation has to isolate exactly which step after the click is leaking users, not just confirm that a leak exists. The three analyses named in the question each answer a different part of that question: SQL step-by-step funnel queries pinpoint WHICH step the drop happens at and how large it is, session replays show WHAT actually happened to a real user at that step, and funnel visualization communicates the pattern to stakeholders and makes a segment-level difference (device, load speed, user type) visible at a glance.

Structured elaboration

Possible leak reasons, as concrete, checkable hypotheses.

• Basket/cart flow friction. The quick-buy click may skip straight to a cart or checkout view that surprises the user (an unexpected item, size, or quantity default; an unclear way to edit the selection before committing), causing an immediate exit rather than a considered abandonment.
• Pricing friction. Shipping cost, tax, or a total price that is only revealed after the click, later than the user expected given the "quick" framing, is a well-established source of late-funnel abandonment; the mismatch between the promised speed and an unexpected cost reveal is especially damaging right after a button that promised simplicity.
• Checkout form friction. Too many required fields, a forced account-creation step, or unclear validation error messages, especially jarring when the button implied a near-instant purchase.
• Payment friction. A limited set of payment methods, a high decline rate on the offered methods, or an extra verification step (such as 3D Secure) that the user did not expect from a "quick" flow.
• Page-load/performance friction. The quick-buy click may route to a heavier page or modal (a full checkout view assembled on click, pulling in payment-provider scripts, tax calculation, and inventory checks synchronously) that loads noticeably slower than the rest of the site; a user who clicked expecting speed and then waits on a slow page is primed to abandon specifically BECAUSE the promise of "quick" was broken by the technical experience, not just by a UX or pricing objection. This is tested directly by joining frontend performance metrics (page-load time or time-to-interactive, captured client-side) to the funnel event stream and comparing step-to-step conversion between fast-loading and slow-loading sessions.
• Expectation mismatch from the button's own framing. If "quick-buy" implies a genuine one-click purchase but the actual flow is still multi-step, the button itself may be attracting clicks from users who are not ready for a multi-step commitment, inflating early-click volume with lower-intent traffic that was never going to complete regardless of flow quality; this is a distinct hypothesis from all the friction-based ones above, since the fix here is about the button's promise matching its actual behavior, not about removing friction from the flow itself.

The three analyses, applied to these hypotheses.

• SQL step-by-step funnel queries. Break the flow into named steps (click, basket view, checkout start, payment submitted, purchase complete) and compute session counts and step-to-step conversion at each boundary; this isolates exactly where the volume drops, which narrows which of the hypotheses above are even in play before any qualitative investigation starts.
• Session replays. For a sample of sessions that reached the leaking step but did not continue, watch the actual recorded session: a user who reaches checkout, pauses on the price line, and leaves supports the pricing-friction hypothesis directly; a user who clicks a form field repeatedly without progressing supports checkout-form friction; visible rage-clicking or a long pause with no interaction right after the page loads supports the performance hypothesis.
• Funnel visualization. Once the SQL analysis identifies the leaking step, visualize step-to-step conversion segmented by a relevant dimension (device type, checkout page-load-time bucket, new versus returning user) so a stakeholder can see at a glance not just THAT there is a drop, but which segment concentrates it, which is what turns a diagnosis into an actionable, scoped fix rather than a flow-wide redesign.

Worked example

A pinned, executed reproduction focused on the page-load-performance hypothesis specifically, since it is the least obvious of the leak reasons and the one most directly testable by joining a performance metric to the funnel: 200 sessions, all reaching quick_buy_click, basket_view, and checkout_start (matching the question's premise that early clicks are up), split evenly between a fast-loading checkout page (500-1100ms) and a slow-loading one (2800-3600ms), with completion behavior differing systematically after that point.

import sqlite3, random
random.seed(20260730)

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE sessions (session_id INTEGER PRIMARY KEY, load_ms INTEGER)")
cur.execute("CREATE TABLE funnel_events (session_id INTEGER, step_name TEXT, step_order INTEGER)")

# 100 fast-loading sessions (500-1100ms), 100 slow-loading sessions (2800-3600ms)
session_rows = [(sid, random.randint(500, 1100)) for sid in range(1, 101)]
session_rows += [(sid, random.randint(2800, 3600)) for sid in range(101, 201)]
cur.executemany("INSERT INTO sessions VALUES (?,?)", session_rows)

events = []
for sid in range(1, 201):
    events += [(sid, 'quick_buy_click', 1), (sid, 'basket_view', 2), (sid, 'checkout_start', 3)]

# 77 of the 100 fast sessions and 34 of the 100 slow sessions continue to payment_submitted
fast_ids = list(range(1, 101)); random.shuffle(fast_ids)
slow_ids = list(range(101, 201)); random.shuffle(slow_ids)
payment_ids = set(fast_ids[:77]) | set(slow_ids[:34])
for sid in payment_ids:
    events.append((sid, 'payment_submitted', 4))

# 93 of the 111 payment_submitted sessions complete the purchase
payment_list = list(payment_ids); random.shuffle(payment_list)
for sid in payment_list[:93]:
    events.append((sid, 'purchase_complete', 5))

cur.executemany("INSERT INTO funnel_events VALUES (?,?,?)", events)
conn.commit()

print(cur.execute("""
SELECT step_name, step_order, COUNT(DISTINCT session_id) AS sessions,
    ROUND(100.0 * COUNT(DISTINCT session_id) / (SELECT COUNT(*) FROM sessions), 1) AS pct_of_step1
FROM funnel_events GROUP BY step_name, step_order ORDER BY step_order;
""").fetchall())

print(cur.execute("""
WITH checkout_sessions AS (
    SELECT fe.session_id, s.load_ms,
        CASE WHEN s.load_ms < 2000 THEN 'fast (<2000ms)' ELSE 'slow (>=2000ms)' END AS load_bucket
    FROM funnel_events fe JOIN sessions s ON s.session_id = fe.session_id
    WHERE fe.step_name = 'checkout_start'
),
payment_sessions AS (SELECT DISTINCT session_id FROM funnel_events WHERE step_name = 'payment_submitted')
SELECT cs.load_bucket, COUNT(DISTINCT cs.session_id) AS checkout_start_sessions,
    COUNT(DISTINCT p.session_id) AS reached_payment,
    ROUND(100.0 * COUNT(DISTINCT p.session_id) / COUNT(DISTINCT cs.session_id), 1) AS pct_continued
FROM checkout_sessions cs LEFT JOIN payment_sessions p ON p.session_id = cs.session_id
GROUP BY cs.load_bucket ORDER BY cs.load_bucket ASC;
""").fetchall())

Step-by-step funnel, all sessions (SQLite, actually executed):

step_name | step_order | sessions | pct_of_step1
quick_buy_click | 1 | 200 | 100.0
basket_view | 2 | 200 | 100.0
checkout_start | 3 | 200 | 100.0
payment_submitted | 4 | 111 | 55.5
purchase_complete | 5 | 93 | 46.5

This confirms the question's premise, volume is healthy through checkout_start, then drops sharply, but does not yet say why. Joining the checkout page's recorded load time to the same sessions and splitting the checkout-to-payment conversion by load bucket:

load_bucket | checkout_start_sessions | reached_payment | pct_continued
fast (<2000ms) | 100 | 77 | 77.0
slow (>=2000ms) | 100 | 34 | 34.0

Sessions with a slow checkout-page load continue to payment at 34.0%, versus 77.0% for fast-loading sessions, a large, directly measured gap that turns "maybe performance is a factor" into a specific, quantified, testable hypothesis: it points the session-replay review specifically toward slow-load sessions (rather than a random sample of all non-continuers) and gives the funnel visualization a concrete segment (load-time bucket) to render.

Trade-offs and pitfalls

• A step-by-step SQL query only shows WHERE the drop happens, never WHY; treating a located step as a solved diagnosis without following up with session replays or a segment-level split (as in the worked example) risks shipping a fix aimed at the wrong root cause within that step.
• Session replay review is inherently a sample, not a census; a small number of replays can create a vivid but unrepresentative impression (a handful of dramatic rage-click sessions can feel like "the" answer even if the SQL-measured segment split points to a different, larger-volume cause), so use replays to generate and sharpen hypotheses, then confirm the hypothesis's actual scale with the quantitative funnel and segment data, not the reverse.
• Correlation between a slow load and lower completion (as in the worked example) does not by itself prove causation; a slower-loading session could correlate with a worse network connection or device, which independently correlates with lower purchase intent for unrelated reasons, so a genuinely rigorous conclusion needs either a controlled experiment (deliberately testing a faster checkout implementation) or at minimum ruling out the most obvious confounds (device type, connection quality) before attributing the full gap to load time alone.
• The expectation-mismatch hypothesis (the button's promise of "quick" not matching a still-multi-step reality) is easy to overlook because it does not show up as friction inside any single step; it requires comparing the quick-buy path's overall completion rate against a comparable non-quick-buy entry point into the same checkout flow, not just analyzing the quick-buy path in isolation.

Direct answer

Deduplication needs two layers, not one: prevention, so as few duplicates as possible are created in the first place (idempotency keys enforced on both the client and the server), and post-ingest cleanup in SQL or an extract-transform-load (ETL) job, because some duplicates will slip through prevention no matter how carefully it is built (retries during a network partition, at-least-once delivery from a message queue, multi-region replication). For the post-ingest layer, the actual matching technique, exact event_id, a fingerprint hash of key fields, or a time-window heuristic, is chosen based on what identifying information the event actually carries, trading exactness for coverage as you move from event_id toward time-window.

Structured elaboration

Prevention, client-side. Generate a unique event identifier (a UUID is standard) at the moment the event is created on the device, not at send time, and attach it to the event payload. Buffer the event locally (a queue or outbox) before transmission, so a retry caused by a timeout or app restart resends the exact same event_id rather than minting a new one for what is logically the same user action. This is the single highest-leverage prevention step, because it fixes duplication at its most common source: client-side retry logic.

Prevention, server-side. The ingestion endpoint enforces idempotency using that event_id: before accepting a write, check a fast key-value store (commonly Redis or an equivalent low-latency cache) keyed by event_id with a time-to-live (TTL) set to cover the expected retry window (for example, a few minutes). If the key already exists, return a success response without reprocessing rather than writing a second row; this makes the write operation idempotent from the client's point of view, retrying is always safe.

Post-ingest deduplication (SQL/ETL). Even with both prevention layers in place, duplicates still reach storage: legacy or third-party sources without a reliable event_id (webhooks, older client versions), at-least-once delivery semantics from the underlying event bus (Kafka, Kinesis, and similar systems guarantee delivery, not exactly-once arrival, unless the consumer adds its own dedup layer), or a bug in the idempotency-key generation itself. The standard SQL pattern is a windowed dedup: ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingestion_time ASC), keeping only rn = 1, run either as a scheduled batch job or as an upsert (MERGE/INSERT ... ON CONFLICT) directly against the target table keyed by event_id. In a streaming pipeline, the equivalent is a stateful deduplication operator (for example Flink's or Kafka Streams' state store) holding a bounded lookback window of recently seen ids, since holding every id ever seen in memory forever is not viable at scale.

Trade-offs among the three matching techniques.

How you validate this works. A dedup design is only as good as its test plan, and that plan has three layers. Unit tests on the dedup logic in isolation: feed the ROW_NUMBER SQL (or the equivalent stream-processor state logic) a fixed set of rows with known duplicates by event_id and by fingerprint, and assert the exact surviving row count and row identities, not just "count went down"; include a negative test where two genuinely distinct events happen to share a fingerprint's non-hashed fields but occur in different time buckets, and must both survive. Integration tests exercising the full client-to-server path: simulate a client network timeout that triggers a retry, and assert the server receives two requests carrying the same event_id but persists exactly one row, proving the idempotency-key enforcement (not just the offline SQL job) actually works end to end. Production validation via client-server reconciliation: compare a client-side "events sent" counter (or local outbox queue depth) against the server's ingested-and-deduplicated row count for the same time window and user population, run continuously rather than as a one-time check. A persistent gap is diagnostic: server counts running higher than client counts signals under-deduplication (a broken or missing event_id); server counts running lower signals either over-aggressive dedup (false-positive fingerprint collisions) or true event loss upstream of ingestion.

Worked example

Six raw event rows land in the table:

Running the exact-event_id ROW_NUMBER dedup (SQLite, actually executed):

CREATE TABLE raw_events (row_id INTEGER, event_id TEXT, user_id INTEGER, event_name TEXT, ingestion_time TEXT);
INSERT INTO raw_events VALUES
 (1,'evt-abc-123',1,'add_to_cart','2024-04-01 10:00:00.000'),
 (2,'evt-abc-123',1,'add_to_cart','2024-04-01 10:00:00.900'),
 (3,NULL,2,'purchase','2024-04-01 11:00:00.000'),
 (4,NULL,2,'purchase','2024-04-01 11:00:00.050'),
 (5,'evt-def-456',3,'add_to_cart','2024-04-01 12:00:00.000'),
 (6,'evt-ghi-789',3,'add_to_cart','2024-04-01 12:00:00.400');

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingestion_time ASC) AS rn
    FROM raw_events
    WHERE event_id IS NOT NULL
)
SELECT row_id, event_id, user_id, event_name, ingestion_time FROM ranked WHERE rn = 1 ORDER BY row_id;

row_id | event_id | user_id | event_name | ingestion_time
1 | evt-abc-123 | 1 | add_to_cart | 2024-04-01 10:00:00.000
5 | evt-def-456 | 3 | add_to_cart | 2024-04-01 12:00:00.000
6 | evt-ghi-789 | 3 | add_to_cart | 2024-04-01 12:00:00.400

Row 2 (the retried duplicate) is correctly dropped, and rows 5 and 6 both survive, because they carry distinct event_ids even though they are close in time, exactly the case a naive time-window rule would have wrongly collapsed. For the two rows with no event_id, a fingerprint of user_id | event_name | 1-second time bucket is used instead:

SELECT row_id, user_id, event_name, ingestion_time,
    user_id || '|' || event_name || '|' || CAST(strftime('%s', ingestion_time) AS INTEGER) AS fp
FROM raw_events WHERE event_id IS NULL;

row_id | user_id | event_name | ingestion_time | fp
3 | 2 | purchase | 2024-04-01 11:00:00.000 | 2|purchase|1711969200
4 | 2 | purchase | 2024-04-01 11:00:00.050 | 2|purchase|1711969200

Row 4, the 50-millisecond-later duplicate delivery of the same logical purchase, shares row 3's fingerprint bucket exactly, so it correctly collapses into row 3.

Trade-offs and pitfalls

• Deduplicating too aggressively is a real failure mode, not just under-deduplicating: an overly coarse time window or an under-specified fingerprint will silently merge two real user actions into one, which quietly deflates true funnel volume in a way that is much harder to notice than a duplicate-inflation bug, because the numbers just look "clean."
• Decide and document a tie-break rule for which row survives when duplicates are detected (first-seen by ingestion_time is standard, since it best represents when the user actually acted), because downstream joins that assume a unique event_id per logical event will behave inconsistently if the choice is arbitrary or changes between runs.
• A stream-processor's deduplication state store has a bounded lookback window by necessity; a duplicate arriving after that window has aged out of state will not be caught by streaming dedup and must rely on the batch/ETL pass as a backstop, so treat the two as complementary layers, not either/or.
• Prevention (idempotency keys) reduces the RATE of duplicates but does not eliminate the need for post-ingest dedup, since third-party and legacy sources will never carry a reliable id; a design that only implements one layer is incomplete regardless of which layer is chosen.

Direct answer

Survival analysis treats "has this user reached the next funnel step yet" as time-to-event data: each user's clock starts the moment they enter a step (for example add_to_cart) and stops either when they transition to the next step, the observed EVENT, or when the observation window closes first without that transition, RIGHT-CENSORING, meaning their true time-to-convert is unknown but at least as long as what was observed, not that they failed to convert. Kaplan-Meier estimates the resulting step-to-step survival curve, the probability a user is still stuck in the step after t hours or days, directly from a mix of event and censored observations with no assumption about the shape of the timing distribution. Cox proportional hazards extends this to a regression that quantifies how covariates (device type, whether a discount was shown, cart value) speed up or slow down the instantaneous rate of moving to the next step, the hazard.

Structured elaboration

Framing convention used throughout this answer (stated explicitly because it is easy to get backwards): the EVENT of interest is "the user advances to the next funnel step." So survival, $S(t)$, means "still stuck in the current step, has not yet advanced," and hazard means "the instantaneous rate of advancing." A HIGHER hazard is therefore a GOOD outcome here (faster progression through the funnel), the mirror image of a churn/retention hazard model where a higher hazard means the bad outcome of leaving the product. Keeping this direction fixed matters because the same machinery, applied to time-until-a-user-churns from the product over weeks or months, is a materially different question (open-ended, recurring engagement, no bounded next step) that belongs to a different analysis; everything below is scoped to the bounded, short time window between two specific funnel steps.

Right-censoring. A user who added an item to their cart two hours before the data was pulled and has not purchased yet is not known to have failed to convert, they might purchase in hour three. Two naive alternatives both distort the estimate: dropping every still-pending user entirely biases the sample toward fast converters (silently removing exactly the users who are taking longer, which looks like survivorship bias); labeling every still-pending user as "did not convert" manufactures a false negative and systematically inflates the apparent dropout rate. Right-censoring records the correct, honest fact instead: this user's true time-to-convert is $\ge$ 2 hours, exact value unknown, and folds that partial information into the estimate without pretending it is a completed observation.

Hazard function. The instantaneous rate of advancing to the next step at time $t$, conditional on not having advanced yet:

$h(t) = \lim_{\Delta t \to 0} \frac{P(t \le T < t + \Delta t \mid T \ge t)}{\Delta t}$

Survival and hazard are two views of the same underlying process, related by:

$S(t) = \exp\left(-\int_0^t h(u)\,du\right)$

$S(t)$ answers "what fraction are still stuck at time $t$"; $h(t)$ answers "of the people still stuck right now, how fast are they leaving that state at this instant." A hazard that rises sharply right after step entry and then flattens describes a step where users either convert quickly or settle into a long-tail lingering pattern, a shape a single summary conversion rate cannot show at all.

Kaplan-Meier. A non-parametric, step-function estimator of $S(t)$, the product-limit estimator:

$S(t) = \prod_{t_i \le t} \left(1 - \frac{d_i}{n_i}\right)$

where $t_i$ ranges over the distinct observed event times, $n_i$ is the number of users still at risk (still in the step, not yet converted or censored) immediately before $t_i$, and $d_i$ is the number who convert exactly at $t_i$. At each event time the survival probability accumulated so far is multiplied by "the fraction of the currently-at-risk users who did NOT convert right now," which is what makes it a product-LIMIT estimator. Because it makes no assumption about the shape of the time-to-convert distribution (not exponential, not normal), it is a good default for funnel-step timing, which is rarely a clean parametric shape in practice (a spike of near-immediate conversions right after step entry, then a long, irregular tail).

Cox proportional hazards. A semi-parametric regression on the hazard itself:

$h(t \mid X_i) = h_0(t)\exp\left(\beta_1 X_{i1} + \beta_2 X_{i2} + \cdots + \beta_p X_{ip}\right)$

$h_0(t)$, the baseline hazard, is left unspecified (nonparametric, estimated implicitly); only the multiplicative effect of each covariate is estimated, via the partial-likelihood method. The proportional-hazards assumption is that a covariate's effect is a constant multiplier on the hazard at every $t$, it can shift the whole curve up or down but not change its shape over time. The resulting hazard ratio between two users differing only in covariates,

$\text{HR} = \frac{h(t \mid X_i)}{h(t \mid X_j)} = \exp\left(\beta^{\top}(X_i - X_j)\right)$

directly answers "which covariates increase hazard": a hazard ratio above 1 for, say, "discount shown" means users who saw a discount advance to purchase faster at every point in time than otherwise-identical users who did not (their hazard, in this event-is-advancing framing, is higher, meaning they linger less and convert sooner); a ratio below 1 means that covariate slows progression down, showing up as more of that group still stuck, or censored, later in the window.

Worked example

Ten users add an item to their cart; time is hours until purchase, or until the observation window closes with no purchase yet (right-censored). Pinned, hand-verified data:

Applying the product-limit formula at each distinct event time (censored observations reduce the risk set for later times without themselves producing a drop in $S(t)$):

# Kaplan-Meier (product-limit) estimator for funnel-step dropout: time is hours
# from add_to_cart until purchase (the event); right-censoring occurs when the
# observation window closes before a user purchases (we simply do not yet know
# if/when they would have converted, not that they "churned").
#
# (time_hours, event) where event=1 means "reached purchase" (an observed
# transition), event=0 means "right-censored" (still in add_to_cart when the
# data was pulled, next-step status genuinely unknown after this point).
observations = [
    (1, 1),   # user 1: purchased at 1h
    (2, 1),   # user 2: purchased at 2h
    (2, 1),   # user 3: purchased at 2h (tie)
    (3, 0),   # user 4: censored at 3h (window closed, still in add_to_cart)
    (5, 1),   # user 5: purchased at 5h
    (5, 1),   # user 6: purchased at 5h (tie)
    (7, 0),   # user 7: censored at 7h
    (9, 1),   # user 8: purchased at 9h
    (9, 0),   # user 9: censored at 9h (tie with an event: standard convention
              #          treats the censoring as occurring just after events at
              #          the same recorded time, so it stays in the risk set
              #          used to compute that time's hazard)
    (10, 0),  # user 10: censored at 10h
]

n = len(observations)
distinct_event_times = sorted({t for (t, e) in observations if e == 1})

# The risk set at time t is everyone whose observation time is >= t: an event
# at e keeps that subject at risk for every t <= e, and a censoring at c keeps
# that subject at risk for every t <= c (it only removes them for t > c). This
# is what correctly drops user 4 (censored at t=3) out of the risk set used
# for t=5, even though t=3 is not itself a distinct event time.
survival = 1.0
rows = []  # (time, at_risk, d_events, hazard, survival)

for t in distinct_event_times:
    at_risk = sum(1 for (ti, ei) in observations if ti >= t)
    d = sum(1 for (ti, ei) in observations if ti == t and ei == 1)
    hazard = d / at_risk
    survival *= (1 - hazard)
    rows.append((t, at_risk, d, hazard, survival))

print("=== Kaplan-Meier product-limit table ===")
print(f"{'t (hrs)':>8} {'n at risk':>10} {'d events':>9} {'hazard d/n':>11} {'S(t)':>10}")
for (t, n_risk, d, hazard, surv) in rows:
    print(f"{t:>8} {n_risk:>10} {d:>9} {hazard:>11.4f} {surv:>10.6f}")

print("\n=== Sanity checks ===")
print(f"total observations n = {n}")
print(f"distinct event times = {distinct_event_times}")
print(f"S(t) is non-increasing: {all(rows[i][4] >= rows[i+1][4] for i in range(len(rows)-1))}")
print(f"final S(9) = {rows[-1][4]:.6f}  (probability a user is still in add_to_cart, i.e. has NOT yet purchased, more than 9 hours after adding to cart)")

# Cross-check every step by hand-derived fractions
assert rows[0] == (1, 10, 1, 1/10, 0.9)
assert abs(rows[1][4] - (0.9 * (7/9))) < 1e-9
assert abs(rows[2][4] - (0.9 * (7/9) * (4/6))) < 1e-9
assert abs(rows[3][4] - (0.9 * (7/9) * (4/6) * (2/3))) < 1e-9
print("hand-derived fractions at each step match the code's output: True")

Output (actually executed with python3, standard library only):

=== Kaplan-Meier product-limit table ===
 t (hrs)  n at risk  d events  hazard d/n       S(t)
       1         10         1      0.1000   0.900000
       2          9         2      0.2222   0.700000
       5          6         2      0.3333   0.466667
       9          3         1      0.3333   0.311111

=== Sanity checks ===
total observations n = 10
distinct event times = [1, 2, 5, 9]
S(t) is non-increasing: True
final S(9) = 0.311111  (probability a user is still in add_to_cart, i.e. has NOT yet purchased, more than 9 hours after adding to cart)
hand-derived fractions at each step match the code's output: True

Reading the table: at $t=1$, 10 users are at risk and 1 converts, so $S(1) = 0.9$. At $t=2$, 9 remain at risk (the 1 hour-1 convert left the risk set) and 2 convert together:

$S(2) = 0.9 \times \left(1 - \frac{2}{9}\right) = 0.9 \times \frac{7}{9} = 0.7$

At $t=5$, the risk set has dropped to 6, not 7, because user 4's censoring at $t=3$ removed them from the risk set for every later time even though $t=3$ produced no event of its own; this is exactly why the risk set is defined as "everyone whose observed time is $\ge t$," not "everyone remaining after the last event." At $t=9$, user 9's censoring is tied with user 8's event; the standard convention keeps the tied censoring in the risk set used to compute that time's hazard (it is treated as occurring an instant after any tied event), giving a risk set of 3 and a hazard of $1/3$. The final estimate, $S(9) \approx 0.311$, says roughly 31 percent of users who add an item to their cart are still sitting there, unconverted, more than 9 hours later; a product team could read the same curve to find the median time-to-purchase (the first $t$ where $S(t)$ crosses 0.5, here somewhere between $t=2$ and $t=5$) or compare it against a second curve from a checkout-flow experiment using a log-rank test to see whether the whole timing distribution shifted, not just the final conversion percentage.

Trade-offs and pitfalls

• Window length drives precision, not just coverage. If the observation window is short relative to typical time-to-convert, most users end up censored and the tail of the curve (later $t$) is estimated from a shrinking, noisy risk set; report and act on the well-supported early part of the curve and be explicit that late-window estimates carry real uncertainty (a formal treatment uses Greenwood's formula for the standard error at each $t$, worth reaching for once the curve is going into a dashboard rather than an exploratory read).
• The proportional-hazards assumption can fail. A covariate's effect on drop-off speed is not guaranteed to be constant over time (a checkout-page redesign might matter enormously in the first hour and barely at all by hour 10); check it (for example via Schoenfeld residuals) before trusting a single hazard ratio, and consider a stratified Cox model or a time-varying covariate if it does not hold.
• Common mistake: this is not a churn model. The same product-limit machinery applied to time until a user stops returning to the product over weeks or months is a genuinely different question, open-ended and recurring rather than bounded between two adjacent funnel steps; keep $T$ defined as hours or days since entering this specific step, never let it drift into a whole-account-lifetime clock.
• Common mistake: mislabeling censored users as failures, which was covered above as a modeling error, resurfaces as a reporting error too: a dashboard that shows "$X\%$ dropped off" from a raw event/no-event count on data pulled mid-window is quietly making the fail-label mistake at the reporting layer even if the underlying model got right-censoring correct.
• Ties matter for Cox specifically. Kaplan-Meier's handling of tied event times is standardized, but Cox's partial likelihood has two common approximations for ties (Breslow and Efron); with many ties (coarse timestamp granularity, for example daily rather than hourly logging) the choice can shift coefficients meaningfully, so know which one a library defaults to.
• Events-per-covariate matters for Cox stability. With a small absolute number of observed conversions relative to the number of covariates in the model, coefficient estimates get unstable; a common rule of thumb is wanting on the order of ten observed events per covariate before trusting the fitted hazard ratios.

Direct answer

Aggregate step-X-to-step-Y drop-off into two windows, the last 7 days and the prior 3 weeks (21 days), then run a two-proportion z-test comparing the drop-off RATE (not the raw count) between the two windows: if the p-value falls below your alpha and the confidence interval for the difference excludes zero, the increase is statistically significant, not just noise. A two-proportion z-test is the right test here because drop-off is a binary per-user outcome (did they proceed or not) aggregated into a rate, which is exactly the shape a two-proportion test is built for; a chi-square test of independence on the same 2x2 table would give an equivalent conclusion, since a two-sided two-proportion z-test and a 2x2 chi-square test are algebraically the same test viewed two ways.

Structured elaboration

Why a two-proportion test, and why not something else. Daily funnel counts give, for each day, how many users reached step X and how many of those reached step Y; aggregated over a window, this is a count of successes (reached Y) out of a count of trials (reached X), a binomial-shaped quantity. Comparing two such rates (recent window vs. prior window) for a real difference is precisely the two-proportion z-test's use case. This is deliberately narrower than general time-series anomaly or change-point detection (control charts, CUSUM, cumulative sum control charting that tracks a running sum of deviations from a baseline to catch a sustained small shift, and Bayesian change-point models): those tools are built to monitor an arbitrary metric continuously over time and flag a shift wherever it occurs, appropriate general-purpose monitoring infrastructure for ANY metric. This question is scoped tighter: one specific step-pair, one specific recent-window-versus-baseline comparison, already identified as the thing to check, which is exactly what a single hypothesis test is for, no continuous monitoring machinery required.

The two windows. Recent: the last 7 days. Prior baseline: the 3 weeks (21 days) immediately preceding the recent window, adjacent, not overlapping and not gapped, so the comparison is "how has this week performed relative to the established recent baseline," not confounded by an arbitrary gap between the two periods.

The test, formally. Let $p_{\text{recent}}$ and $p_{\text{prior}}$ be the drop-off rates (fraction of step-X users who did NOT reach step Y) in each window, with $x_{\text{recent}}$, $x_{\text{prior}}$ the step-X user counts in each window. The null hypothesis is that the true drop-off rate is the same in both windows:

Pooled proportion and pooled standard error, used for the hypothesis test itself:

The confidence interval for the DIFFERENCE uses the unpooled standard error instead (standard practice, since a confidence interval should not assume the null is true the way the hypothesis test's pooled SE does):

Worked example

Synthetic 8 weeks (56 days) of daily step-X and step-Y counts, engineered with a stable ~40% baseline drop-off rate for the first 49 days and a genuine, injected regression to ~48% drop-off for the final 7 days (simulating, for example, a checkout-page bug shipped a week ago).

SQL aggregation (executed against SQLite as a stand-in for the target warehouse engine):

CREATE TABLE daily_funnel_counts (day TEXT, step_x_users INTEGER, step_y_users INTEGER);

INSERT INTO daily_funnel_counts VALUES
 ('2026-01-01',4879,2879),
 ('2026-01-02',4872,2832),
 ('2026-01-03',5276,3144),
 ('2026-01-04',4760,2794),
 ('2026-01-05',5037,3036),
 ('2026-01-06',5212,3121),
 ('2026-01-07',5064,2952),
 ('2026-01-08',5230,3085),
 ('2026-01-09',5002,3091),
 ('2026-01-10',4901,2887),
 ('2026-01-11',4986,2941),
 ('2026-01-12',4766,2855),
 ('2026-01-13',5102,3006),
 ('2026-01-14',5104,3098),
 ('2026-01-15',5197,3126),
 ('2026-01-16',5155,2999),
 ('2026-01-17',5055,2952),
 ('2026-01-18',4826,2981),
 ('2026-01-19',4976,2935),
 ('2026-01-20',5061,3054),
 ('2026-01-21',4825,2834),
 ('2026-01-22',4702,2794),
 ('2026-01-23',4773,2861),
 ('2026-01-24',5143,3000),
 ('2026-01-25',5021,3058),
 ('2026-01-26',5095,3124),
 ('2026-01-27',5041,2969),
 ('2026-01-28',5292,3201),
 ('2026-01-29',5000,2991),
 ('2026-01-30',5000,2991),
 ('2026-01-31',5000,2991),
 ('2026-02-01',5000,2991),
 ('2026-02-02',5000,2991),
 ('2026-02-03',5000,2991),
 ('2026-02-04',5000,2991),
 ('2026-02-05',5000,2991),
 ('2026-02-06',5000,2991),
 ('2026-02-07',5000,2991),
 ('2026-02-08',5000,2991),
 ('2026-02-09',5000,2991),
 ('2026-02-10',5000,2991),
 ('2026-02-11',5000,2991),
 ('2026-02-12',5000,2991),
 ('2026-02-13',5000,2991),
 ('2026-02-14',5000,2991),
 ('2026-02-15',5000,2991),
 ('2026-02-16',5000,2991),
 ('2026-02-17',5000,2991),
 ('2026-02-18',5020,3011),
 ('2026-02-19',4986,2601),
 ('2026-02-20',4986,2601),
 ('2026-02-21',4986,2601),
 ('2026-02-22',4986,2601),
 ('2026-02-23',4986,2601),
 ('2026-02-24',4986,2601),
 ('2026-02-25',4992,2607);

WITH bounds AS (
    SELECT MAX(day) AS max_day FROM daily_funnel_counts
),
recent AS (
    SELECT SUM(step_x_users) AS x_total, SUM(step_y_users) AS y_total, COUNT(*) AS n_days
    FROM daily_funnel_counts, bounds
    WHERE day > date(bounds.max_day, '-7 days')
),
prior AS (
    SELECT SUM(step_x_users) AS x_total, SUM(step_y_users) AS y_total, COUNT(*) AS n_days
    FROM daily_funnel_counts, bounds
    WHERE day <= date(bounds.max_day, '-7 days')
      AND day > date(bounds.max_day, '-28 days')
)
SELECT 'recent_7d' AS window, x_total, y_total, n_days,
       ROUND(1.0 - 1.0 * y_total / x_total, 4) AS dropoff_rate
FROM recent
UNION ALL
SELECT 'prior_21d' AS window, x_total, y_total, n_days,
       ROUND(1.0 - 1.0 * y_total / x_total, 4) AS dropoff_rate
FROM prior;

Output (actually executed):

window         x_total   y_total  n_days   dropoff
recent_7d        34908     18213       7    0.4783
prior_21d       105020     62831      21    0.4017

Significance test, computed in Python from the aggregated counts above (actually executed):

import math

x_recent, y_recent, n_recent = 34908, 18213, 7
x_prior, y_prior, n_prior = 105020, 62831, 21

fail_recent = x_recent - y_recent
fail_prior = x_prior - y_prior

p_recent = fail_recent / x_recent
p_prior = fail_prior / x_prior
diff = p_recent - p_prior

p_pool = (fail_recent + fail_prior) / (x_recent + x_prior)
se_pool = math.sqrt(p_pool * (1 - p_pool) * (1 / x_recent + 1 / x_prior))
z = diff / se_pool
p_value = math.erfc(abs(z) / math.sqrt(2))

se_unpooled = math.sqrt(p_recent * (1 - p_recent) / x_recent + p_prior * (1 - p_prior) / x_prior)
ci_lo = diff - 1.96 * se_unpooled
ci_hi = diff + 1.96 * se_unpooled

print(f"Drop-off rate, recent 7 days: {p_recent:.4f}  ({fail_recent:,}/{x_recent:,})")
print(f"Drop-off rate, prior 21 days: {p_prior:.4f}  ({fail_prior:,}/{x_prior:,})")
print(f"Observed difference (recent - prior): {diff:.4f}")
print(f"Pooled proportion: {p_pool:.4f}")
print(f"Pooled SE: {se_pool:.6f}")
print(f"z statistic: {z:.3f}")
print(f"two-tailed p-value: {p_value:.6f}")
print(f"95% CI for the difference (unpooled SE): [{ci_lo:.4f}, {ci_hi:.4f}]")
print(f"CI excludes 0: {ci_lo > 0 or ci_hi < 0}")

alpha = 0.05
print(f"At alpha={alpha}: {'REJECT H0 (drop-off increase is statistically significant)' if p_value < alpha else 'FAIL TO REJECT H0'}")

Output (actually executed):

Drop-off rate, recent 7 days: 0.4783  (16,695/34,908)
Drop-off rate, prior 21 days: 0.4017  (42,189/105,020)
Observed difference (recent - prior): 0.0765
Pooled proportion: 0.4208
Pooled SE: 0.003050
z statistic: 25.093
two-tailed p-value: 0.000000
95% CI for the difference (unpooled SE): [0.0705, 0.0826]
CI excludes 0: True
At alpha=0.05: REJECT H0 (drop-off increase is statistically significant)

The 7.65-percentage-point increase (40.2% to 47.8%) is overwhelmingly significant here (z well above the roughly 1.96 threshold for $\alpha=0.05$, p-value effectively zero, confidence interval [7.05, 8.26] percentage points, comfortably excluding zero), consistent with the injected regression being large relative to this funnel's daily traffic volume.

Confidence-interval extension. The 95% confidence interval above, [0.0705, 0.0826], is the direct answer to "how much did drop-off actually increase, with uncertainty," a more complete and more decision-useful statement than the p-value alone: a p-value only says whether the increase is distinguishable from zero, while the confidence interval says how big it plausibly is, which is what determines whether the underlying cause is worth an urgent fix versus a lower-priority investigation.

Complexity

The SQL aggregation is two $O(d)$ scans over $d$ days of pre-aggregated daily counts (56 rows here), trivial at this grain regardless of how many raw events fed into those daily counts upstream; the actual cost lives in whatever job produced the daily rollup, not in this query. The z-test and confidence-interval computation are $O(1)$ arithmetic once the two window totals are in hand.

Edge cases

• A step-X count of zero in either window makes the drop-off rate undefined (division by zero); guard for this explicitly (skip or flag the window) rather than letting the query silently return NULL or error deep in a dashboard pipeline.
• Very small counts in either window (a low-traffic step, or a short window) push the normal approximation the z-test relies on toward invalidity; a common rule of thumb is requiring at least 5 to 10 expected successes and failures in each group, worth checking before trusting the p-value at low volume.
• A window boundary that splits a single day (a partial day of data at the report's exact cutoff) can bias the comparison if included inconsistently; anchoring cleanly on whole calendar days, as the worked example does, avoids this.

Trade-offs and pitfalls

• Common mistake: comparing raw drop-off COUNTS between the two windows instead of RATES. Because the windows have different total traffic (34,908 users in 7 days versus 105,020 in 21 days), a naive count comparison is meaningless without normalizing by the number of users who reached step X in each window; the rate, not the count, is the quantity the hypothesis is actually about.
• Traffic-mix confounds. If the recent 7 days happens to include a different marketing-channel mix, device mix, or day-of-week composition than the prior 21 days (a holiday, a new ad campaign launch), the significant difference detected here could reflect a MIX shift rather than a genuine step-level regression; segmenting the same test by channel or device before concluding "the step itself broke" is a reasonable next check.
• A statistically significant result at this scale does not by itself prove urgency. With tens of thousands of users per window, even a modest, business-marginal shift can reach significance; pair the p-value and confidence interval with the ABSOLUTE revenue or user impact of the shift before treating it as a fire drill.
• This test is intentionally narrow, not a substitute for general monitoring. It answers "is THIS specific, already-suspected step-pair's recent change real," on demand, for one comparison. It is not a replacement for continuous anomaly-detection infrastructure that watches every step-pair automatically and flags whichever one moves: that is a general-purpose monitoring system, built once with control charts or change-point models and run continuously across every metric, not the single, already-scoped hypothesis test this question asks for.