# Data Collection Sources

Gather data from every available source. Handle failures gracefully: log what was unavailable and proceed with partial data. Never fabricate data; use only what can actually be retrieved.

## 1. CRM Data

Pull all active client/company records from available CRM systems.

OneWave CRM (if available):
- `mcp__onewave-crm__list_companies` -- Get all company records
- `mcp__onewave-crm__get_company` -- Get detailed company info for each
- `mcp__onewave-crm__list_deals` -- Get all active deals
- `mcp__onewave-crm__get_deal` -- Get deal details (stage, value, close date)
- `mcp__onewave-crm__get_dashboard` -- Get dashboard overview metrics
- `mcp__onewave-crm__get_mrr_breakdown` -- Get MRR data per account
- `mcp__onewave-crm__get_pipeline_board` -- Get pipeline stage data
- `mcp__onewave-crm__list_contacts` -- Get all contacts
- `mcp__onewave-crm__get_timeline` -- Get activity timeline per account
- `mcp__onewave-crm__list_tasks` -- Get open tasks per account

HubSpot CRM (if available):
- `mcp__claude_ai_HubSpot__search_crm_objects` -- Search companies, deals, tickets
- `mcp__claude_ai_HubSpot__get_crm_objects` -- Get detailed object records
- `mcp__claude_ai_HubSpot__get_properties` -- Get custom properties for scoring
- `mcp__claude_ai_HubSpot__search_owners` -- Map owners to accounts

Extract per client: company name and ID; account owner / CSM; contract value (ARR/MRR); contract start and renewal date; current deal stage; account tier (enterprise/mid-market/SMB); custom health fields if they exist.

## 2. Support Ticket Data

- Check CRM for ticket/case objects associated with each company
- Search HubSpot tickets: `mcp__claude_ai_HubSpot__search_crm_objects` with objectType "tickets"
- Glob for local exports: `**/*ticket*`, `**/*support*`, `**/*case*`
- Search email for escalation threads: `mcp__claude_ai_Gmail__gmail_search_messages` with queries like "escalation", "urgent", "critical issue"

Extract per client: total open tickets; critical/high-priority open tickets; average resolution time (days); ticket volume trend (30/60/90 days); most recent ticket date and subject; escalations in last 90 days.

## 3. Usage and Engagement Metrics

- Glob for analytics exports: `**/*usage*`, `**/*analytics*`, `**/*metrics*`, `**/*engagement*`
- Check for CSV/Excel data files with usage information
- Search CRM custom properties for usage fields
- Check for any dashboard or reporting data

Extract per client: login frequency (DAU/WAU/MAU); feature adoption rate; usage trend over last 90 days; last login date; key feature usage breakdown; API call volume (if applicable); storage/resource consumption (if applicable).

## 4. Billing and Financial Data

- CRM deal values and MRR data from `mcp__onewave-crm__get_mrr_breakdown`
- HubSpot deal records with amount fields
- Glob for billing exports: `**/*billing*`, `**/*invoice*`, `**/*revenue*`, `**/*arr*`, `**/*mrr*`
- Check for payment status information

Extract per client: current ARR/MRR; payment status (current/overdue/at-risk); revenue trend (expanding/flat/contracting); days until renewal; expansion opportunity; discount level (if applicable); invoice payment timeliness.

## 5. Communication and Engagement Logs

- `mcp__onewave-crm__get_timeline` -- Activity timeline per account
- `mcp__claude_ai_Gmail__gmail_search_messages` -- Recent email threads with each client
- `mcp__claude_ai_Slack__slack_search_public_and_private` -- Client mentions in Slack
- CRM activity logs (calls, meetings, emails logged)
- Glob for meeting notes: `**/*meeting*`, `**/*notes*`

Extract per client: days since last contact (any channel); days since last meeting; email response rate / average response time; touchpoints in last 30/60/90 days; sentiment of recent communications; executive sponsor engagement level; NPS or CSAT score (if available).

# Report Output Format and Formatting

Write the final report to `client-health-report.md` in the current working directory (or a directory the user specifies).

## Report Structure

The report MUST follow this exact structure:

```markdown
# Client Health Report

**Generated**: [Current date and time]
**Report Period**: [Date range of data analyzed]
**Total Accounts Analyzed**: [Count]
**Data Sources**: [List of sources successfully queried]

---

## Executive Summary

**Overall Portfolio Health**:
- RED accounts: [Count] ([Percentage]%)
- AMBER accounts: [Count] ([Percentage]%)
- GREEN accounts: [Count] ([Percentage]%)

**Total ARR at Risk**: $[Sum of RED + AMBER account ARR]
**Renewals in Next 90 Days**: [Count] (RED: [n], AMBER: [n], GREEN: [n])
**Accounts Requiring Immediate Action**: [Count]

**Key Trends**:
- [Top 3-5 portfolio-wide observations]

**Top Priority Actions**:
1. [Most urgent action item with client name]
2. [Second most urgent]
3. [Third most urgent]
4. [Fourth most urgent]
5. [Fifth most urgent]

---

## RED Accounts -- Immediate Intervention Required

[Sorted by health score ascending (worst first)]

### [Client Name] -- Health Score: [Score]/100 [RED]

| Metric | Value | Status |
|--------|-------|--------|
| **Health Score** | [Score]/100 | RED |
| **Trend** | [Improving/Stable/Declining] | [Direction indicator] |
| **ARR/MRR** | $[Value] | [Status] |
| **Renewal Date** | [Date] | [Days until renewal] |
| **Days Since Last Contact** | [Days] | [Status] |
| **Open Tickets** | [Count] ([Critical count] critical) | [Status] |
| **Usage Trend** | [Description] | [Status] |
| **Account Owner** | [Name] | -- |

**Score Breakdown**:
| Dimension | Score | Weight | Weighted |
|-----------|-------|--------|----------|
| Product Usage | [Score] | 25% | [Weighted] |
| Support Health | [Score] | 20% | [Weighted] |
| Engagement | [Score] | 20% | [Weighted] |
| Financial Health | [Score] | 20% | [Weighted] |
| Relationship | [Score] | 15% | [Weighted] |

**Risk Factors**:
- [Specific risk factor 1]
- [Specific risk factor 2]
- [Additional risk factors as applicable]

**Recommended Actions**:
1. **[Action Title]** -- [Specific description with owner and timeline]
2. **[Action Title]** -- [Specific description with owner and timeline]
3. **[Action Title]** -- [Specific description with owner and timeline]

---

## AMBER Accounts -- Proactive Attention Needed

[Same format as RED accounts, sorted by health score ascending]

---

## GREEN Accounts -- Healthy

[Same format but with expansion opportunity section added]

### [Client Name] -- Health Score: [Score]/100 [GREEN]

[Same metrics table]
[Same score breakdown]

**Expansion Opportunity**: [High/Medium/Low]
- [Specific expansion opportunity details]

**Maintenance Actions**:
1. [Action to maintain health]
2. [Action to pursue expansion]

---

## Renewal Calendar

| Client | Renewal Date | Days Until | Health | ARR | Risk Level |
|--------|-------------|------------|--------|-----|------------|
[All clients sorted by renewal date ascending]

---

## Data Quality Notes

- [List any data sources that were unavailable]
- [List any clients with incomplete data]
- [List any assumptions made due to missing data]
- [List confidence level for scores where data was sparse]
```

## Report Formatting Rules

- Do NOT use emojis anywhere in the report
- Use plain text RAG indicators: `[RED]`, `[AMBER]`, `[GREEN]`
- Format dollar amounts with commas: $1,234,567
- Use YYYY-MM-DD format for all dates
- Sort RED accounts by health score ascending (worst first)
- Sort AMBER accounts by health score ascending (worst first)
- Sort GREEN accounts by health score descending (best first)
- Include all clients even if data is sparse; note data gaps
- Round health scores to nearest integer
- Use em dashes (--) not hyphens for separators in text

## Handling Missing Data

When data is unavailable for a dimension:
- Score that dimension as 50 (neutral) with a note that data was unavailable
- Flag it in the Data Quality Notes section
- Reduce confidence level for that client's overall score
- Recommend data collection as an action item

When an entire data source is unavailable:
- Note it prominently in the Executive Summary
- Adjust all affected dimension scores to 50 (neutral)
- Add a caveat to the report header about reduced confidence
- List specific data gaps in Data Quality Notes

# Risk Analysis and Recommendations

For each client, generate specific, actionable recommendations based on scores and data.

## Risk Factor Identification

Critical Risk Factors (any one triggers RED consideration):
- No contact in 60+ days
- 3+ critical open tickets
- Usage declined >50% in 90 days
- Payment overdue >60 days
- Key champion departed
- Explicit cancellation or downgrade request
- Renewal within 90 days AND score below 50

Warning Risk Factors (accumulation triggers AMBER):
- No contact in 30-60 days
- Rising ticket volume trend
- Usage declined 20-50% in 90 days
- Payment overdue 30-60 days
- Executive sponsor disengaged
- Renewal within 180 days AND score below 65
- Feature adoption below 25%
- NPS/CSAT decline

## Recommendation Engine

Generate 2-4 specific recommendations per client based on their weakest dimensions.

For low Usage scores:
- Schedule product training or enablement session
- Share relevant case studies showing ROI from underutilized features
- Propose a Quarterly Business Review (QBR) focused on adoption
- Assign a technical account manager for hands-on guidance
- Create a custom adoption plan with milestones

For low Support scores:
- Escalate open critical tickets to engineering leadership
- Schedule a support review call with the client
- Assign a dedicated support engineer
- Conduct root cause analysis on recurring issues
- Propose a service improvement plan with SLA commitments

For low Engagement scores:
- Schedule an executive check-in call within 5 business days
- Send a personalized value report highlighting their ROI
- Invite to upcoming customer event or webinar
- Propose a QBR with agenda tailored to their goals
- Have account owner send a personal outreach message

For low Financial scores:
- Review billing issues with finance team
- Schedule a renewal planning call 120+ days before expiry
- Prepare a value justification deck for budget holders
- Offer a payment plan for overdue accounts
- Identify and propose expansion opportunities to offset contraction risk

For low Relationship scores:
- Map new stakeholders and identify potential champions
- Request introduction to executive sponsor through existing contacts
- Send NPS follow-up to understand detractor reasons
- Propose an executive alignment meeting
- Assign senior leadership to match their seniority

## Expansion Opportunity Assessment

For each GREEN and high-AMBER client, evaluate expansion potential:
- High: Growing usage, new use cases emerging, additional departments interested, budget available
- Medium: Stable usage with room to grow, some interest in new features
- Low: Fully adopted within current scope, limited growth vectors
- Not applicable: Account is at risk; focus on retention first

# Health Score Calculation

Calculate a composite health score (0-100) for each client using a weighted model. Higher scores indicate healthier accounts.

## Scoring Dimensions

Each dimension is scored 0-100, then weighted:

| Dimension | Weight | Score Criteria |
|-----------|--------|----------------|
| Product Usage | 25% | Login frequency, feature adoption, usage trend, DAU/MAU ratio |
| Support Health | 20% | Open ticket count (inverse), resolution time, escalation frequency, ticket trend |
| Engagement | 20% | Days since contact (inverse), meeting frequency, response rates, touchpoint volume |
| Financial Health | 20% | Payment timeliness, revenue trend, contract value stability |
| Relationship | 15% | Executive sponsor access, NPS/CSAT, sentiment, champion strength |

## Dimension Scoring Rules

Product Usage (0-100):
- 90-100: Daily active usage, high feature adoption (>75%), increasing trend
- 70-89: Weekly active usage, moderate feature adoption (50-75%), stable trend
- 50-69: Monthly active usage, low feature adoption (25-50%), stable/slight decline
- 25-49: Infrequent usage, minimal feature adoption (<25%), declining trend
- 0-24: Near-zero usage, single feature only, sharp decline or dormant

Support Health (0-100):
- 90-100: Zero open tickets, fast resolution (<24h avg), no escalations
- 70-89: 1-2 open tickets (low priority), good resolution (<48h), no recent escalations
- 50-69: 3-5 open tickets, moderate resolution (48-72h), 1 escalation in 90 days
- 25-49: 5-10 open tickets or 1+ critical, slow resolution (>72h), multiple escalations
- 0-24: 10+ open tickets or 3+ critical, very slow resolution (>1 week), frequent escalations

Engagement (0-100):
- 90-100: Contact within last 7 days, weekly meetings, fast response rate
- 70-89: Contact within last 14 days, biweekly meetings, good response rate
- 50-69: Contact within last 30 days, monthly meetings, moderate response rate
- 25-49: Contact 30-60 days ago, infrequent meetings, slow response rate
- 0-24: No contact in 60+ days, no scheduled meetings, unresponsive

Financial Health (0-100):
- 90-100: Payments current, revenue expanding, upsell in progress
- 70-89: Payments current, revenue stable, some expansion potential
- 50-69: Payments current, revenue flat, no expansion signals
- 25-49: Late payments, revenue contracting, discount requests
- 0-24: Severely overdue, significant contraction, cancellation signals

Relationship (0-100):
- 90-100: Strong exec sponsor, NPS 9-10, positive sentiment, active champion
- 70-89: Good exec access, NPS 7-8, neutral-positive sentiment, identified champion
- 50-69: Limited exec access, NPS 5-6, neutral sentiment, weak champion
- 25-49: No exec sponsor, NPS 3-4, negative sentiment, champion departed
- 0-24: Hostile relationship, NPS 0-2, very negative sentiment, no internal allies

## Composite Score

```
health_score = (usage * 0.25) + (support * 0.20) + (engagement * 0.20) + (financial * 0.20) + (relationship * 0.15)
```

## RAG Status Assignment

| RAG Status | Score Range | Meaning |
|------------|-------------|---------|
| RED | 0-39 | Critical risk -- immediate intervention required |
| AMBER | 40-69 | Moderate risk -- proactive attention needed |
| GREEN | 70-100 | Healthy -- maintain current engagement |

## Trend Direction

Compare current health score against the implied trajectory from available data:
- Improving: Usage increasing, tickets decreasing, engagement rising, positive signals
- Stable: Metrics holding steady, no significant changes in any dimension
- Declining: Usage dropping, tickets increasing, engagement falling, negative signals

Determine trend using: usage trend over last 90 days; ticket volume trend; contact frequency trend; revenue trajectory; recent sentiment shifts.