Enterprise Pipeline Velocity and Funnel Leakage Diagnostic

Role: Senior Strategic Revenue Operations Architect & Analytics Lead

Context

• Pipeline Snapshot Schema: {{pipeline_snapshot_table}}
• Stage Taxonomy & Lifecycle: {{deal_stage_taxonomy}}
• Baseline Sales Cycle Target: {{sales_cycle_baseline_days}}
• Historical Analysis Period: {{cohort_timeframe}}
• Target Pipeline Tiers: {{revenue_attribution_tier}}
• Closed-Lost Reason Attributes: {{churn_loss_reason_field}}

Task

Produce an enterprise pipeline velocity and stage-by-stage leakage diagnostic report powered by complex temporal SQL queries, isolating conversion bottlenecks and delivering actionable commercial sales enablement plays.

Method

1. Interrogate {{pipeline_snapshot_table}} using SQL window functions (LAG/LEAD) to map historic deal migrations across {{deal_stage_taxonomy}}.
2. Calculate the average and median dwell time (in days) spent within each discrete sales milestone during {{cohort_timeframe}}.
3. Measure stage-to-stage transition probabilities and identify the exact inflection point where deal drop-off exceeds normal thresholds.
4. Write SQL logic to calculate the Pipeline Velocity metric formula: (Number of Opportunities * Win Rate * Average Deal Size) / Sales Cycle Length segmented by {{revenue_attribution_tier}}.
5. Compare historical dwell time per stage against {{sales_cycle_baseline_days}} to flag stalled opportunities and deal slippage patterns.
6. Aggregate {{churn_loss_reason_field}} distributions specifically for opportunities exiting the pipeline at late validation stages.
7. Translate empirical funnel bottlenecks into sales enablement interventions, revised qualification criteria, and territory reassignments.

Constraints

• The SQL query MUST correctly account for bi-directional stage movement (e.g., deals moved backward from Negotiation to Qualification).
• You MUST NOT treat omitted snapshot days as stage completions; handle snapshot gaps using windowed interval logic.
• Dwell time metrics MUST present both median and mean to avoid skew from stalled zombie deals.
• Recommendations MUST target specific sales leadership roles (SDR management, Account Executives, or Sales Enablement).

Output format

Format the deliverable as an executive RevOps report with these sections:

1. Pipeline Health & Velocity Scorecard (markdown summary matrix of win rates, velocity index, and cycle time)
2. Funnel Transition SQL Diagnostic (fully documented SQL query calculating stage transitions and dwell intervals)
3. Stage Conversion & Leakage Deep Dive (breakdown of drop-off rates and dwell duration across {{deal_stage_taxonomy}})
4. Lost Deal Root-Cause Breakdown (quantitative summary of {{churn_loss_reason_field}})
5. Strategic RevOps Action Plan (4 prioritized initiatives addressing identified bottlenecks)

Self-review

• Ensure the SQL query properly handles deals currently open without biasing closed-won/closed-lost durations.
• Confirm that the Pipeline Velocity calculation mathematically aligns with the stated formula.
• Verify that all context variables are appropriately embedded in both narrative analysis and SQL logic.