Conversion Funnel Optimization Questions

Direct answer

Treat the rolled-out regions as the treatment group and choose control regions on the basis of pre-period TREND similarity, not just level, since difference-in-differences (DiD) validity rests entirely on the parallel-trends assumption: absent the new checkout, treated and control regions would have kept moving together. Estimate the effect from the treatment-by-post-period interaction term in a panel regression, test parallel trends specifically against pre-treatment data (never the post period, which is exactly what you are trying to explain), add covariates to sharpen precision and control for observable differences, and treat spillovers and staggered rollout timing as real threats to the estimate that need an explicit fix, not caveats to mention in passing.

Structured elaboration

Choosing control regions. Two criteria, not one: similar pre-period LEVEL of conversion (so the regions are roughly comparable populations) and, more important, similar pre-period TREND (so parallel trends has a real chance of holding). A region with a matching current conversion rate but a very different growth trajectory is a poor control even though its level looks right. Look at several pre-periods, not one point, and either inspect the pre-trends directly or, when no single natural comparison region fits well, build a synthetic control: a weighted combination of several candidate regions chosen so the weighted composite tracks the treated region's own pre-period trajectory as closely as possible.

The DiD estimator. On a panel of (region, period) observations:

$Y_{it} = \beta_0 + \beta_1 \text{Treated}_i + \beta_2 \text{Post}_t + \beta_3 (\text{Treated}_i \times \text{Post}_t) + \varepsilon_{it}$

$\beta_3$, the coefficient on the treatment-by-post interaction, is the DiD estimate: the treated group's change from pre to post, net of whatever change the control group experienced over the same window. Equivalently, as a difference of means:

$\hat\tau_{DiD} = (\bar{Y}_{T,\text{post}} - \bar{Y}_{T,\text{pre}}) - (\bar{Y}_{C,\text{post}} - \bar{Y}_{C,\text{pre}})$

Both forms give the identical number on a simple two-group, two-period panel; the regression form is what extends cleanly once covariates, more than two groups, or staggered timing enter the picture.

Testing the parallel-trends assumption. The assumption describes what would have happened WITHOUT treatment, which is unobservable in the post period by definition, so it can only be tested indirectly using data from before treatment. With at least two pre-periods, compute the identical treated-minus-control difference for a pre-period-to-pre-period change, a placebo DiD using only data from before the checkout change existed, and check that it is close to zero. More formally, regress the outcome on region and period fixed effects plus a set of leads of the treatment indicator (interactions of "will eventually be treated" with each pre-period), and test whether those lead coefficients are jointly indistinguishable from zero. A large, non-zero pre-trend difference is direct evidence the two groups were already diverging before the rollout, which would bias the post-period DiD estimate regardless of how carefully that later step is done.

Covariates. Add region-level or time-varying covariates, regional marketing spend, seasonality controls, a competitor's local presence, to net out differences between treated and control regions that shift over time in a way correlated with rollout timing. This tightens standard errors by explaining more outcome variance and, where a covariate is a genuine confounder, reduces bias from imperfect region selection. One firm rule: never control for a variable that is itself downstream of the treatment (site traffic measured after the new checkout launched, for example); conditioning on a post-treatment outcome removes part of the very effect being measured.

Spillovers. If a "control" region's users are indirectly exposed, word of mouth, a shared app build, cross-region browsing, the control group is contaminated and the DiD estimate is biased toward zero, understating the true effect because the control partially received treatment too. Diagnose by checking whether control regions show any post-period movement unexplained by their own pre-trend; mitigate by favoring control regions with minimal interaction with treated ones, or by explicitly modeling exposure intensity in the control group rather than assuming it is cleanly untreated.

Staggered rollouts. When regions are treated at different times rather than all at once, the standard two-way fixed-effects DiD estimator can be badly biased: already-treated regions end up implicitly serving as part of the comparison for later-treated regions, which can put negative weight on some genuine treatment effects in the aggregated estimate. The practical fix is a staggered-adoption-robust estimator that compares each newly-treated cohort only against not-yet-treated regions at that specific moment in time, then aggregates those clean two-by-two comparisons, rather than running one pooled two-way fixed-effects regression across every region and period at once.

Worked example

A small, pinned synthetic panel: four regions, three periods (two pre-treatment, one post). Regions A and B get the new checkout at "post"; C and D are controls, never treated.

import numpy as np

# Pinned synthetic panel: 4 regions x 3 periods (pre2, pre1, post).
# Regions A, B get the new checkout flow at "post"; C, D are controls, never treated.
# Outcome = conversion rate, in percentage points.
data = {
    # region: (pre2, pre1, post)
    "A": (6.0, 7.0, 10.5),
    "B": (5.0, 6.0, 9.5),
    "C": (6.2, 7.2, 8.2),
    "D": (5.4, 6.4, 7.4),
}
treated_regions = {"A", "B"}

# --- 1. Pre-trend (parallel trends) placebo check: pre2 -> pre1, before any treatment exists ---
treated_pre_trend = np.mean([data[r][1] - data[r][0] for r in treated_regions])
control_pre_trend = np.mean([data[r][1] - data[r][0] for r in data if r not in treated_regions])
placebo_did = treated_pre_trend - control_pre_trend
print("=== Parallel-trends placebo check (pre2 -> pre1, pre-treatment) ===")
print(f"treated regions average pre-trend: {treated_pre_trend:+.3f} pts")
print(f"control regions average pre-trend: {control_pre_trend:+.3f} pts")
print(f"placebo DiD (should be ~0 if parallel trends holds): {placebo_did:+.3f} pts")

# --- 2. The actual 2x2 DiD estimate: pre1 (last pre-period) vs post ---
treated_pre1 = np.mean([data[r][1] for r in treated_regions])
treated_post = np.mean([data[r][2] for r in treated_regions])
control_pre1 = np.mean([data[r][1] for r in data if r not in treated_regions])
control_post = np.mean([data[r][2] for r in data if r not in treated_regions])

treated_diff = treated_post - treated_pre1
control_diff = control_post - control_pre1
did_estimate = treated_diff - control_diff

print("\n=== 2x2 difference-in-differences (means) ===")
print(f"treated: pre1={treated_pre1:.3f}, post={treated_post:.3f}, diff={treated_diff:+.3f}")
print(f"control: pre1={control_pre1:.3f}, post={control_post:.3f}, diff={control_diff:+.3f}")
print(f"DiD estimate (treated diff - control diff): {did_estimate:+.3f} percentage points")

# --- 3. Confirm the same estimate via the regression form:
#         y = b0 + b1*treated + b2*post + b3*(treated*post) + e
#     using the 8 region-period observations (pre1, post only, matching the 2x2 window above).
rows = []
for r in data:
    for period, val in zip(["pre1", "post"], data[r][1:]):
        treated = 1.0 if r in treated_regions else 0.0
        post = 1.0 if period == "post" else 0.0
        rows.append((treated, post, treated * post, val))

X = np.array([[1.0, t, p, tp] for (t, p, tp, y) in rows])
y = np.array([y for (t, p, tp, y) in rows])
beta, *_ = np.linalg.lstsq(X, y, rcond=None)
b0, b1, b2, b3 = beta
print("\n=== OLS regression: y = b0 + b1*treated + b2*post + b3*(treated*post) ===")
print(f"b0 (control, pre)            = {b0:.3f}")
print(f"b1 (treated vs control, pre) = {b1:.3f}")
print(f"b2 (post vs pre, control)    = {b2:.3f}")
print(f"b3 (DiD estimate: treated*post interaction) = {b3:.3f}")
print(f"b3 matches the 2x2 mean-difference estimate: {abs(b3 - did_estimate) < 1e-9}")

# --- 4. True injected effect, for reference (this is synthetic data; the true
#     effect was built into the generator, so we can confirm the estimator recovers it) ---
true_effect = 2.5
print(f"\ntrue injected treatment effect (by construction of the synthetic data): {true_effect:.3f}")
print(f"DiD estimate recovers the true effect exactly: {abs(did_estimate - true_effect) < 1e-9}")

Output (actually executed with python3 and numpy):

=== Parallel-trends placebo check (pre2 -> pre1, pre-treatment) ===
treated regions average pre-trend: +1.000 pts
control regions average pre-trend: +1.000 pts
placebo DiD (should be ~0 if parallel trends holds): +0.000 pts

=== 2x2 difference-in-differences (means) ===
treated: pre1=6.500, post=10.000, diff=+3.500
control: pre1=6.800, post=7.800, diff=+1.000
DiD estimate (treated diff - control diff): +2.500 percentage points

=== OLS regression: y = b0 + b1*treated + b2*post + b3*(treated*post) ===
b0 (control, pre)            = 6.800
b1 (treated vs control, pre) = -0.300
b2 (post vs pre, control)    = 1.000
b3 (DiD estimate: treated*post interaction) = 2.500
b3 matches the 2x2 mean-difference estimate: True

true injected treatment effect (by construction of the synthetic data): 2.500
DiD estimate recovers the true effect exactly: True

The placebo check on the two pre-periods (before any treatment existed) shows both groups moved by an identical 1.0 point, so the parallel-trends assumption holds cleanly on this synthetic data, exactly the check that should be run and reported before trusting the headline number. The 2x2 mean-difference estimate and the regression's interaction coefficient $\hat\beta_3$ agree exactly at 2.5 percentage points, confirming the two equivalent forms of the estimator described above produce the same answer, and because this is synthetic data with a known injected effect of 2.5 points, the estimator's recovery of that exact value is a genuine correctness check on the SQL-adjacent arithmetic itself, not just a plausible-looking number.

Trade-offs and pitfalls

• Common mistake: choosing a control region on convenience rather than pre-trend similarity. A region picked because it is easy to pull data for, not because its trajectory matches, turns the "treatment effect" into mostly a measurement of how different the two regions already were.
• Common mistake: testing parallel trends on a single pre-period pair and calling it settled. A longer pre-period window with a formal joint test on several lead coefficients is far more convincing than eyeballing two points; two periods can look parallel by chance.
• Standard errors need to account for within-region correlation. Outcomes for the same region across periods are correlated (serial correlation), so plain ordinary least squares (OLS) standard errors understate real uncertainty; cluster standard errors at the region level.
• A small number of regions makes cluster-robust standard errors unreliable regardless of clustering. With only a handful of treated and control regions, consider randomization inference or a wild-cluster bootstrap instead of relying on asymptotic cluster-robust standard errors.
• "No significant pre-trend" is not the same claim as "trends are definitely parallel." A short or noisy pre-period can fail to detect a real divergence; that is a statistical-power problem, not evidence the assumption holds.

Direct answer

This is scored on whether the candidate can walk an interviewer through a real incident with the discipline of an actual post-mortem, not on how dramatic the bug was. Structure it with Situation, Task, Action, Result (STAR): the context the tracking sat in and why it mattered, how the discrepancy was actually noticed (not "we just knew"), the concrete diagnostic steps that pinned the root cause and the fix that followed, exactly how the business impact was communicated to stakeholders once it was understood, and the specific preventive measure put in place afterward so the same class of bug cannot recur silently again.

Structured elaboration

Situation (context). State what the tracking was being used for and why a bug in it was consequential: a decision (a launch go/no-go, a budget reallocation across channels, a pricing change) was made, or was about to be made, using a metric that turned out to be wrong. Name the specific metric and the specific decision it fed, not "our metrics were affected in general," since the business-impact weight of this story depends entirely on what was actually at stake.

Task. The candidate's specific role in discovering and resolving it. Ideally something owned directly (the person who found the anomaly, or who was assigned to run it down), not a bug someone else diagnosed that the candidate only heard about secondhand.

Action, detection. How the discrepancy was actually noticed matters to the story's credibility: a number that looked implausible against a sanity check (a conversion rate suddenly higher than any comparable historical period, or a channel showing zero volume when spend clearly continued), a cross-check against an independent source (a payment processor's own reporting disagreeing with the internal dashboard), or a stakeholder asking a question the data could not actually answer consistently. Naming the specific trigger, rather than a vague "I noticed something was off," is what separates a credible account from a generic one.

Action, diagnosis and fix. Walk through the actual debugging path: which layer turned out to be at fault (a client-side instrumentation bug, an error in the extract-transform-load or ETL data-processing step, a broken join, a misconfigured filter in the reporting layer), how that was isolated (comparing counts at successive stages of the pipeline, checking a recent deploy or schema change against the timing of when the numbers started drifting), and what the fix specifically was (a corrected event definition, a patched join key, a backfill of affected data). This is the part interviewers probe hardest, so it needs to show real technical reasoning, not just "we found the bug and fixed it."

Action, stakeholder communication. Describe how the impact was actually communicated once understood: what was said, to whom, and how directly the "this changes the decision we were about to make" implication was stated rather than buried in a technical caveat. A strong version of this shows the candidate proactively surfacing a finding that reflected badly on the team's own prior reporting, rather than only fixing it quietly.

Result, preventive measures. What changed afterward so the same failure mode cannot recur unnoticed: a monitoring check or alert on the specific metric or pipeline stage that broke, a reconciliation process comparing two independent sources on a schedule, a code-review or schema-change checklist item, or a broader instrumentation-testing practice. A story that ends at "we fixed the bug" without this part is missing the piece that shows the candidate thinks about systems, not just incidents.

Worked example

An illustrative version of this story, built to show the shape rather than as a claim of a specific real outcome. At a company running a multi-step signup funnel, the marketing team was preparing to reallocate a meaningful share of acquisition spend toward the channel that appeared to be converting best according to the internal dashboard. Before the reallocation was finalized, the metric looked implausible on a routine sanity check: that channel's conversion rate had climbed well above every other channel's, and above its own historical range, right after a client library update had shipped two weeks earlier. Diagnosing it: comparing raw event counts against the BI-layer numbers at each pipeline stage showed the raw counts were consistent, but a join in the transformation layer that attributed conversions to channels was silently defaulting unmatched attribution records to that one channel instead of dropping or flagging them, a bug introduced by the same library update that had changed how a upstream field was formatted. The fix corrected the join's matching logic and reprocessed the affected date range. Communicating the impact meant going directly to the marketing lead before the reallocation was approved, stating plainly that the channel's apparent lead was substantially an artifact of the bug rather than real performance, and providing the corrected comparison so the decision could be re-evaluated on accurate numbers rather than quietly footnoting the correction after the fact. Afterward, the team added an automated check comparing total attributed conversions against total raw conversions on a schedule, alerting if the two diverged beyond a small tolerance, specifically so an attribution-layer bug like this one would surface on its own rather than depending on someone noticing an implausible number before a decision got made on it.

Trade-offs and pitfalls

• Fabricating precise-sounding metrics is the single most common way this answer goes wrong. A suspiciously exact improvement or error figure with no explanation of how it was derived reads as invented to an experienced interviewer; if exact numbers cannot be shared or are not precisely remembered, describe the direction and rough magnitude honestly ("meaningfully higher than any other channel," "roughly a third of the reported conversions") rather than reaching for false precision.
• Skipping the communication step, or describing it too vaguely, undercuts the story. "I let the team know" gives an interviewer nothing to probe; naming who specifically was told, what was actually at stake for them, and how directly the finding was stated is what makes this part credible.
• Ending at the fix, without naming a concrete preventive measure, leaves the story half-finished. The prevention step is what shows systems thinking rather than one-off firefighting, and a vague "we were more careful after that" is a weaker answer than a specific monitoring check, alert, or process change.
• Taking sole credit for a diagnosis that was actually a team effort tends to read as less credible, not more; a version of the story that includes how the candidate collaborated with whoever owned the affected pipeline stage is usually a stronger and more believable account than a solo-hero framing.

Direct answer

Match each signup to its NEAREST preceding visit within 7 days, then each purchase to its nearest preceding qualifying signup within 7 days, using two correlated lookups rather than each user's globally-first visit and globally-first signup. This single design choice is what correctly handles repeated events, out-of-order timestamps, and users who re-enter the funnel: it always looks for the closest, chronologically valid predecessor for each step rather than assuming there is exactly one clean occurrence of each stage per user.

Structured elaboration

Why "nearest preceding match," not "first occurrence of each stage." This question is a genuinely harder companion to a simpler stage-by-stage funnel query: that simpler query counts distinct users who reached each stage at all, with no ordering or timing constraint between stages. This one requires visit, then signup, then purchase, IN ORDER, with each consecutive pair no more than 7 days apart. Taking the globally-first visit and globally-first signup per user (the natural first instinct) breaks under funnel RE-ENTRY: a user who visited once long ago, came back and visited again more recently, then signed up shortly after the SECOND visit, should qualify, since a valid 7-day-windowed chain genuinely exists using the second visit, even though the gap from the first visit is too large. Anchoring on the globally-first visit would incorrectly reject that user. Matching each signup to its nearest preceding visit within the window (via a correlated MAX(visit_ts) WHERE visit_ts <= signup_ts AND gap <= 7 days), and each purchase to its nearest preceding qualifying signup the same way, finds a valid chain if one exists, regardless of how many times the user re-entered earlier stages.

Repeated events. A user firing visit twice, or purchase twice, needs no special deduplication logic beyond what the nearest-match query already does: signup_matched and purchase_matched each independently produce one row per signup and one row per purchase, and the correlated subquery naturally picks the single closest qualifying predecessor among however many candidate rows exist, so duplicate visits do not double-count or confuse the match.

Out-of-order timestamps. A data-quality bug (client clock skew, or an event arriving out of order through a distributed pipeline) can produce a purchase row whose occurred_at is earlier than the user's signup row, even though the purchase logically happened after. Enforcing the ordering constraint directly on the TIMESTAMP COLUMN itself (signup_ts <= purchase_ts), rather than relying on row insertion order or event-arrival order, is what correctly rejects this case: no valid matched_signup_ts is found for a purchase whose timestamp precedes every candidate signup, so that user is correctly excluded from the completed count rather than incorrectly matched.

Worked example

Schema: events(user_id STRING, event_name STRING, occurred_at TIMESTAMP). Six synthetic users, each engineered to hit exactly one of the complications above:

• u1: clean pass, visit day 0, signup day 3, purchase day 8. Should qualify.
• u2: signup 10 days after visit (exceeds the 7-day window). Should NOT qualify.
• u3: REPEATED visit events (day 0 and day 2); signup day 4 should match the closer visit. Should qualify.
• u4: OUT-OF-ORDER timestamps, purchase recorded at day 4, signup recorded at day 5 (purchase timestamp precedes signup timestamp due to a logging bug). Should NOT qualify.
• u5: FUNNEL RE-ENTRY, a stale visit at day 0, a genuine re-entry visit at day 20, signup day 22 (2 days after the re-entry visit, 22 days after the stale one), purchase day 25. Should qualify only under nearest-match logic, not under a naive first-occurrence anchor.
• u6: visits, never signs up. Counts in the visitor denominator only.

CREATE TABLE events (user_id TEXT, event_name TEXT, occurred_at TIMESTAMP);

INSERT INTO events VALUES
 ('u1','visit','2026-01-01 00:00:00'),
 ('u1','signup','2026-01-04 00:00:00'),
 ('u1','purchase','2026-01-09 00:00:00'),
 ('u2','visit','2026-01-01 00:00:00'),
 ('u2','signup','2026-01-11 00:00:00'),   -- 10 days after visit, exceeds the 7-day window
 ('u2','purchase','2026-01-13 00:00:00'),
 ('u3','visit','2026-01-01 00:00:00'),
 ('u3','visit','2026-01-03 00:00:00'),    -- repeated visit; signup should match this closer one
 ('u3','signup','2026-01-05 00:00:00'),
 ('u3','purchase','2026-01-07 00:00:00'),
 ('u4','visit','2026-01-01 00:00:00'),
 ('u4','purchase','2026-01-05 00:00:00'), -- purchase timestamp precedes signup: logging/clock-skew bug
 ('u4','signup','2026-01-06 00:00:00'),
 ('u5','visit','2026-01-01 00:00:00'),    -- stale first visit
 ('u5','visit','2026-01-21 00:00:00'),    -- genuine re-entry visit
 ('u5','signup','2026-01-23 00:00:00'),   -- 2 days after re-entry, 22 days after the stale visit
 ('u5','purchase','2026-01-26 00:00:00'),
 ('u6','visit','2026-01-01 00:00:00');    -- visits, never signs up

WITH visits AS (
    SELECT user_id, occurred_at AS visit_ts FROM events WHERE event_name = 'visit'
),
signups AS (
    SELECT user_id, occurred_at AS signup_ts FROM events WHERE event_name = 'signup'
),
purchases AS (
    SELECT user_id, occurred_at AS purchase_ts FROM events WHERE event_name = 'purchase'
),
-- Match each signup to its NEAREST PRECEDING visit within 7 days (not the
-- user's globally-first visit). This correctly handles both repeated visit
-- events (u3) and funnel re-entry (u5): a re-entry visit that is
-- chronologically closer to the signup wins over a stale, too-old first visit.
signup_matched AS (
    SELECT
        s.user_id,
        s.signup_ts,
        (SELECT MAX(v.visit_ts) FROM visits v
         WHERE v.user_id = s.user_id
           AND v.visit_ts <= s.signup_ts
           AND julianday(s.signup_ts) - julianday(v.visit_ts) <= 7) AS matched_visit_ts
    FROM signups s
),
qualifying_signups AS (
    SELECT user_id, signup_ts FROM signup_matched WHERE matched_visit_ts IS NOT NULL
),
-- Same nearest-preceding-match logic for purchase -> signup. Enforcing
-- signup_ts <= purchase_ts on the TIMESTAMP COLUMN (not row order) is what
-- correctly rejects u4's out-of-order purchase.
purchase_matched AS (
    SELECT
        p.user_id,
        p.purchase_ts,
        (SELECT MAX(qs.signup_ts) FROM qualifying_signups qs
         WHERE qs.user_id = p.user_id
           AND qs.signup_ts <= p.purchase_ts
           AND julianday(p.purchase_ts) - julianday(qs.signup_ts) <= 7) AS matched_signup_ts
    FROM purchases p
),
completed AS (
    SELECT user_id, MIN(purchase_ts) AS completed_at
    FROM purchase_matched
    WHERE matched_signup_ts IS NOT NULL
    GROUP BY user_id
),
visitor_base AS (
    SELECT DISTINCT user_id FROM visits
)
SELECT
    (SELECT COUNT(*) FROM visitor_base) AS total_visitors,
    (SELECT COUNT(*) FROM completed) AS completed_users,
    ROUND(1.0 * (SELECT COUNT(*) FROM completed) / (SELECT COUNT(*) FROM visitor_base), 4) AS conversion_rate;

Output (actually executed against SQLite as a stand-in for BigQuery-compatible standard SQL; julianday() is SQLite's date-arithmetic function, the equivalent of DATE_DIFF in BigQuery):

total_visitors   completed_users   conversion_rate
             6                 3               0.5

Per-user diagnostic confirming exactly the expected 3 completions (u1, u3, u5) and exactly the expected 2 non-matches (u2, u4):

('u1', '2026-01-09 00:00:00', '2026-01-04 00:00:00', 'COMPLETED')
('u2', '2026-01-13 00:00:00', None, 'not matched')
('u3', '2026-01-07 00:00:00', '2026-01-05 00:00:00', 'COMPLETED')
('u4', '2026-01-05 00:00:00', None, 'not matched')
('u5', '2026-01-26 00:00:00', '2026-01-23 00:00:00', 'COMPLETED')

u5's completion is the key result: the query correctly used the re-entry visit (day 20) to qualify the signup, which a first-occurrence-only anchor on the stale day-0 visit would have missed entirely.

Complexity

Each correlated subquery (finding the nearest preceding match) costs, in the naive form shown, $O(k)$ per row where $k$ is the number of candidate predecessor rows for that user, so the overall query is roughly $O(V \cdot S)$ where $V$ and $S$ are typical per-user visit and signup counts; at real scale, this is rewritten as a window-function-based nearest-match (using LAST_VALUE with a range-based frame, or a self-join restricted to a bounded date range) so the engine can use sort-based or index-based access instead of a per-row correlated lookup. Space: $O(V + S + P)$ for the intermediate CTEs, where $V$, $S$, $P$ are total visit, signup, and purchase row counts.

Edge cases

• A user with a qualifying signup but whose EVENTUAL purchase falls outside the window of every qualifying signup (say, they signed up correctly but bought 3 months later, well past any 7-day link) is correctly excluded, since no matched_signup_ts exists within range for that purchase.
• The retention question this data invites, "what happens to users who reached purchase, do they come back," is deliberately NOT computed here: that is a recurring-behavior question about ALREADY-converted users, a different measurement (repeat visits after conversion) from this funnel-completion query (whether a user reaches purchase at all in the first place), so it falls outside this query's scope.

Trade-offs and pitfalls

• Common mistake: using each user's globally-first occurrence of every stage as if that were always the "cleanest" or safest choice. It is the wrong default specifically when a funnel is order-and-window-constrained (as this one is): the worked example's u5 shows a globally-first anchor produces a false negative for a user who genuinely completed a valid, timely sequence via re-entry.
• This interval and stage set are parameters, not fixed facts about the query. The same nearest-match structure generalizes directly to a 24-hour window, a 30-day window, or a weekly-cohort framing by changing only the <= 7 comparisons and, for a cohort framing, adding a GROUP BY on the visit week; none of the matching LOGIC changes.
• At real data volumes, the naive correlated-subquery form above should be rewritten with window functions for performance (as noted under Complexity); shipping the correlated-subquery version to production on a billion-row events table without that rewrite is a common way this pattern becomes a slow, expensive query in practice.

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.

Direct answer

Moving free-to-paid conversion from 2% to 5% is a 150% relative increase, too large to bet on any single test, so the right deliverable is a sequenced, 6-month roadmap of experiments, front-loaded with the highest-confidence, lowest-effort changes to build early evidence and momentum, escalating toward bigger, riskier swings once the cheap wins are exhausted. Every experiment in the sequence needs its own stated hypothesis, primary metric, and success threshold, and the roadmap as a whole needs an honest, explicit check on whether the cumulative target is actually achievable in the stated window, which is a distinct and necessary part of the answer, not just the list of experiments itself.

Structured elaboration

Why sequencing, not just a list, matters. Running experiments in priority order rather than in parallel or arbitrary order does two things a flat list does not: it lets each experiment's real result inform whether the next one's underlying assumption still holds (an onboarding-friction fix that under-performs suggests the team's theory of what's blocking conversion needs revision before investing in a bigger, related bet), and it produces early, credible wins that justify continued investment in the later, more expensive experiments to skeptical stakeholders.

Sequencing logic used below. Early months prioritize changes that are cheap to build, fast to measure, and target a well-understood friction point (signup and first-session experience) where a positive result is likely and the downside of a negative result is small. Later months escalate to changes that require more engineering investment, take longer to reach a valid read (a trial-length experiment cannot conclude faster than the trial itself), or touch acquisition mix rather than only in-product behavior. This is the same confidence-versus-effort logic behind formal prioritization frameworks like RICE (reach, impact, confidence, effort) or ICE (impact, confidence, ease); here that logic is applied directly to produce an ordering, not re-derived as its own separate framework.

Alternative ways to arrive at a similar sequence, worth naming briefly. The same prioritized-experiment-sequence approach applies directly if the target metric were a B2B activation rate moving from 20% to 35% instead of a freemium-to-paid rate moving from 2% to 5%: the sequencing LOGIC (cheap and high-confidence first, expensive and uncertain later) does not change, only the specific experiments shift toward B2B-relevant activation levers (a guided admin setup wizard, a dedicated onboarding call for larger accounts) instead of freemium in-product prompts. A second, equally valid way to derive a sequence is customer-journey mapping: lay out every touchpoint from signup through billing (signup, first login, first key action, the upgrade moment, checkout) and design one experiment per touchpoint, rather than ranking by confidence and effort; this tends to produce a similar set of experiments organized by WHERE in the journey they sit rather than by WHEN to run them, which is a useful alternate lens when communicating the roadmap to stakeholders who think in journey stages.

Worked example

The month-6 sample-size calculation, worked explicitly (compact version: applied directly below using the standard two-proportion formula, without re-deriving it from first principles). Using the standard two-proportion sample-size formula

with $p_1 = 0.08$, $p_2 = 0.11$, $z_{\alpha/2} = 1.9600$ (5% two-sided significance), and $z_{\beta} = 0.8416$ (80% power), the computed sample size is 1,499 users per arm (2,998 total), actually computed in Python rather than estimated:

import math
from statistics import NormalDist
nd = NormalDist()
p1, p2, alpha, power = 0.08, 0.11, 0.05, 0.8
z_alpha, z_beta = nd.inv_cdf(1 - alpha/2), nd.inv_cdf(power)
p_bar = (p1 + p2) / 2
n = ((z_alpha*math.sqrt(2*p_bar*(1-p_bar)) + z_beta*math.sqrt(p1*(1-p1)+p2*(1-p2)))**2) / (p2-p1)**2
print(f"n per arm (raw) = {n:.2f}, ceil = {math.ceil(n)}, total = {2*math.ceil(n)}")

Output (actually executed): n per arm (raw) = 1498.39, ceil = 1499, total = 2998. This is what actually determines whether month 6 is even a realistically completable experiment: if this segment has materially fewer than roughly 3,000 eligible new users across the month, the experiment as designed cannot reach a conclusive read in the available time, and either the effect size being tested for needs to be larger (accepting the risk of missing a real but smaller effect) or the experiment needs to run longer than the single month slotted for it in this roadmap.

Trade-offs and pitfalls

• The arithmetic reality of the target deserves explicit acknowledgment. Moving from 2% to 5% free-to-paid conversion is a 150% relative increase; even optimistic individual experiments in a reasonably mature product typically produce single-digit-to-low-teens percent RELATIVE lifts each, not 150% in aggregate from six experiments. A senior answer states this tension directly rather than presenting the roadmap as guaranteed to hit the target: either the current 2% baseline reflects specific, large, fixable frictions (plausible if the product is young or has known major UX gaps, in which case bigger individual lifts are genuinely achievable), or the 5% target itself needs to be revisited as the roadmap's early results come in, with the sequencing designed to surface that reality quickly (front-loading experiments) rather than only in month 6.
• Each experiment's success threshold should be tied to what the CUMULATIVE roadmap needs to add up to the target, not chosen independently per experiment; a set of individually "successful" experiments that each clear their own small threshold can still fall well short of the 2%-to-5% goal if nobody checked the arithmetic adds up across all six.
• A common mistake is running all six experiments as fully independent, non-interacting bets; several of these (the upgrade prompt, the personalized emails, the paywall redesign) touch overlapping parts of the same conversion moment and can interact (a redesigned paywall changes the context the upgrade prompt is shown in), so later experiments in the sequence should account for the compounding effect of earlier shipped changes rather than being designed and analyzed in isolation as if the product were unchanged.
• The app-store-listing experiment (month 5) is measurement-different from the others in a way worth flagging: because it changes acquisition mix rather than in-product behavior, its "success" metric is necessarily cohort-based (comparing users acquired before versus after the change) rather than a randomized A/B split of existing users, which is a weaker causal design and more vulnerable to confounds like seasonality; treat its result with correspondingly more caution than the randomized in-product experiments.