athina-ai/pipeline-review

Pipeline Review

Pulls deal and meeting data from whatever system the user tracks their pipeline in, analyzes it over a chosen time period, and produces a report that answers the questions a founder, sales leader, or AE actually cares about: Are we booking enough? Are they qualified? Where are deals getting stuck? What's working and what isn't?

Two output modes:

• Executive summary: 1-page snapshot. Numbers, trends, red flags. What a founder reads over morning coffee.
• Detailed diagnostic: Full data tables, stage-by-stage breakdown, source analysis, stuck deal list, and specific recommendations.

Both are always produced. The executive summary sits at the top of the report.

When to Auto-Load

Load this composite when:

• User says "review my pipeline", "pipeline report", "how's our pipeline looking", "deal review"
• User says "1:1 prep", "board meeting prep", "weekly sales review"
• An upstream workflow (Pipeline Ops, daily BDR rhythm) triggers an end-of-period review
• User asks about meeting quality, conversion rates, or pipeline health

---

Step 0: Configuration (One-Time Setup)

On first run, collect and store these preferences. Skip on subsequent runs.

Data Source Config

| Question | Options | Stored As | |----------|---------|-----------| | Where do you track your pipeline? | Salesforce / HubSpot / Pipedrive / Close / Supabase / Google Sheets / CSV / Notion / Other | crm_tool | | How do we access it? | API / Export CSV / MCP tools / Direct query | access_method | | What object represents a "deal" in your system? | Opportunity / Deal / Lead / Meeting / Custom | deal_object |

Pipeline Stage Definitions

| Question | Purpose | Stored As | |----------|---------|-----------| | What are your pipeline stages in order? | Map data to a standard funnel | pipeline_stages | | Which stage means "qualified"? | Qualification rate calculation | qualified_stage | | Which stage means "closed won"? | Win rate calculation | won_stage | | Which stage means "closed lost"? | Loss analysis | lost_stage | | What is your expected sales cycle length? (days) | Identify stuck deals | expected_cycle_days |

Example stage mapping:

pipeline_stages: [
  "Lead",
  "Meeting Booked",
  "Meeting Held",
  "Qualified (Discovery Done)",
  "Proposal Sent",
  "Negotiation",
  "Closed Won",
  "Closed Lost"
]
qualified_stage: "Qualified (Discovery Done)"
won_stage: "Closed Won"
lost_stage: "Closed Lost"
expected_cycle_days: 30

Field Mapping

Different CRMs use different field names. Map the user's fields to standard analysis fields.

| Standard Field | Purpose | Stored As | Examples | |---------------|---------|-----------|---------| | Deal name | Identification | field_deal_name | "Name", "Deal Name", "Opportunity Name" | | Company | Account grouping | field_company | "Company", "Account", "Organization" | | Stage | Funnel position | field_stage | "Stage", "Pipeline Stage", "Status" | | Owner | Rep-level analysis | field_owner | "Owner", "Assigned To", "Rep" | | Source | Channel attribution | field_source | "Lead Source", "Source", "Channel", "UTM Source" | | Created date | Period filtering | field_created_date | "Created Date", "Created At", "Date Added" | | Close date | Velocity tracking | field_close_date | "Close Date", "Expected Close", "Closed At" | | Amount | Revenue analysis | field_amount | "Amount", "Deal Value", "ARR", "MRR" | | Last activity date | Stale deal detection | field_last_activity | "Last Activity", "Last Modified", "Last Touched" | | Meeting date | Meeting analysis | field_meeting_date | "Meeting Date", "Call Date", "Demo Date" | | Qualification status | Qual rate | field_qual_status | "Qualified", "ICP Fit", "BANT Score" | | Loss reason | Loss analysis | field_loss_reason | "Loss Reason", "Closed Lost Reason", "Disqualification Reason" |

Not all fields are required. The analysis adapts to whatever data is available. Minimum viable: deal name + stage + created date.

Benchmarks (Optional)

| Question | Purpose | Stored As | |----------|---------|-----------| | What's your target meeting volume per week/month? | Compare actuals to goals | target_meetings | | What's your target qualification rate? | Flag if below target | target_qual_rate | | What's your target win rate? | Flag if below target | target_win_rate | | What's your target pipeline value? | Revenue gap analysis | target_pipeline_value |

Store config in the current working directory or wherever the user prefers.

---

Step 1: Pull Pipeline Data

Purpose: Extract deal/meeting data from the configured CRM for the specified time period.

Input Contract

period: {
  type: "weekly" | "fortnightly" | "monthly" | "quarterly" | "custom"
  start_date: string              # ISO date (auto-calculated from type, or user-specified)
  end_date: string                # ISO date (default: today)
  comparison_period: boolean      # Include prior period for trend comparison (default: true)
}
crm_tool: string                  # From config
access_method: string             # From config
field_mapping: { ... }            # From config

Process

Based on crm_tool and access_method:

| CRM | Access Method | How to Pull | |-----|--------------|-------------| | Salesforce | API / CSV export | SOQL query or user uploads CSV export | | HubSpot | API / CSV export | Deals API or user uploads CSV export | | Pipedrive | API / CSV export | Deals API or user uploads CSV export | | Close | API / CSV export | Leads/Opportunities API or CSV | | Supabase | Direct query | Query deals/outreach_log tables | | Google Sheets | Sheets API / CSV | Read sheet or user exports CSV | | Notion | Notion MCP | Query database | | CSV | File read | User provides file path |

Pull two datasets:

1. Current period: All deals created or active within the specified date range
2. Comparison period: Same-length prior period (e.g., if reviewing last 2 weeks, also pull the 2 weeks before that)

Data Standardization

Regardless of source, normalize all data into a standard structure:

deals: [
  {
    id: string
    name: string
    company: string
    stage: string                   # Mapped to pipeline_stages
    owner: string | null
    source: string | null           # Lead source / channel
    created_date: string            # ISO date
    close_date: string | null       # ISO date
    last_activity_date: string | null
    meeting_date: string | null
    amount: number | null
    qualification_status: string | null
    loss_reason: string | null
    days_in_current_stage: integer  # Calculated
    total_age_days: integer         # Calculated from created_date
  }
]

Output Contract

pipeline_data: {
  current_period: {
    start_date: string
    end_date: string
    deals: [...]                    # Standardized deal records
    deal_count: integer
  }
  comparison_period: {
    start_date: string
    end_date: string
    deals: [...]
    deal_count: integer
  } | null
}

Human Checkpoint

## Data Pulled

Source: [CRM name]
Current period: [start] to [end] — X deals
Comparison period: [start] to [end] — Y deals
Fields available: [list of mapped fields]
Fields missing: [any unmapped fields — analysis will adapt]

Data looks correct? (Y/n)

---

Step 2: Analyze Pipeline

Purpose: Run the full analysis across seven dimensions. Pure computation + LLM reasoning — inherently tool-agnostic.

Input Contract

pipeline_data: { ... }               # From Step 1
pipeline_stages: string[]            # From config
qualified_stage: string              # From config
won_stage: string                    # From config
lost_stage: string                   # From config
expected_cycle_days: integer         # From config
benchmarks: { ... } | null           # From config (optional)

Analysis Dimensions

Run all seven analyses on the current period data. Where comparison period exists, calculate period-over-period trends.

---

Analysis 1: Volume Metrics

Questions answered: How many meetings/deals did we book? Is the volume going up or down?

| Metric | How to Calculate | |--------|-----------------| | Total deals created | Count deals with created_date in period | | Meetings booked | Count deals that reached "Meeting Booked" stage or have a meeting_date | | Meetings held | Count deals at or past "Meeting Held" stage | | No-show rate | (Meetings booked - Meetings held) / Meetings booked | | Period-over-period change | Compare to comparison period | | vs. Target | Compare to target_meetings if set | | Weekly run rate | Total / weeks in period |

Output:

volume: {
  deals_created: integer
  meetings_booked: integer
  meetings_held: integer
  no_show_count: integer
  no_show_rate: percentage
  weekly_run_rate: float
  vs_prior_period: {
    deals_change: percentage          # "+15%" or "-8%"
    meetings_change: percentage
  } | null
  vs_target: {
    target: integer
    actual: integer
    gap: integer                      # Positive = ahead, negative = behind
  } | null
}

---

Analysis 2: Qualification Breakdown

Questions answered: How many meetings were qualified? Unqualified? What's our qualification rate?

| Metric | How to Calculate | |--------|-----------------| | Qualified deals | Deals at or past qualified_stage | | Unqualified deals | Deals that were closed lost before reaching qualified_stage, or deals marked as unqualified | | Qualification rate | Qualified / (Qualified + Unqualified) | | Pending qualification | Deals still between "Meeting Held" and qualified_stage | | Disqualification reasons | Group loss_reason for deals lost pre-qualification | | vs. Prior period | Compare qualification rate | | vs. Target | Compare to target_qual_rate if set |

Output:

qualification: {
  qualified_count: integer
  unqualified_count: integer
  pending_count: integer
  qualification_rate: percentage
  vs_prior_period: percentage_change | null
  vs_target: { target: percentage, actual: percentage } | null
  top_disqualification_reasons: [
    { reason: string, count: integer, percentage: percentage }
  ]
}

---

Analysis 3: Source / Channel Attribution

Questions answered: Where are leads coming from? Which sources produce the most qualified meetings?

| Metric | How to Calculate | |--------|-----------------| | Deals by source | Group by source, count | | Meetings by source | Group by source, count meetings | | Qualification rate by source | For each source: qualified / total | | Best source (volume) | Source with most deals | | Best source (quality) | Source with highest qualification rate (min 5 deals for statistical relevance) | | Source with highest conversion to won | Source with best close rate | | Source trends | Compare to prior period |

Output:

source_attribution: {
  by_source: [
    {
      source: string
      deals_created: integer
      meetings_booked: integer
      meetings_qualified: integer
      qualification_rate: percentage
      deals_won: integer
      win_rate: percentage
      revenue: number | null
      vs_prior_period: percentage_change | null
    }
  ]
  best_volume_source: string
  best_quality_source: string
  best_conversion_source: string
  source_concentration_warning: string | null   # Flag if >60% from one source
}

---

Analysis 4: Stage Distribution & Velocity

Questions answered: Where are deals sitting in the pipeline? How fast are they moving through stages?

| Metric | How to Calculate | |--------|-----------------| | Deal count by stage | Group active deals by current stage | | Revenue by stage | Sum amount per stage | | Average days in each stage | For deals that passed through each stage, average days_in_current_stage | | Stage-to-stage conversion | What % of deals move from one stage to the next | | Pipeline velocity | (Deals × Win Rate × Avg Deal Size) / Avg Cycle Length | | Total pipeline value | Sum of amount for all active deals | | Weighted pipeline | Sum of (amount × stage probability) for each deal |

Stage probability defaults (override with actual data if available):

Lead: 5%
Meeting Booked: 10%
Meeting Held: 20%
Qualified: 40%
Proposal Sent: 60%
Negotiation: 80%
Closed Won: 100%
Closed Lost: 0%

Output:

stage_analysis: {
  by_stage: [
    {
      stage: string
      deal_count: integer
      revenue: number | null
      weighted_revenue: number | null
      avg_days_in_stage: float
      conversion_to_next_stage: percentage
    }
  ]
  total_pipeline_value: number | null
  weighted_pipeline_value: number | null
  pipeline_velocity: number | null
  avg_cycle_length_days: float
  vs_expected_cycle: string          # "On pace", "X days slower than expected", etc.
}

---

Analysis 5: Stuck Deals & At-Risk Pipeline

Questions answered: Are there deals that have been sitting too long? What's at risk of going stale?

| Metric | How to Calculate | |--------|-----------------| | Stuck deals | Deals where days_in_current_stage > 2× average for that stage, OR > expected_cycle_days total | | No activity deals | Deals where last_activity_date > 14 days ago | | Aging deals | Deals where total_age_days > 1.5× expected_cycle_days | | At-risk revenue | Sum of amount for stuck + no activity + aging deals | | Concentration risk | Any single deal > 30% of total pipeline value |

Stuck deal threshold: A deal is "stuck" if it's been in its current stage for more than 2× the average time deals spend in that stage. If we don't have enough data for stage averages, use these defaults:

| Stage | Expected Max Days | |-------|------------------| | Lead | 3 days | | Meeting Booked | 7 days | | Meeting Held | 5 days | | Qualified | 10 days | | Proposal Sent | 14 days | | Negotiation | 14 days |

Output:

stuck_and_at_risk: {
  stuck_deals: [
    {
      name: string
      company: string
      stage: string
      days_in_stage: integer
      expected_max_days: integer
      owner: string | null
      amount: number | null
      last_activity: string | null
      recommended_action: string      # "Follow up", "Ask for timeline", "Qualify out"
    }
  ]
  no_activity_deals: [
    {
      name: string
      company: string
      stage: string
      days_since_activity: integer
      owner: string | null
      recommended_action: string
    }
  ]
  at_risk_revenue: number | null
  concentration_risks: [
    { deal_name: string, amount: number, percentage_of_pipeline: percentage }
  ] | null
}

---

Analysis 6: Win/Loss Analysis

Questions answered: What's our win rate? Why are we losing deals?

| Metric | How to Calculate | |--------|-----------------| | Win rate | Won / (Won + Lost) for deals closed in period | | Win rate by source | Group by source | | Win rate by owner | Group by rep | | Average deal size (won) | Avg amount of won deals | | Average time to close | Avg days from created to closed won | | Top loss reasons | Group loss_reason, count | | Loss stage distribution | At which stage are deals most often lost? | | vs. Prior period | Compare win rate | | vs. Target | Compare to target_win_rate if set |

Output:

win_loss: {
  deals_won: integer
  deals_lost: integer
  win_rate: percentage
  vs_prior_period: percentage_change | null
  vs_target: { target: percentage, actual: percentage } | null
  avg_deal_size_won: number | null
  avg_days_to_close: float
  win_rate_by_source: [ { source: string, win_rate: percentage, count: integer } ]
  win_rate_by_owner: [ { owner: string, win_rate: percentage, count: integer } ] | null
  top_loss_reasons: [
    { reason: string, count: integer, percentage: percentage }
  ]
  loss_by_stage: [
    { stage: string, lost_count: integer, percentage: percentage }
  ]
}

---

Analysis 7: Forecast & Coverage

Questions answered: Are we going to hit our number? Do we have enough pipeline to cover the target?

Only run this analysis if target_pipeline_value or revenue targets are configured.

| Metric | How to Calculate | |--------|-----------------| | Pipeline coverage ratio | Weighted pipeline / remaining quota | | Commit forecast | Sum of deals in Negotiation + Proposal stage | | Best case forecast | Commit + Qualified deals × historical win rate | | Gap to target | Target - best case forecast | | Required deals to close gap | Gap / average deal size | | Required meetings to close gap | Required deals / historical meeting-to-close rate |

Output:

forecast: {
  target: number
  commit_forecast: number
  best_case_forecast: number
  gap_to_target: number
  pipeline_coverage_ratio: float     # 3x+ is healthy, <2x is risky
  deals_needed_to_close_gap: integer
  meetings_needed_to_close_gap: integer
  coverage_assessment: "Healthy (3x+)" | "Adequate (2-3x)" | "At risk (<2x)" | "Critical (<1x)"
} | null

---

Output Contract (Full Analysis)

analysis: {
  period: { type, start_date, end_date }
  volume: { ... }
  qualification: { ... }
  source_attribution: { ... }
  stage_analysis: { ... }
  stuck_and_at_risk: { ... }
  win_loss: { ... }
  forecast: { ... } | null
}

No human checkpoint after this step — the analysis feeds directly into report generation.

---

Step 3: Generate Report

Purpose: Transform the raw analysis into two report formats: an executive summary and a detailed diagnostic. Pure LLM reasoning.

Input Contract

analysis: { ... }                     # From Step 2
benchmarks: { ... } | null            # From config

Executive Summary Format

One page. Numbers and trends. What a founder or sales leader needs to see in 60 seconds.

# Pipeline Review — [Period Type]: [Start Date] to [End Date]

## Snapshot
| Metric | This Period | Prior Period | Change |
|--------|------------|-------------|--------|
| Meetings booked | X | Y | +/-Z% |
| Meetings held | X | Y | +/-Z% |
| Qualification rate | X% | Y% | +/-Z pts |
| Win rate | X% | Y% | +/-Z pts |
| Pipeline value | $X | $Y | +/-Z% |
| Avg deal size | $X | $Y | +/-Z% |
| Avg days to close | X | Y | +/-Z |

## Red Flags
- [Any metric trending down significantly]
- [Stuck deals above threshold]
- [Pipeline coverage below 2x]
- [Single source >60% of pipeline]
- [No-show rate above 20%]

## Green Lights
- [Metrics trending up]
- [Sources performing well]
- [Stages moving faster than expected]

## Top 3 Actions
1. [Most impactful thing to do this week]
2. [Second most impactful]
3. [Third most impactful]

Detailed Diagnostic Format

Full data tables, charts (as markdown tables), and commentary.

# Pipeline Diagnostic — [Period]

## 1. Volume
[Volume metrics table]
[Commentary: is volume on track? Trending up or down? Meeting goals?]

## 2. Qualification
[Qualification breakdown table]
[Disqualification reasons table]
[Commentary: are we meeting with the right people? Common DQ reasons to address?]

## 3. Source Effectiveness
[Source attribution table — sorted by qualification rate]
[Commentary: which channels are working? Which are wasting time?
 Where should we invest more? Where should we cut?]

## 4. Stage Distribution & Velocity
[Stage breakdown table with counts, revenue, avg days, conversion rates]
[Commentary: where is the pipeline fat? Where is it thin?
 Is velocity healthy or slowing?]

## 5. Stuck Deals
[Stuck deals table with recommended actions]
[No-activity deals table]
[Commentary: total at-risk revenue, common patterns in stuck deals]

## 6. Win/Loss Analysis
[Win rate table — overall, by source, by owner]
[Loss reasons table]
[Loss by stage table]
[Commentary: why are we losing? At which stage? Any patterns?]

## 7. Forecast & Coverage (if targets set)
[Forecast table]
[Coverage assessment]
[Commentary: will we hit the number? What needs to happen?]

## Recommendations
[Numbered list of specific, actionable recommendations based on the data.
 Each recommendation should cite the specific data point that drives it.]

Recommendations Logic

Generate recommendations based on patterns found in the analysis:

| Pattern | Recommendation | |---------|---------------| | Qualification rate <40% | "Review ICP targeting — we're meeting with too many unqualified prospects. Top DQ reason is [X]. Consider tightening [source/criteria]." | | No-show rate >20% | "Implement meeting confirmation flow — send reminders 24h and 1h before. Current no-show rate of X% is costing Y meetings/period." | | One source >60% of pipeline | "Diversify lead sources — [source] drives X% of pipeline. If this channel underperforms, pipeline collapses. Test [alternative channels]." | | High-quality source with low volume | "Scale [source] — it has a X% qualification rate (best in pipeline) but only Y% of volume. Invest more here." | | Deals stuck in a specific stage | "Unblock [stage] — X deals averaging Y days (2× normal). Common pattern: [observation]. Recommended: [specific action]." | | Win rate declining | "Win rate dropped from X% to Y%. Top loss reason shifted to [Z]. Consider: [specific response]." | | Pipeline coverage <2x | "Pipeline coverage is [X]x against [target]. Need Z more qualified deals to close the gap. At current conversion rates, that requires W meetings." | | Average cycle lengthening | "Deals are taking X days longer to close vs. prior period. Bottleneck is at [stage]. Consider: [specific action to accelerate]." | | High loss rate at specific stage | "X% of losses happen at [stage]. This suggests [interpretation]. Consider: [action]." |

Output Contract

report: {
  executive_summary: string           # Markdown formatted
  detailed_diagnostic: string         # Markdown formatted
  recommendations: [
    {
      priority: "high" | "medium" | "low"
      area: string                    # "volume", "qualification", "source", "velocity", "stuck", "win_loss", "forecast"
      recommendation: string
      data_point: string              # The specific metric that drove this recommendation
      expected_impact: string         # What fixing this would do
    }
  ]
  stuck_deal_actions: [
    {
      deal_name: string
      company: string
      action: string
      owner: string | null
    }
  ]
}

Human Checkpoint

Present the executive summary first, then offer the detailed diagnostic:

[Executive Summary rendered]

---

Full detailed diagnostic is also available.

Actions from this review:
1. [High priority recommendation]
2. [High priority recommendation]
3. [Medium priority recommendation]

Stuck deals requiring immediate attention:
| Deal | Company | Stage | Days Stuck | Action |
|------|---------|-------|------------|--------|
| ... | ... | ... | ... | ... |

Want to see the full diagnostic? Or take action on any of these recommendations?

---

Step 4: Export & Share (Optional)

Purpose: Save the report and optionally push it to the user's preferred location.

Process

Based on user preference:

| Destination | How | |-------------|-----| | Markdown file | Save to the current working directory | | Google Sheets | Export data tables to a sheet (metrics, deal list, source breakdown) | | Notion | Push to a Notion database page via Notion MCP | | Slack | Send executive summary to a channel | | Email | Send via AgentMail API (agentmail.dev) or other email API | | stdout | Just display it (default) |

---

Execution Summary

| Step | Tool Dependency | Human Checkpoint | Typical Time | |------|----------------|-----------------|--------------| | 0. Config | None | First run only | 5 min (once) | | 1. Pull Data | Configurable (CRM API, CSV, Supabase, etc.) | Verify data looks correct | 1-2 min | | 2. Analyze | None (computation + LLM reasoning) | None — feeds directly to report | Automatic | | 3. Generate Report | None (LLM reasoning) | Review executive summary, drill into details | 5-10 min | | 4. Export | Configurable (file, Sheets, Notion, etc.) | Optional | 1 min |

Total human review time: ~10-15 minutes for a full pipeline review that would normally take 30-60 minutes of manual CRM digging.

---

Adapting to Data Availability

Not every CRM has every field. The analysis degrades gracefully:

| Missing Field | What Gets Skipped | Analysis Still Works? | |--------------|-------------------|----------------------| | amount | Revenue metrics, weighted pipeline, forecast | Yes — volume and stage analysis still run | | source | Source attribution (Analysis 3) | Yes — everything else still runs | | loss_reason | Loss reason breakdown | Yes — win/loss rate still calculates | | owner | Per-rep analysis | Yes — aggregate metrics still run | | last_activity_date | No-activity deal detection | Partially — stuck deals still detected via stage duration | | close_date | Velocity, avg days to close | Partially — stage distribution still works | | Comparison period data | Period-over-period trends | Yes — single period analysis still produces full report |

Minimum viable data for a useful report: Deal name + Stage + Created date. Everything else enriches but isn't required.

---

Cadence Guide

| Review Type | Period | Audience | Focus | |-------------|--------|----------|-------| | Weekly standup | Last 7 days | Sales team | Volume, stuck deals, this week's priorities | | Fortnightly review | Last 14 days | Sales leader | Qualification, source effectiveness, trends | | Monthly review | Last 30 days | Founder / VP Sales | Full diagnostic, win/loss, forecast | | Quarterly business review | Last 90 days | Leadership / Board | Trends, unit economics, strategic recommendations |

The report depth automatically scales with the period length. A weekly review emphasizes volume and stuck deals. A quarterly review emphasizes trends, conversion rates, and strategic patterns.

---

Tips

• Run this at a consistent cadence. The value compounds — trends only become visible when you have multiple data points. A monthly review is 10x more useful when you can compare to the last 3 months.
• Don't skip the stuck deals section. This is where the immediate ROI lives. Every stuck deal surfaced and unblocked is revenue accelerated.
• Source attribution is the highest-leverage insight. Knowing that LinkedIn generates 3x the qualification rate of cold email means you should shift budget and effort. Most teams never run this analysis.
• Watch for vanity metrics. "We booked 40 meetings" means nothing if qualification rate is 15%. Volume without quality is a waste of everyone's time.
• The no-show rate is a hidden leak. Most teams track meetings booked but not meetings held. A 25% no-show rate means 1 in 4 meetings is wasted effort. Fix with confirmation sequences.
• Pipeline coverage below 2x is a red flag. If you need $100K this quarter and your weighted pipeline is $150K, you probably won't make it. Healthy is 3x+.
• Loss reason data is only useful if reps actually fill it in. If 80% of losses say "Other" or are blank, the loss analysis is meaningless. Fix data hygiene first.