Conversion Funnel Optimization Questions

Direct answer

A channel's raw conversion lift is confounded whenever exposure to it was not randomly assigned, which is almost always true of observational channel data, so before crediting the channel with a causal effect, run diagnostics for the three usual suspects, selection bias (who got exposed), seasonality (when the lift appeared), and other concurrent confounders (what else changed at the same time), and once a specific one is implicated, adjust the estimate with weighting, matching, or a placebo test depending on what exactly needs correcting.

Structured elaboration

Selection bias. The cohort that ended up exposed to the channel is systematically different from the cohort that did not, in ways related to the outcome itself, not just to the channel. A common concrete case: the channel targets warm leads or a lookalike audience built from past converters, so its exposed group was already more likely to convert before the channel ever touched them. Diagnostic: compare the exposed and unexposed cohorts' characteristics and, critically, their PRE-exposure baseline conversion rate; if the exposed group already converted better before the channel launched, that gap is evidence of selection, not of the channel's effect.

Seasonality. Conversion moves on its own calendar rhythm, day-of-week, a holiday season, a promotional calendar unrelated to this specific channel, and that rhythm can coincide with the channel's spend ramp by accident. Diagnostic: compare the exposed cohort's lift against the same period's movement in a comparable unexposed cohort, or against overall site-wide conversion; if everything moved together across cohorts that never saw the channel, a shared calendar effect is implicated instead of the channel.

Other confounders. Any third factor influencing both who got exposed and how likely they were to convert: a concurrent product launch, a competitor's outage, a parallel pricing promotion, a broad demand shift. Diagnostic: cross-reference the lift's timing against a change log or launch calendar for anything else that shipped in the same window, and check whether the "lift" also shows up in metrics the channel could not plausibly influence; a lift appearing broadly across unrelated funnels or products points at a shared external driver, not a channel-specific effect.

Adjustment methods, named as asked, mechanism and when to use each:

Worked example

Weighting, with hand-computed numbers. Suppose users split into two propensity strata by prior purchase history, "high" and "low." In the exposed group, 70 percent of users are high-propensity (converting at 20 percent) and 30 percent are low-propensity (converting at 4 percent):

$0.7 \times 20\% + 0.3 \times 4\% = 14\% + 1.2\% = 15.2\%$

In the unexposed group the composition is inverted, only 30 percent high-propensity, 70 percent low, with similar within-stratum rates (18 percent and 3 percent):

$0.3 \times 18\% + 0.7 \times 3\% = 5.4\% + 2.1\% = 7.5\%$

The naive comparison, 15.2 percent versus 7.5 percent, is a 7.7-point gap that looks like a large channel effect. But most of that gap is composition: the exposed group simply contains far more of the users who convert well regardless of the channel. Reweighting the unexposed group to the exposed group's composition (apply the exposed group's 70/30 mix to the unexposed group's own within-stratum rates):

$0.7 \times 18\% + 0.3 \times 3\% = 12.6\% + 0.9\% = 13.5\%$

The adjusted comparison is now 15.2 percent versus 13.5 percent, a 1.7-point gap, versus the naive 7.7-point gap: roughly three quarters of the apparent lift was compositional imbalance, not a channel effect.

Placebo test, with hand-computed numbers. Suppose the exposed cohort converts at 8 percent in the post-period against 5 percent for the unexposed cohort, a naive 3-point lift. Before trusting that number, run the identical comparison on the pre-period, before the channel existed: exposed 6 percent, unexposed 4 percent, a 2-point gap that predates the channel entirely and is direct evidence of a pre-existing difference between these two cohorts (selection bias). Netting the pre-period gap out of the post-period gap, the same difference-in-differences arithmetic used for a designed quasi-experiment, applied here as a diagnostic rather than a planned design:

$(8\% - 6\%) - (5\% - 4\%) = 2\% - 1\% = 1\%$

The genuine, confounder-adjusted lift is closer to 1 point, not the naive 3. The placebo test did its job: it caught that most of the naive lift was a pre-existing gap between the two cohorts, not something the channel produced.

Trade-offs and pitfalls

• All three methods only correct for what you can see or that shows up in the pre-period. Weighting and matching adjust for measured covariates; a genuinely unmeasured confounder (a sales rep manually reaching out to accounts that also happen to fall inside this channel's targeting) is invisible to either. A placebo test can catch a confounder that produces a spurious pre-period gap, but not one that only appears after the channel launched and mimics its timing exactly. Real unmeasured confounding is what an instrumental-variable approach or an actual randomized design exists to solve instead.
• Common mistake: running one diagnostic and declaring the effect confirmed. A clean covariate-balance check does not rule out seasonality, and a clean placebo test at one date does not rule out a different, later confounder. Each diagnostic only rules out one class of failure, so use several, not one, before trusting the estimate.
• Common mistake: treating a passed placebo test as proof, not a failed falsification attempt. A placebo test that shows a spurious effect is strong, direct evidence against the causal claim; a placebo test that shows nothing is necessary support for the claim, not sufficient proof of it.
• Weighting-specific pitfall: propensity scores near 0 or 1 produce very large weights that let a handful of units dominate the estimate; trim or cap extreme weights and check the effective sample size after weighting, not just the raw count before it.
• Matching-specific pitfall: a close match on the wrong covariates (matching on demographics when the real confounder is browsing behavior) creates a false sense of rigor without removing the actual bias; matching only helps to the extent the matched covariates are the ones truly driving both exposure and outcome.

Direct answer

Model the clickstream as a per-user, timestamp-ordered event sequence; collapse consecutive repeated screens (loops) into one node before aggregating, cap path length so a small number of erratic users cannot dominate the cardinality of the "distinct paths" set, count occurrences of each resulting path among users who reached purchase, and take the top 10 by count. At scale, that exact-counting approach eventually needs to give way to approximate techniques (sketches, sampling); a Sankey diagram is the standard visualization once you have a ranked, bounded set of paths to show.

Structured elaboration

Data model. One row per event: (user_id, screen, event_timestamp), the standard raw event-log shape for funnel analysis generally: one row per user action, timestamped, with no pre-aggregation. Path analysis derives a PER-USER ORDERED SEQUENCE from this by sorting each user's events by timestamp, distinct from a stage-conversion table, which only cares whether a stage was reached, not the order or the screens in between.

Limiting path cardinality. Two levers, both needed: (1) collapse consecutive repeated screens (see loops below) BEFORE counting, since without this, "product, product, product, cart" and "product, cart" count as different paths despite representing the same browsing behavior; (2) cap the path LENGTH after collapsing, since a small number of erratic or bot-like users can otherwise generate arbitrarily long, unique paths that each occur exactly once and add pure noise. A common cap keeps only the last $K$ steps immediately before conversion, the steps closest to the purchase decision, prefixing a truncation marker to signal dropped history.

Handling loops and repeated screens. Two distinct patterns: a REPEATED screen (the same screen fired multiple times in a row, e.g. viewing three products, all logged as product) is collapsed to one node; a true LOOP (a cycle back to an earlier, non-adjacent screen, e.g. product -> cart -> product) is real, meaningful behavior and should NOT be collapsed, only immediately-adjacent repeats are.

Visualization. A Sankey diagram is the standard choice for a ranked set of top paths: it shows both relative volume (link width proportional to count) and structure (which screens funnel into which) in one view, which a bar chart of path strings cannot. For many near-duplicate paths, pre-aggregating to the top $N$ plus an "other" bucket keeps it readable.

SQL and algorithmic approaches at scale. The core aggregation (group by simplified path string, count, sort, limit 10) is a standard GROUP BY once per-user simplified-path strings exist; building those strings at scale is the actual bottleneck, since it needs a per-user ordered STRING_AGG/ARRAY_AGG over potentially many events, memory- and shuffle-heavy on a distributed engine at high volume. The at-scale techniques below trade some exactness for tractability once naive full-path aggregation becomes too expensive.

Worked example

Synthetic clickstream: 500 users, a designed random-walk transition model over screens {home, search, product, cart, checkout, purchase} with an explicit drop-off ("END") probability at every screen, generated with a pinned seed (random.Random(20260730)), executed end to end.

The full generation code (self-contained, so every SQL/Python snippet below can be run against it in order):

import sqlite3
import random
from datetime import datetime, timedelta

SEED = 20260730
rnd = random.Random(SEED)

SCREENS = ["home", "search", "product", "cart", "checkout", "purchase"]
END = "END"

TRANSITIONS = {
    "home":     {"search": 0.35, "product": 0.30, "cart": 0.05, "home": 0.05, END: 0.25},
    "search":   {"product": 0.50, "search": 0.15, "home": 0.10, END: 0.25},
    "product":  {"product": 0.30, "cart": 0.25, "search": 0.10, "home": 0.05, END: 0.30},
    "cart":     {"checkout": 0.45, "product": 0.20, "cart": 0.10, "home": 0.05, END: 0.20},
    "checkout": {"purchase": 0.55, "cart": 0.20, END: 0.25},
    "purchase": {END: 1.0},
}

def next_screen(cur):
    options = TRANSITIONS[cur]
    return rnd.choices(list(options.keys()), weights=list(options.values()), k=1)[0]

N_USERS = 500
MAX_STEPS = 25
sessions = []
base_time = datetime(2026, 7, 1, 8, 0, 0)

for uid in range(1, N_USERS + 1):
    screen = "home"
    t = base_time + timedelta(minutes=rnd.randint(0, 60 * 24 * 20))
    path = [(screen, t)]
    for _ in range(MAX_STEPS):
        nxt = next_screen(screen)
        if nxt == END:
            break
        gap_minutes = rnd.choice([rnd.uniform(0.2, 4), rnd.uniform(4, 90)])
        t = t + timedelta(minutes=gap_minutes)
        path.append((nxt, t))
        screen = nxt
        if screen == "purchase":
            break
    sessions.append((f"u{uid}", path))

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE clickstream (user_id TEXT, screen TEXT, event_timestamp TEXT)")
event_rows = []
for uid, path in sessions:
    for screen, t in path:
        event_rows.append((uid, screen, t.strftime("%Y-%m-%d %H:%M:%S")))
cur.executemany("INSERT INTO clickstream VALUES (?,?,?)", event_rows)
conn.commit()

(1) A simpler complementary query: top 2-step TRANSITIONS, before building full paths. Before tackling full-path aggregation, a simpler, cheaper starting query counts consecutive screen-to-screen transitions directly:

WITH ordered AS (
    SELECT
        user_id, screen, event_timestamp,
        LEAD(screen) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS next_screen
    FROM clickstream
)
SELECT screen, next_screen, COUNT(*) AS n
FROM ordered
WHERE next_screen IS NOT NULL
GROUP BY screen, next_screen
ORDER BY n DESC
LIMIT 10;

Output (actually executed):

from        to             count
home        search           207
product     product          187
home        product          175
search      product          149
product     cart             131
cart        checkout         103
checkout    purchase          54
product     search            48
cart        product           44
search      search            37

(2) The full top-10 paths to purchase, loop-collapsed and capped. Loop-collapsing merges consecutive duplicates (product, product becomes product); the cap keeps the last 6 steps before purchase, prefixing ... when truncated:

from collections import Counter

def collapse_loops(seq):
    out = []
    for s in seq:
        if not out or out[-1] != s:
            out.append(s)
    return out

CAP = 6

purchase_paths = []
for uid, path in sessions:
    screens_only = [s for s, _ in path]
    if screens_only[-1] != "purchase":
        continue
    collapsed = collapse_loops(screens_only)
    if len(collapsed) > CAP:
        capped = ["..."] + collapsed[-CAP:]
    else:
        capped = collapsed
    purchase_paths.append(tuple(capped))

path_counts = Counter(purchase_paths)
top10 = path_counts.most_common(10)  # top 10 computed; first 5 shown below

print(f"Users who reached purchase: {len(purchase_paths)} / {N_USERS} "
      f"({len(purchase_paths)/N_USERS:.1%} overall conversion)")
for i, (path, count) in enumerate(top10[:5], 1):
    print(f"{i:>2}. {' -> '.join(path):<55} count={count}")

Output (actually executed, Python over the same synthetic sessions):

Users who reached purchase: 54 / 500 (10.8% overall conversion)
 1. home -> product -> cart -> checkout -> purchase         count=21
 2. home -> search -> product -> cart -> checkout -> purchase count=10
 3. home -> cart -> checkout -> purchase                    count=7
 4. ... -> product -> cart -> checkout -> cart -> checkout -> purchase count=5
 5. ... -> product -> search -> product -> cart -> checkout -> purchase count=3

Path 4 shows a real, meaningful LOOP preserved correctly: cart -> checkout -> cart -> checkout reflects a user genuinely returning to cart after starting checkout (not collapsed away, since these are non-adjacent repeats separated by other screens), while consecutive product, product repeats within a browsing burst are correctly absent from every listed path.

(3) Most common next action within 1 hour (a distinct, time-windowed variant). "What happens right after cart, if it happens soon" is a different question from "what's the most common step overall," since it requires a time filter on top of the ordering:

WITH ordered AS (
    SELECT
        user_id, screen, event_timestamp,
        LEAD(screen) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS next_screen,
        LEAD(event_timestamp) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS next_ts
    FROM clickstream
)
SELECT next_screen, COUNT(*) AS n
FROM ordered
WHERE screen = 'cart' AND next_screen IS NOT NULL
  AND (julianday(next_ts) - julianday(event_timestamp)) * 24 <= 1.0
GROUP BY next_screen
ORDER BY n DESC;

Output (actually executed):

next_action      count
checkout            84
product             29
cart                22
home                12

(4) Massive-scale approximate computation: a real count-min sketch, executed and compared to exact counts. At true scale (billions of path occurrences), exact per-distinct-path counting can outgrow available memory; a count-min sketch (a fixed-size counter grid updated via several independent hash functions, which never underestimates a count and bounds the overestimation probabilistically) is one concrete, standard technique for this:

import hashlib

class CountMinSketch:
    def __init__(self, width=64, depth=5, seed=0):
        self.width = width
        self.depth = depth
        self.table = [[0] * width for _ in range(depth)]
        self.seed = seed

    def _hashes(self, key):
        for i in range(self.depth):
            h = hashlib.md5(f"{self.seed}:{i}:{key}".encode()).hexdigest()
            yield int(h, 16) % self.width

    def add(self, key, count=1):
        for i, idx in enumerate(self._hashes(key)):
            self.table[i][idx] += count

    def estimate(self, key):
        return min(self.table[i][idx] for i, idx in enumerate(self._hashes(key)))

all_purchase_paths_uncapped = []
for uid, path in sessions:
    screens_only = [s for s, _ in path]
    if screens_only[-1] != "purchase":
        continue
    all_purchase_paths_uncapped.append(">".join(collapse_loops(screens_only)))

exact_counts = Counter(all_purchase_paths_uncapped)
cms = CountMinSketch(width=32, depth=4, seed=SEED)
for p in all_purchase_paths_uncapped:
    cms.add(p)

print(f"Count-min sketch vs exact counts (width=32, depth=4, {len(exact_counts)} distinct paths):")
print(f"{'path':<45}{'exact':>8}{'cms_est':>10}{'over-est':>10}")
for path, exact in exact_counts.most_common(3):  # top 3 of 8 shown; same pattern holds for the rest
    est = cms.estimate(path)
    err = est - exact
    label = path if len(path) <= 43 else path[:40] + "..."
    print(f"{label:<45}{exact:>8}{est:>10}{err:>10}")

Count-min sketch vs exact counts (width=32, depth=4, 18 distinct paths):
path                                            exact   cms_est  over-est
home>product>cart>checkout>purchase                21        21         0
home>search>product>cart>checkout>purchase         10        10         0
home>cart>checkout>purchase                         7         7         0

At this run's scale (18 distinct paths, sketch width 32) there were no hash collisions among the top paths, so estimates exactly matched true counts; overestimation grows as distinct-path count approaches or exceeds sketch width, exactly the high-cardinality regime where this technique earns its keep over exact counting. Reservoir sampling (a fixed-size, uniformly-representative random sample maintained as the stream flows past) is the complementary alternative when the need is an actual representative SAMPLE of raw paths, not frequency estimates for known candidates.

(5) Event-to-event Markov transition-probability matrix (arbitrary product screens, not marketing channels). Distinct from a channel-attribution Markov model (which assigns conversion credit across MARKETING channels), this matrix describes transition PROBABILITIES between arbitrary product screens, useful for simulating likely future paths or identifying the highest-probability next step from any given screen:

from collections import defaultdict

transition_counts = defaultdict(lambda: defaultdict(int))
for uid, path in sessions:
    screens_only = [s for s, _ in path] + [END]
    for a, b in zip(screens_only, screens_only[1:]):
        transition_counts[a][b] += 1

states = SCREENS + [END]
print("Markov transition-probability matrix (rows sum to 1.0):")
header = "from\\to".ljust(10) + "".join(s[:8].rjust(9) for s in states)
print(header)
matrix = {}
for a in SCREENS:
    row_total = sum(transition_counts[a].values())
    matrix[a] = {}
    row_str = a.ljust(10)
    for b in states:
        p = transition_counts[a][b] / row_total if row_total else 0.0
        matrix[a][b] = p
        row_str += f"{p:9.2f}"
    print(row_str)
    if row_total:
        row_sum = sum(matrix[a].values())
        assert abs(row_sum - 1.0) < 1e-9, f"row {a} does not sum to 1.0: {row_sum}"

Markov transition-probability matrix (rows sum to 1.0):
from\to        home   search  product     cart checkout purchase      END
home           0.05     0.35     0.29     0.06     0.00     0.00     0.26
search         0.09     0.13     0.51     0.00     0.00     0.00     0.27
product        0.05     0.09     0.34     0.24     0.00     0.00     0.29
cart           0.07     0.00     0.20     0.12     0.47     0.00     0.14
checkout       0.00     0.00     0.00     0.25     0.00     0.52     0.22
purchase       0.00     0.00     0.00     0.00     0.00     0.00     1.00

Every non-empty row was verified (in-script) to sum to 1.0. This matrix directly answers "from checkout, what fraction of the time does the user actually purchase versus retreat to cart or leave" (52% purchase, 25% back to cart, 22% drop, 0% to any other screen in this run), a genuinely different, aggregate-probability view of the same underlying data as the ranked path list above.

Complexity

Building each simplified path (sort, collapse duplicates, truncate) is $O(k)$ per $k$-event session, $O(n)$ total across $n$ events; the Counter aggregation is $O(p)$ for $p$ purchase-reaching sessions. The count-min sketch is $O(1)$ per update and query regardless of distinct-path count, trading that constant-time guarantee for bounded overestimation instead of the worse degradation a plain hash map risks under adversarial key distributions. The Markov matrix build is $O(n)$ (one pass over event pairs) plus $O(s^2)$ to materialize it for $s$ screen types, negligible here since $s$ stays small (7 states) even as volume grows.

Edge cases

• A user with exactly one event produces a length-one path that never reaches purchase, correctly excluded from the top-10 ranking but still contributing to the transition and Markov counts.
• A screen that never occurs in a given window produces an all-zero Markov row; the code must treat that row's total as zero and omit or explicitly flag it as undefined (0/0) rather than dividing and producing a spurious value.
• Count-min sketch collisions become visible once distinct-path count approaches sketch width; a production implementation should expose an estimated error bound (from depth and width) rather than present the point estimate as exact.

Trade-offs and pitfalls

• Common mistake: counting raw (uncollapsed) event sequences as "paths," which inflates the distinct-path count with noise from browsing bursts and makes the top-10 list look more fragmented than the underlying behavior actually is.
• The path-length cap trades completeness for readability. Keeping only the last 6 steps is a defensible default for "what leads to purchase," but discards early-funnel context; a "full journey" question needs a longer cap or a different truncation strategy (keep first + last N, not only last N).
• A count-min sketch trades a small, bounded overestimation risk for large memory savings, and never underestimates. Not the right tool for EXACT counts (billing) or when the analysis needs the identity of rare paths, not just approximate frequency; right specifically for high-cardinality frequency estimation where approximate ranking is good enough.
• The Markov matrix assumes the process is memoryless (first-order): the next screen depends only on the CURRENT screen, not how the user got there. A user who arrived at product via search may behave differently than one who arrived via home directly, information a first-order model discards; a higher-order model (last 2 or 3 screens) captures more history at the cost of a much larger, sparser state space.

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

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.