Pipeline Health Analyzer

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)
• By value threshold (deals above $X)
• By sales stage

Query Example for Full Pipeline:

SELECT opportunityid, name, estimatedvalue, estimatedclosedate, createdon, modifiedon,
       salesstage, stepname, customerid, accountid, contactid, ownerid,
       budgetstatus, need, purchasetimeframe, purchaseprocess, decisionmaker,
       msdyn_opportunityscore, msdyn_opportunitygrade, msdyn_forecastcategory,
       closeprobability, description, currentsituation, customerneed, customerpainpoints
FROM opportunity
WHERE statecode = 0

Step 2: Analyze Individual Opportunity Health

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:

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)

2.3 Qualification Weakness Assessment Evaluate BANT (Budget, Authority, Need, Timeline) signals:

Qualification Score Calculation:

• Strong signal = 2 points
• Moderate signal = 1 point
• Weak signal = 0 points
• Total: 0-10 points (10 = fully qualified)

Flag as Under-qualified if:

• Score < 5 for deals in Propose/Close stage
• Budget is unknown for deals > $50K
• 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:

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:

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):

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

Dataverse Tables Used

Key Fields Reference

opportunity:

• salesstage (CHOICE) - Qualify(0), Develop(1), Propose(2), Close(3)
• stepname (NVARCHAR) - Current business process step
• estimatedvalue (MONEY) - Deal value
• estimatedclosedate (DATE) - Expected close
• closeprobability (INT) - Win probability % (0-100)
• budgetstatus (CHOICE) - No Committed Budget(0), May Buy(1), Can Buy(2), Will Buy(3)
• need (CHOICE) - Must have(0), Should have(1), Good to have(2), No need(3)
• purchasetimeframe (CHOICE) - Immediate(0), This Quarter(1), Next Quarter(2), This Year(3), Unknown(4)
• purchaseprocess (CHOICE) - Individual(0), Committee(1), Unknown(2)
• decisionmaker (BIT) - Decision maker confirmed (true/false)
• msdyn_opportunityscore (INT) - AI predictive score (0-100)
• msdyn_opportunitygrade (CHOICE) - Grade A(0), B(1), C(2), D(3)
• msdyn_opportunityscoretrend (CHOICE) - Improving(0), Steady(1), Declining(2), Not enough info(3)
• msdyn_forecastcategory (CHOICE) - Pipeline(100000001), Best case(100000002), Committed(100000003), Omitted(100000004), Won(100000005), Lost(100000006)
• statecode (STATE) - Open(0), Won(1), Lost(2)
• statuscode (STATUS) - In Progress(1), On Hold(2) Open; Won(3) Won; Canceled(4), Out-Sold(5) Lost

activitypointer:

• regardingobjectid (LOOKUP) - Polymorphic link to opportunity, account, contact, lead, etc.
• activitytypecode (NVARCHAR) - Type: phonecall, email, appointment, task
• createdon (DATETIME) - When activity was created
• actualstart, actualend (DATETIME) - Actual activity times
• statecode (STATE) - Open(0), Completed(1), Canceled(2), Scheduled(3-appointments only)
• scheduledstart, scheduledend (DATETIME) - Planned times

Configurable Thresholds

Organizations can customize these parameters:

• Days without activity thresholds per stage
• Stage duration benchmarks by deal size
• Qualification score minimums per stage
• Pipeline coverage targets
• Concentration risk percentages

Examples

Example 1: Weekly Pipeline Review

User says: "How is my pipeline looking this week?"

Actions:

1. Query all open opportunities owned by current user
2. Analyze each deal against health criteria (stuck, missing next steps, qualification gaps)
3. Calculate pipeline coverage vs. quota
4. Generate prioritized action list

Result:

PIPELINE HEALTH SUMMARY
- Total Pipeline: $1.2M (42 opportunities)
- Healthy Deals: 28 (67%)
- Needs Attention: 10 (24%)
- Critical: 4 (9%)

TOP ACTIONS:
1. Re-engage Contoso deal - no activity in 12 days
2. Schedule discovery call with Fabrikam - missing BANT
3. Update close date on 3 past-due opportunities

Example 2: Find Stuck Deals

User says: "Which of my deals are stuck?"

Actions:

1. Query all open opportunities
2. For each, check last activity date against stage-specific thresholds
3. Flag deals exceeding warning/critical thresholds
4. Sort by days stuck and deal value

Result:

STUCK DEALS (4 found):
1. Northwind Traders - $150K - 21 days in Propose stage (CRITICAL)
2. Alpine Ski House - $75K - 14 days in Develop stage (WARNING)

Example 3: Qualify Under-Qualified Deals

User says: "Find deals with weak qualification"

Actions:

1. Calculate BANT score for each open opportunity
2. Flag deals in late stages (Propose/Close) with low qualification scores
3. Identify specific missing qualification elements

Result:

UNDER-QUALIFIED DEALS IN LATE STAGES:
1. Contoso Enterprise Deal - $200K in Propose
   Missing: Budget not confirmed, Decision maker unknown
   Recommendation: Schedule qualification call before proposal review

Troubleshooting

Error: No opportunities found

Cause: User has no open opportunities or filter criteria too restrictive Solution:

• Check that statecode = 0 filter is correct
• Verify owner filter matches user's systemuserid
• Expand date range or remove value thresholds

Error: Activity data appears incomplete

Cause: Activities not linked to opportunity via regardingobjectid Solution:

• Check if activities are linked to account instead of opportunity
• Query activities for both opportunity and related account
• Remind users to link activities to opportunities

Error: Pipeline coverage calculation incorrect

Cause: Quota data not available or forecast categories not set Solution:

• Accept quota as user-provided input
• Check msdyn_forecastcategory values are populated
• Default to estimatedvalue if forecast fields unavailable