Unlocking Revenue Insights with AI - Part II

Last time out we connected our CRM to an LLM and then summarized opportunity notes — it is a great baseline, in case you missed it:

Today we’re going to build on that base and use AI to perform much deeper levels of analysis.

The scripts we are working with in this piece move from simple summarization to trend identification, competitive intelligence, and sentiment analysis, all within the context of improving sales performance.

AI can machine read everything in every field across activity, opportunity, lead, contact, case and any other object you can name in the modern CRM.

Better yet — the reasoning capabilities give these systems an incredible capability to contextualize. This opens the door for useful reporting and even automations that rely on AI.

Of course, before we advance into today’s scripts and examples, let’s recognize that analytics doesn’t require fancy AI. You can deliver incredible analytics with Python and old-fashioned stats. If you haven’t yet, enjoy this 4-part series on revenue engine analytics aka revenue intelligence:

Optimizing Revenue Engines Using Intelligence

Last time we summarized Closed Lost notes.

Let’s add some depth to the basic example we used last.

Idea 1: Loss Reason Trend Analysis & Actionable Recommendations

What it does: Instead of just categorizing individual loss reasons, this analysis identifies trends in loss reasons over time and, crucially, suggests actionable steps to address the most common issues.

How it works:

1. Enhanced Data Retrieval: Modify get_closed_lost_opportunities to fetch a larger number of opportunities (adjust the LIMIT and potentially add a date range WHERE CloseDate >= LAST_N_MONTHS:3). You'll need more data to see trends. Also retrieve the CloseDate field.

2. Time-Based Aggregation: In analyze_opportunities, after you categorize each opportunity (using your existing categorization_prompt), group the results by month (or quarter) and category. For example:

from collections import defaultdict
category_counts = defaultdict(lambda: defaultdict(int))
for opp in opportunities:
    # ... (your existing code to get the category) ...
    close_date = opp['CloseDate']
    # Convert CloseDate string to datetime object (handle different formats if needed)
    close_date_dt = datetime.strptime(close_date, '%Y-%m-%dT%H:%M:%S.%fZ')
    month_year = close_date_dt.strftime('%Y-%m')  # e.g., "2024-03"
    category_counts[month_year][category.strip()] += 1

print("\n--- Loss Reason Trends ---")
for month_year, counts in category_counts.items():
    print(f"\n{month_year}:")
    for category, count in counts.items():
        print(f"  {category}: {count}")

3. Trend Identification & Actionable Insights (LLM): This is where the LLM shines. Feed the aggregated data (the category_counts) to Gemini with a prompt like this:

trend_analysis_prompt = f"""
Analyze the following trends in reasons for Closed/Lost sales opportunities.
Identify the top 3 most frequent loss reasons across all periods.
For EACH of the top 3, suggest 2-3 CONCRETE and ACTIONABLE steps the sales or product teams could take to address these issues.
Data (format is 'Month-Year: Category: Count'):

{category_counts}

Provide your analysis in the following format:

Top Loss Reasons:
1. [Reason 1] - [Percentage of Total Losses]
2. [Reason 2] - [Percentage of Total Losses]
3. [Reason 3] - [Percentage of Total Losses]

Actionable Recommendations:

Reason 1:
- Action Step 1
- Action Step 2
- Action Step 3

Reason 2:
- Action Step 1
- Action Step 2

Reason 3:
- Action Step 1
"""
trend_analysis = call_gemini_api(trend_analysis_prompt)
print("\n--- Trend Analysis and Actionable Recommendations ---")
print(trend_analysis)

4. Calculate Percentage: In your prompt, you request the percentage to get a better understanding of how significant is each reason.

This moves beyond simple reporting to strategic insights. It helps identify systemic problems and provides concrete steps to improve win rates.

Let’s look at another twist you can add, once you get your CRM talking to AI.

Idea 2: Competitor Analysis from Lost Opportunity Notes

What it does: Extracts mentions of competitors from the Reason_Closed_Lost_Comments__c field and analyzes their presence and potential impact on deal outcomes.

How it works:

1. Competitor Extraction (LLM): Use Gemini with a prompt specifically designed to extract competitor names. You could maintain a hardcoded list of competitors, but the LLM is more flexible and can handle variations and new entrants.

competitor_extraction_prompt = f"""
Identify and list any competitor companies mentioned in the following notes from a Closed/Lost sales opportunity.
If no competitors are mentioned, return 'None'.
Only return a comma separated list of the competitors.

Opportunity: {opp['Name']}
Comments: {notes}
"""
competitors = call_gemini_api(competitor_extraction_prompt)
# Store the competitors for later analysis (e.g., in a list or dictionary)

2. Competitor Frequency Analysis: Similar to the trend analysis, count how often each competitor is mentioned.

from collections import Counter
all_competitors = []
for opp in opportunities:
    # ... (your competitor extraction code) ...
    if competitors.lower() != 'none':
        all_competitors.extend([c.strip() for c in competitors.split(',')])

competitor_counts = Counter(all_competitors)

print("\n--- Competitor Mentions ---")
for competitor, count in competitor_counts.most_common():
    print(f"{competitor}: {count}")

3. Competitor-Specific Loss Reason Analysis (LLM - Optional): For the most frequently mentioned competitors, you can use AI to go deeper: Why are we losing to that specific competitor?

# Get the top competitor (for example)
top_competitor = competitor_counts.most_common(1)[0][0]

competitor_analysis_prompt = f"""
Analyze the following notes from Closed/Lost sales opportunities where the competitor '{top_competitor}' was mentioned.
Summarize the main reasons why we lost deals to this competitor. Focus on recurring themes.
Notes:

{all_notes}  # Use the combined notes from all opportunities
"""

competitor_loss_reasons = call_gemini_api(competitor_analysis_prompt)
print(f"\n--- Why We Lose to {top_competitor} ---")
print(competitor_loss_reasons)

This use case provides direct competitive intelligence, helps understand your strengths and weaknesses relative to specific competitors, and can inform sales strategies and product development.

Let’s look at one more twist, and then we’ll evaluate a full working script and unpack it.

Idea 3: Sentiment Analysis and Correlation with Loss Reasons

What it does: Analyzes the sentiment (positive, negative, neutral) expressed in the Reason_Closed_Lost_Comments__c field and explores whether sentiment correlates with specific loss reasons.

How it works:

1. Sentiment Analysis (LLM): Use a Gemini prompt designed for sentiment analysis.

sentiment_prompt = f"""
Analyze the sentiment of the following text from a Closed/Lost sales opportunity.
Return one of these three words, and nothing else: 'Positive', 'Negative', or 'Neutral'.

Opportunity: {opp['Name']}
Comments: {notes}
"""
sentiment = call_gemini_api(sentiment_prompt).strip()
# Store the sentiment for each opportunity (e.g., in the opp dictionary)
opp['sentiment'] = sentiment

2. Correlation Analysis: After analyzing all opportunities, look for correlations between sentiment and loss categories. For example:

   • Are "Price" related losses more likely to have negative sentiment?

   • Are "Relationship" losses more likely to have neutral or mixed sentiment?

# After processing all opportunities:
sentiment_by_category = defaultdict(lambda: defaultdict(int))
for opp in opportunities:
    category = opp['category'] #Assuming you add the category in a new field
    sentiment = opp['sentiment']
    sentiment_by_category[category][sentiment] += 1

print("\n--- Sentiment Analysis by Loss Category ---")
for category, sentiment_counts in sentiment_by_category.items():
    print(f"\n{category}:")
    for sentiment, count in sentiment_counts.items():
        print(f"  {sentiment}: {count}")

3. Narrative explanation (LLM): Provide the sentiment_by_category result to the LLM to get an interpretation.

sentiment_correlation_prompt = f"""
Analyze the following data showing the relationship between sentiment and loss categories for sales opportunities.
Data:

{sentiment_by_category}

Identify any significant correlations.
For example, are certain loss categories consistently associated with more negative sentiment?
"""

sentiment_correlation = call_gemini_api(sentiment_correlation_prompt)
print(f"\n--- Sentiment Correlation analysis---")
print(sentiment_correlation)

This powerful method adds another layer of understanding to why deals are lost. Negative sentiment might indicate a breakdown in the sales process or a serious customer dissatisfaction issue, even if the stated reason is something else (like "Price"). This information can inform process improvements and customer communication strategies.

We can do much more with this technology, like directly increase our conversion rates and sales velocity.

Learning From Your Wins