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
Hi C B - you've diagnosed it exactly: the snapshot filters to open-only, so closed deals fall out and a WoW comparison can't tell a win from a loss from an aged-out row. Here's the pattern for all three questions.
Q1 - extend capture for recently-closed deals: change the snapshot source criteria from 'open only' to 'open OR closed in the last N days'. If you snapshot with a scheduled Flow, filter the Get Records to IsClosed = false OR CloseDate >= TODAY() minus 60. If you use a native Reporting Snapshot, set the source report filter to 'Closed = false OR Close Date = LAST N DAYS:60' (filter logic 1 OR 2). Recently-closed deals then keep getting captured through the trend window and naturally age out once their close date passes 60 days - so you get WoW continuity without hoarding every closed deal forever.
Q3 (the important one) - flag win vs loss from the snapshot alone, no live lookup: capture the disposition ON each snapshot row at capture time. Add IsClosed and IsWon checkboxes to your Pipeline Growth Snapshot object and populate them when you write the row - or a small formula field 'Disposition' = Open / Won / Lost derived from the captured Stage. Because it's stored on the snapshot, the report reads it directly - no cross-object lookup to the live Opportunity Stage needed. Then bucket/group the WoW change by that captured disposition, so a deal that drops out because it was Won is categorized as a win, and only a genuine Closed Lost shows as a real pipeline decrease.
Q2 - native Historical Trend Reporting: it does not have the drop-out problem (it retains a deal's history for the retention window whether or not it closed), so it's worth a look. The trade-offs: it's limited to a rolling retention window (default ~3 months, configurable), tracks only up to ~5 fields, and the 'Opportunities with Historical Trending' report type/format is fairly rigid (matrix, a handful of snapshot dates). Great for 'what did this deal look like X weeks ago', but for bespoke WoW logic plus longer retention the custom object is more flexible - once you fix the source filter from Q1. So use HTR if 3 months and its report format is enough; otherwise keep your object and just widen the capture criteria and store the disposition.
Net: widen the source filter to include recently-closed deals, and stamp IsWon/IsClosed (or a Won/Lost/Open formula) onto every snapshot row - that combination kills the phantom drop and lets the report separate wins from losses with no live lookup. Hope that helps!
