Scans client data against revenue opportunity scenarios to identify and rank the top sales opportunities. Matches clients to scenarios like FIA replacements approaching surrender, cash drag opportunities, and concentrated portfolio positions. Calculates revenue potential and generates prioritized opportunity reports for financial advisors.
Identifies revenue opportunities by matching clients against pre-defined scenarios and calculating potential revenue.
Use when the user asks to:
Client Data: Google Sheets link with tabs:
Client_Master: Client demographics, portfolio totals, cash balancesProducts: FIAs, life insurance, annuities with detailsHoldings: Investment positions for concentration analysisScenario Selection (defaults to MVP scenarios):
For detailed guidance on specific topics, consult these reference files:
Use these templates for creating new scenarios:
# Load Google Sheets data
client_master = load_sheet(url, 'Client_Master')
products = load_sheet(url, 'Products')
holdings = load_sheet(url, 'Holdings')
# Validate required columns exist
required_columns = {
'Client_Master': ['Client_ID', 'Client_Name', 'Total_Portfolio', 'Cash_Balance', 'Cash_Yield'],
'Products': ['Client_ID', 'Product_Type', 'Current_Value', 'Surrender_End_Date', 'Cap_Rate'],
'Holdings': ['Client_ID', 'Current_Value', 'Percent_of_Portfolio']
}
# Report any missing data
Criteria:
Revenue Calculation:
revenue = product['Current_Value'] * 0.05 # 5% commission
Output Template:
Client: {client_name}
Type: FIA Replacement Opportunity
Details: {product_name} FIA with ${current_value:,} approaching surrender end in {months} months. Current cap rate of {cap_rate} is below market average of 5.5-6.5%.
Revenue Estimate: ${revenue:,}
Action: Schedule review meeting to discuss current product performance and illustrate replacement options with higher crediting rates.
Criteria:
Revenue Calculation:
revenue = client['Cash_Balance'] * 0.01 # 1% AUM fee
Output Template:
Client: {client_name}
Type: Cash Repositioning Opportunity
Details: ${cash_balance:,} in cash earning {cash_yield}. Money market funds currently yielding 4.5-5.5%.
Revenue Estimate: ${revenue:,} annually
Action: Quick call to reposition to higher-yielding money market or short-term treasury fund.
Criteria:
Revenue Calculation:
# Assume 30% of concentrated position can be repositioned
reposition_amount = position['Current_Value'] * 0.30
# Alternative investment typically 5% upfront
revenue = reposition_amount * 0.05
Output Template:
Client: {client_name}
Type: Concentration Risk / Diversification
Details: {ticker} position of ${position_value:,} represents {percentage}% of portfolio. Significant single-stock concentration risk.
Revenue Estimate: ${revenue:,}
Action: Schedule portfolio review to discuss diversification into alternative investments or sector diversification.
# Collect all opportunities
all_opportunities = []
all_opportunities.extend(fia_opportunities)
all_opportunities.extend(cash_opportunities)
all_opportunities.extend(concentration_opportunities)
# Deduplicate: One opportunity per client (keep highest revenue)
client_best_opp = {}
for opp in all_opportunities:
client_id = opp['client_id']
if client_id not in client_best_opp:
client_best_opp[client_id] = opp
elif opp['revenue'] > client_best_opp[client_id]['revenue']:
client_best_opp[client_id] = opp
# Sort by revenue (highest first)
sorted_opportunities = sorted(
client_best_opp.values(),
key=lambda x: x['revenue'],
reverse=True
)
Report Format:
═══════════════════════════════════════════════════
OPPORTUNITYIQ MVP SCAN RESULTS
═══════════════════════════════════════════════════
Generated: {current_date_time}
Clients Scanned: {total_clients}
Opportunities Found: {total_opportunities}
Total Revenue Potential: ${total_revenue:,}
───────────────────────────────────────────────────
OPPORTUNITY #1 - ${revenue:,}
Client: {client_name} ({client_id})
Type: {scenario_name}
Details:
{detailed_description}
Recommended Action:
{next_steps}
───────────────────────────────────────────────────
[Repeat for each opportunity]
───────────────────────────────────────────────────
SUMMARY BY TYPE:
• FIA Replacement: {fia_count} opportunities - ${fia_total:,}
• Cash Repositioning: {cash_count} opportunities - ${cash_total:,}
• Concentration/Diversification: {conc_count} opportunities - ${conc_total:,}
PRIORITY ACTIONS:
1. High Priority (>$5,000): {high_priority_count} opportunities
2. Medium Priority ($2,000-$5,000): {medium_priority_count} opportunities
3. Quick Wins (<$2,000): {quick_win_count} opportunities
NEXT STEPS:
□ Review each opportunity for accuracy
□ Prioritize top 3-5 for immediate outreach
□ Note any false positives to refine criteria
□ Decide: Expand to more scenarios or scale client base?
Handle common data issues gracefully:
# Missing data
if 'Surrender_End_Date' not in products.columns:
print("⚠️ Warning: Surrender_End_Date not found in Products sheet. Skipping FIA scenario.")
# Invalid dates