)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Sales Ops and RevOps managers can reconstruct exactly what their Salesforce pipeline looked like at any past date by importing OpportunityFieldHistory data into Google Sheets or Excel using Coefficient’s Salesforce connector and combining it with Coefficient’s Snapshots feature for ongoing historical capture. Salesforce shows your pipeline as it stands today. It cannot reconstruct what your pipeline looked like at month-end last quarter, which deals were in which stages on a specific date, or how your pipeline composition has shifted over time.
A common challenge for RevOps teams preparing QBR analysis or forecast accuracy reviews: the question is always “what did we think would close?” and Salesforce has no native way to answer it without a data warehouse or expensive third-party tooling.
How to build retroactive and ongoing pipeline snapshot reports
Step 1. Import OpportunityFieldHistory to reconstruct past pipeline states
Open Coefficient in Google Sheets or Excel and select Import from Salesforce. Choose From Objects and Fields and select the OpportunityFieldHistory object. Pull OpportunityId, StageName, CreatedDate, OldValue and NewValue, filtered for Field equals StageName. With this data you can calculate which stage each opportunity was in on any historical date by finding the most recent stage change before that date for each deal.
Step 2. Build retroactive pipeline aggregation logic
Add a parameter cell in your sheet where you enter the date you want to reconstruct, for example, the last day of the previous quarter. Add a formula column that flags each opportunity’s stage on that date by finding the latest field history row where CreatedDate is on or before the parameter date. Then aggregate by StageName using COUNTIFS and SUMIFS to see your pipeline composition as it existed on that specific date.
Step 3. Configure automated Snapshots for ongoing historical capture going forward
In Coefficient, enable the Snapshots feature on your live pipeline import and set it to capture the full tab at month-end or week-end. Each snapshot preserves the complete deal dataset as it existed at capture time, including all custom fields and stage assignments. Fahmi Rashid, reviewing on the Pipedrive Marketplace, put it directly: “Snapshots is one of the neat features where you can capture a set of data for historical trend analysis.”
Step 4. Build period-over-period pipeline comparison views
Create a summary sheet pulling from your snapshot history to show pipeline value by stage across multiple periods, current quarter versus prior quarter, or month-by-month for the past year. Add percentage change columns to surface whether each stage is building or eroding. This gives your sales leadership a trend view that is impossible to produce from Salesforce natively.
What you get
Your team can answer “what did our pipeline look like at end of last quarter?” without pulling a data warehouse report or digging through Salesforce audit logs. Retroactive analysis from field history covers the past. Ongoing snapshots cover the future. Forecast accuracy reviews and QBR prep become a matter of opening the sheet rather than rebuilding data.
Start capturing your pipeline history automatically at coefficient.io/get-started.
