Self-serve funnel: ‘the biggest leak’ is two different questions

The business question

A B2B software company sells self-serve. An account’s path is five steps:

visited_pricing → started_trial → activated (first real use) → invited_teammate → converted_paid

One quarter of event data. The growth team has one sprint and has to point it at a step. Someone asks the usual question: where is the biggest leak?

That question has two defensible answers that land on different steps, and a third answer that only appears when the funnel is cut by acquisition channel. This worked example produces all three.

The data

One row per account per step reached — an event log, which is what the platform’s funnel tool consumes. Columns: account_id, stage, event_at, channel.

The dataset is CONSTRUCTED: per-channel counts at each stage are fixed, then day offsets and row order are drawn with a fixed seed. Re-knitting reproduces data.csv byte-for-byte.

set.seed(20260824)

stages   <- c("visited_pricing", "started_trial", "activated",
              "invited_teammate", "converted_paid")
channels <- c("paid_search", "direct", "partner")

# accounts reaching each stage, by channel (rows = channel, cols = stage)
counts <- rbind(
  paid_search = c(1200, 456, 274, 110, 82),
  direct      = c( 800, 344, 216,  97, 74),
  partner     = c( 500, 250, 190,  38, 24)
)
colnames(counts) <- stages

# days BETWEEN consecutive steps, drawn per account and accumulated, so an account's
# timestamps can never run backwards (a trial cannot activate before it starts)
gap_lo <- c(0, 0, 1, 2,  7)
gap_hi <- c(0, 2, 5, 9, 20)

start_pool <- as.Date("2026-04-01") + 0:75   # a quarter of first-touch dates

# distinct id prefix per channel — "paid_search" and "partner" share their first two
# letters, so a 2-char prefix silently collides 500 account ids across two channels
prefix <- c(paid_search = "psr", direct = "dir", partner = "ptn")

rows <- do.call(rbind, lapply(channels, function(ch) {
  n_top  <- counts[ch, 1]
  ids    <- sprintf("acct_%s_%04d", prefix[[ch]], seq_len(n_top))
  first  <- start_pool[sample(length(start_pool), n_top, replace = TRUE)]

  # cumulative day offsets per account: column s is days from the pricing visit to stage s
  gaps <- sapply(seq_along(stages), function(s)
    if (gap_hi[s] == gap_lo[s]) rep(gap_lo[s], n_top)
    else sample(gap_lo[s]:gap_hi[s], n_top, replace = TRUE))
  offs <- t(apply(gaps, 1, cumsum))

  # the first k accounts of each channel progress to each stage: deterministic nesting,
  # so every account's path is a genuine prefix of the funnel
  do.call(rbind, lapply(seq_along(stages), function(s) {
    k <- counts[ch, s]
    if (k == 0) return(NULL)
    data.frame(account_id = ids[seq_len(k)],
               stage      = stages[s],
               event_at   = first[seq_len(k)] + offs[seq_len(k), s],
               channel    = ch,
               stringsAsFactors = FALSE)
  }))
}))

rows <- rows[order(rows$account_id, rows$event_at), ]
rownames(rows) <- NULL
write.csv(rows, "data.csv", row.names = FALSE)

nrow(rows)

## [1] 4655

head(rows, 8)

account_id	stage	event_at	channel
acct_dir_0001	visited_pricing	2026-04-24	direct
acct_dir_0001	started_trial	2026-04-26	direct
acct_dir_0001	activated	2026-04-30	direct
acct_dir_0001	invited_teammate	2026-05-04	direct
acct_dir_0001	converted_paid	2026-05-12	direct
acct_dir_0002	visited_pricing	2026-05-05	direct
acct_dir_0002	started_trial	2026-05-07	direct
acct_dir_0002	activated	2026-05-10	direct

The funnel

reached <- sapply(stages, function(s) sum(rows$stage == s))
lost    <- c(NA, head(reached, -1) - tail(reached, -1))
rate    <- c(NA, tail(reached, -1) / head(reached, -1))

wilson <- function(x, n) {
  if (is.na(x)) return(c(NA, NA))
  ct <- prop.test(x, n)          # Wilson score with continuity correction
  ct$conf.int * 100
}
ci <- t(sapply(seq_along(stages), function(i)
  if (i == 1) c(NA, NA) else wilson(reached[i], reached[i - 1])))

funnel_tbl <- data.frame(
  step        = c("—", paste(head(stages, -1), "→", tail(stages, -1))),
  reached     = as.integer(reached),
  lost_here   = lost,
  step_rate   = ifelse(is.na(rate), NA, sprintf("%.1f%%", 100 * rate)),
  ci_95       = ifelse(is.na(ci[, 1]), NA, sprintf("%.1f – %.1f", ci[, 1], ci[, 2]))
)
knitr::kable(funnel_tbl, align = "lrrrr",
             col.names = c("step", "accounts reaching", "lost at this step",
                           "step conversion", "95% CI (pp)"),
             caption = "Quarterly self-serve funnel. Two different columns answer 'biggest leak'.")

Quarterly self-serve funnel. Two different columns answer ‘biggest leak’.
	step	accounts reaching	lost at this step	step conversion	95% CI (pp)
	—	2500	NA	NA	NA
visited_pricing	visited_pricing → started_trial	1050	1450	42.0%	40.1 – 44.0
started_trial	started_trial → activated	680	370	64.8%	61.8 – 67.6
activated	activated → invited_teammate	245	435	36.0%	32.4 – 39.8
invited_teammate	invited_teammate → converted_paid	180	65	73.5%	67.4 – 78.8

biggest_loss_i <- which.max(lost)
worst_rate_i   <- which.min(rate)
cat("MOST ACCOUNTS LOST :", funnel_tbl$step[biggest_loss_i],
    "-", lost[biggest_loss_i], "accounts\n")

## MOST ACCOUNTS LOST : visited_pricing → started_trial - 1450 accounts

cat("WORST CONVERSION   :", funnel_tbl$step[worst_rate_i],
    "-", sprintf("%.1f%%", 100 * rate[worst_rate_i]), "\n")

## WORST CONVERSION   : activated → invited_teammate - 36.0%

cat("end to end         :", sprintf("%.1f%%", 100 * reached[5] / reached[1]), "\n")

## end to end         : 7.2%

Two honest answers, two different steps. The entry step bleeds the most accounts because it starts with the most; the invite step converts worst.

Are those two rates actually distinguishable, or is one inside the other’s error bar?

rbind(`visited→trial`    = wilson(reached[2], reached[1]),
      `activated→invite` = wilson(reached[4], reached[3]))

##                      [,1]     [,2]
## visited→trial    40.05916 43.96564
## activated→invite 32.43729 39.78168

The intervals do not overlap, so the invite step is genuinely the worse-converting one — not a ranking that a re-run could flip.

par(mar = c(6.5, 5, 3, 1))
bp <- barplot(reached, col = "#5fa9dd", border = NA, las = 2, ylim = c(0, 2800),
              names.arg = gsub("_", "\n", stages), ylab = "accounts",
              main = "The funnel, with what each step costs")
text(bp, reached + 130, format(reached, big.mark = ","), font = 2, cex = 0.9)
for (i in 2:5) {
  segments(bp[i - 1], reached[i - 1], bp[i], reached[i], col = "#F97316", lwd = 2)
  text((bp[i - 1] + bp[i]) / 2, (reached[i - 1] + reached[i]) / 2 + 190,
       sprintf("-%s", format(lost[i], big.mark = ",")), col = "#F97316", font = 2, cex = 0.9)
}

The same funnel, cut by acquisition channel

ch_rate <- function(s_to, s_from) counts[, s_to] / counts[, s_from]
by_ch <- data.frame(
  channel                = channels,
  accounts               = as.integer(counts[, 1]),
  `visited→trial`        = sprintf("%.1f%%", 100 * ch_rate(2, 1)),
  `trial→activated`      = sprintf("%.1f%%", 100 * ch_rate(3, 2)),
  `activated→invited`    = sprintf("%.1f%%", 100 * ch_rate(4, 3)),
  `invited→paid`         = sprintf("%.1f%%", 100 * ch_rate(5, 4)),
  check.names = FALSE, row.names = NULL
)
knitr::kable(by_ch, align = "lrrrrr",
             caption = "Channel cut. Read the two middle columns together.")

Channel cut. Read the two middle columns together.
channel	accounts	visited→trial	trial→activated	activated→invited	invited→paid
paid_search	1200	38.0%	60.1%	40.1%	74.5%
direct	800	43.0%	62.8%	44.9%	76.3%
partner	500	50.0%	76.0%	20.0%	63.2%

Partner-sourced accounts are the best at activating (76.0% against paid search’s 60.1%) and the worst at inviting a teammate (20.0% against paid search’s 40.1%). The aggregate invite rate of 36.0% is an average over a channel that is twice as good and a channel that is half as good.

# partner vs paid_search at the activation step
act <- prop.test(c(counts["partner", 3], counts["paid_search", 3]),
                 c(counts["partner", 2], counts["paid_search", 2]))
# partner vs paid_search at the invite step
inv <- prop.test(c(counts["partner", 4], counts["paid_search", 4]),
                 c(counts["partner", 3], counts["paid_search", 3]))
c(activation_p = act$p.value, invite_p = inv$p.value)

## activation_p     invite_p 
## 2.951109e-05 7.561370e-06

act$conf.int * 100

## [1]  8.657803 23.166758
## attr(,"conf.level")
## [1] 0.95

inv$conf.int * 100

## [1] -28.71798 -11.57399
## attr(,"conf.level")
## [1] 0.95

Both directions of the reversal are far beyond chance.

par(mar = c(4, 5, 3, 1))
m <- rbind(`trial→activated`   = 100 * ch_rate(3, 2),
           `activated→invited` = 100 * ch_rate(4, 3))
bp2 <- barplot(m, beside = TRUE, col = c("#3fbf6f", "#F97316"), border = NA,
               ylim = c(0, 108), ylab = "step conversion (%)",   # headroom: at 90 the 76% label collided with the legend
               main = "The reversal: best at one step, worst at the next")
text(bp2, m + 4, sprintf("%.0f%%", m), cex = 0.85)
legend("top", rownames(m), fill = c("#3fbf6f", "#F97316"), bty = "n", border = NA,
       horiz = TRUE, inset = c(0, -0.02), xpd = TRUE)   # horizontal, above the plot: never over a bar label
abline(h = 100 * rate[4], lty = 2, col = "#918e98")
text(par("usr")[2], 100 * rate[4] + 3, "aggregate invite rate", adj = c(1, 0),
     col = "#918e98", cex = 0.8)

The chooser the lesson teaches