Reviews opportunities in the sales pipeline to identify stuck deals, missing next steps, weak qualification, and unbalanced stages. Use when user asks "how is my pipeline looking", "analyze pipeline health", "which deals are stuck", "pipeline review", "find at-risk deals", "identify stalled opportunities", or "audit my open deals".
Reviews opportunities in the sales pipeline to identify stuck deals, missing next steps, weak qualification, and unbalanced stages. Use when user asks "how is my pipeline looking", "analyze pipeline health", "which deals are stuck", "pipeline review", "find at-risk deals", "identify stalled opportunities", or "audit my open deals".
Sales teams often review pipelines weekly to identify at-risk deals manually. This skill automates that process by analyzing opportunity records against configurable health criteria, flagging issues, and providing prioritized recommendations to improve deal progression.
Instructions
Step 1: Determine Analysis Scope
Identify the pipeline scope to analyze:
Option A - Full Pipeline:
All open opportunities (statecode = 0)
Option B - Filtered View:
By owner (specific sales rep or team)
By time period (opportunities created/modified in range)
For each opportunity, evaluate against these health dimensions:
2.1 Stuck Deal Detection
Query activity history for each opportunity:
SELECT activityid, activitytypecode, createdon, actualend, statecode
FROM activitypointer
WHERE regardingobjectid = '[opportunityid]'
ORDER BY createdon DESC
Stuck Deal Criteria:
Stage
Days Without Activity = Warning
Days Without Activity = Critical
Qualify
7 days
14 days
Develop
10 days
21 days
Propose
5 days
10 days
Close
3 days
7 days
Flag as Stuck if:
No activities in threshold period
Only automated activities (emails) but no calls/meetings
Last activity was over 2 weeks ago regardless of stage
2.2 Missing Next Steps Analysis
Check for scheduled future activities:
SELECT activityid, activitytypecode, scheduledstart, scheduledend, subject, statecode
FROM activitypointer
WHERE regardingobjectid = '[opportunityid]'
AND statecode = 0
Note: Filter results where scheduledstart > current date. Activity statecodes:
0 = Open (scheduled activities)
1 = Completed
2 = Canceled
3 = Scheduled (specific to appointments)
Flag if:
No scheduled future tasks or appointments
Next scheduled activity is more than 2 weeks away
Scheduled activity is past due (scheduledend < today, still open)
No decision maker identified for deals in Committee process
2.4 Stage Age Analysis
Calculate time in current stage:
Use stage transition tracking or estimate from modifiedon
Compare against expected stage duration benchmarks
Typical Stage Duration Benchmarks:
Stage
Small Deal (<$25K)
Medium Deal ($25K-$100K)
Enterprise (>$100K)
Qualify
7 days
14 days
21 days
Develop
14 days
30 days
60 days
Propose
7 days
14 days
30 days
Close
14 days
30 days
45 days
Flag if: Time in stage > 1.5x benchmark
2.5 Close Date Validity
Check estimated close date reasonableness:
Flag if:
estimatedclosedate is in the past
estimatedclosedate has been pushed out 3+ times (if tracking available)
Close date is within 7 days but stage is Qualify/Develop
Close date is > 6 months out but forecast category is "Committed"
2.6 Competitive Risk Assessment
Analyze competitive signals from opportunity fields:
Check description, currentsituation for competitor mentions
Evaluate against known competitor patterns
Flag if:
Competitor explicitly mentioned
"Evaluating alternatives" or similar language detected
Multiple vendors mentioned
Step 3: Pipeline Balance Analysis
3.1 Stage Distribution
SELECT salesstage, COUNT(opportunityid) as count, SUM(estimatedvalue) as total_value
FROM opportunity
WHERE statecode = 0
GROUP BY salesstage
Healthy Pipeline Ratios:
Stage
Ideal Count %
Ideal Value %
Qualify
40-50%
30-40%
Develop
25-35%
25-35%
Propose
15-20%
20-25%
Close
5-15%
10-20%
Flag Imbalances:
Top-Heavy: Too many deals in early stages (lead gen working, conversion not)
Bottom-Heavy: Too few deals in early stages (future quarter risk)
Propose Jam: Deals stuck in proposal phase (pricing or competitive issues)
3.2 Close Date Clustering
Analyze deals by expected close timing:
SELECT estimatedclosedate, COUNT(opportunityid) as count, SUM(estimatedvalue) as total_value
FROM opportunity
WHERE statecode = 0
GROUP BY estimatedclosedate
Flag if:
50% of pipeline value closing in last week of quarter ("hockey stick")
Current month has more committed deals than capacity
Next quarter has insufficient pipeline coverage
3.3 Value Distribution
Analyze deal size distribution using separate queries (CASE not supported in Dataverse SQL):
Small Deals (<$25K):
SELECT COUNT(opportunityid) as count, SUM(estimatedvalue) as total_value
FROM opportunity
WHERE statecode = 0 AND estimatedvalue < 25000
Medium Deals ($25K-$100K):
SELECT COUNT(opportunityid) as count, SUM(estimatedvalue) as total_value
FROM opportunity
WHERE statecode = 0 AND estimatedvalue >= 25000 AND estimatedvalue < 100000
Enterprise Deals (>$100K):
SELECT COUNT(opportunityid) as count, SUM(estimatedvalue) as total_value
FROM opportunity
WHERE statecode = 0 AND estimatedvalue >= 100000
Flag Concentration Risk:
Single deal > 30% of pipeline value
Top 3 deals > 50% of pipeline value
Over-reliance on one deal size category
Step 4: Generate Health Scores and Insights
Individual Opportunity Health Score (0-100):
Component
Weight
Max Points
Activity Recency
25%
25
Next Steps Defined
20%
20
Qualification Strength
25%
25
Stage Velocity
15%
15
Close Date Validity
15%
15
Score Interpretation:
80-100: Healthy - On track
60-79: At Risk - Needs attention
40-59: Critical - Immediate action required
0-39: Stalled - May need to be disqualified
Step 5: Output Pipeline Health Report
Executive Summary:
PIPELINE HEALTH REPORT
Generated: [Date]
Scope: [Owner/Team] Open Opportunities
PIPELINE SNAPSHOT
═══════════════════════════════════════════════════
Total Pipeline Value: $2,450,000
Opportunity Count: 47
Weighted Pipeline: $1,225,000 (based on close probability)
Average Deal Size: $52,128
Average Health Score: 68/100
HEALTH DISTRIBUTION
═══════════════════════════════════════════════════
Healthy (80+): 18 deals $890,000 (36%)
At Risk (60-79): 15 deals $720,000 (29%)
Critical (40-59): 10 deals $540,000 (22%)
Stalled (<40): 4 deals $300,000 (12%)
STAGE BALANCE
═══════════════════════════════════════════════════
Qualify: 23 deals $680,000 ⚠️ (49% - slight excess)
Develop: 12 deals $720,000 ✓ (26% - healthy)
Propose: 8 deals $650,000 ✓ (17% - healthy)
Close: 4 deals $400,000 ⚠️ (9% - needs more)
Priority Action List:
IMMEDIATE ATTENTION REQUIRED
════════════════════════════════════════════════════
1. 🔴 Contoso Enterprise Deal - $150,000
Health Score: 35/100 (Stalled)
Issues:
- No activity in 23 days
- No scheduled next steps
- Close date was yesterday (past due)
Recommendation: Schedule urgent call to re-engage
2. 🟠 Fabrikam Cloud Migration - $85,000
Health Score: 52/100 (Critical)
Issues:
- Budget not confirmed (deal in Propose stage)
- Decision maker not identified
- Competitor mentioned in notes
Recommendation: Qualify budget and identify champion
3. 🟡 Acme Corp Expansion - $65,000
Health Score: 64/100 (At Risk)
Issues:
- Stage age: 45 days in Develop (benchmark: 30 days)
- Next scheduled call is 3 weeks away
Recommendation: Accelerate next steps, check for blockers
Pipeline Risks:
STRATEGIC RISKS IDENTIFIED
════════════════════════════════════════════════════
⚠️ CONCENTRATION RISK
Top 3 deals = 52% of pipeline value
- Contoso Enterprise: $150,000
- Fabrikam Cloud: $85,000
- Global Industries: $75,000
⚠️ CLOSE DATE CLUSTERING
67% of Q1 value expected in final 2 weeks
High probability of slip to Q2
⚠️ QUALIFICATION GAP
12 deals in Propose stage without confirmed budget
Combined value at risk: $340,000
⚠️ PIPELINE COVERAGE
Q2 pipeline coverage: 1.8x target
Recommended: 3x minimum
Action: Increase early-stage prospecting
Step 6: Drill-Down Recommendations
For Stuck Deals:
Suggest specific re-engagement messages
Recommend alternative contacts to try
Propose disqualification if multiple attempts failed
For Under-Qualified Deals:
Generate discovery questions to fill BANT gaps
Suggest qualification call agenda
Recommend resources (case studies, ROI calculators)
For Pipeline Imbalance:
If top-heavy: Focus on conversion activities
If bottom-heavy: Increase prospecting
If propose jam: Review pricing strategy, competitive positioning
