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

To see where your own flow loses people, step by step, try the free funnel analysis tool.

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("n/a", 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 to %.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)
	n/a	2500	NA	NA	NA
visited_pricing	visited_pricing → started_trial	1050	1450	42.0%	40.1 to 44.0
started_trial	started_trial → activated	680	370	64.8%	61.8 to 67.6
activated	activated → invited_teammate	245	435	36.0%	32.4 to 39.8
invited_teammate	invited_teammate → converted_paid	180	65	73.5%	67.4 to 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? Comparing their separate intervals is the common shortcut, but it is lopsided: intervals that do not overlap do show a difference, while intervals that overlap do not show there is none. The direct check is an interval for the difference itself.

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

step_diff <- prop.test(c(reached[2], reached[4]), c(reached[1], reached[3]))
round(c(difference_pp = unname(100 * (rate[2] - rate[4])),
        ci_low = 100 * step_diff$conf.int[1], ci_high = 100 * step_diff$conf.int[2]), 1)

## difference_pp        ci_low       ci_high 
##           6.0           1.8          10.2

The entry step converts 6.0 points better than the invite step (95% CI 1.8 to 10.2). The 680 activated accounts are a subset of the 2,500 visitors, so treating the two rates as independent samples is an approximation; with a gap this size it does not change the reading. On this quarter’s data the invite step converts worse. A future quarter can still differ, which is why the check is repeated each quarter rather than settled once.

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

If these accounts were a sample from a larger stream of sign-ups, both differences would be far larger than sampling noise produces. This dataset is constructed, with the counts set by hand, so here the tests show how the check is done, not a discovery; on your own data they are the test that a reversal like this is not noise.

Where are the invite-step losses, in accounts?

inv_lost <- counts[, 3] - counts[, 4]
inv_tbl <- data.frame(channel = channels, activated = as.integer(counts[, 3]),
                      lost_at_invite = as.integer(inv_lost),
                      share_of_losses = sprintf("%.0f%%", 100 * inv_lost / sum(inv_lost)),
                      row.names = NULL)
knitr::kable(inv_tbl, align = "lrrr", caption = "Invite-step losses by channel")

Invite-step losses by channel
channel	activated	lost_at_invite	share_of_losses
paid_search	274	164	38%
direct	216	119	27%
partner	190	152	35%

# extra invites if partner accounts invited at paid search's rate
partner_gap <- unname(round(counts["partner", 3] * ch_rate(4, 3)["paid_search"] - counts["partner", 4]))
partner_gap

## [1] 38

Partner accounts are 28% of activated accounts but 35% of the invite-step losses: 152 of 435. Paid search loses more in absolute terms (164). If partner accounts invited at paid search’s rate, about 38 more would invite a teammate each quarter.

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