@microsoft/win-loss-pattern-analyzer

---
name: win-loss-pattern-analyzer
description: Analyzes closed opportunities to identify patterns between won and lost deals. Compares sales cycles, activities, stakeholder engagement, and deal characteristics for actionable playbook insights. Use when user asks "why are we losing deals", "win loss analysis", "what makes deals win", "analyze lost opportunities", "sales pattern analysis", "compare won vs lost deals", or "improve win rate".
metadata:
  author: Dataverse
  version: 1.0.0
  category: sales-analytics
---

# Win/Loss Pattern Analyzer

Sales teams need to understand why they win and lose deals. This skill analyzes historical closed opportunities to identify patterns, highlight what winners do differently, recognize common loss signals, and provide data-driven playbook recommendations.

## Instructions

### Step 1: Define Analysis Scope
When user asks "Why are we losing enterprise deals in manufacturing vertical?":

**1.1 Parse Analysis Criteria:**
Extract filters from user query:
- Deal size segment (enterprise, mid-market, SMB)
- Industry/vertical (manufacturing, financial services, etc.)
- Time period (last 6 months, last year, etc.)
- Sales stage of loss (if applicable)
- Competitor (if mentioned)
- Owner/team (if specified)

**1.2 Build Analysis Query:**

**Important: Dataverse SQL Limitations**
Dataverse SQL does NOT support: DATEADD(), GETUTCDATE(), subqueries, HAVING, DISTINCT, UNION, CASE, DATEDIFF.
Calculate date filters and durations programmatically (e.g., use '2025-03-01' for last year).

```
SELECT opportunityid, name, accountid, customerid, estimatedvalue, actualvalue,
       createdon, actualclosedate, statecode, statuscode,
       salesstage, closeprobability, budgetstatus, need, 
       purchasetimeframe, purchaseprocess, decisionmaker,
       description, qualificationcomments, msdyn_forecastcategory,
       ownerid
FROM opportunity
WHERE actualclosedate > '2025-03-01'
```

Then filter programmatically for statecode = 1 (Won) or statecode = 2 (Lost).

**Add Segment Filters:**
```
-- Enterprise deals (example threshold)
SELECT opportunityid, name, accountid, estimatedvalue, actualvalue,
       createdon, actualclosedate, statecode, statuscode
FROM opportunity
WHERE actualclosedate > '2025-03-01'
AND estimatedvalue >= 100000

-- Industry filter (via account join)
SELECT o.opportunityid, o.name, o.estimatedvalue, a.industrycode
FROM opportunity o
JOIN account a ON o.accountid = a.accountid
WHERE o.actualclosedate > '2025-03-01'
AND a.industrycode = 12
```

#### Step 2: Segment Won vs Lost Deals

**2.1 Basic Segmentation:**
```
WON DEALS:
- Count: [N]
- Total Value: $[X]
- Average Value: $[Y]
- Win Rate: [Z%]

LOST DEALS:
- Count: [N]
- Total Value: $[X]
- Average Value: $[Y]
- Common Loss Reasons (statuscode):
  - Out-Sold (5): [N]
  - Canceled (4): [N]
```

**2.2 Query Won Deals:**
```
SELECT opportunityid, name, accountid, actualvalue, actualclosedate,
       createdon, salesstage, closeprobability,
       budgetstatus, need, purchasetimeframe, decisionmaker, ownerid
FROM opportunity
WHERE statecode = 1
AND actualclosedate > '2025-03-01'
```

**2.3 Query Lost Deals:**
```
SELECT opportunityid, name, accountid, estimatedvalue, actualclosedate,
       createdon, statuscode, salesstage,
       budgetstatus, need, purchasetimeframe, decisionmaker, ownerid
FROM opportunity
WHERE statecode = 2
AND actualclosedate > '2025-03-01'
```

#### Step 3: Analyze Sales Cycle Patterns

**3.1 Calculate Sales Cycle Length:**
```
For each opportunity (calculate programmatically):
sales_cycle_days = actualclosedate - createdon (in days)
```

Note: DATEDIFF is not supported in Dataverse SQL. Calculate durations in your application logic.

**3.2 Compare Won vs Lost Cycles:**
```
WON DEALS:
- Average Sales Cycle: [X] days
- Median Sales Cycle: [Y] days
- Shortest: [Z] days
- Longest: [W] days

LOST DEALS:
- Average Sales Cycle: [X] days
- Median Sales Cycle: [Y] days
- Shortest: [Z] days
- Longest: [W] days

INSIGHT:
- Deals that drag [X]+ days are [Y]x more likely to lose
- Sweet spot for wins: [A] - [B] days
```

**3.3 Analyze Stage Duration:**
```
For each deal, calculate time in each stage:
- Time in Qualify
- Time in Develop
- Time in Propose
- Time in Close

Compare averages between won and lost:
| Stage | Won Avg Days | Lost Avg Days | Difference |
|-------|--------------|---------------|------------|
| Qualify | [X] | [Y] | [+/-Z] |
| Develop | [X] | [Y] | [+/-Z] |
| Propose | [X] | [Y] | [+/-Z] |
| Close | [X] | [Y] | [+/-Z] |
```

#### Step 4: Analyze Activity Patterns

**4.1 Query Activities for Won Deals:**
```
SELECT o.opportunityid, a.activitytypecode, COUNT(*) as count
FROM opportunity o
JOIN activitypointer a ON o.opportunityid = a.regardingobjectid
WHERE o.statecode = 1
AND [segment filters]
GROUP BY o.opportunityid, a.activitytypecode
```

**4.2 Query Activities for Lost Deals:**
```
SELECT o.opportunityid, a.activitytypecode, COUNT(*) as count
FROM opportunity o
JOIN activitypointer a ON o.opportunityid = a.regardingobjectid
WHERE o.statecode = 2
AND [segment filters]
GROUP BY o.opportunityid, a.activitytypecode
```

**4.3 Compare Activity Metrics:**
```
| Metric | Won Avg | Lost Avg | Delta | Insight |
|--------|---------|----------|-------|---------|
| Total Activities | [X] | [Y] | [+/-Z] | Winners have more touchpoints |
| Phone Calls | [X] | [Y] | [+/-Z] | Calls correlate with wins |
| Meetings | [X] | [Y] | [+/-Z] | Face time matters |
| Emails | [X] | [Y] | [+/-Z] | Similar email volume |
| Tasks Completed | [X] | [Y] | [+/-Z] | Execution discipline |

ACTIVITY PATTERNS:
- Won deals average [X] activities, lost deals average [Y]
- [Z]% more phone calls in won deals
- Won deals have [W]x more in-person meetings
```

**4.4 Activity Frequency Analysis:**
```
Calculate activities per week throughout sales cycle:

Won Deals Activity Curve:
Week 1: [avg activities]
Week 2: [avg activities]
Week 3: [avg activities]
...

Lost Deals Activity Curve:
Week 1: [avg activities]
Week 2: [avg activities]
Week 3: [avg activities]
...

INSIGHT:
- Winners maintain consistent engagement
- Losers often show activity gaps in weeks [X-Y]
- Key difference at [stage]: winners have [X] more activities
```

#### Step 5: Analyze Stakeholder Engagement

**5.1 Count Contacts Engaged:**

Contacts involved in activities are stored in `activityparty`, not `activitypointer`. Run per opportunity:
```
SELECT ap.partyid, ap.participationtypemask
FROM activityparty ap
JOIN activitypointer a ON ap.activityid = a.activityid
WHERE a.regardingobjectid = '[opportunityid]'
AND a.statecode = 1
```
Deduplicate `partyid` values programmatically per opportunity to count unique contacts engaged.

**5.2 Analyze Contact Roles:**
```
Identify roles of engaged contacts:
- Economic Buyers (C-level, VP)
- Technical Evaluators (Directors, Managers)
- End Users (Individual Contributors)
- Procurement/Legal
```

**5.3 Compare Stakeholder Engagement:**
```
| Metric | Won Deals | Lost Deals | Insight |
|--------|-----------|------------|---------|
| Avg Contacts Engaged | [X] | [Y] | Wider engagement wins |
| Economic Buyer Engaged | [X%] | [Y%] | Must engage decision maker |
| Multi-threaded (3+ contacts) | [X%] | [Y%] | Don't single-thread |
| Champion Identified | [X%] | [Y%] | Internal advocates matter |

KEY FINDING:
- Won deals engaged [X] contacts on average vs [Y] for lost
- [Z%] of won deals had economic buyer engagement vs [W%] for lost
- Single-threaded deals ([1 contact]) have [X%] lower win rate
```

#### Step 6: Analyze Qualification Patterns

**6.1 Compare BANT Completion:**
```
| Qualification Factor | Won % Complete | Lost % Complete | Gap |
|---------------------|----------------|-----------------|-----|
| Budget Confirmed | [X%] | [Y%] | [Z%] |
| Decision Maker ID'd | [X%] | [Y%] | [Z%] |
| Need Validated | [X%] | [Y%] | [Z%] |
| Timeline Defined | [X%] | [Y%] | [Z%] |
| Process Understood | [X%] | [Y%] | [Z%] |

INSIGHT:
- Biggest qualification gap: [Factor] ([X%] difference)
- Deals without confirmed budget are [X]x more likely to lose
- Decision maker engagement is strongest predictor of win
```

**6.2 Analyze by Sales Stage at Loss:**
```
For lost deals, at which stage did they lose?

| Stage at Loss | Count | % of Losses | Value Lost |
|---------------|-------|-------------|------------|
| Qualify | [N] | [X%] | $[Y] |
| Develop | [N] | [X%] | $[Y] |
| Propose | [N] | [X%] | $[Y] |
| Close | [N] | [X%] | $[Y] |

INSIGHT:
- [X%] of losses happen at [Stage]
- Late-stage losses (Propose/Close) indicate [specific issue]
- Early losses (Qualify) may indicate poor lead quality
```

#### Step 7: Analyze Deal Characteristics

**7.1 Compare Deal Attributes:**
```
| Attribute | Won Avg/Mode | Lost Avg/Mode | Pattern |
|-----------|--------------|---------------|---------|
| Deal Size | $[X] | $[Y] | [Larger/smaller lose more?] |
| Company Size | [X] employees | [Y] employees | [Sweet spot?] |
| Industry Mix | [breakdown] | [breakdown] | [Industry patterns] |
| Source | [top sources] | [top sources] | [Source quality] |

DEAL SIZE ANALYSIS:
- Win rate by deal size:
  $0-25K: [X%]
  $25K-100K: [Y%]
  $100K-500K: [Z%]
  $500K+: [W%]
  
INSIGHT: Win rate drops significantly above $[threshold]
```

**7.2 Competitive Analysis:**
```
Analyze mentions of competitors in descriptions and notes:

| Competitor | Times Faced | We Won | We Lost | Win Rate vs Them |
|------------|-------------|--------|---------|------------------|
| Competitor A | [N] | [W] | [L] | [X%] |
| Competitor B | [N] | [W] | [L] | [X%] |
| Competitor C | [N] | [W] | [L] | [X%] |
| No Competitor | [N] | [W] | [L] | [X%] |

INSIGHT:
- We struggle against [Competitor] (only [X%] win rate)
- We dominate against [Competitor] ([Y%] win rate)
- Deals with no competition have [Z%] win rate
```

#### Step 8: Identify Differentiating Factors

**8.1 Statistical Analysis:**
Calculate correlation between each factor and win probability:

```
FACTORS MOST CORRELATED WITH WINNING:
1. Economic buyer engagement (r = 0.72)
2. Number of meetings (r = 0.65)
3. Multi-threading (3+ contacts) (r = 0.61)
4. Budget confirmation (r = 0.58)
5. Response time to inquiries (r = 0.54)

FACTORS MOST CORRELATED WITH LOSING:
1. Single-threaded relationship (r = -0.68)
2. Extended time in Propose stage (r = -0.55)
3. Competitor [X] involvement (r = -0.52)
4. Activity gaps > 10 days (r = -0.48)
5. Missing decision maker engagement (r = -0.45)
```

**8.2 What Winners Did Differently:**
```
WINNING BEHAVIORS:
✓ Engaged economic buyer by [X] stage in [Y%] of won deals
✓ Averaged [X] in-person/video meetings
✓ Multi-threaded with [X]+ stakeholders
✓ Confirmed budget before Propose stage
✓ Maintained [X] activities per week minimum
✓ Closed within [X] days of proposal submission
✓ Had internal champion identified

LOSING BEHAVIORS:
✗ Single-threaded with only one contact
✗ Skipped discovery, went straight to proposal
✗ Long gaps between activities (10+ days)
✗ Never engaged economic buyer
✗ Let proposal sit for [X]+ days without follow-up
✗ Didn't qualify budget until late stage
```

#### Step 9: Generate Playbook Insights

**Output Format:**

```
════════════════════════════════════════════════════════════════
WIN/LOSS PATTERN ANALYSIS
════════════════════════════════════════════════════════════════

Analysis Criteria:
• Segment: Enterprise deals (>$100K)
• Industry: Manufacturing
• Time Period: Last 12 months
• Total Deals Analyzed: [N] (Won: [W], Lost: [L])

════════════════════════════════════════════════════════════════
EXECUTIVE SUMMARY
════════════════════════════════════════════════════════════════

Overall Win Rate: [X%]
Average Deal Size - Won: $[X] | Lost: $[Y]
Average Sales Cycle - Won: [X] days | Lost: [Y] days

TOP 3 REASONS WE'RE LOSING:
1. Single-threaded relationships (72% of losses)
2. Late-stage competitor entry (48% of losses)
3. Extended time in Propose stage (43% of losses)

TOP 3 THINGS WINNERS DO DIFFERENTLY:
1. Engage economic buyer before Propose stage
2. Multi-thread with 4+ stakeholders
3. Maintain weekly touchpoints throughout cycle

════════════════════════════════════════════════════════════════
DETAILED ANALYSIS
════════════════════════════════════════════════════════════════

📊 SALES CYCLE COMPARISON
────────────────────────────────────────────────────────────────

                    WON          LOST         INSIGHT
Average Cycle:      68 days      92 days      Winners close 26% faster
Time in Qualify:    12 days      18 days      Faster qualification
Time in Develop:    28 days      35 days      Efficient discovery
Time in Propose:    18 days      32 days      ⚠️ Key difference
Time in Close:      10 days      7 days       Similar (losses give up)

KEY INSIGHT: Deals in Propose stage >25 days have 67% lower win rate.
ACTION: If no progress after 20 days, escalate or re-qualify.

📊 ACTIVITY PATTERN COMPARISON
────────────────────────────────────────────────────────────────

                    WON AVG      LOST AVG     DELTA
Total Activities:   24           16           +50%
Phone Calls:        8            4            +100% ⬆️
Meetings:           5            2            +150% ⬆️
Emails:             10           9            Similar
Tasks:              6            3            +100%

ACTIVITY FREQUENCY:
- Won deals: 3.2 activities/week average
- Lost deals: 1.8 activities/week average
- Gap widens in weeks 3-6 of sales cycle

KEY INSIGHT: Winning deals have 2x the phone calls and meetings.
ACTION: Minimum 2 calls and 1 meeting per week during active pursuit.

📊 STAKEHOLDER ENGAGEMENT
────────────────────────────────────────────────────────────────

                        WON         LOST        DELTA
Contacts Engaged:       4.2         1.8         +133% ⬆️
Economic Buyer Met:     78%         23%         +55% ⬆️⬆️
Champion Identified:    85%         34%         +51% ⬆️⬆️
Multi-threaded (3+):    72%         18%         +54% ⬆️⬆️

CONTACT ROLE ANALYSIS:
- Won deals engage VP+ in 78% of cases
- Lost deals often stuck at Manager level
- Single-threaded deals win only 12% of the time

KEY INSIGHT: Economic buyer engagement is the #1 predictor of winning.
ACTION: Do not move to Propose without executive meeting scheduled.

📊 QUALIFICATION ANALYSIS
────────────────────────────────────────────────────────────────

                        WON         LOST        GAP
Budget Confirmed:       82%         41%         +41%
Decision Maker ID:      91%         52%         +39%
Need Validated:         88%         67%         +21%
Timeline Defined:       76%         48%         +28%
Process Understood:     84%         35%         +49% ⬆️

KEY INSIGHT: Understanding the buying process is critical but often skipped.
ACTION: Always ask "Walk me through your decision process" in discovery.

📊 COMPETITIVE ANALYSIS
────────────────────────────────────────────────────────────────

COMPETITOR WIN RATES:
• vs. Acme Corp: 35% (significant weakness)
• vs. GlobalTech: 62% (slight advantage)  
• vs. No competitor: 78% (best scenario)
• Multi-vendor evaluation: 42%

ACME CORP PATTERN:
- They win on price (mentioned in 80% of losses to them)
- We win on service/support (mentioned in wins against them)
- They entered late-stage in 60% of their wins

KEY INSIGHT: When Acme is involved, emphasize support/service early.
ACTION: If Acme detected, proactively address price with ROI analysis.

════════════════════════════════════════════════════════════════
PLAYBOOK RECOMMENDATIONS
════════════════════════════════════════════════════════════════

STAGE: QUALIFY
────────────────────────────────────────────────────────────────
□ Confirm budget status in first substantive call
□ Identify and map all stakeholders by role
□ Understand buying process and timeline
□ Ask about other solutions being evaluated

Benchmark: Complete Qualify in ≤14 days
Red Flag: Can't identify economic buyer after 2 weeks

STAGE: DEVELOP
────────────────────────────────────────────────────────────────
□ Schedule meeting with economic buyer
□ Engage minimum 3 stakeholders
□ Identify and enable champion
□ Complete technical validation
□ Address competitive positioning proactively

Benchmark: Minimum 3 activities per week
Red Flag: Only one contact engaged; no exec access

STAGE: PROPOSE
────────────────────────────────────────────────────────────────
□ Proposal walk-through meeting (don't just email)
□ Economic buyer reviews proposal directly
□ Address objections within 48 hours
□ Negotiate with decision-maker present

Benchmark: Decision within 21 days of proposal
Red Flag: >25 days in Propose without progress

STAGE: CLOSE
────────────────────────────────────────────────────────────────
□ Verbal commitment from economic buyer
□ Legal/procurement engaged early
□ Implementation timeline discussed
□ Success criteria defined

Benchmark: Contract signed within 14 days of verbal
Red Flag: Legal delays without clear timeline

════════════════════════════════════════════════════════════════
EARLY WARNING SIGNALS
════════════════════════════════════════════════════════════════

Watch for these loss indicators:
⚠️ Single contact engagement after 3 weeks
⚠️ Can't schedule economic buyer meeting
⚠️ Activity gap >10 days in active stage
⚠️ Competitor mentioned in late stage
⚠️ Budget discussion deferred repeatedly
⚠️ Proposal sitting without response >14 days
⚠️ "We need more time" without specific next step

════════════════════════════════════════════════════════════════
```

### Dataverse Tables Used
| Table | Purpose |
|-------|---------|
| `opportunity` | Primary entity for win/loss data |
| `account` | Industry and firmographic filters |
| `contact` | Stakeholder engagement analysis |
| `activitypointer` | Activity pattern analysis |
| `phonecall` | Call frequency analysis |
| `appointment` | Meeting frequency analysis |
| `email` | Email engagement |
| `annotation` | Notes for competitive intelligence |

### Key Fields Reference
**opportunity:**
- `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]
- `createdon` (DATETIME) - Start of sales cycle
- `actualclosedate` (DATE) - End of sales cycle (when closed)
- `estimatedvalue` (MONEY) - Expected deal size
- `actualvalue` (MONEY) - Actual won value
- `salesstage` (CHOICE) - Stage at close: Qualify(0), Develop(1), Propose(2), Close(3)
- `budgetstatus` (CHOICE) - No 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)
- `decisionmaker` (BIT) - Was decision maker engaged
- `closeprobability` (INT) - Final win probability (0-100)
- `msdyn_opportunityscore` (INT) - AI predictive score at close
- `msdyn_opportunitygrade` (CHOICE) - Grade A(0), B(1), C(2), D(3)

**account:**
- `industrycode` (CHOICE) - Industry classification (Accounting(1), Agriculture(2), etc.)
- `numberofemployees` (INT) - Company size
- `revenue` (MONEY) - Annual revenue

### Analysis Best Practices

1. **Sufficient sample size:** Need minimum 20-30 deals for meaningful patterns
2. **Control variables:** Compare similar deal sizes and industries
3. **Time relevance:** Focus on recent data (last 12-18 months)
4. **Qualitative context:** Numbers show what, notes show why
5. **Action-oriented:** Every insight should drive specific behavior change
6. **Regular refresh:** Re-run analysis quarterly to update playbook

## Examples

### Example 1: Why Are We Losing Enterprise Deals?

**User says:** "Why are we losing enterprise deals in manufacturing?"

**Actions:**
1. Filter opportunities: estimatedvalue > $100K, industry = manufacturing
2. Segment into won (statecode=1) vs lost (statecode=2)
3. Compare sales cycles, activity patterns, stakeholder engagement
4. Identify statistically significant differentiators

**Result:**
```
WIN/LOSS ANALYSIS: Enterprise Manufacturing
Period: Last 12 months | Won: 12 | Lost: 18 | Win Rate: 40%

KEY DIFFERENTIATORS:

1. SALES CYCLE
   Won: 65 days avg | Lost: 98 days avg
   Insight: Deals dragging past 80 days are 3x more likely to lose

2. STAKEHOLDER ENGAGEMENT
   Won: 3.2 contacts engaged avg | Lost: 1.4 contacts
   Insight: Multi-threading increases win rate by 45%

3. ACTIVITY PATTERNS
   Won: 2.1 meetings per deal | Lost: 0.8 meetings
   Insight: Face-to-face engagement critical in manufacturing

RECOMMENDATIONS:
- Set 80-day checkpoint for deal health review
- Require 3+ contacts engaged before Propose stage
- Schedule on-site visits for deals > $100K
```

### Example 2: Compare Top Rep vs Team Average

**User says:** "What does Sarah do differently to win more deals?"

**Actions:**
1. Filter Sarah's closed deals vs team average
2. Compare activity types, timing, stakeholder engagement
3. Identify behavioral differences

**Result:**
```
SARAH'S WINNING PATTERNS:

1. SPEED TO FIRST MEETING
   Sarah: 2.1 days | Team avg: 5.3 days
   
2. MULTI-THREADING
   Sarah: Always adds 2nd contact by day 7
   Team avg: Single contact for first 21 days
   
3. ACTIVITY MIX
   Sarah: 60% calls, 30% meetings, 10% email
   Team: 20% calls, 10% meetings, 70% email

PLAYBOOK RECOMMENDATION:
Adopt Sarah's "24-48-7" rule: First call within 24 hours, 
first meeting within 48 hours, second contact by day 7.
```

### Example 3: Competitive Loss Analysis

**User says:** "Why are we losing to Acme Corp?"

**Actions:**
1. Find lost deals with Acme mentioned (description, notes, opportunityclose)
2. Analyze deal characteristics and loss patterns
3. Generate competitive positioning recommendations

**Result:**
```
COMPETITIVE ANALYSIS: vs Acme Corp
Deals Lost to Acme: 8 | Total Competitive: 15 | Loss Rate: 53%

LOSS PATTERNS:
- 75% lost on price (Acme undercuts by avg 20%)
- 25% lost on feature (Acme's mobile app cited)

WHERE WE WIN vs ACME:
- Enterprise segment (62% win rate)
- When economic buyer engaged early
- When implementation timeline is priority

BATTLECARD TALKING POINTS:
1. Emphasize TCO, not initial price
2. Lead with enterprise security/compliance
3. Engage economic buyer before Acme can anchor on price
```

## Troubleshooting

### Error: Insufficient data for analysis
**Cause:** Too few closed deals matching criteria
**Solution:**
- Expand date range
- Broaden filters (combine industries, expand size range)
- Note limited sample size in results

### Error: No clear patterns found
**Cause:** High variance in data or too many confounding variables
**Solution:**
- Narrow analysis to more homogeneous segment
- Focus on extreme cases (fastest wins, longest losses)
- Supplement with qualitative analysis of notes

### Error: Activity data incomplete
**Cause:** Activities not consistently linked to opportunities
**Solution:**
- Query activities for related accounts as backup
- Use date overlap method (activities during deal lifecycle)
- Advise on data hygiene improvements