Sales Pipeline Tracker Google Sheets

SYSTEM OVERVIEW & PURPOSE

1. Purpose & Scope

This framework establishes a production-grade, end-to-end Sales Pipeline Tracker designed for B2B revenue operations. It tracks opportunities from initial inbound qualification through closed-won/lost execution, monitors velocity, calculates weighted forecasting, and surfaces operational bottlenecks.

2. Data Update Cadence

• Real-Time / Event-Driven: Deal stage changes, value adjustments, and close date shifts are updated immediately by Account Executives (AEs).
• Daily Operations: Inbound lead ingestion and activity logging (calls, emails, demos).
• Weekly Cadence: Management pipeline review, stale deal cleansing (stalled > 14 days in stage), and win/loss ratio analysis.

---

COLUMN DEFINITIONS & VALIDATION RULES TABLE

| Column Name | Field Name | Data Format | Required / Optional | Validation Rules / Dropdown Source | | :--- | Deal_ID | Alphanumeric | Required | Auto-generated/Unique (e.g., DEAL-1001) | | Company_Name | Company | Text | Required | Standardized text string | | Contact_Name | Contact | Text | Required | Primary decision-maker name | | Deal_Size | Amount | Currency ($#,##0) | Required | Greater than 0 (> 0) | | Pipeline_Stage | Stage | Dropdown | Required | 01 - Prospecting, 02 - Discovery, 03 - Proposal, 04 - Negotiation, 05 - Closed Won, 06 - Closed Lost | | Probability_Pct| Probability| Percentage (0%) | Required | Mapped to stage: 10%, 30%, 60%, 80%, 100%, 0% | | Close_Date | CloseDate | Date (YYYY-MM-DD) | Required | Valid future or historical calendar date | | Owner | AE | Dropdown | Required | List of active Sales Reps (e.g., J. Doe, A. Smith) | | Lead_Source | Source | Dropdown | Required | Inbound - Organic, Outbound - Cold, Partner Referral, Paid Ads, Event | | Last_Activity | LastActivity| Date (YYYY-MM-DD) | Required | Date of last recorded touchpoint | | Weighted_Value | WeightedValue| Currency ($#,##0) | System Calculated | =Amount * Probability | | Deal_Age_Days | AgeDays | Integer (0) | System Calculated | =TODAY() - Created_Date | | Pipeline_Status | Status | Text / Flag | System Calculated | =IF(Stage="05 - Closed Won","Won",IF(Stage="06 - Closed Lost","Lost",IF(TODAY()-LastActivity>14,"Stalled","Active"))) |

---

COMPLETE MASTER DATA TABLE

---

FORMULAS & CALCULATION LOGIC

1. Row-Level Calculated Fields (Applied to Master Data Table)

• Weighted Value (Column K): =D2 * F2
• Pipeline Status (Column M): =IF(E2="05 - Closed Won", "Won", IF(E2="06 - Closed Lost", "Lost", IF((TODAY() - J2) > 14, "Stalled", "Active")))

2. Summary & KPI Formulas (Referencing Master Range A2:M100)

• Total Pipeline Value (Unweighted): =SUMIFS(D2:D100, E2:E100, "<>05 - Closed Won", E2:E100, "<>06 - Closed Lost")
• Total Weighted Forecast: =SUMIFS(K2:K100, E2:E100, "<>05 - Closed Won", E2:E100, "<>06 - Closed Lost")
• Win Rate (%): =COUNTIF(E2:E100, "05 - Closed Won") / (COUNTIF(E2:E100, "05 - Closed Won") + COUNTIF(E2:E100, "06 - Closed Lost"))
• Stalled Deal Count (>14 days inactive): =COUNTIF(M2:M100, "Stalled")
• Average Deal Size (Active Pipeline): =AVERAGEIFS(D2:D100, E2:E100, "<>05 - Closed Won", E2:E100, "<>06 - Closed Lost")

---

SUMMARY KPI DASHBOARD

1. High-Level Metrics Layout

+-----------------------------------+-----------------------------------+
| METRIC NAME                       | VALUE                             |
+-----------------------------------+-----------------------------------+
| Total Active Pipeline (Unweighted)| $725,000                          |
| Total Weighted Forecast           | $416,500                          |
| Closed Won Revenue (YTD)          | $300,000                          |
| Win Rate (%)                      | 66.7%                             |
| Average Sales Cycle Length        | 31 Days                           |
| Stalled Deals (>14 Days Inactive) | 2 Deals                           |
+-----------------------------------+-----------------------------------+

2. Health Status Flags & Conditional Formatting Rules

• Stalled Deal Alert:
  • Condition: If Pipeline_Status equals "Stalled".
  • Formatting: Light Red Fill (#F4CCCC), Dark Red Text (#CC0000). Highlights accounts requiring immediate management intervention.
• High-Value Opportunity Indicator:
  • Condition: If Deal_Size is greater than $100,000 and stage is active.
  • Formatting: Light Green Fill (#D9EAD3), Dark Green Text (#38761D). Highlights enterprise accounts.
• Closed Won Confirmation:
  • Condition: If Pipeline_Stage equals "05 - Closed Won".
  • Formatting: Soft Blue Fill (#C9DAF8), Bold Text.

---

STANDARD OPERATING WORKFLOW

Step 1: Initial Setup & Configuration

1. Open a new Google Sheet and name it [Company] - Master Sales Pipeline Tracker.
2. Set up two tabs: Dashboard and Master_Pipeline.
3. Apply standard formatting: Header row dark navy fill (#1C3D5A), white bold text, frozen top row (View > Freeze > 1 row).

Step 2: Data Validation Setup

1. Highlight Master_Pipeline!E2:E100 (Stage column). Go to Data > Data validation > Add rule. Select Dropdown (from a range) and input the exact stage strings: 01 - Prospecting, 02 - Discovery, 03 - Proposal, 04 - Negotiation, 05 - Closed Won, 06 - Closed Lost.
2. Repeat data validation for Owner and Lead_Source columns to ensure data cleanliness.

Step 3: Daily Data Hygiene (Account Executives)

1. Inbound Leads: Input new prospects at the bottom of the Master_Pipeline table. Assign a unique Deal_ID, enter the Company_Name, Deal_Size, and set the stage to 01 - Prospecting.
2. Activity Logging: Whenever a call, email, or meeting occurs, update the Last_Activity date to the current day (Ctrl + ; in Google Sheets). This resets the staleness clock.
3. Stage Progression: Move deals forward as criteria are met. Ensure Probability_Pct updates automatically or matches the standard stage percentage.

Step 4: Weekly Operations & Management Review (Sales Leaders)

1. Stalled Deal Review: Filter the Pipeline_Status column by "Stalled". Review these deals during the 1-on-1 meeting to determine if they should be accelerated, disqualified, or marked 06 - Closed Lost.
2. Forecast Accuracy Check: Review the Dashboard tab to compare the Total Weighted Forecast against quarterly sales quotas.
3. Pipeline Coverage Ratio: Divide total active unweighted pipeline by remaining quota. Ensure a minimum 3x coverage ratio is maintained.