Build a sales pipeline report with stage-history data

Today's opportunity stage cannot reliably tell you which stage the opportunity occupied last month. A historical pipeline report needs stage history or a trustworthy snapshot. Select the latest known stage before the reporting cutoff, then apply the definition of an open opportunity.

The same principle applies to amount, ownership and currency when those fields change. A historically correct stage joined to today's amount can still produce a historically incorrect pipeline value.

Inspect a compact opportunity history

The synthetic opportunity fixture fixes each opportunity's value to keep the example focused on stages. The stage history records every change from creation.

O1 moves from qualified to proposal on January 10 and wins on January 20. O2 enters proposal on January 15 and is lost on February 5. O3 is qualified on January 10 and lost on January 25. O4 is not created until February 1.

Immediately before January 16, O1 and O2 are proposals worth 150,000 paise together, while O3 is qualified at 80,000. Open pipeline totals 230,000 paise.

Reconstruct state before the cutoff

WITH ranked AS (
 SELECT opportunity_id,stage,changed_at,
 ROW_NUMBER() OVER (
   PARTITION BY opportunity_id ORDER BY changed_at DESC
 ) AS rn
 FROM stage_history WHERE changed_at<'2026-01-16'
)
SELECT h.stage,COUNT(*) AS opportunities,SUM(o.value_paise) AS pipeline_paise
FROM opportunities o JOIN ranked h USING(opportunity_id)
WHERE o.created_at<'2026-01-16' AND h.rn=1
 AND h.stage NOT IN ('won','lost')
GROUP BY h.stage ORDER BY h.stage;

The output is one qualified opportunity at 80,000 and two proposals at 150,000. The strict cutoff excludes changes on or after January 16. With date-only records, this is a start-of-day boundary, not an intraday reconstruction.

Salesforce's standard opportunity report documentation distinguishes pipeline history and trend reporting. Whatever CRM supplies the source, verify which fields and historical periods its export actually preserves.

Verify two snapshots

from build_and_verify import database

db = database()
def snapshot(cutoff):
    result = {}
    for opportunity,created,value in db.execute('SELECT * FROM opportunities WHERE created_at<?',(cutoff,)):
        row = db.execute('''SELECT stage FROM stage_history
          WHERE opportunity_id=? AND changed_at<?
          ORDER BY changed_at DESC LIMIT 1''',(opportunity,cutoff)).fetchone()
        if row is None:
            raise ValueError('missing historical stage')
        if row[0] not in ('won','lost'):
            result[opportunity]=(row[0],value)
    return result

earlier, later = snapshot('2026-01-16'), snapshot('2026-02-01')
db.close()
assert earlier == {'O1':('proposal',100000),'O2':('proposal',50000),'O3':('qualified',80000)}
assert later == {'O2':('proposal',50000)}
assert sum(value for stage,value in earlier.values())==230000
assert sum(value for stage,value in later.values())==50000
print({'January15_end':earlier,'January31_end':later})

At the end of January, O2 remains open at 50,000. Its February loss must not leak backward into that snapshot. O4 is excluded because it does not yet exist under the same cutoff.

Do not interpret pipeline shrinkage as one kind of outcome

The open pipeline falls by 180,000 paise between these snapshots: 100,000 exits through a win and 80,000 through a loss. Those movements have very different business meanings. A waterfall should distinguish wins, losses, new opportunities, amount changes and other state movements.

This fixture has no amount changes or reopened opportunities. Real histories may move backward through stages or reopen after a closed state. Preserve those transitions rather than imposing a one-way funnel that the source does not support.

Separate snapshot, conversion and forecast questions

A pipeline snapshot asks what is open at a time. A conversion report follows a defined entry cohort to a specified outcome horizon. A forecast combines assumptions or a model with available information. Summing open values is not automatically an expected-revenue forecast.

O2 has not resolved by January 31. Treating it as a final loss would bias a conversion analysis toward faster outcomes. Report pending cases and maturity, or use a method designed for censored time-to-event data when appropriate.

History completeness matters too. If tracking started after an opportunity was created, the earliest observed stage may not be its true starting stage. Keep a coverage flag instead of inferring an undocumented earlier journey.

Exercise: add an amount reduction for O2 on January 20 in a separate amount-history table. Reconstruct both snapshots with as-of values and verify that the earlier amount does not change.

NeuraPath's Data Analytics with Generative AI course connects SQL window functions with commercial reporting. A reliable pipeline analysis reconstructs the information available at the time and explains each movement.

Continue learning

This article is part of the Domain analytics and business cases sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Analyze operating expenses without changing the cost taxonomy.
• Continue with Measure campaign profitability after refunds and discounts.