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

Pipeline Stage Definitions

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.

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

Benchmarks (Optional)

Store config in: clients/<client-name>/config/pipeline-review.json or equivalent.

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:

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?

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?

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?

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?

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?

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:

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?

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.

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:

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:

Execution Summary

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:

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

Cadence Guide

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.