The backflow ratio finds your fake stages

Every sales pipeline has a stage nobody believes in. The deals enter it, sit for a while, and then move backwards to the stage before, or forwards on a date that has nothing to do with the customer. Ask the team what the stage means and you get three answers. The stage is still there because removing it feels like admitting the process was wrong, and because nobody has a number that says it is. There is such a number, and the CRM already has the data to compute it.

A stage is a proven fact

A pipeline stage should mean that something has been proven about the deal. "Qualified" means someone confirmed the budget and the authority. "Proposal sent" means a document left the building. "Negotiation" means the customer replied with terms. If moving a deal into a stage requires no evidence, the stage is a mood: how the rep feels about the deal this week. Moods change, and when they do the deal moves backwards.

The vendors' defaults are not the problem, and they are worth looking at because they show how many stages a pipeline needs. HubSpot's default deal pipeline has seven stages, five open and two closed, each with a win probability attached. Salesforce's default opportunity stages, as described in guides such as Salesforce Ben's, run to ten, from Prospecting through Perception Analysis to the two closed states. Zoho's deal stages are system-defined and cover a similar arc. Every one of them is customisable, which is where the trouble starts, because a team customises by adding.

The ratio

Every CRM records stage transitions, because it has to, to draw the history on the deal. Almost none of them show the transitions in aggregate. The unified CRM I built stores every transition as a row: deal, from stage, to stage, timestamp, actor. From that table one number falls out per stage, and I call it the backflow ratio: of all the departures from a stage in the last ninety days, the share that went to an earlier stage rather than forward or to closed.

SELECT from_stage,
       AVG(CASE WHEN to_rank < from_rank THEN 1 ELSE 0 END) AS backflow_ratio,
       COUNT(*) AS departures
FROM stage_transitions
WHERE moved_at > now() - interval '90 days'
GROUP BY from_stage;

A stage with a ratio near zero is a fact: once proven, it stays proven. A stage above roughly 0.2 is a status flag wearing a stage's clothes: a fifth of the deals that leave it go backwards, which means entering it never required evidence in the first place. A pipeline whose stages all sit under 0.1 is one the team actually believes, and it is usually shorter than the one they started with.

Why "Proposal sent" leaks

The example stage is the usual culprit, and the reason is instructive. Sending a proposal is an action the rep takes, not a fact about the customer. The rep sends it, moves the deal forward, and then the customer asks for a second demo, or goes quiet, or turns out not to have been qualified after all. The rep moves the deal back, because the stage no longer describes it. The stage was recording the rep's activity, and activity is a field on the deal with a timestamp, not a place in a sequence.

That is the demotion the ratio points at. A leaky stage becomes a boolean with a date: proposal_sent_at. The pipeline loses a stage and gets shorter, the report that used to ask how many deals are in Proposal Sent now asks how many deals have a proposal date in the last month, and both questions have better answers than before, because the second one cannot be undone by a mood.

Backflow is not loss

One distinction keeps the ratio honest. A deal that moves from Negotiation to Closed Lost has not moved backwards; it has moved to a terminal state, and the ratio counts it as a forward departure. Losing deals late is a sales problem worth its own report, but it is not evidence that Negotiation was a mood. Backflow is specifically a move to an earlier open stage, which is the system saying that a fact it recorded turned out not to be one. Conflating the two inflates the ratio on the late stages and hides the real leak, which is usually in the middle of the pipeline where actions masquerade as facts.

The 0.2 line is a working rule rather than a law. Below it, one departure in five or fewer goes backwards, which is the level of churn a stage with genuine entry criteria produces from ordinary mistakes and corrections. Above it, more than one in five, the stage's meaning is being revised by the people who use it, and the revision is the data. Teams with strict pipelines sit well under 0.1 everywhere; the first time I computed the ratio on a pipeline that had grown by accretion, three of its nine stages were over 0.25, and the team recognised all three by name before I showed them the numbers.

Running it on your own pipeline

The query needs a transitions table with a rank per stage, and every major CRM exposes one under some name: a stage history object, a property history, an audit log of the stage field. Export it, attach the rank from the pipeline's stage order, and the query above runs in a spreadsheet if it has to. The only care needed is with pipelines that were reordered during the window, because a stage that moved from third to fifth makes old transitions look like backflow they were not. Compute within the period of the current order, or rank by the order in force at the time of each move.

What the ratio does not say

A high ratio is a symptom with more than one cause, and the query only finds the symptom. Sometimes the stage is fine and the process is not: deals go backwards from Negotiation because reps move deals forward to hit a weekly target and the manager moves them back on Monday. The ratio flags Negotiation, and the fix is a conversation, not a schema change. Sometimes a low ratio is hiding a problem: a stage nobody moves deals out of at all has a ratio of zero and a departure count of zero, and the second number is the one to read. The query returns both for a reason.

The ratio also needs a window. Ninety days is long enough to include a few hundred departures in most pipelines and short enough that the process being measured is the current one. A ratio computed over three years measures three years of process changes at once and says nothing about any of them.

What this changed in the CRM

Two things. The transition table went from a private detail behind the deal history to a first-class report, with the ratio per stage on the pipeline settings page next to the button that adds a stage. And the pipeline editor got one question that appears when a stage is created: what has to be true about the deal for it to enter this stage. A blank answer is allowed, but it is shown next to the ratio a quarter later, and the two together usually settle the argument that the stage should have been a field.

The number the team was missing was in their own database the whole time. Every CRM keeps the transitions; the report that reads them is a single GROUP BY.