Salesforce is great at many things, but time-based reporting on historical pipeline data isn't one of them.
At Zaghop, we work with clients who need to track trends over time like:
- Total open pipeline each month
- Expected revenue trends week over week
- How many Opportunities were active, lost, or won as of a past date
Unfortunately, Salesforce’s native field history tracking only goes so far. It won’t help you build a graph of pipeline size over time, and reporting on past stages or estimated revenue is tricky if you didn’t capture those values when they changed.
🚫 The Limitations We Faced
Our client needed to visualize and report on:
- Pipeline volume and value at the start of each week and month
- Trends in open Opportunities over time
- The effect of changes in stage, probability, and amount
Salesforce’s built-in options like Historical Trending, Reporting Snapshots, and Field History Tracking came with big limitations:
- Historical Trending only supports certain fields and objects
- Reporting Snapshots can’t retroactively show what your data looked like last quarter
- And Field History is more audit trail than analytics tool
✅ The Workaround: Custom Snapshot Objects
We created a set of custom snapshot objects to track Opportunity data over time.
🔧 Step 1: Create Snapshot Objects
We created two custom objects:
- Weekly Opportunity Snapshot
- Monthly Opportunity Snapshot
Each snapshot record included:
- The Opportunity ID
- Owner, Stage, Close Date, Amount, Probability
- A “Snapshot Date” field representing the week or month being recorded
🔁 Step 2: Use a Scheduled Flow to Capture Weekly & Monthly Snapshots
To track forward-looking data, we built Scheduled Flows that:
- Ran at the start of each week or month
- Queried all currently open Opportunities
- Created snapshot records for each, tagged with that week’s or month’s Snapshot Date
This gave us a living historical record of the pipeline, regardless of how those Opportunities changed later.
📦 Step 3: Backfilling Historical Data with Flow Logic
Of course, that only solves the problem going forward. So we also needed a way to retroactively populate the snapshot objects with past Opportunity states, even though Salesforce hadn’t tracked them natively.
We used a clever Flow-based solution:
- Created a record for each week/month in the past year
- Queried all Opportunities that were:
- Created on or before the snapshot week/month, and
- Closed after that period or still open
- Created on or before the snapshot week/month, and
- For each qualifying Opportunity, the flow created a snapshot record reflecting its status as of that past period
By comparing the Opportunity’s Created Date and Close Date to the week/month boundaries, we could reconstruct historical pipeline data with good accuracy, even if the field values had changed since.
📈 Step 4: Build Reports and Dashboards to Visualize Trends
With real snapshot records in place, the client could now:
- Report on pipeline totals and count over time
- Create trend lines and compare current performance to past weeks
- Slice data by rep, stage, or region - all based on actual past data, not guesses
💡 Why This Matters
This approach gave our client the kind of historical trend data that Salesforce doesn't natively provide - all using out-of-the-box tools.
- No expensive add-ons.
- No BI platform needed.
- Just Flows and creativity.
🧠 Final Thought: If You Can’t Report on It, You Can’t Improve It
At Zaghop, we specialize in building practical, scalable solutions to frustrating limitations. This snapshot framework is now something we use as a pattern across clients in various industries, not just sales teams.
If you're struggling to answer questions like “What did our pipeline look like last quarter?” or “How has our close rate changed over time?”, we’d love to show you how we make this possible - all inside Salesforce.
