How to Track Salesforce Opportunity Health (0-100 Score)

Written by: Doug Camplejohn, CEO & Co-Founder, Coffee | Last updated: August 30, 2026

Key Takeaways

• Salesforce opportunities stay healthy when you track field history, apply automated scoring, and trigger real-time alerts on risk.
• Manual monitoring misses stage regressions, close-date slippage, and inactivity drift that often sit unnoticed between weekly reviews.
• A custom 0-100 Opportunity Health Score formula blends stage, days-in-stage, activity recency, close-date pushes, and amount changes to flag risk daily.
• Pipeline Inspection with color-coded thresholds plus Spring ’26 Activity Heatmap and Contacts columns makes at-risk and single-threaded deals easy to spot.
• Teams can automate data capture, dashboards, and alerts with Coffee without writing additional code.

The Problem: Manual Monitoring Hides Real Pipeline Risk

Manual pipeline monitoring fails because it is episodic. Weekly reviews often miss a substantial share of meaningful deal changes in the week they occur. Many risk signals stay hidden until the next session, which drives forecast misses.

Stage regression. Deals that move from later stages back to earlier ones have much lower close rates. When a regression sits undetected for four or more days, the window for intervention often closes before a manager ever sees it.

Close-date slippage. Deals pushed multiple times rarely close on the revised date. A close date pushed three or more times usually signals a placeholder date, and those deals should leave the committed forecast.

Inactivity drift. Sixty-eight percent of deals inactive for 21 days close-lost, so 14 days becomes the point where inactivity starts predicting close-lost outcomes. A large share of B2B pipeline is stalled at any given time. For an AE with a 1.2 million dollar quota, that represents meaningful stuck pipeline value.

Data entry dependency creates this blind spot. When reps skip logging, the CRM loses its signal. Coffee Agent fixes that by capturing every email, call, and calendar interaction automatically, so the health score reflects ground truth instead of partial rep input.

Prerequisites for Your 0-100 Health Score Setup

Confirm these items before you start the six-step setup:

• Salesforce Enterprise or Unlimited Edition for Flow automation and Pipeline Inspection
• Opportunity Field History Tracking enabled on the object (you will refine this in Step 1)
• Admin or System Administrator profile with read/write access for formula fields and Flows
• Coffee Companion App authenticated and syncing to your Salesforce org

How to Run a Health Check in Salesforce

Step 1: Turn On Opportunity Field History and Connect Coffee Activity Sync

Start by enabling field history. Navigate to Setup → Object Manager → Opportunity → Fields & Relationships → Set History Tracking. Turn on tracking for Stage, Close Date, Amount, and any custom fields used in the formula below. Salesforce creates a history record with old value, new value, user, and timestamp that appears in the History related list and does not count against storage limits. Salesforce retains field history data for up to 18 months without Field Audit Trail, or 24 months via the API.

With field history tracking enabled, the next task is to feed the health score with accurate activity data. In the Coffee Companion App, navigate to Settings → Salesforce Sync and confirm that Activity Logging is set to Auto. This setting lets Coffee Agent write Last Activity Date, call transcripts, and email interactions back to the Opportunity record without rep input, so the formula in Step 2 always scores fresh engagement.

Step 2: Create the Custom 0-100 Health Score Formula Field

Next, add the score field. Navigate to Setup → Object Manager → Opportunity → Fields & Relationships → New → Formula → Number (0 decimal places). Name the field Opportunity_Health_Score__c. Use this formula, which produces a 0–100 weighted score using five signals from the MaxIQ OHS weighted-average model and the RevOps Engineering deal health framework:

( /* Stage weight (25 pts max) */ CASE(StageName, "Discovery", 5, "Qualified", 9, "Demo Completed", 13, "Proposal Sent", 17, "Negotiation", 21, "Contract Review", 25, 0 ) /* Days-in-stage penalty (25 pts max) */ + IF(TODAY() - LastStageChangeDate__c <= 14, 25, IF(TODAY() - LastStageChangeDate__c <= 21, 18, IF(TODAY() - LastStageChangeDate__c <= 30, 10, 0) ) ) /* Activity recency via Coffee-logged Last Activity (25 pts max) */ + IF(TODAY() - LastActivityDate <= 3, 25, IF(TODAY() - LastActivityDate <= 7, 19, IF(TODAY() - LastActivityDate <= 14, 12, IF(TODAY() - LastActivityDate <= 21, 5, 0) ) ) ) /* Close-date slippage penalty (15 pts max) */ + IF(Close_Date_Push_Count__c = 0, 15, IF(Close_Date_Push_Count__c = 1, 10, IF(Close_Date_Push_Count__c = 2, 4, 0) ) ) /* Amount variance from original (10 pts max) */ + IF(Amount >= Original_Amount__c * 0.9, 10, IF(Amount >= Original_Amount__c * 0.75, 6, IF(Amount >= Original_Amount__c * 0.5, 2, 0) ) ) )

Save the field. The score recalculates on every record save. Coffee Agent’s automatic activity logging keeps LastActivityDate current without rep effort, which removes the most common failure point in manual health score setups.

Step 3: Use Pipeline Inspection with Color Thresholds and Spring ’26 Columns

Now make the score visible in your pipeline view. Navigate to Setup → Pipeline Inspection → Configure. Add Opportunity_Health_Score__c as a visible column. Apply conditional highlighting so scores from 75 to 100 display green, 50 to 74 display yellow, and below 50 display red, aligned with Velaro’s deal health scoring thresholds.

Spring ’26 introduced two Pipeline Inspection columns that support health monitoring. The Activity Heatmap column shows inbound and outbound interactions over a rolling 30-day period for each opportunity. The Contacts column shows how many individual stakeholders engaged in activities during the same 30-day window, which makes single-threaded deals obvious. Enable both columns. Single-threaded deals close at roughly half the rate of multi-threaded deals, so the Contacts column acts as a direct risk signal.

Step 4: Create Flow Alerts and Validation Rules for Low Scores

Set up proactive alerts next. In Flow Builder, create a Scheduled-Triggered Flow that runs daily at 7:00 AM. Use entry criteria of Opportunity_Health_Score__c < 60 and IsClosed = FALSE. Add an action that creates a Task for the Opportunity Owner with subject “Health Alert: Deal Below Threshold,” and a second action that emails the owner’s manager. Add a second branch for stage duration that triggers when TODAY() - LastStageChangeDate__c > 21 for mid-market cycles, following recommended mid-market inactivity thresholds of 10–14 business days that escalate to 21 days.

Then add a validation rule that blocks stage progression when Opportunity_Health_Score__c < 40 unless a manager-approved override field is true. This keeps data quality high at the point of entry instead of relying on cleanup later.

Step 5: Use Coffee Pipeline Compare for Week-over-Week Regressions

With alerts in place, add trend visibility. In the Coffee Companion App, open Pipeline Intelligence → Pipeline Compare. Coffee Agent maintains a built-in data warehouse of every field change, so week-over-week comparisons need no CSV exports or manual snapshots. The dashboard highlights progressed deals, stalled opportunities, and new additions in a single view.

Schedule a weekly digest to the RevOps Slack channel every Monday at 8:00 AM. This replaces manual pipeline snapshots and helps teams respond quickly to critical deal changes, which reduces forecast variance compared with weekly-review-only workflows.

Step 6: Send Slack and Email Digests of At-Risk Deals with Coffee Agent

Finish by routing real-time alerts through Coffee Agent. In Coffee Agent Settings → Alerts, create a rule that sends a Slack notification to the deal owner and their manager within one hour when Opportunity_Health_Score__c drops below 60 or when a close date push occurs. Set a daily digest at 6:00 PM that lists all open opportunities with scores below 60 closing within the next 45 days, following the MaxIQ OHS recommended filter for high-risk, high-urgency pipeline reviews.

Automate Steps 5 and 6 with Coffee without writing a single line of code.

Healthy vs. At-Risk Signals Benchmarks

The table below summarizes four of the most predictive risk signals from the health score formula. It shows the thresholds that separate healthy deals from at-risk ones so you can calibrate alert rules and pipeline filters with concrete numbers.

Signal	Healthy Threshold	At-Risk Threshold	Source
Days since last buyer activity	≤7 days	≥14 days	Unified Pipeline
Close-date push count	0 pushes	≥2 pushes	ORM Technologies
Stage duration vs. historical median	≤1.0x median	≥1.5x median	Outreach
Stakeholder contact count (30-day window)	≥2 contacts active	1 contact (single-threaded)	Salesforce Spring ’26

With the six-step system configured and these benchmarks in place, you can now focus on troubleshooting the most common issues that break stalled-deal detection.

How to Flag Stalled Deals in Salesforce

Three implementation issues cause most failures in stalled-deal detection setups.

Missing field history data. When Field History Tracking was off before the formula went live, LastStageChangeDate__c stays null for existing records, so the days-in-stage component returns zero. Fix this by backfilling the field using a data loader export of the OpportunityHistory object, then import the earliest stage-change date per opportunity.

Formula syntax errors. The nested IF structure in Step 2 often triggers compile errors. Validate the formula in a sandbox org first. When LastActivityDate returns null for any record, which is common before Coffee Agent finishes syncing, wrap each reference in IF(ISBLANK(LastActivityDate), 0, ...) so the entire formula does not fail.

Alert fatigue. A Flow threshold set too low, such as below 40 instead of below 60, floods reps with alerts on nearly every deal and trains them to ignore notifications. Apply the 60 and 50 split described in Step 4, using 60 for alerts and 50 for pipeline review filters, then recalibrate after 30 days of data to avoid noise.

For inactivity specifically, apply the 14-day risk threshold mentioned earlier, then tighten to 7–10 days in late-stage Negotiation or Procurement for mid-market cycles. Coffee Agent’s automatic activity logging keeps LastActivityDate aligned with every email and call, which removes the most common source of false “stalled” flags.

Validation Checklist for Your First Month

Run these checks at the end of Week 1 and again at the end of Month 1:

• Confirm that Opportunity_Health_Score__c is populated on all open opportunities. Null values usually indicate a formula error or a missing field history record.
• Verify that Coffee Agent logged at least one activity on every opportunity touched in the past seven days. A sync gap here will suppress health scores.
• Confirm that at least 80 percent of open opportunities have one or more activities in the prior 14 days. A rate below 60 percent points to a systemic adoption problem, not a scoring issue.
• Check that the Flow alert fired on every opportunity that crossed the score-60 threshold in the prior week by reviewing the Flow’s fault email log.
• Measure forecast accuracy at the end of the first full forecast period after go-live and compare it with the prior period baseline. Target a 15–25 percent accuracy improvement based on Clari 2024 benchmarks.

Scaling Health Scoring from 10 to 50 Reps

The six-step system above fits teams of 10 to 25 reps. Scaling to 50 reps requires two key adjustments.

First, segment Flow alert routing by territory or pod so managers receive alerts only for their direct reports’ deals. A single Slack channel that aggregates alerts for 50 reps creates the same alert fatigue described earlier.

Second, layer Einstein Opportunity Scoring as a secondary signal once the org has at least 200 closed-won and 200 closed-lost opportunities in the past 24 months. Einstein produces a 1–99 score recalculated daily, with default tiers of Hot (80 and above), Warm (30–79), and Cold (under 30). Use the Einstein score as a tiebreaker when two deals share a similar custom health score. A deal at health score 58 with an Einstein score of 25 (Cold) deserves faster intervention than a deal at health score 55 with an Einstein score of 72 (Warm). OHS weights remain configurable, so high-velocity teams can increase the weight on CRM completeness while enterprise teams can increase the weight on stakeholder engagement. Recalibrate the formula weights quarterly using closed-won data from the prior 90 days.

Frequently Asked Questions

How long does the full six-step setup take?

A RevOps admin with Salesforce admin access and the Coffee Companion App already connected can complete all six steps in under two hours. Steps 1 and 2, which cover Field History and formula creation, take about 30 minutes. Step 3, which configures Pipeline Inspection, takes about 15 minutes. Step 4, which builds the Flow, takes 30 to 45 minutes depending on Flow Builder experience. Steps 5 and 6, which configure Coffee Pipeline Compare and alert routing, take 15 to 20 minutes combined. Validating the formula syntax in a sandbox before production usually takes the most time and is strongly recommended.

Who owns ongoing maintenance of the health score formula?

The RevOps manager or Salesforce admin owns the formula field and Flow logic. A quarterly review of the weight distribution using the prior quarter’s closed-won and closed-lost data keeps the weights aligned with real win patterns. Coffee Agent’s Pipeline Compare dashboard surfaces week-over-week regressions automatically, so day-to-day monitoring shifts from the RevOps team to the agent. The primary human task becomes acting on alerts, not generating them.

Does this setup require any third-party tools beyond Coffee?

No additional tools are required beyond Coffee. Steps 1 through 4 rely on native Salesforce features available on Enterprise and Unlimited editions, including Field History Tracking, formula fields, Pipeline Inspection, and Flow Builder. Steps 5 and 6 use the Coffee Companion App, which connects via OAuth to your Salesforce org and writes data back to standard and custom fields. You do not need extra data enrichment tools, pipeline analytics add-ons, or Einstein licenses to implement the core 0–100 score and alert system.

What is the Salesforce opportunity health score formula?

The formula in Step 2 calculates a 0–100 score by summing five weighted components. These components are pipeline stage progression at 25 points, days-in-stage recency at 25 points, Coffee-logged activity recency at 25 points, close-date push count penalty at 15 points, and amount variance from the original deal value at 10 points. Each component awards a sliding scale of points based on defined thresholds, and the total caps at 100. Scores below 60 trigger automated alerts, while scores below 40 mark a deal that likely needs immediate intervention or removal from the pipeline.

How does Coffee Agent prevent the formula from breaking due to missing data?

The most common formula failure comes from a null LastActivityDate caused by reps not logging calls or emails. Coffee Agent prevents this by automatically capturing every email, calendar event, and call transcript from connected Google Workspace or Microsoft 365 accounts and writing the activity back to the Opportunity record in Salesforce. Because the agent handles data entry continuously instead of relying on rep updates, the LastActivityDate field stays current without human effort, and the activity recency component of the formula reflects real engagement instead of stale placeholders.

Conclusion

Inconsistent manual monitoring hides stalled deals and inflates forecasts. The six-step system described here, which combines Field History, a weighted 0–100 formula field, color-coded Pipeline Inspection with Spring ’26 Activity Heatmap and Contacts columns, Flow alerts, Coffee Pipeline Compare, and Coffee Agent Slack digests, surfaces deal risk the same day it appears instead of several days later in a weekly review.

The system works because Coffee Agent removes the dependency on rep data entry. Every activity is logged automatically, every field change is captured in the data warehouse, and every at-risk deal triggers an alert before a human notices it. Salesforce remains the system of record, while Coffee Agent acts as the always-on worker that keeps it accurate.

Build your first automated health score alert with Coffee today.