Side Quest

Revenue Intelligence

Does the pipeline we still need change when win rate comes from the close, not the current stage?

The problem

Pipeline reviews read the current stage, so win rate and sales cycle describe the snapshot instead of the history.

The current stage column is a convenience. Historical win rate and cycle have to come from the stage transition that closed the deal.

Without a repeatable answer: A lost deal whose current stage was edited still counts as open, and the pipeline requirement moves with it.

My contribution

I built the queries in gtm-revenue-os. The seeded attainment is a fixture, not a measured result.

One example

Dashboard with date, territory, rep, segment, and stage filters. The stage filter does not rewrite historical win rate. Moving an open stage leaves the historical win rate unchanged. This version tests: Does the required-pipeline number change when win rate is taken from closes instead of the current stage?

Who uses it
A pipeline review on the same fictional book. One seeded loss still shows Negotiation on the opportunity row.
Decision it enables
What is still required after bookings, without treating a stale stage as open pipeline.
What it produces
Pipeline by stage, territory, and rep. Win rate and cycle from the history row that closed the deal. Planned new ARR, bookings, forecast commit, and pipeline still required.

One system

GTM Revenue OS

Planning, coverage, and pipeline review for one fictional company. The figures are planning fixtures. These workflows belong to the same system, and each write-up stays on its own page.

  • Planning

    Separate retention, expansion, and the new-business residual for one planning year.

    Explore the project: Planning
  • Coverage and capacity

    See whether a hiring change moves planned capacity while bookings stay still.

    Explore the project: Coverage and capacity
  • Pipeline review

    Read win rate from the close, not from the current stage.

    You are on this workflow.

The working planner is a separate app. These pages explain the decisions. They do not save a scenario, export a file, or open that app.

Request a walkthrough
Workflow and next steps
Inputs → review → output
Filters → stage as of a date from history → win rate and cycle from the close → plan versus bookings versus still required.
Planned next
If a CRM sync arrives, keep stage history as the source of truth. Do not backfill win rate from the latest stage.
Technologies in this repo or a linked project
Next.js and PostgreSQL in gtm-revenue-os. A language model does not calculate the results.

Metric to test

A win rate taken from the current stage would be wrong on the seeded loss. No claim that a team booked this pipeline.

How it works

Project overview

A separate application with tested SQL queries. This page explains the workflow.

A Next.js and Postgres application in gtm-revenue-os. SQL lives in lib/intelligence/sql.ts.

What I was working from

Synthetic book for the same fictional company: accounts, contacts, reps, territories, opportunities, stage history, activities, targets, and forecast submissions. One seeded loss still shows Negotiation on the opportunity row, so a win rate taken from the current stage would be wrong.

Workflow

Choose the window and the cut → Resolve each opportunity's stage as of that date → Read coverage, win rate, cycle, aging, slippage, and attainment → Compare the plan with bookings and the forecast commit

In plain language

Filters → Stage as of a date → Metric queries → Plan versus execution

Implementation notes

Proposed architecture. The technology section identifies what each project uses today.

Next.js · PostgreSQL · SQL in lib/intelligence/sql.ts · Stage history as the source of truth

Territory · Rep · Account · Contact · Opportunity · Stage history · Activity · Revenue target · Forecast submission

Implementation: gtm-revenue-os. Account potential is excluded from the plan-versus-execution comparison.

Goals

  • Report pipeline by stage, territory, and rep from Postgres.
  • Calculate win rate and sales cycle from the history row that closed the deal.
  • Compare planned new ARR, actual bookings, the latest forecast commit, and pipeline still required.

Out of scope

  • Treating account potential as pipeline or bookings.
  • Calculating history from opportunities.stage.
  • Presenting the seeded attainment as a measured result.

Metrics I would watch

These are not results unless the section above says they are on record.

  • Win rate and cycle cite the close event
  • Queries are explained on the SQL lesson in the app
  • Filters used in a review
  • Hypothesis: the gap that matters is planned versus booked versus still required

A win rate taken from the close event changes the pipeline a team thinks it still needs. The seeded book is a fixture.

Tradeoffs

  • A stage-history table costs writes on every move. Reporting off the current stage would be shorter and wrong.

What I would build next

  • When a CRM sync arrives, keep stage history as the source of truth. Do not backfill win rate from the latest stage.

Explore related work

Back to all projects