Practical framework / 5 MIN READ
Keep historical pipeline reports from rewriting themselves
Distinguish what was known then from what is true now.
AI-assisted research and analysis for the SNS Creative Fieldbook
Sources checked 2026-11-06
A live CRM often stores the latest stage or customer classification. Joining that value onto every past report can make yesterday’s pipeline appear to have information that only arrived later. Historical analysis needs an explicit time perspective.
What you will leave with: Specify a point-in-time dataset that supports pipeline snapshots without pretending missing history can be reconstructed.
Use this when
- Last month’s pipeline changes every time the dashboard refreshes.
- A lead-scoring review uses today’s customer status as a past input.
This is a historical data-design lesson, not a sales forecasting formula or permission to retain unnecessary personal data.
Write the historical question precisely
Separate three questions: what the team knew on a date, what ultimately happened to records created then, and what the current pipeline contains. All can be useful, but they require different data. A current-stage report filtered by creation month does not recreate the stage at that month’s end.
Choose the entity, timestamp and status fields needed for the decision. Include reopened opportunities and corrected records in the definition. Decide whether a correction updates the best current history or preserves the originally reported view alongside a corrected version.
Inspect available history honestly
Look for trustworthy status-change events or historical snapshots before designing a reconstruction. dbt documents snapshots that record changes in mutable tables with validity intervals. That technique can preserve observed states over time, but it cannot conjure every earlier transition from a single current record.
Check how often states were captured. A nightly snapshot can miss a stage entered and exited during one afternoon. Mark the resulting limitation rather than presenting a precise transition duration. Verify stable record keys and how deletions, merges and restored records are represented.
Prevent future information from leaking backward
For a historical decision review, join the state valid at the chosen cutoff and only the inputs available then. A later closed-won label can be a future outcome to evaluate, but should not become an explanatory input the old decision maker supposedly knew.
Keep the outcome window separate from the information cutoff. If results require several months to mature, show that lag explicitly. This makes a retrospective assessment useful without flattering a model or workflow with information from the future. Restrict access and retain only fields needed for the analysis.
Publish the view with its clock
Label the report as an as-of snapshot, a current view or an eventual-outcome view. Show the snapshot date and refresh date independently. Reconcile selected records against available change history and investigate overlapping or missing validity intervals before aggregating.
Agree what happens when history is incomplete. The appropriate answer may be to start reliable capture now and qualify past comparisons. Document the owner, authorized retention period and repair procedure. Do not quietly replace a missing historical state with today’s value simply because the chart expects a category.
Make the reasoning concrete.
A hypothetical opportunity was qualified on March 31 and became won on April 12. A March pipeline report using today’s stage labels it won; a true March 31 snapshot labels it qualified. Both facts can appear in a later outcome review.
Reuse current status
Opening: Join the latest CRM stage to March-created records.
Hypothesis: The report describes their present position.
Risk: Calling it the March snapshot introduces future information.
Keep two clocks
Opening: Show March status and later outcome in separate fields.
Hypothesis: The review distinguishes the original decision context from its result.
Risk: Unavailable stage history must remain a disclosed gap.
Use the historical qualified state for the March snapshot and the later win for a separately labeled outcome analysis. Do not invent a timestamp if history was not retained.
Take the questions into your next brief.
Use these prompts in your own notes. Nothing is submitted from this page.
Question
What was known then, or what eventually happened?
Evidence
Which history actually exists and at what frequency?
Cutoff
Which inputs were available at the decision time?
Validity
How are changes, merges and corrections represented?
Publication
Which clocks and limitations must the reader see?
Watch for the wrong conclusion.
- Filtering by creation date and calling the result a historical snapshot.
- Using future outcomes as past predictors.
- Assuming snapshots capture every transition.
- Inventing missing status history.
Your next action
Choose one recurring pipeline report and define its as-of cutoff separately from its outcome window.
Sources & scope
Frameworks, worksheets and hypothetical examples are original editorial synthesis. Linked primary sources support only the stated concepts. Source checking is not an independent audit of effectiveness. No named human author or reviewer is claimed.
- dbt: Add snapshots to your DAG →Primary technical documentation · Supports: Describes snapshots that record changes in mutable source tables using validity intervals. Does not recover changes that were never observed or retained.