---
name: pipeline-analysis
description: >
  Runs a full sales pipeline analysis and renders an interactive visual dashboard.
  Use this skill whenever the user shares deal data (CSV, HubSpot, Salesforce) and wants
  to analyze their pipeline — even if they just say "analyze my pipeline", "how's my pipeline
  looking", "show me my deals", "forecast this month", or "who's my top rep".
  Covers pipeline value, stage conversion, stuck deals, forecast, rep performance,
  and sales velocity. Always produces an interactive visual artifact as output.
---

# Pipeline Analysis

You are an expert sales analyst. The user will provide deal data via CSV, HubSpot MCP,
or Salesforce MCP. Your job is to extract the data, run a full analysis, and render
an interactive dashboard artifact.

Always respond in the user's language.

---

## Phase 1 — Data Ingestion

### Source A — CSV
The user pastes or uploads a CSV. Expected columns (flexible naming, normalize on ingest):
- Deal name → `name`
- Stage → `stage`
- Amount / ARR → `amount` (numeric, strip currency symbols)
- Close date → `close_date` (parse to ISO date)
- Owner / Rep → `owner`
- Created date → `created_date` (parse to ISO date)
- Company / Account → `company`
- Probability → `probability` (optional, numeric 0-100)

If column names differ, infer from context. If probability is missing, assign defaults
based on stage name (see Stage Probability Defaults below).

### Source B — HubSpot MCP
Use the HubSpot MCP to fetch open deals:
- Fetch deals with properties: `dealname`, `dealstage`, `amount`, `closedate`,
  `hubspot_owner_id`, `createdate`, `hs_probability`, `associated_company`
- Resolve owner IDs to names via the owners endpoint
- Filter: only fetch deals where `pipeline = default` (or ask user which pipeline)
- Normalize to the standard schema above

### Source C — Salesforce MCP
Use the Salesforce MCP to query opportunities:
```sql
SELECT Name, StageName, Amount, CloseDate, Owner.Name, CreatedDate,
       Probability, Account.Name
FROM Opportunity
WHERE IsClosed = false
```
Normalize to the standard schema above.

### Stage Probability Defaults
If probability is not provided, use these defaults. Adapt if the user's stages differ:
```
Prospecting / Discovery    → 10%
Qualification              → 20%
Demo / Meeting scheduled   → 30%
Proposal sent              → 50%
Negotiation                → 70%
Contract sent              → 85%
Closed Won                 → 100%
Closed Lost                → 0%
```

---

## Phase 2 — Data Validation

Before analysis, flag any data quality issues inline (don't block the analysis):
- Deals with no amount → flag as "amount missing", exclude from value calculations
- Deals with close date in the past and still open → flag as "overdue"
- Deals with no owner → group under "Unassigned"
- Negative amounts → flag and exclude
- Duplicate deal names → flag, keep both

Report flagged records as a small warning section at the top of the dashboard.

---

## Phase 3 — Run the Analysis

Compute the following metrics from the normalized data:

### 3.1 Pipeline Overview
- **Total pipeline value** — sum of all open deal amounts
- **Weighted pipeline value** — sum of (amount × probability) for each deal
- **Deal count** — total number of open deals
- **Average deal size** — total value / deal count
- **Median deal size**
- **Pipeline coverage ratio** — total pipeline / monthly quota (ask user for quota if not provided, or skip)

### 3.2 Stage Breakdown
For each stage:
- Deal count
- Total value
- Weighted value
- Average deal size
- % of total pipeline value
- Conversion rate stage-to-stage (if historical closed/lost data is available)

### 3.3 Stuck Deals (At-Risk)
A deal is **stuck** if:
- It has been in the current stage for more than 2× the average time in that stage, OR
- Close date is more than 14 days in the past and still open, OR
- Created more than 90 days ago and still in an early stage (Prospecting / Qualification)

For each stuck deal, surface:
- Deal name + company
- Stage
- Days in current stage
- Amount
- Owner
- Recommended action (based on stage — see Action Templates below)

### 3.4 Forecast
Group deals by close date into:
- **This month** — sum of weighted values closing this calendar month
- **Next month** — sum of weighted values closing next calendar month
- **This quarter** — sum of weighted values closing this quarter
- **Beyond** — everything else

For each period: deal count, weighted value, raw value, and list of top 5 deals by amount.

Flag deals closing this month with probability < 30% as "at risk of slipping".

### 3.5 Rep Performance
For each owner / rep:
- Deal count
- Total pipeline value
- Weighted pipeline value
- Average deal size
- Number of stuck deals
- Deals closing this month (count + value)
- Rank by weighted pipeline value

### 3.6 Sales Velocity
- **Average sales cycle length** — average days from created_date to today for open deals
  (use closed_won deals if available for a more accurate figure)
- **Average days per stage** — mean time spent in each stage across all deals
- **Velocity by rep** — average cycle length per owner
- **Deals at risk of missing close date** — close date within 7 days, probability < 50%

---

## Phase 4 — Build the Dashboard Artifact

Render a **single interactive React artifact** with the following structure.
Use Recharts for all charts. Use Tailwind utility classes for layout and styling.
The dashboard must work with the actual computed data — no mock data.

### Dashboard Layout

```
┌─────────────────────────────────────────────────────┐
│  HEADER: Pipeline Analysis — [date range] — [source] │
│  Data quality warnings (if any)                      │
├──────────┬──────────┬──────────┬────────────────────┤
│ KPI Card │ KPI Card │ KPI Card │ KPI Card           │
│ Total    │ Weighted │ Deals    │ Avg Deal Size      │
│ Pipeline │ Pipeline │ Count    │                    │
├──────────┴──────────┴──────────┴────────────────────┤
│ TABS: Overview │ Stages │ At-Risk │ Forecast │ Reps │ Velocity │
├─────────────────────────────────────────────────────┤
│ [Tab content — charts + tables]                      │
└─────────────────────────────────────────────────────┘
```

### Tab Contents

**Overview tab**
- Bar chart: pipeline value by stage
- Pie/donut chart: deal count by stage
- Summary table: stage name | deals | value | weighted value | % of pipeline

**Stages tab**
- Funnel visualization: deals flowing stage to stage
- Table: stage | count | total value | avg deal size | avg days in stage

**At-Risk tab**
- Summary: X stuck deals, $Y at risk
- Table with colored risk indicators:
  - Red: close date overdue
  - Orange: stuck > 2× avg stage time
  - Yellow: early stage > 90 days
- Columns: deal | company | owner | stage | days stuck | amount | reason | recommended action

**Forecast tab**
- Grouped bar chart: weighted vs raw value per period (this month / next month / quarter / beyond)
- Table per period: deals closing, count, weighted value
- "At risk of slipping" list highlighted in orange

**Reps tab**
- Horizontal bar chart: weighted pipeline by rep
- Table: rep | deals | total value | weighted value | avg deal size | stuck deals | closing this month

**Velocity tab**
- Bar chart: avg days per stage
- Table: rep | avg cycle length | deals closing this month | at-risk deals

### Styling rules
- Use a clean, professional color palette: blues and greens for positive metrics,
  orange/red for at-risk items
- KPI cards: large number, label, and a subtle trend indicator if comparable data exists
- Tables: sortable columns (click header to sort), alternating row colors
- All monetary values formatted as currency (€ or $ based on user's data)
- Dates formatted as DD/MM/YYYY for European users, MM/DD/YYYY for US

---

## Phase 5 — Recommendations

After the dashboard, output a short prioritized action list (max 8 items) in this format:

```
## Priority Actions

1. [URGENT] Deal X (Company Y) — close date passed 12 days ago, $45K at risk.
   → Owner: follow up today, update stage or mark lost.

2. [THIS WEEK] 3 deals stuck in Proposal Sent for 30+ days.
   → Send a follow-up sequence via lemlist campaign "Proposal Follow-up".

3. [FORECAST] Pipeline coverage for this month is 1.2× quota — below the 3× healthy ratio.
   → Prioritize moving 5 deals from Demo to Proposal this week.
```

Tailor recommendations to what's visible in the data. Never invent metrics not present.

---

## Action Templates by Stage

Use these when recommending actions for stuck deals:

| Stage | Recommended Action |
|---|---|
| Prospecting | Re-qualify or disqualify — no activity in 30+ days suggests poor fit |
| Qualification | Schedule a discovery call — ask 3 BANT questions to move forward |
| Demo scheduled | Send pre-demo prep email + confirm attendance |
| Proposal sent | Send a follow-up sequence, offer to answer objections on a call |
| Negotiation | Escalate to manager or offer a limited-time incentive |
| Contract sent | Direct call to legal/finance contact to unblock signature |

---

## Handling Missing Data Gracefully

- If `probability` is missing: derive from stage using defaults — note this in the dashboard header
- If `created_date` is missing: skip velocity calculations — note this
- If only one rep: skip rep performance tab, merge into overview
- If no close dates: skip forecast tab — note this
- Never crash or refuse to analyze — always produce the best analysis possible with available data