Where we start
The Analyst track is for the people who build the numbers the Business track reads. Six sessions: tonight, the data model; then conversion done honestly, by cohort; velocity and cycle time, including deals still open; three forecast methods; how to measure a forecast's error; and the forecast honesty bench, where the three methods meet one real quarter. Every widget computes in your browser. The deal timings and outcomes come from real rows of Olist's public B2B funnel; Kettle & Co's stages, amounts and reps are constructed and labelled wherever they appear.
★ What you walk out with tonight. A three-table pipeline model you can draw on a whiteboard, the as-of join that turns a stage-history log into a weekly snapshot, and a written list of what your own CRM keeps and throws away. Every later session assumes you can rebuild the pipeline as it stood on any past date.
Three tables, and the one most teams lack
5 min liveA pipeline needs three tables. The opportunity table has one row per deal and holds its current state. The stage-history table has one row per change: which deal, which stage, from when. The snapshot table has one row per deal per week and records where every deal stood. The first is what the CRM shows. The second is what actually happened. The third is derived from the second, and it is what every trend chart needs.
LiveWhat each table is for3 min▶
Opportunity. Today's pipeline review. Amount, owner, close date, stage now. It is the table the CRM edits in place, so it can never answer a question about the past.
Stage history. Append-only: a row is added when a deal changes stage, and nothing is ever updated. Columns: deal id, new stage, timestamp, and ideally who changed it. Many CRMs keep some version of this (a field-history or audit log), often with a retention limit or only for fields someone switched on. Find out which before you promise a trend.
Snapshot. Derived. One row per deal per reporting period, with the stage it was in at the end of that period. Conversion by cohort, stage-to-stage timing and forecast accuracy all read from here.
Self-studyIf your CRM keeps no history at all2 min▶
Start a snapshot tonight: a scheduled job that copies the opportunity table, with a date column, once a week. It cannot recover the past, but in a quarter you will have thirteen weeks of evidence. Some CRMs offer a built-in historical trending or snapshot report; it is worth turning on even if you also run your own copy, because it is the vendor's record of what the table said. Re-verify how far back your vendor's history goes before you rely on it.
The public data has no history either. Olist's public funnel, the real rows behind this track, ships two tables: every lead with its first-contact date and origin, and every won deal with its won date. There is no stage history and no lost deal. That is common in exports: what survives is the start and the happy ending. Every stage you see on Kettle's pages had to be constructed, which is exactly what a team without an event log ends up doing by hand.
How the current state erases what happened
5 min liveWhen a deal moves backwards, the stage field is simply set to the lower stage. When it is lost, it becomes "Lost". The opportunity table then shows a clean end state and nothing of the path. Two of Kettle's eight deals went backwards. Without the event log, nobody can tell that K-102 once reached a tasting trial, or that K-107 sat at Proposal for three weeks.
LiveThree questions you cannot answer from current state3 min▶
What did the pipeline look like on the first day of the quarter? The forecast was made from that pipeline. To score the forecast later (Analyst session 5), you need it frozen as it was.
How long do deals sit in each stage? Needs the entry and exit date of each stage, which only the event log holds. Analyst session 3 uses first contact to won because the public rows have nothing in between.
How often do deals go backwards? A reversal is a real signal: a trial that disappointed, a budget that moved. Current state reports the end and hides the stall.
Self-studyClose dates get overwritten too2 min▶
The expected close date is the field most often edited and least often logged. A deal whose close date moved from June to July to September looks, in the opportunity table, like a September deal with no history. Log close-date changes as events alongside stage changes. Business session 2 calls these deals "pushed out", and you can only count them if the old dates were kept.
Rebuild any week from the events
5 min liveThe snapshot for week w is, for every deal that exists by then, its most recent event at or before w. That operation is an as-of join, and it is the one piece of SQL every pipeline analyst should be able to write from memory. Get the inequality right (at or before, not before) and the snapshot matches what a person looking at the CRM that week would have seen.
LiveThe as-of join in SQL4 min▶
Most warehouses can do this with a window function. Rank each deal's events at or before the snapshot date, newest first, and keep rank 1. Some engines also have a dedicated ASOF JOIN; the window version runs almost everywhere.
For a weekly snapshot table, cross join the deals with a calendar of week-end dates first, then apply the same rule per week.
Self-studyTwo bugs that hide in the inequality2 min▶
Strictly before. Using < instead of <= drops a change made on the snapshot date itself, so a deal that moved on Monday morning appears one stage lower in Monday's snapshot.
Time zones. A change stamped 23:30 in one time zone is the next day in another. Decide which clock the snapshot uses (usually the sales team's) and convert before comparing. A forecast frozen on the wrong side of midnight is scored against a pipeline nobody saw.
Rebuild a past week from the log
★ 22 min · everyone buildsThe widget holds Kettle & Co's eight-deal stage-history log and runs the as-of join in your browser every time you move the slider. The right-hand column is what the opportunity table shows today. Work the five steps and write down what the widget prints; the log is constructed, so the lesson is the mechanism, not the counts.
Step 1 · Read week 6. The slider starts there. Copy the verdict line and count how many deals sit at Tasting trial.
Step 2 · Move to week 9. Four deals now sit at Proposal. Look at K-102: it has dropped back to Need confirmed. The right-hand column says Lost, which was true only from week 12.
Step 3 · Score week 9's Proposal deals against today. Of the four deals at Proposal in week 9, read their right-hand column. This is the smallest possible stage-to-outcome table, and you just built it from events.
Step 4 · Read the hint line. It lists every backward move the log holds. Write both down; then ask how your own CRM would store each one.
Step 5 · Write your own model in three lines. For your CRM, name the table or report that plays each role, and mark the role nobody plays yet. That gap is your first data request.
Freeze the forecast's pipeline. On the day a quarter's forecast is made, save that day's snapshot with the forecast. Analyst session 5 scores the forecast against what closed, and the score means nothing if the pipeline it was made from has since been edited.
Before Analyst session 2
30 min- Find your stage history. Locate the field-history or audit table for opportunity stage in your CRM, and note how far back it goes and whether close-date changes are logged too.
- Write the as-of join against it for the first day of last quarter, and count deals by stage.
- Compare that count with what the opportunity table says today about the same deals. Write down how many changed stage, and how many went backwards.
- If there is no history, schedule a weekly snapshot copy today, and note the date it starts. Every later session gets easier from that date on.
Three questions
Clear all three and Analyst session 1 gets stamped in your passport.
1 · Which table can rebuild the other two?
Current state is the latest event per deal; a snapshot is the latest event at or before each week. Neither can rebuild the events.
2 · In week 9, K-102 sat at Need confirmed; the CRM today says Lost. What does a snapshot at week 9 show for K-102?
The as-of join takes the latest event at or before the snapshot date. The week-12 loss had not happened yet.
3 · A stage change is stamped on the snapshot date itself. Your query filters changed_at < snapshot_date. What goes wrong?
"At or before" means <=. A strict inequality silently lags every same-day change.
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 1 cheat sheet · pin this
You can rebuild the pipeline as it stood on any date. Analyst session 2 uses that to measure conversion honestly: by the month a lead arrived, not the month a deal closed, on the real Olist rows, where the period version tells a very different story.