CRM System

Contacts & Leads

Contact	Company	Source	Status	Owner	Value	Deal

Sales Pipeline Board

Drag deal cards between stage columns to simulate pipeline movement.

Deal Detail

Activity Logging

Recent Activity

Sales Team Leaderboard

Rep	Closed Won	Closed Deals	Open Pipeline	Win Rate	Activity Count	Avg Deal Size	Avg Cycle	Quota Attainment

1. Pipeline Value by Stage

Open pipeline is calculated from deals not closed won or closed lost. Closed Won and Closed Lost are shown separately.

Open Pipeline

Closed Won

Closed Lost

Weighted Pipeline

Stage	Open Pipeline	Bar

SQL Query

SELECT
    stage,
    COUNT(DISTINCT id) AS deal_count,
    SUM(amount) AS total_amount,
    SUM(amount * probability / 100.0) AS weighted_amount
FROM deals
WHERE stage NOT IN ('Closed Won', 'Closed Lost')
GROUP BY stage
ORDER BY CASE stage
    WHEN 'Prospecting' THEN 1
    WHEN 'Qualified' THEN 2
    WHEN 'Proposal' THEN 3
    WHEN 'Negotiation' THEN 4
    ELSE 5
END;

2. Conversion Rates Between Pipeline Stages

Conversion is calculated as deals that reached a stage divided by deals that reached the prior stage.

From Stage	To Stage	Deals From Previous	Deals Converted	Conversion

SQL Query

WITH stage_order AS (
    SELECT 1 AS stage_order, 'Prospecting' AS stage UNION ALL
    SELECT 2, 'Qualified' UNION ALL
    SELECT 3, 'Proposal' UNION ALL
    SELECT 4, 'Negotiation' UNION ALL
    SELECT 5, 'Closed Won'
),
reached_stage AS (
    SELECT
        to_stage,
        COUNT(DISTINCT deal_id) AS deals_reached
    FROM pipeline_stage_history
    GROUP BY to_stage
)
SELECT
    s1.stage AS from_stage,
    s2.stage AS to_stage,
    COUNT(DISTINCT h.from_stage) AS deals_from_previous,
    COUNT(DISTINCT h.deal_id) AS deals_converted,
    ROUND(
        COUNT(DISTINCT h.deal_id) * 100.0 /
        NULLIF(COUNT(DISTINCT CASE WHEN h.from_stage = s1.stage THEN h.deal_id END), 0),
        1
    ) AS conversion_rate
FROM pipeline_stage_history h
JOIN stage_order s1 ON s1.stage = h.from_stage
JOIN stage_order s2 ON s2.stage = h.to_stage AND s2.stage_order = s1.stage_order + 1
GROUP BY s1.stage, s2.stage
ORDER BY s1.stage_order;

3. Average Deal Size and Sales Cycle Length

Average Closed Deal Size

Average Sales Cycle

Closed Won Deal Size

Closed Won Cycle

Deal	Stage	Amount	Created	Closed	Cycle Days

SQL Query

SELECT
    ROUND(AVG(amount), 2) AS avg_deal_size,
    ROUND(AVG(EXTRACT(DAY FROM closed_at - created_at)), 1) AS avg_sales_cycle_days
FROM deals
WHERE stage = 'Closed Won'
  AND closed_at IS NOT NULL;

4. Sales Rep Performance Comparison

Rep	Closed Won	Closed Deals	Open Pipeline	Win Rate	Activity Count	Avg Deal Size	Avg Cycle	Quota Attainment

SQL Query

SELECT
    u.name,
    SUM(CASE WHEN d.stage = 'Closed Won' THEN d.amount ELSE 0 END) AS closed_won_amount,
    COUNT(CASE WHEN d.stage IN ('Closed Won', 'Closed Lost') THEN d.id END) AS closed_deals,
    SUM(CASE WHEN d.stage NOT IN ('Closed Won', 'Closed Lost') THEN d.amount ELSE 0 END) AS open_pipeline,
    ROUND(
        COUNT(CASE WHEN d.stage = 'Closed Won' THEN d.id END) * 100.0 /
        NULLIF(COUNT(CASE WHEN d.stage IN ('Closed Won', 'Closed Lost') THEN d.id END), 0),
        1
    ) AS win_rate,
    COUNT(a.id) AS activity_count,
    ROUND(
        SUM(CASE WHEN d.stage = 'Closed Won' THEN d.amount ELSE 0 END) /
        NULLIF(COUNT(CASE WHEN d.stage = 'Closed Won' THEN d.id END), 0),
        2
    ) AS avg_deal_size,
    ROUND(
        AVG(
            CASE WHEN d.stage = 'Closed Won'
                 THEN EXTRACT(DAY FROM d.closed_at - d.created_at)
            END
        ),
        1
    ) AS avg_sales_cycle_days,
    ROUND(
        SUM(CASE WHEN d.stage = 'Closed Won' THEN d.amount ELSE 0 END) * 100.0 /
        NULLIF(u.quota_amount, 0),
        1
    ) AS quota_attainment
FROM sales_team_members u
LEFT JOIN deals d ON d.owner_id = u.id
LEFT JOIN activities a ON a.user_id = u.id
GROUP BY u.id, u.name, u.quota_amount
ORDER BY closed_won_amount DESC;

5. Forecast vs Actual Closed Deals This Quarter

Quarter used: Q2 2026. Forecast is calculated from open deals at the start of the quarter with expected close dates inside the quarter.

Forecast Amount

Actual Closed Won

Forecast Accuracy

Forecast Deal Count

Deal	Rep	Stage at Quarter Start	Forecast Amount

SQL Query

WITH quarter AS (
    SELECT DATE '2026-04-01' AS q_start, DATE '2026-06-30' AS q_end
),
latest_stage_at_start AS (
    SELECT
        deal_id,
        to_stage,
        amount_at_time,
        probability_at_time,
        ROW_NUMBER() OVER (
            PARTITION BY deal_id
            ORDER BY occurred_at DESC
        ) AS rn
    FROM pipeline_stage_history
    WHERE occurred_at < (SELECT q_start FROM quarter)
),
forecast AS (
    SELECT
        d.id,
        d.name,
        d.owner_id,
        h.to_stage,
        COALESCE(h.amount_at_time, d.amount) AS forecast_amount,
        COALESCE(h.probability_at_time, d.probability) AS forecast_probability
    FROM deals d
    LEFT JOIN latest_stage_at_start h ON h.deal_id = d.id AND h.rn = 1
    WHERE d.created_at <= (SELECT q_start FROM quarter)
      AND d.expected_close_date BETWEEN (SELECT q_start FROM quarter) AND (SELECT q_end FROM quarter)
      AND d.closed_at IS NULL
),
actual AS (
    SELECT SUM(amount) AS actual_closed_won
    FROM deals
    WHERE stage = 'Closed Won'
      AND closed_at BETWEEN (SELECT q_start FROM quarter) AND (SELECT q_end FROM quarter)
)
SELECT
    ROUND(SUM(forecast_amount * forecast_probability / 100.0), 2) AS forecast_amount,
    ROUND(actual_actual, 2) AS actual_closed_won,
    ROUND(actual_actual * 100.0 / NULLIF(SUM(forecast_amount * forecast_probability / 100.0), 0), 1) AS forecast_accuracy,
    COUNT(f.id) AS forecast_deal_count
FROM forecast f
CROSS JOIN actual;

6. Lead Source Effectiveness

Conversion rate is closed-won deals divided by total leads from each source.

Source	Leads	Qualified	Won	Conversion	Avg Won Deal	Open Pipeline

SQL Query

WITH lead_contacts AS (
    SELECT
        id,
        source
    FROM contacts
    WHERE contact_type = 'Lead'
)
SELECT
    lc.source,
    COUNT(DISTINCT lc.id) AS leads,
    COUNT(DISTINCT CASE WHEN d.stage = 'Closed Won' THEN d.id END) AS won_deals,
    COUNT(DISTINCT CASE WHEN d.stage = 'Closed Lost' THEN d.id END) AS lost_deals,
    ROUND(
        COUNT(DISTINCT CASE WHEN d.stage = 'Closed Won' THEN d.id END) * 100.0 /
        NULLIF(COUNT(DISTINCT lc.id), 0),
        1
    ) AS conversion_rate,
    ROUND(
        SUM(CASE WHEN d.stage = 'Closed Won' THEN d.amount ELSE 0 END) /
        NULLIF(COUNT(DISTINCT CASE WHEN d.stage = 'Closed Won' THEN d.id END), 0),
        2
    ) AS avg_won_deal_size,
    SUM(CASE WHEN d.stage NOT IN ('Closed Won', 'Closed Lost') THEN d.amount ELSE 0 END) AS open_pipeline
FROM lead_contacts lc
JOIN deals d ON d.contact_id = lc.id
GROUP BY lc.source
ORDER BY conversion_rate DESC, leads DESC;

Database Schema

CREATE TABLE sales_team_members (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    role VARCHAR(50),
    region VARCHAR(100),
    quota_amount NUMERIC(12, 2),
    active BOOLEAN DEFAULT TRUE
);

CREATE TABLE contacts (
    id INT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    company VARCHAR(150),
    email VARCHAR(150),
    phone VARCHAR(50),
    source VARCHAR(100),
    status VARCHAR(50),
    contact_type VARCHAR(50),
    owner_id INT,
    estimated_value NUMERIC(12, 2),
    created_at DATE,
    last_activity_at DATE,
    FOREIGN KEY (owner_id) REFERENCES sales_team_members(id)
);

CREATE TABLE deals (
    id INT PRIMARY KEY,
    contact_id INT,
    owner_id INT,
    name VARCHAR(150),
    stage VARCHAR(50),
    probability INT,
    amount NUMERIC(12, 2),
    expected_close_date DATE,
    created_at DATE,
    closed_at DATE,
    is_closed BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (contact_id) REFERENCES contacts(id),
    FOREIGN KEY (owner_id) REFERENCES sales_team_members(id)
);

CREATE TABLE activities (
    id INT PRIMARY KEY,
    deal_id INT,
    contact_id INT,
    user_id INT,
    activity_type VARCHAR(50),
    subject VARCHAR(150),
    notes TEXT,
    occurred_at DATE,
    duration_minutes INT,
    outcome VARCHAR(100),
    FOREIGN KEY (deal_id) REFERENCES deals(id),
    FOREIGN KEY (contact_id) REFERENCES contacts(id),
    FOREIGN KEY (user_id) REFERENCES sales_team_members(id)
);

CREATE TABLE pipeline_stage_history (
    id INT PRIMARY KEY,
    deal_id INT,
    from_stage VARCHAR(50),
    to_stage VARCHAR(50),
    user_id INT,
    occurred_at DATE,
    amount_at_time NUMERIC(12, 2),
    probability_at_time INT,
    FOREIGN KEY (deal_id) REFERENCES deals(id),
    FOREIGN KEY (user_id) REFERENCES sales_team_members(id)
);