Pipeline Review

Phase 0: Intake

1. Read agency.config.json from the project root.
2. Extract:
   • agency.name -- for report header
   • crm.type -- CRM platform (google_sheets, etc.)
   • crm.sheet_id -- for data access
   • crm.webhook_url -- for reading data
   • crm.tabs.pipeline -- pipeline data tab name
   • crm.tabs.outreach_log -- outreach activity data
   • crm.tabs.calling -- calling activity data
   • crm.tabs.dashboard -- where to write the report summary
   • outreach.cadence -- to assess if cadence is being followed
   • outreach.daily_caps -- to assess if activity targets are being met
   • scoring.thresholds -- hot/warm/cool definitions
   • services[] -- for deal categorization by service type
3. Accept parameters:
   • review_period -- timeframe to analyze. Default: last_7_days. Options: last_7_days, last_14_days, last_30_days, mtd, qtd
   • targets -- (optional) revenue and activity targets:
     • monthly_revenue_target -- target MRR or monthly revenue
     • quarterly_revenue_target -- target quarterly revenue
     • weekly_demos -- target demos per week
     • weekly_proposals -- target proposals per week
     • daily_outreach -- target outreach activities per day
   • compare_period -- compare to previous period for trend. Default: previous_week
   • forecast_months -- months to forecast. Default: 3
   • output_to_crm -- write report summary to Dashboard tab. Default: true

Phase 1: Data Collection

Pull data from all relevant CRM tabs using crm-writer.

Pipeline data (from Pipeline tab):

• All active deals with: company name, deal value, stage, created date, last activity date, assigned to, service type, source (inbound/outbound), expected close date, probability

Required pipeline stages (map to CRM columns):

LEAD -> QUALIFIED -> DEMO_SCHEDULED -> DEMO_DONE -> PROPOSAL_SENT -> NEGOTIATION -> CLOSED_WON -> CLOSED_LOST

If CRM uses different stage names, map them to these standard stages.

Outreach data (from Outreach CRM tab):

• All outreach activities in the review period: date, lead name, channel (email/linkedin/instagram/call), activity type (initial/follow-up), response received (y/n)

Historical data (from Pipeline tab, all time):

• All closed deals (won and lost) with dates and values for conversion rate calculations

Compile raw data:

DATA COLLECTION:
---
Active deals: [count]
Deals in review period: [count]
Outreach activities in review period: [count]
Closed won (all time): [count]
Closed lost (all time): [count]
Data quality issues: [list any missing fields or inconsistencies]

Phase 2: Pipeline Health Metrics

Calculate the core metrics that determine pipeline health.

2.1: Pipeline Value

Pipeline-to-target ratio benchmarks:

• Healthy: 3x or higher (you need 3x your target in pipeline to hit it)
• Caution: 2-3x
• Critical: under 2x

2.2: Stage Distribution

Count and value deals at each stage:

Stage health indicators:

• Top-heavy: too many deals in LEAD/QUALIFIED, not enough advancing = prospecting without qualifying
• Bottleneck: disproportionate count at one stage = process issue at that stage
• Bottom-heavy: too many in PROPOSAL/NEGOTIATION = slow closing or pricing issues
• Balanced: roughly equal distribution with natural funnel narrowing = healthy

2.3: Velocity Metrics

Velocity trend:

• Compare velocity score to previous period
• Increasing velocity = deals are moving faster and/or getting bigger
• Decreasing velocity = something is slowing down (identify which variable)

2.4: Aging Analysis

Identify stale deals that need attention:

List all COOLING and STALE deals with:

• Company name
• Deal value
• Current stage
• Days since last activity
• Recommended action

2.5: Activity Metrics

Compare to outreach.daily_caps multiplied by business days in the period.

Phase 3: Conversion Analysis

Analyze where deals are being won and lost.

3.1: Conversion Funnel

LEAD -> QUALIFIED: [X%] conversion ([N] leads -> [M] qualified)
QUALIFIED -> DEMO: [X%] conversion
DEMO -> PROPOSAL: [X%] conversion
PROPOSAL -> WON: [X%] conversion
---
Overall LEAD -> WON: [X%] conversion

Identify the weakest conversion point -- this is where the biggest improvement opportunity exists.

3.2: Win/Loss by Segment

Identify the highest-performing segments by win rate and deal size.

3.3: Loss Reasons

If loss reasons are tracked in CRM, aggregate:

Phase 4: Forecasting

Generate a revenue forecast based on pipeline data.

4.1: Weighted Pipeline Forecast

Apply stage-specific probabilities:

Total weighted forecast: $[sum]

4.2: Scenario Forecasting

4.3: Time-Based Forecast

Based on expected close dates:

4.4: Pipeline Coverage

Revenue target: $[target]
Weighted pipeline: $[weighted]
Coverage ratio: [weighted/target]x
---
Need to add: $[shortfall * 3] in new pipeline to hit target (at 3x coverage)
= [shortfall * 3 / avg_deal_size] new qualified leads needed

Phase 5: Trend Analysis

Compare current period to previous period(s).

Flag concerning trends:

• 2+ consecutive weeks of declining pipeline value
• Win rate dropping below historical average
• Sales cycle lengthening
• Outreach volume below 80% of target
• Response rate declining

Phase 6: Recommendations

Generate prioritized, actionable recommendations based on the analysis.

Recommendation categories:

1. Immediate actions (this week):

   • Stale deals to follow up on (list with suggested action)
   • Bottleneck stages to unblock (specific deals + actions)
   • Activity gaps to fill (which channels are underperforming)

2. Process improvements (this month):

   • Stage conversion issues to address
   • Segment-specific strategy adjustments
   • Outreach mix optimization (shift activity to higher-performing channels)

3. Strategic recommendations (this quarter):

   • Pipeline coverage gaps and required lead volume
   • Service/segment mix optimization based on win rate and deal size
   • Pricing and positioning adjustments based on loss reasons

Priority scoring for recommendations:

Rank all recommendations by priority score. Top 3 become the "Weekly Focus."

Phase 7: Output

Return structured JSON:

{
  "report_metadata": {
    "agency": "Agency Name",
    "review_period": "2026-03-03 to 2026-03-09",
    "generated_at": "2026-03-10T09:00:00Z",
    "data_quality": "good",
    "data_issues": []
  },
  "pipeline_snapshot": {
    "total_value": 450000,
    "weighted_value": 185000,
    "deal_count": 18,
    "avg_deal_size": 25000,
    "new_this_period": 4,
    "lost_this_period": 1,
    "net_change": 75000,
    "pipeline_to_target_ratio": 3.2,
    "health": "healthy"
  },
  "stage_distribution": [
    {"stage": "LEAD", "count": 5, "value": 125000, "pct_of_pipeline": 0.28},
    {"stage": "QUALIFIED", "count": 4, "value": 100000, "pct_of_pipeline": 0.22},
    {"stage": "DEMO_SCHEDULED", "count": 3, "value": 75000, "pct_of_pipeline": 0.17},
    {"stage": "DEMO_DONE", "count": 2, "value": 50000, "pct_of_pipeline": 0.11},
    {"stage": "PROPOSAL_SENT", "count": 3, "value": 75000, "pct_of_pipeline": 0.17},
    {"stage": "NEGOTIATION", "count": 1, "value": 25000, "pct_of_pipeline": 0.06}
  ],
  "stage_health": "slightly_top_heavy",
  "velocity": {
    "avg_sales_cycle_days": 28,
    "velocity_score": 4821,
    "velocity_trend": "stable",
    "stage_durations": [
      {"stage": "LEAD_to_QUALIFIED", "avg_days": 5},
      {"stage": "QUALIFIED_to_DEMO", "avg_days": 7},
      {"stage": "DEMO_to_PROPOSAL", "avg_days": 4},
      {"stage": "PROPOSAL_to_CLOSE", "avg_days": 12}
    ]
  },
  "aging": {
    "fresh": 8,
    "active": 5,
    "cooling": 3,
    "stale": 2,
    "dead": 0,
    "attention_needed": [
      {"company": "Company A", "value": 30000, "stage": "PROPOSAL_SENT", "days_stale": 12, "action": "Call this week -- proposal sent 12 days ago with no response"},
      {"company": "Company B", "value": 20000, "stage": "QUALIFIED", "days_stale": 18, "action": "Re-engage or move to lost"}
    ]
  },
  "activity_metrics": {
    "emails_sent": 22,
    "linkedin_touches": 18,
    "instagram_touches": 8,
    "calls_made": 6,
    "total_activities": 54,
    "target_activities": 75,
    "pct_of_target": 0.72,
    "response_rate": 0.12,
    "demos_booked": 2,
    "proposals_sent": 1
  },
  "conversion_funnel": {
    "lead_to_qualified": 0.65,
    "qualified_to_demo": 0.55,
    "demo_to_proposal": 0.70,
    "proposal_to_won": 0.35,
    "overall_lead_to_won": 0.09,
    "weakest_point": "proposal_to_won",
    "weakest_point_insight": "35% proposal-to-close rate suggests pricing objections or proposal quality issues"
  },
  "forecast": {
    "weighted_total": 185000,
    "scenarios": {
      "conservative": 100000,
      "moderate": 185000,
      "best_case": 290000
    },
    "monthly_forecast": [
      {"month": "March 2026", "weighted_value": 75000, "deal_count": 4},
      {"month": "April 2026", "weighted_value": 65000, "deal_count": 3},
      {"month": "May 2026", "weighted_value": 45000, "deal_count": 2}
    ],
    "pipeline_coverage": {
      "target": 150000,
      "weighted_pipeline": 185000,
      "ratio": 1.23,
      "shortfall": 0,
      "new_leads_needed": 0
    }
  },
  "trends": {
    "pipeline_value_trend": "up",
    "win_rate_trend": "stable",
    "deal_size_trend": "up",
    "cycle_length_trend": "stable",
    "activity_trend": "down",
    "concerning_trends": ["Outreach activity 28% below target for 2nd consecutive week"]
  },
  "recommendations": {
    "weekly_focus": [
      {"priority": 1, "action": "Follow up on 3 cooling deals worth $70K combined", "category": "immediate", "revenue_impact": "high"},
      {"priority": 2, "action": "Increase outreach volume to hit daily caps -- 28% below target", "category": "immediate", "revenue_impact": "medium"},
      {"priority": 3, "action": "Review proposal template -- 35% close rate is below benchmark", "category": "process", "revenue_impact": "high"}
    ],
    "immediate_actions": [],
    "process_improvements": [],
    "strategic_recommendations": []
  }
}

Phase 8: Report Distribution

After generating the report:

1. Present to user: show the key metrics, concerning trends, and weekly focus items
2. Write to CRM: if output_to_crm is true, write a summary row to the Dashboard tab via crm-writer
3. Flag urgent items: if any deals are stale and high-value, highlight prominently
4. Compare to last week: show week-over-week changes for the top 5 metrics

Phase 9: Review

Present the pipeline review:

1. Lead with the health verdict: healthy / caution / critical
2. Show pipeline snapshot (total value, weighted value, deal count)
3. Highlight the 3 weekly focus actions
4. Show the forecast scenarios
5. Flag any concerning trends
6. List stale deals needing immediate attention
7. Ask if any data needs correction or if the user wants to drill into a specific area

Example Usage

Trigger phrases:

• "Run a pipeline review"
• "How is the pipeline looking?"
• "Weekly pipeline check"
• "What is our forecast for this quarter?"
• "Which deals need attention?"
• "Pipeline health report"
• "How are we tracking against targets?"

User: Run a pipeline review for this week
Assistant: [reads config, pulls CRM data via crm-writer, calculates all metrics, identifies stale deals, generates forecast, compares to previous week, returns health report with 3 focus actions]

User: How is the pipeline looking? Any deals going stale?
Assistant: [pulls pipeline data, runs aging analysis, identifies cooling and stale deals, returns deal-level recommendations with urgency ranking]