Where we start
Last session you learned to rebuild the pipeline as it stood on any date. Tonight that discipline meets the most quoted number in sales: conversion. Every rate is a fraction, and every honest question about it is a question about the denominator. Which leads are in it? Did they all have the same time to convert? Was the table that records the wins even switched on when they arrived? You will answer all three on 8,000 real leads and 842 real won deals, in your browser.
★ What you walk out with tonight. A cohort conversion query you can write from memory, a habit of checking when each table starts before you trust a rate, and one sentence you can say to a sales leader when a won-only report claims a segment "wins more": we have the numerator, not the denominator.
Two rates with the same name
5 min liveThe cohort rate follows a group of leads: of everyone who entered in month m, what share ever won? The period rate looks at a calendar month: wins dated in m, divided by leads that arrived in m. The period rate is quicker to compute and appears on most dashboards, but its numerator and denominator are different people. The wins dated in January came from leads that arrived in December, October, and January; the January leads mostly win later.
LiveThe same month, two answers3 min▶
January 2018. 1,141 leads made first contact. 152 of them eventually won: a cohort rate of 13.3%. But only 73 wins carry a January date, so the period rate is 6.4%. The January leads were not worse; most of them simply won in February, March or later.
April 2018. 1,352 leads, 183 eventually won: 13.5%. Wins dated April: 207, a period rate of 15.3%. Now the period rate is the higher one, because April's wins include the deals that January, February and March leads closed in April.
The rule. When the arrival rate of leads changes month to month, the period rate swings for reasons that have nothing to do with how well anyone sells. Use the cohort rate to judge selling; use wins dated in the month only when the question is cash or capacity in that month, and then do not divide by that month's leads.
Self-studyWhen the period rate is the right tool2 min▶
Wins dated in a month are the right count for "how much did we book in April" and "did the team have capacity for April's closes". What breaks is the ratio: April's wins over April's leads. If you need a monthly ratio quickly, report the cohort rate with a fixed window instead, for example "won within 60 days of first contact", so every cohort gets the same amount of time. Analyst session 3 shows why the window matters and how to handle cohorts that have not had it yet.
Constructed Kettle & Co example. Kettle's BD lead sees conversion "jump" in April and asks Ben what changed. Constructed arithmetic: 100 offices contacted in March, 12 of them ever win (2 sign in March, 10 in April); 100 contacted in April, 12 ever win (5 in April, 7 in May). April's period rate is (10 + 5) / 100 = 15%. Both cohorts converted at 12 / 100 = 12%. Nothing changed but the calendar: March's tasting trials happened to land in April.
Check when each table starts
5 min liveOlist's lead table starts on 14 June 2017. Its closed-deal table starts on 5 December 2017. A lead that arrived in July 2017 and won in, say, October would never appear as won, because the log of wins did not exist yet. So the 2017 cohorts look dead: July 2017 converts at 0.8%, October at 3.4%, December at 5.5%, against 13.0% for the 2018 cohort. That is a logging artefact, not a buyer who said no.
LiveThree dates to write down before any rate3 min▶
When does the denominator table start? Here, the lead table: 14 June 2017.
When does the numerator table start? The closed-deal table: 5 December 2017. Any cohort that arrived well before this date is undercounted, and the further back, the worse.
When was the data cut? Leads stop on 31 May 2018 and wins on 14 November 2018. The May 2018 cohort (10.0%) had the least time to convert of any 2018 cohort. Some of that gap is slower buyers and some is simply less time: this is right-censoring, and Analyst session 3 handles it properly with a survival curve.
Self-studyThe closed-lost logging lag2 min▶
Wins get logged fast, because someone is paid on them. Losses get logged late or never: the buyer goes quiet, the rep keeps the deal open "just in case", and months later a cleanup marks it lost in bulk. So "open" deals quietly include losses, and any rate computed as won / (won + lost) looks better than it is, because the quiet losses sit in neither column.
Olist is the extreme case: it logs no losses at all. Every lead that did not win is simply a lead with no closed-deal row. That is why every rate tonight is won / all leads in the cohort, never won / (won + lost). In your own CRM, count deals with no activity for twice your median cycle and ask whether they are open or lost-but-unlogged. The rep's side of that cleanup is in AI Sales, CRM hygiene.
A year of leads that "never converted". On the real Olist rows, the July 2017 cohort has 239 leads and 2 logged wins; October has 416 leads and 14. Show those months on a conversion trend and it reads as a sales team that learned to sell in December. The honest chart starts the trend at the first cohort the win log could fully see, and says so in a footnote.
Rates by origin, and the missing denominator
5 min liveSplit the 2018 cohort by where each lead came from and the rates spread out: paid search 14.8%, organic search 14.7%, referral 11.4%, social 6.3%, email 4.0%. The best-converting group is "unknown" at 20.7%. A channel called unknown is not a channel; it is a tracking question. Then there is a second trap that no amount of splitting fixes: a table that only describes the winners.
LiveWhy "unknown" tops the chart3 min▶
An origin is a field someone or something fills in. Web forms fill it automatically; a lead a rep adds by hand after a phone call or a meeting often gets no origin at all. Those hand-entered leads are frequently the warmest ones, which would explain a high rate. It is a hypothesis, not a finding: the rows cannot tell you who left the field blank.
Before ranking channels, find out how origin gets set and what share of leads lack it. Here 813 of 5,998 leads in the 2018 cohort are "unknown", and the even smaller blank group (48 leads) converts at 27.1%. Small groups produce the most extreme rates; always print the denominator next to the rate, as the figure does.
Marketing reads this same funnel for budget and cost per acquired seller in Marketing, the funnel bench. Here the angle is narrower: is the rate honest enough to rank anything?
Self-studySurvivorship in a won-only table3 min▶
Olist's closed-deal table carries business segment and lead type, but only for the won deals. The leads table has no segment. So you can count which segments the winners came from, and you cannot compute a win rate by segment, because the losing and still-open leads of each segment were never described.
"Most of our won deals are segment X" then says nothing about whether segment X wins more often. It may simply be the segment you pitch most. A segment you rarely pitch can win most of its few chances and still look small among the winners. Without the denominator, the counts describe where you spent effort, not where effort pays.
The fix is upstream: capture segment at first contact, on every lead, so the denominator exists. Until then, say "we have the numerator, not the denominator" and refuse to rank.
Constructed Kettle & Co example. Kettle's signed-contract sheet records office size for every signed customer, and most are mid-size offices. Chiara proposes focusing on mid-size. The sheet has no row for the offices that took a tasting trial and did not sign, so there is no rate to compare. The first data request is not a new chart; it is an office-size field on the contact form.
Conversion two ways, on real rows
★ 22 min · everyone buildsThe widget holds real Olist B2B funnel rows (Brazilian marketplace seller acquisition) standing in for Kettle's history: every lead's first-contact month and origin, and every won deal's date. It computes both rates in your browser. Work the five steps, copy what the widget prints, and then write the query yourself.
Step 1 · Read the default verdict. The widget opens on "By month: cohort vs period". Copy the verdict line: it is the cohort rate for every lead that arrived January to May 2018, after the win log was running.
Step 2 · Compare January's two rates. Find the 2018-01 row. Read the cohort rate and the period rate side by side, then do the same for 2018-04, where the order flips.
Step 3 · Find the 2017 rows and explain them. Three of them below. The widget marks cohort rates under 6% in red. Write one sentence on why, using the date the closed-deal log starts.
Step 4 · Switch to "By origin". Copy the new verdict line, then find the top and bottom rows that have more than 300 leads. Check that the "five-fold" claim survives when you ignore the tiny groups.
Step 5 · Write the cohort query. Group leads by their entry month, count the ones that ever won, divide by all leads in that month. Run it on your own tables; do not trust a number until it matches the widget's logic on yours.
LEFT JOIN, never INNER. An inner join to the won table drops every lead that did not win, and the "rate" becomes won / won. The left join keeps the denominator; COUNT on the won side counts only matches.
Before Analyst session 3
30 min- Find your conversion dashboard and decide whether it shows a cohort rate or a period rate. Write down which, and where its numerator and denominator come from.
- Write down three start dates: when your lead table starts, when your won (or closed) table starts, and when lost deals began to be logged consistently.
- Run the cohort query from Step 5 on your own data for the last twelve months, and mark the cohorts too young to compare fairly.
- List every field you only have for won deals (segment, size, industry, deal type). For each, note where it would need to be captured so the denominator exists.
Three questions
Clear all three and Analyst session 2 gets stamped in your passport.
1 · January 2018's leads have a cohort rate of 13.3% and a period rate of 6.4%. Why do they differ?
The cohort rate follows the same 1,141 leads along their row. The period rate divides January-dated wins, from several cohorts, by January's leads.
2 · The July 2017 cohort converts at 0.8% against 13.0% for 2018. What is the most likely cause?
The 2017 cohorts had the most time, not the least. They look dead because the numerator table was not running yet: a logging artefact.
3 · A table describes segment only for won deals, and most won deals are in one segment. What can you conclude about win rate by segment?
A won-only table holds the numerator. Most winners in one segment may just mean most effort went there.
What this session teaches, and where it came from
Every fact on this page, and its verification tier, is recorded in the course's source map.
Analyst session 2 cheat sheet · pin this
You can measure conversion by cohort and spot the log that was not running. Analyst session 3 asks how long winning takes: the real cycle-time distribution, why the mean misleads, and how a survival curve gives young cohorts like May 2018 a fair reading.