Sales Pipeline Audit · System 01 · what the CRM actually recorded

The CRM Shows What Changed, Not What Happened

A stage moves in a CRM for one of two reasons: the buyer genuinely advanced, or somebody edited the record. A rep drags a deal forward to keep it off a stalled-deal report. A backlog of updates gets entered on a Friday afternoon, dated for whenever felt right. A deal skips straight from discovery to proposal because typing three intermediate stages felt like paperwork nobody was going to read. Every one of those looks identical to real progress in a stage-by-stage report.

The idea in one line: build a deal-by-deal ledger from what the system actually captured, flag the parts a rep’s habits could have produced instead of the buyer, and treat anything you can’t corroborate as a question rather than a fact.

What your CRM already has

All three of the common small-business CRMs can produce a usable deal-transition dataset. None of them hand it to you clean.

Salesforce

Stage changes are captured by default through Opportunity History, and LastStageChangeDate is native. A usable dataset still needs configuration: required close and loss fields, a consistent stage list, and report design.

HubSpot

The strongest default of the three. Entry and exit dates per stage, time in current stage, and an automatic stalled-deal flag are all documented default deal properties, with no configuration required.

Pipedrive

Current stage, activities, and duration reporting are solid by default. Getting a clean row-by-row history of every past transition is less self-evident, and usually means the Insights layer or the API rather than a plain export.

Whichever one you run on, the underlying fact doesn’t change. A timestamp on a stage tells you when the CRM record changed. It does not, on its own, tell you when the buyer moved — and the gap between those two things is where most pipeline reporting goes wrong before the analysis even starts.

Six ways the record misleads you

None of these are malicious. Every one of them is a rep doing their job under time pressure, in a system that was never going to notice.

What to check before you trust a number

Skipped stages: a deal jumps straight from discovery to proposal. A funnel report reads this as every intervening condition having been met. Calculate both “ever entered stage” and “direct transition” rates — the gap between them is the skip rate.

Backward movement: deals get downgraded, reopened, or revived after going cold. “Latest time in stage” understates how long a deal has actually been alive. Track re-entry count and cumulative age separately from the current stage’s duration.

Late or backfilled updates: reps update the CRM at end of week, end of month, or after a manager asks. The timestamp measures when the system was told, not when the customer decided. Don’t treat it as the real decision date without an activity record to back it up.

Blank or low-information loss reasons: a rep closes a lost deal quickly and picks whatever’s fastest, or leaves it blank. “Lost to competitor,” “timing,” and “no decision” become unreliable exactly where you need them most.

Activity that never touched the deal: the call happened on a mobile phone, the email lives in a personal inbox, the follow-up is a text nobody logged. “No activity” in the CRM can mean no activity happened, or it can mean nobody recorded it. Those are different findings.

A deal that looks active but has no next step: a note got logged, an email got sent, but nothing dated is on the calendar. This is the single most actionable version of “stalled,” and it’s invisible to any report that only checks for recent activity.

None of these six is a reason to distrust the CRM wholesale. They’re the reason a pipeline audit starts by classifying what the record can actually support, before any of it gets summed into a total.

Build the deal ledger

One row per deal, at the transition grain, is the dataset the rest of this audit runs on. Export or reconstruct these fields.

The minimum ledger

Deal: id, account, owner, source or channel, stated deal size

Transitions: from stage, to stage, timestamp, and a running count of re-entries into any earlier stage

Current state: status, close date if closed, normalized loss reason plus the free-text note if there is one

Activity: last CRM activity timestamp, last customer-facing activity if it’s distinguishable, next scheduled activity, activity count by type

People: number of contacts engaged, whether an economic buyer or decision-maker field is filled in, the champion’s last-engaged date

This is a spreadsheet, not a data warehouse. Pull it from a stage-history or property-history export where your CRM offers one; where it doesn’t, reconstruct the last quarter by hand from the activity timeline on each deal.

Reconstructing this by hand, deal by deal, is slow and exactly the kind of pattern-matching an AI assistant is fast at — as long as you keep it extracting rather than concluding:

Do this step with an AI assistant

Export whatever stage-history or activity report your CRM will give you, and send it in one chat with these two messages in order. The second is where most of the value is: it asks the assistant to flag what the export can’t support, rather than filling the gap with a guess.

“Here’s a stage-history export from my CRM. Turn it into one row per deal: current stage, every transition with its timestamp, re-entry count, last and next activity dates, and loss reason if closed lost. Don’t interpret it yet.”
“For each deal, flag anything the data can’t confirm: a stage skipped on the way to where it is now, a transition that happened right after a long gap with no logged activity, or a loss reason that’s blank or generic. List those separately from the deals where the record looks clean.”

Set a stall threshold that isn’t one number for everything

“Stalled” only means something relative to how this kind of deal usually moves.

A two-week silence is nothing on a quarterly enterprise procurement cycle and a genuine warning sign on a self-serve SMB deal that usually closes in ten days. Set the threshold per segment, not once for the whole pipeline: open, no future task, no qualifying customer activity inside the segment’s normal gap, and stage age above that segment’s typical time-in-stage. HubSpot’s native stalled flag uses a comparable idea — time in stage running meaningfully longer than that owner’s own closed-won average for the same stage — which is a reasonable default if you don’t want to build the segments yourself yet.

Write your thresholds before you run the flag

Segment: ______ (by deal size, motion, or product — whatever actually changes the sales cycle)

Normal time in this stage: ______ (median, from your own closed-won history, not a guess)

Normal gap between customer touches: ______

Stalled means: open, no future task, no qualifying activity inside the gap above, and stage age past the median for this segment

A deal that fails this test isn’t necessarily dead. It’s a deal that needs a human decision, which is what System 03 is for.

A worked example

A 14-person B2B services firm runs its pipeline in HubSpot and believes its close rate has been slipping. The property-history export covers the last two quarters and 61 deals.

Reconstructed at the transition grain, three things show up that the summary funnel report never surfaced:

What the ledger found

9 of 61 deals skip “Needs Analysis” entirely, jumping from Discovery straight to Proposal — all 9 belong to the same rep.
14 deals sit in “Proposal Sent” with no future task and no customer activity in over three weeks, well past this segment’s 9-day median.
22 of the 31 deals marked Closed Lost this period have “Other” as the loss reason, with no free-text note.

What that changes

The skip pattern isn’t a broken funnel stage — it’s one rep’s habit, worth a five-minute conversation rather than a process redesign.
14 stalled Proposal-stage deals is the actual finding worth sizing: real pipeline value sitting with nobody assigned to move it.
A loss-reason field that’s 70% “Other” means the “why are we losing” question this audit was supposed to answer can’t be answered yet — so fixing that field becomes the first item on the list.

None of that was visible in the close-rate trend line the firm started with. The trend line said “something is worse.” The ledger said where, and separated a coaching conversation from a genuine backlog from a data-quality problem — three different things that a single percentage had been flattening into one.

Once you have a ledger you trust and a stalled flag that fits the segment, you have a list of deals worth a second look and a question mark next to the ones you can’t yet trust. What you don’t have yet is a reason for each one. System 02 covers the recurring shapes a stalled deal takes, and how to tell a coaching conversation from a genuine process problem.

Next in the Sales Pipeline Audit · System 02Why Deals Actually Stall 8 min read