sales-pipeline

name	Sales Pipeline
description	Tracking deals through stages from initial contact to close, including pipeline design, deal progression, forecasting, probability calculation, and sales analytics.

Sales Pipeline

Current Level: Intermediate
Domain: CRM / Sales

---

Overview

Sales pipeline management tracks deals through stages from initial contact to close. This guide covers pipeline design, deal progression, forecasting, and analytics for managing sales processes and predicting revenue.

Pipeline Concepts

Pipeline: Prospecting → Qualification → Proposal → Negotiation → Closed Won/Lost

Stage Properties:
- Name
- Probability (0-100%)
- Expected duration
- Required actions

Database Schema

-- pipelines table
CREATE TABLE pipelines (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  is_default BOOLEAN DEFAULT FALSE,
  active BOOLEAN DEFAULT TRUE,
  
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- stages table
CREATE TABLE stages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  pipeline_id UUID REFERENCES pipelines(id) ON DELETE CASCADE,
  
  name VARCHAR(255) NOT NULL,
  type VARCHAR(50) DEFAULT 'open',
  display_order INTEGER NOT NULL,
  probability INTEGER DEFAULT 0,
  
  expected_duration_days INTEGER,
  required_fields TEXT[],
  
  active BOOLEAN DEFAULT TRUE,
  
  created_at TIMESTAMP DEFAULT NOW(),
  
  INDEX idx_pipeline (pipeline_id),
  INDEX idx_order (display_order)
);

-- deals table
CREATE TABLE deals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  pipeline_id UUID REFERENCES pipelines(id),
  stage_id UUID REFERENCES stages(id),
  
  name VARCHAR(255) NOT NULL,
  amount DECIMAL(15, 2),
  currency VARCHAR(3) DEFAULT 'USD',
  
  contact_id UUID REFERENCES contacts(id),
  company_id UUID REFERENCES companies(id),
  owner_id UUID REFERENCES users(id),
  
  probability INTEGER DEFAULT 0,
  weighted_amount DECIMAL(15, 2),
  
  expected_close_date DATE,
  actual_close_date DATE,
  
  status VARCHAR(50) DEFAULT 'open',
  won_reason TEXT,
  lost_reason TEXT,
  
  next_step TEXT,
  
  custom_fields JSONB,
  
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  
  INDEX idx_pipeline (pipeline_id),
  INDEX idx_stage (stage_id),
  INDEX idx_owner (owner_id),
  INDEX idx_status (status),
  INDEX idx_close_date (expected_close_date)
);

-- deal_history table
CREATE TABLE deal_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  deal_id UUID REFERENCES deals(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id),
  
  action VARCHAR(100) NOT NULL,
  from_stage_id UUID REFERENCES stages(id),
  to_stage_id UUID REFERENCES stages(id),
  
  changes JSONB,
  
  created_at TIMESTAMP DEFAULT NOW(),
  
  INDEX idx_deal (deal_id)
);

-- deal_activities table
CREATE TABLE deal_activities (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  deal_id UUID REFERENCES deals(id) ON DELETE CASCADE,
  
  type VARCHAR(50) NOT NULL,
  subject VARCHAR(255),
  description TEXT,
  
  completed BOOLEAN DEFAULT FALSE,
  completed_at TIMESTAMP,
  due_date TIMESTAMP,
  
  created_by UUID REFERENCES users(id),
  
  created_at TIMESTAMP DEFAULT NOW(),
  
  INDEX idx_deal (deal_id),
  INDEX idx_due_date (due_date)
);

Deal Service

// services/deal.service.ts
export class DealService {
  async createDeal(data: CreateDealDto): Promise<Deal> {
    // Get default pipeline
    const pipeline = await db.pipeline.findFirst({
      where: { isDefault: true },
      include: { stages: { orderBy: { displayOrder: 'asc' } } }
    });

    if (!pipeline || pipeline.stages.length === 0) {
      throw new Error('No pipeline configured');
    }

    const firstStage = pipeline.stages[0];

    const deal = await db.deal.create({
      data: {
        ...data,
        pipelineId: pipeline.id,
        stageId: firstStage.id,
        probability: firstStage.probability,
        weightedAmount: data.amount * (firstStage.probability / 100),
        status: 'open'
      }
    });

    
     .(deal., , , firstStage.);

     deal;
  }

   (: , : <>): <> {
     deal =  db..({
      : { : dealId },
      : {
        ...updates,
        :  ()
      }
    });

    
     (updates. || updates.) {
       .(dealId);
    }

     deal;
  }

   (: , : , : ): <> {
     [deal, stage] =  .([
      db..({ : { : dealId } }),
      db..({ : { : stageId } })
    ]);

     (!deal || !stage) {
        ();
    }

    
     updated =  db..({
      : { : dealId },
      : {
        stageId,
        : stage.,
        : deal. * (stage. / ),
        :  ()
      }
    });

    
     .(dealId, , deal., stageId, userId);

    
     (stage. === ) {
       .(dealId);
    }   (stage. === ) {
       .(dealId);
    }

     updated;
  }

   (: , ?: ): <> {
     db..({
      : { : dealId },
      : {
        : ,
        :  (),
        : reason,
        : 
      }
    });
  }

   (: , ?: ): <> {
     db..({
      : { : dealId },
      : {
        : ,
        :  (),
        : reason,
        : ,
        : 
      }
    });
  }

   (: ): <[]> {
     db..({
      : {
        stageId,
        : 
      },
      : {
        : ,
        : ,
        : 
      },
      : {
        : 
      }
    });
  }

   (: , :  = ): <[]> {
     db..({
      : {
        ownerId,
        status
      },
      : {
        : ,
        : ,
        : 
      },
      : {
        : 
      }
    });
  }

    (: ): <> {
     deal =  db..({ : { : dealId } });
    
     (deal) {
       db..({
        : { : dealId },
        : {
          : deal. * (deal. / )
        }
      });
    }
  }

    (
    : ,
    : ,
    :  | ,
    : ,
    ?: 
  ): <> {
     db..({
      : {
        dealId,
        action,
        fromStageId,
        toStageId,
        userId
      }
    });
  }
}

  {
  : ;
  : ;
  ?: ;
  ?: ;
  : ;
  ?: ;
}

Pipeline Visualization

// services/pipeline-visualization.service.ts
export class PipelineVisualizationService {
  async getPipelineView(pipelineId: string): Promise<PipelineView> {
    const pipeline = await db.pipeline.findUnique({
      where: { id: pipelineId },
      include: {
        stages: {
          orderBy: { displayOrder: 'asc' }
        }
      }
    });

    if (!pipeline) throw new Error('Pipeline not found');

    const stagesWithDeals = await Promise.all(
      pipeline.stages.map(async (stage) => {
        const deals = await db.deal.findMany({
          where: {
            stageId: stage.id,
            status: 'open'
          },
          include: {
            contact: true,
            company: true,
            owner: true
          }
        });

         totalValue = deals.( sum + deal., );
         weightedValue = deals.( sum + deal., );

         {
          stage,
          deals,
          : deals.,
          totalValue,
          weightedValue
        };
      })
    );

     {
      pipeline,
      : stagesWithDeals,
      : stagesWithDeals.( sum + s., ),
      : stagesWithDeals.( sum + s., ),
      : stagesWithDeals.( sum + s., )
    };
  }
}

  {
  : ;
  : [];
  : ;
  : ;
  : ;
}

  {
  : ;
  : [];
  : ;
  : ;
  : ;
}

Forecasting

// services/sales-forecast.service.ts
export class SalesForecastService {
  async getForecast(period: ForecastPeriod): Promise<Forecast> {
    const deals = await db.deal.findMany({
      where: {
        status: 'open',
        expectedCloseDate: {
          gte: period.start,
          lte: period.end
        }
      },
      include: {
        stage: true,
        owner: true
      }
    });

    // Best case: all deals close
    const bestCase = deals.reduce((sum, deal) => sum + deal.amount, 0);

    // Worst case: only high probability deals close
    const worstCase = deals
      .filter(deal => deal.probability >= 80)
      .reduce((sum, deal) => sum + deal.amount, 0);

    // Most likely: weighted by probability
    const mostLikely = deals.reduce( sum + deal., );

    
     byStage = .(deals);

    
     byOwner = .(deals);

     {
      period,
      bestCase,
      worstCase,
      mostLikely,
      : deals.,
      byStage,
      byOwner
    };
  }

   (?: ): <> {
     :  = {
      : { : [, ] }
    };

     (ownerId) {
      where. = ownerId;
    }

     deals =  db..({ where });

     won = deals.( d. === ).;
     lost = deals.( d. === ).;
     total = won + lost;

     {
      : total >  ? (won / total) *  : ,
      : total,
      : won,
      : lost,
      : .(deals.( d. === ))
    };
  }

   (: []): <, > {
     : <, []> = {};

    deals.( {
       stageName = deal..;
       (!grouped[stageName]) {
        grouped[stageName] = [];
      }
      grouped[stageName].(deal);
    });

     .(
      .(grouped).( [
        stage,
        {
          : stageDeals.,
          : stageDeals.( sum + d., ),
          : stageDeals.( sum + d., )
        }
      ])
    );
  }

   (: []): <, > {
     : <, []> = {};

    deals.( {
       ownerName = deal..;
       (!grouped[ownerName]) {
        grouped[ownerName] = [];
      }
      grouped[ownerName].(deal);
    });

     .(
      .(grouped).( [
        owner,
        {
          : ownerDeals.,
          : ownerDeals.( sum + d., ),
          : ownerDeals.( sum + d., )
        }
      ])
    );
  }

   (: []):  {
     (deals. === )  ;
     deals.( sum + d., ) / deals.;
  }
}

  {
  : ;
  : ;
}

  {
  : ;
  : ;
  : ;
  : ;
  : ;
  : <, >;
  : <, >;
}

  {
  : ;
  : ;
  : ;
}

  {
  : ;
  : ;
  : ;
}

  {
  : ;
  : ;
  : ;
  : ;
  : ;
}

Pipeline Metrics

// services/pipeline-metrics.service.ts
export class PipelineMetricsService {
  async getMetrics(pipelineId: string, period: DateRange): Promise<PipelineMetrics> {
    const deals = await db.deal.findMany({
      where: {
        pipelineId,
        createdAt: {
          gte: period.start,
          lte: period.end
        }
      },
      include: {
        stage: true
      }
    });

    const wonDeals = deals.filter(d => d.status === 'won');
    const lostDeals = deals.filter(d => d.status === 'lost');

    return {
      totalDeals: deals.length,
      openDeals: deals.filter(d => d.status === 'open').length,
      wonDeals: wonDeals.length,
      lostDeals: lostDeals.length,
      
      : .(wonDeals., lostDeals.),
      : .(wonDeals),
      :  .(wonDeals),
      
      :  .(pipelineId),
      
      : deals.( sum + d., ),
      : wonDeals.( sum + d., ),
      : deals.( d. === ).( sum + d., )
    };
  }

   (: , : ):  {
     total = won + lost;
     total >  ? (won / total) *  : ;
  }

   (: []):  {
     (deals. === )  ;
     deals.( sum + d., ) / deals.;
  }

    (: []): <> {
     cycles = deals
      .( d.)
      .( {
         created =  (d.);
         closed =  (d.!);
         (closed.() - created.()) / ( *  *  * ); 
      });

     (cycles. === )  ;
     cycles.( sum + c, ) / cycles.;
  }

    (: ): <<, >> {
     stages =  db..({
      : { pipelineId },
      : { :  }
    });

     : <, > = {};

     ( i = ; i < stages. - ; i++) {
       currentStage = stages[i];
       nextStage = stages[i + ];

       dealsInCurrent =  db..({
        : { : currentStage. }
      });

       dealsMovedToNext =  db..({
        : {
          : currentStage.,
          : nextStage.
        }
      });

      rates[] = 
        dealsInCurrent >  ? (dealsMovedToNext / dealsInCurrent) *  : ;
    }

     rates;
  }
}

  {
  : ;
  : ;
  : ;
  : ;
  : ;
  : ;
  : ;
  : <, >;
  : ;
  : ;
  : ;
}

  {
  : ;
  : ;
}

Best Practices

1. Pipeline Design - Create clear, logical stages
2. Probability - Assign realistic probabilities to stages
3. Forecasting - Use weighted forecasting
4. Metrics - Track conversion rates between stages
5. Activities - Log all deal-related activities
6. Win/Loss - Analyze win/loss reasons
7. Sales Cycle - Monitor and optimize sales cycle length
8. Automation - Automate stage progression rules
9. Reporting - Generate regular pipeline reports
10. Training - Train team on pipeline management

---

Quick Start

Pipeline Stages

const PIPELINE_STAGES = [
  { name: 'Prospecting', probability: 10, duration: 7 },
  { name: 'Qualification', probability: 25, duration: 14 },
  { name: 'Proposal', probability: 50, duration: 7 },
  { name: 'Negotiation', probability: 75, duration: 14 },
  { name: 'Closed Won', probability: 100 },
  { name: 'Closed Lost', probability: 0 }
]

async function moveDealToStage(dealId: string, stageName: string) {
  const stage = PIPELINE_STAGES.find(s => s.name === stageName)
  
  await db.deals.update({
    where: { id: dealId },
    data: {
      stage: stageName,
      : stage.,
      : stage. 
        ? ( (), stage.)
        : 
    }
  })
}

Pipeline Forecasting

async function forecastRevenue(pipelineId: string): Promise<number> {
  const deals = await db.deals.findMany({
    where: { pipelineId, status: 'open' }
  })
  
  return deals.reduce((sum, deal) => {
    return sum + (deal.value * deal.probability / 100)
  }, 0)
}

---

Production Checklist

• Pipeline Design: Define pipeline stages
• Stage Properties: Probability and duration per stage
• Deal Tracking: Track deals through pipeline
• Forecasting: Revenue forecasting
• Analytics: Pipeline analytics and metrics
• Automation: Automate stage progression
• Reporting: Regular pipeline reports
• Integration: Integrate with CRM
• Training: Train team on pipeline
• Documentation: Document pipeline process
• Optimization: Optimize pipeline stages
• Monitoring: Monitor pipeline health

---

Anti-patterns

❌ Don't: No Probability

// ❌ Bad - No probability
const deal = { value: 10000, stage: 'Proposal' }
// Can't forecast!

// ✅ Good - With probability
const deal = { 
  value: 10000, 
  stage: 'Proposal',
  probability: 50  // 50% chance
}
// Forecast: $5,000

❌ Don't: Stale Deals

# ❌ Bad - Deals stuck in pipeline
Deal 1: In "Proposal" for 6 months
Deal 2: In "Negotiation" for 1 year

# ✅ Good - Deal hygiene
- Auto-close stale deals
- Regular pipeline reviews
- Deal age tracking

---

Integration Points

• Lead Management (32-crm-integration/lead-management/) - Lead to deal
• Salesforce Integration (32-crm-integration/salesforce-integration/) - CRM sync
• Analytics (23-business-analytics/) - Pipeline analytics

---

Further Reading

• Sales Pipeline Management
• Pipeline Metrics

Resources

• Sales Forecasting