win-loss-pattern-analyzer

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