Next Best Action Engine

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

SELECT accountid, name, industrycode, numberofemployees, revenue,
       telephone1, emailaddress1, websiteurl, primarycontactid
FROM account
WHERE accountid = '[accountid from opportunity]'

SELECT contactid, fullname, jobtitle, emailaddress1, telephone1,
       accountrolecode, msdyn_decisioninfluencetag
FROM contact
WHERE accountid = '[accountid]'

SELECT activityid, activitytypecode, subject, description,
       createdon, actualstart, actualend, statecode, statuscode,
       prioritycode, ownerid
FROM activitypointer
WHERE regardingobjectid = '[opportunityid]'
ORDER BY createdon DESC

Activity Summary:
- Total Activities: [count]
- Last 30 Days: [count]
- Activity Types: [breakdown]
- Last Activity: [date and type]
- Days Since Last Activity: [N]

Engagement Trend:
- Week 1: [count]
- Week 2: [count]
- Week 3: [count]
- Week 4: [count]
- Trend: [Increasing/Stable/Declining]

For each contact at the account, determine:
- Last interaction date
- Interaction count
- Role (Decision Maker, Influencer, Champion, Blocker, User)
- Engagement level (High/Medium/Low/None)

Check for missing stakeholder engagement:
- Economic Buyer (C-level/VP): [Engaged? Y/N]
- Technical Evaluator: [Engaged? Y/N]
- End User: [Engaged? Y/N]
- Procurement: [Engaged? Y/N] (for late-stage deals)
- Champion: [Identified? Y/N]

Recommended stakeholders to engage: [List]

SELECT c.contactid, c.fullname, c.jobtitle, c.accountrolecode,
       c.msdyn_decisioninfluencetag
FROM contact c
WHERE c.accountid = '[accountid]'

SELECT opportunityid, name, accountid, estimatedvalue, actualvalue,
       salesstage, actualclosedate, createdon,
       budgetstatus, need, purchasetimeframe
FROM opportunity
WHERE statecode = 1
AND actualclosedate > '2024-03-01'

SELECT o.opportunityid, o.name, o.estimatedvalue, a.industrycode, a.numberofemployees
FROM opportunity o
JOIN account a ON o.accountid = a.accountid
WHERE o.statecode = 1
AND o.actualclosedate > '2024-03-01'

similarity_score = (
  size_match_score * 0.25 +
  industry_match_score * 0.20 +
  stage_match_score * 0.20 +
  company_match_score * 0.15 +
  qualification_match_score * 0.20
)

SELECT activitytypecode, COUNT(*) as count, 
       MIN(createdon) as first_activity,
       MAX(createdon) as last_activity
FROM activitypointer
WHERE regardingobjectid = '[won_opportunity_id]'
GROUP BY activitytypecode

Calculate for each similar won deal at this stage:
- What activity type came next?
- How long did they spend in this stage?
- What was the outcome?
- Which stakeholders were engaged?

When similar deals were in [Current Stage]:
- 75% had a demo/presentation next
- 60% engaged the economic buyer
- 50% sent a proposal within 2 weeks
- Average time in stage: 14 days

Priority Actions:
1. Complete qualification (BANT gaps identified)
2. Identify and engage decision maker
3. Schedule discovery meeting
4. Understand competitive landscape

Priority Actions:
1. Demonstrate product/solution fit
2. Expand stakeholder map
3. Address technical requirements
4. Build internal champion

Priority Actions:
1. Present formal proposal
2. Handle objections
3. Engage procurement/legal
4. Negotiate terms

Priority Actions:
1. Finalize contract details
2. Get verbal commitment
3. Process signatures
4. Address last-minute concerns

NEXT BEST ACTION RECOMMENDATION
════════════════════════════════════════════════════

OPPORTUNITY: [Name]
CURRENT STAGE: [Stage]
TIME IN STAGE: [X days]
HEALTH SCORE: [X/100]

════════════════════════════════════════════════════
RECOMMENDED NEXT ACTION
════════════════════════════════════════════════════

📌 [SPECIFIC ACTION]

Example: "Schedule a meeting with Sarah Chen (VP Operations) to present ROI analysis"

WHY THIS ACTION:
• Similar won deals had [X% higher close rate] when economic buyer was engaged at this stage
• Sarah Chen is the VP Operations and likely economic buyer (not yet engaged)
• You've had 4 interactions with technical team but none with leadership
• Competitive intelligence suggests prospect is evaluating alternatives

WHAT SUCCESSFUL DEALS DID:
• 78% of similar won deals engaged executive sponsor before Propose stage
• Average of 2.3 executive meetings before close
• Deals with VP+ engagement closed 40% faster

HOW TO EXECUTE:
1. Ask your champion [Contact Name] for an introduction to Sarah Chen
2. Prepare executive-level ROI summary (attach template)
3. Propose 30-minute business value discussion
4. Bring in your sales leader for executive alignment

TALKING POINTS:
• Business outcomes achieved by [Similar Customer in same industry]
• ROI: [X]% improvement in [metric] over [timeframe]
• Risk of inaction: [competitive/market pressure point]

SUGGESTED TIMELINE:
• Reach out to champion: Today
• Executive meeting: Within 1 week
• This will enable: Moving to [Next Stage] in 2 weeks

ALTERNATIVE ACTIONS (if primary not feasible):
════════════════════════════════════════════════════

Option B: Schedule Technical Deep-Dive
• If champion unavailable, continue building technical consensus
• Request meeting with IT/Engineering stakeholders
• Demonstrate integration capabilities

Option C: Send Case Study
• If scheduling difficult, send relevant customer success story
• Focus on [Industry] case study showing [specific outcome]
• Request follow-up call to discuss applicability

Use create_record with tablename: appointment
{
  "subject": "[Generated meeting subject]",
  "description": "[Context and talking points]",
  "regardingobjectid": "[opportunityid]",
  "scheduledstart": "[suggested datetime]",
  "scheduledend": "[end datetime]",
  "requiredattendees": "[contact ids]",
  "location": "[virtual/in-person]"
}

Use create_record with tablename: phonecall
{
  "subject": "[Generated call subject]",
  "description": "[Context and talking points]",
  "regardingobjectid": "[opportunityid]",
  "phonenumber": "[contact phone]",
  "to": "[contact id]",
  "scheduledstart": "[suggested datetime]"
}

Use create_record with tablename: task
{
  "subject": "[Task description]",
  "description": "[Details and context]",
  "regardingobjectid": "[opportunityid]",
  "scheduledend": "[due date]",
  "prioritycode": 2
}

════════════════════════════════════════════════════
NEXT BEST ACTION ANALYSIS: [Opportunity Name]
════════════════════════════════════════════════════

CURRENT STATE:
• Stage: [Stage]
• Value: [$X]
• Close Date: [Date]
• Time in Stage: [X days]
• Last Activity: [X days ago] - [Type]
• Engagement Trend: [Increasing/Declining/Stable]

QUALIFICATION STATUS:
• Budget: [✓/⚠/✗] [Status]
• Authority: [✓/⚠/✗] [Decision maker identified?]
• Need: [✓/⚠/✗] [Status]
• Timeline: [✓/⚠/✗] [Status]

STAKEHOLDER MAP:
• Economic Buyer: [Name or "Not engaged"]
• Champion: [Name or "Not identified"]
• Technical: [Name or "Engaged/Not engaged"]
• Users: [Status]

COMPETITIVE CONTEXT:
• Competitors: [Mentioned/None detected]
• Risk Level: [Low/Medium/High]

════════════════════════════════════════════════════
🎯 RECOMMENDED NEXT ACTION
════════════════════════════════════════════════════

[Specific action with reasoning]

BASED ON:
• [X] similar won deals analyzed
• Success pattern: [Key insight]
• Risk mitigation: [What this prevents]

EXECUTION STEPS:
1. [Step 1]
2. [Step 2]
3. [Step 3]

EXPECTED OUTCOME:
• [What this should achieve]
• Target timeline: [X days]

════════════════════════════════════════════════════

Shall I create this activity for you? [Yes/No]

NEXT BEST ACTION: Schedule Executive Alignment Meeting

CURRENT SITUATION:
- Stage: Propose (14 days, above average)
- Last Activity: Email sent 14 days ago
- Missing: No C-level engagement identified

WHY THIS ACTION:
- Won deals at this stage had 85% executive engagement
- Economic buyer (CFO) not yet contacted
- Similar deals that stalled here won after exec meeting

SUGGESTED APPROACH:
"Hi [Champion], I'd like to connect with [CFO Name] to discuss how our solution aligns with your strategic priorities. Could you facilitate an introduction?"

NEXT BEST ACTION: Schedule Discovery Call

GAPS IDENTIFIED:
- Budget: Not confirmed
- Timeline: Unknown

RECOMMENDED QUESTIONS:
1. "What's your budget range for this initiative?"
2. "When do you need a solution in place?"

SCHEDULE: Suggested times sent to contact

POTENTIAL BLOCKERS IDENTIFIED:
1. No legal contact engaged (90% of won deals had legal by now)
2. Procurement not in activity history
3. Close date is Friday but no contract sent

RECOMMENDED ACTIONS:
1. Request introduction to procurement contact
2. Send contract for review today
3. Offer implementation timeline to address concerns