GOAL: Show Week over week report of what deals changed in the pipeline excluding closed won or closed lost deals
Built a custom object (Pipeline Growth Snapshot) that takes a weekly snapshot of open opportunities to support week-over-week pipeline trend reporting. It works well for open deals, but we've found that once an opportunity closes (Won or Lost), it only gets captured one more time, the week it closes, and then never again.
This creates a reporting problem: our week-over-week reports compare this week's number to last week's. Once a deal's snapshots stop, the next comparison is a real number vs. nothing, which displays as a large drop, even for deals that closed Won. There's no way from the report side to tell a real loss apart from a win that simply aged out of the snapshot window.
Questions for anyone who's solved this:
- Is there a standard pattern for extending snapshot capture for closed opportunities (e.g., for 30-60 days post-close) specifically to support trend reporting, without keeping every closed deal in the snapshot indefinitely?
- Has anyone used native Historical Trend Reporting instead of a custom snapshot object for this, and does it have the same limitation?
- Any recommended way to flag, on the report itself, whether a dollar change came from a win versus a loss, without needing a live cross-object Stage lookup (our report type doesn't expose the Opportunity's live Stage field, only the snapshot's own captured Stage)?
Appreciate any patterns or gotchas others have run into with this.
#Reports & Dashboards #Reporting #Reports
Happy to share what has worked for me. I would actually split your problem into two tools, because 'the forecast number' and 'what moved in the pipeline week-over-week' are best served by different native features:
For WoW deal movement (your actual goal): before maintaining a custom snapshot, look at two native options that keep closed deals in the dataset:
- Pipeline Inspection: its 'Changes Since' pipeline-changes waterfall shows exactly what moved between two dates, amount increased/decreased, stage changes, close-date pushes, and deals that closed Won/Lost. That is the week-over-week story you built the snapshot for, without the open-only fall-off (Enterprise+ with Sales Cloud).
- Historical Trend Reporting (the 'Opportunities with Historical Trending' report type): natively snapshots Amount/Stage/Close Date daily for the trailing ~3 months and lets you compare across dates in one report, no custom object to maintain, and closed deals stay in the data.
For the forecast number itself: Collaborative Forecasting (Forecasts tab) rolling up by forecast category (Pipeline / Best Case / Commit / Closed) against quota and role hierarchy. If you have Sales Cloud Einstein, Einstein Forecasting adds a predicted-to-land number and Opportunity Scoring to weight deal health.
Methodology I lean on: Collaborative Forecasting for 'where will we land,' Pipeline Inspection or Historical Trending for 'what changed and why since last week,' and Opportunity Scoring to weight the pipeline. That combo answers WoW movement AND the landing number without hand-rolled snapshots.
3rd-party, if native is not enough: the revenue-intelligence platforms, Clari, Gong Forecast, Aviso, Mediafly (InsightSquared),
People.ai, all do pipeline-change waterfalls, WoW deltas, and AI forecasting on top of your Salesforce data. Teams usually move to these when they want rep-level forecast submissions plus inspection plus analytics in one place.
Honestly, if Pipeline Inspection is available in your edition, I would try its change view first, it may let you retire the custom Pipeline Growth Snapshot object entirely.
If this helps, please mark it as the Best Answer so it is easy for the next person to find. Thanks :)
