Lead Scoring Methodology

A framework for prioritizing contacts in your pipeline based on fit and engagement signals, so reps focus on the leads most likely to convert.

What Is Lead Scoring?

Lead scoring assigns a numerical value (0-100) to each contact based on two dimensions:

1. Fit Score — How well does this lead match your ideal customer profile? (Company size, industry, title, budget)

2. Engagement Score — How actively is this lead interacting with your content and sales team? (Emails opened, meetings attended, demos requested)

Combined: Total Score = Fit Score + Engagement Score (each 0-50, total 0-100)

Fit Scoring Criteria

Assign points based on how closely the contact matches your best customers:

Maximum Fit Score: 50 (if top score on all 4 criteria + 5 bonus points for existing customer account)

Implementing in the Spreadsheet

Add a Fit_Score column to the Contacts tab:

=SUM(
  IF(OR(Title contains "VP", Title contains "Director", Title contains "C"), 15, IF(Title contains "Manager", 8, 3)),
  IF(Deal_Value > 50000, 15, IF(Deal_Value > 20000, 8, 3)),
  ... additional criteria
)

Or use a lookup approach with a scoring reference table.

Engagement Scoring Criteria

Points are accumulated based on interactions:

Maximum Engagement Score: 50 (capped)

Calculating in the Spreadsheet

The Activities tab drives engagement scoring:

=MIN(50,
  COUNTIFS(Activities!Contact_ID, A2, Activities!Type, "Demo") * 15 +
  COUNTIFS(Activities!Contact_ID, A2, Activities!Type, "Meeting") * 10 +
  COUNTIFS(Activities!Contact_ID, A2, Activities!Type, "Call") * 5 +
  COUNTIFS(Activities!Contact_ID, A2, Activities!Type, "Email", Activities!Outcome, "Positive") * 5 +
  IF(TODAY() - Last_Contact > 30, -20, 0)
)

Score Interpretation

Score-Based Routing

Use the score to determine next actions:

For Sales Reps

=IF(Score >= 80, "CALL TODAY",
  IF(Score >= 60, "Email within 48h",
    IF(Score >= 40, "Add to weekly cadence",
      "Leave in nurture")))

For Pipeline Stage Advancement

A lead shouldn't advance from Qualification to Discovery unless their score is at least 40. This prevents reps from pushing unqualified leads forward prematurely.

Recency Weighting

Not all engagement is equal. Recent activity matters more than activity from 3 months ago.

Time Decay Formula

Decayed Points = Original Points × 0.9^(weeks_since_activity)

After 4 weeks, a 10-point activity is worth: 10 × 0.9^4 = 6.6 points

After 8 weeks: 10 × 0.9^8 = 4.3 points

After 12 weeks: 10 × 0.9^12 = 2.8 points

Spreadsheet Implementation

=SUMPRODUCT(
  Activity_Points * POWER(0.9, (TODAY() - Activity_Dates) / 7)
)

Negative Scoring

Some signals should reduce a lead's score:

Calibration and Maintenance

Monthly Review

1. Look at all leads that scored 80+ in the past month

2. How many actually converted?

3. If conversion rate is < 30%, your thresholds are too generous

4. If conversion rate is > 70%, you're being too conservative (missing warm leads)

Target: 40-60% Conversion Rate for "Hot" Leads

This means your scoring accurately identifies high-probability prospects without being so restrictive that you miss opportunities.

Quarterly Recalibration

Analyze which scoring criteria actually predict conversion:

1. Export all closed-won deals from the last quarter

2. Look at their scores at the time they entered Negotiation stage

3. Which criteria were most predictive?

4. Increase weight for predictive criteria, decrease for non-predictive ones

Quick Reference: The Contacts Tab

The Engagement_Score column in the Contacts CSV uses a simplified version of this methodology. To implement the full model:

1. Add a Fit_Score column (calculated from company/title/budget)

2. Rename existing score to Engagement_Score (calculated from activities)

3. Add a Total_Score column: =Fit_Score + Engagement_Score

4. Add a Lead_Grade column: =IF(Total>=80,"A",IF(Total>=60,"B",IF(Total>=40,"C","D")))

5. Sort your contact list by Total_Score descending — that's your priority list

Weighted Pipeline Forecasting

A practical methodology for predicting future revenue using probability-weighted deal values in your sales pipeline.

The Core Concept

Weighted pipeline forecasting assigns a probability percentage to each deal based on its current stage, then multiplies that probability by the deal value. The sum of all weighted values gives you an expected revenue figure.

Weighted Value = Deal Value × Stage Probability
Expected Revenue = Sum of all Weighted Values in a period

Example:

• Deal A: $50,000 at Negotiation (70%) → Weighted: $35,000
• Deal B: $30,000 at Discovery (30%) → Weighted: $9,000
• Deal C: $20,000 at Proposal (50%) → Weighted: $10,000
• Expected Revenue: $54,000

Why It Works

A single deal is unpredictable. But across 20-50 deals, probabilities smooth out. If you have 10 deals at 70% probability worth $500K total, you can reasonably expect ~$350K to close. Some will, some won't, but the aggregate is reliable.

Requirements for accuracy:

• At least 15-20 active deals in the pipeline
• Probability percentages calibrated to your actual historical close rates
• Consistent stage definitions (everyone agrees what "Negotiation" means)
• Deals in the pipeline are real opportunities, not wishful thinking

Calibrating Your Probabilities

Default probabilities are just starting points. After 3-6 months of data, calibrate them:

Step 1: Pull Historical Data

For each stage, count:

• How many deals entered this stage in the last 6 months?
• How many of those ultimately became Closed Won?

Step 2: Calculate Actual Probability

Stage Probability = Deals Won from Stage / Total Deals that Entered Stage

Example calibration:

Notice how real probabilities often differ from gut-feel defaults. The data tells the truth.

Step 3: Update the Deal Stages Tab

Replace default probabilities with your calculated ones. Re-calculate weighted pipeline values.

Three Forecast Scenarios

Sophisticated forecasting uses three scenarios:

Best Case (Upside)

=SUM of Deal Values where Probability >= 50%

Everything at Proposal stage or later closes. This is your ceiling — it almost never happens, but it sets expectations for the maximum possible outcome.

Commit (Expected)

=SUM of Deal Values where Probability >= 70%

Only deals in Negotiation or later. You should be confident these will close. If you regularly miss your "commit" number, your Negotiation probability is set too high.

Worst Case (Downside)

=SUM of Deal Values where Probability >= 90%

Only deals with verbal commits or signed term sheets. This is your floor.

The Pipeline Coverage Rule

To reliably hit quota, you need sufficient pipeline at each scenario level:

Example: With a $100K quarterly quota:

• You need $300K in total weighted pipeline
• $200K at Best Case (50%+)
• $120K at Commit (70%+)

If any level is short, you need more pipeline generation activity.

Forecast Formula in the Spreadsheet

The Forecast tab uses SUMPRODUCT to automatically calculate weighted pipeline by month:

=SUMPRODUCT(
  (MONTH(Pipeline!Expected_Close) = target_month) *
  (Pipeline!Deal_Value) *
  (Pipeline!Probability / 100) *
  (Pipeline!Stage <> "Closed Won") *
  (Pipeline!Stage <> "Closed Lost")
)

This only counts open deals expected to close in the target month.

Common Forecasting Mistakes

1. Sandbagging

Reps push expected close dates out to avoid accountability. Fix: require evidence for date changes and track "push rate" (% of deals that slip from their original close date).

2. Happy Ears

Counting deals at higher probability than warranted. A "verbal yes" that hasn't been followed by action in 2 weeks is not 90%. Fix: define objective exit criteria for each stage.

3. Stale Pipeline

Old deals sitting in Discovery for 60+ days inflate the weighted total. Fix: auto-flag deals that exceed 2× the average time in stage. Consider reducing their probability.

4. Not Adjusting for Seasonality

If Q4 historically outperforms Q1 by 40%, your weighted pipeline in Q1 should be discounted accordingly.

5. Ignoring Deal Velocity

A $100K deal that's been in Proposal for 3 days is healthier than one that's been there for 30 days. Consider time-weighting: reduce probability for deals that exceed average stage duration.

Advanced: Sales Velocity as a Forecast Input

Sales Velocity measures how fast revenue flows through your pipeline:

Velocity = (Opportunities × Win Rate × Avg Deal Size) / Avg Sales Cycle Days

This gives you dollars per day. Multiply by remaining business days in the period for an activity-based forecast that doesn't depend on individual deal inspection.

From the sample data:

• Team velocity: $11,289/day
• Days remaining in quarter: 22
• Velocity-based forecast: $248,358

Compare this to your weighted pipeline forecast. If they differ significantly, investigate why.

Putting It Into Practice

1. Weekly: Review and update deal stages, close dates, and values in the Pipeline tab

2. Bi-weekly: Run the three-scenario forecast and compare to quota

3. Monthly: Calibrate probabilities against actual close rates

4. Quarterly: Analyze forecast accuracy and adjust methodology