The pipeline history your CRM doesn't keep

A CRM stores the pipeline as it is now, but forecast calls ask what changed. A daily snapshot and day-over-day change facts in the warehouse give the forecast a memory.

Field history CRM Daily extract Snapshots Change facts What moved Forecast call Stale deals seeded once, marked reconstructed opportunities fixed cutoff any past call, rebuilt day over day since the last call the gap, explained no change in 45 days
A daily snapshot can rebuild any past forecast call, and day-over-day change facts roll up into what moved since the last one. Deals that stop changing branch off as stale.
On this page
  1. One row per open deal, every day
  2. Comparing each day with the day before
  3. A Monday with $500K missing
  4. Daily rows over validity ranges
  5. Tie it out to the CRM every morning
  6. History you can’t get back

A CRM answers questions in the present tense. A forecast call asks nearly all of its questions in the past tense: what slipped, what shrank, what came in new this week, who pulled a deal out of commit.

The CRM keeps each opportunity as it is right now. Most CRMs can track history on a capped set of fields, often with a retention limit, and only from the day someone switched tracking on. Some add a trend report that looks back a few months. Ask what the commit number was at last Monday’s call, and which deals moved it since, and you’re rebuilding it by hand.

The forecast needs a memory of its own, and I’d keep it in the warehouse: a daily snapshot of every open deal, change facts from comparing each day with the day before, and a view that rebuilds any past forecast call exactly.

One row per open deal, every day

The snapshot runs once a day at a fixed cutoff. I’d take 6 a.m. in the timezone the forecast is reported in, so Monday’s snapshot is what Monday’s call looks at, and then never move it. Shifting it later quietly changes what “Monday’s number” means.

Each run writes one row per open opportunity, plus one row for a deal on the day it closes, so every win and loss is on record. Nothing edits a row once it’s written.

The rule people argue with: late edits don’t go back into old snapshots. If a rep cuts an amount on Thursday and says the customer told them on Tuesday, Tuesday’s row stays as it was. The snapshot records what the forecast could see that day, and a forecast you can’t reproduce isn’t worth much in a review.

-- One row per open opportunity per day, plus one row on the day a deal closes.
-- No updates, ever: the daily load is the only writer, and it replaces whole days.
CREATE TABLE analytics.opportunity_snapshot (
  snapshot_date      DATE          NOT NULL,  -- day of the 06:00 cutoff, forecast timezone
  opportunity_id     VARCHAR(32)   NOT NULL,
  stage              VARCHAR(64)   NOT NULL,
  amount             NUMERIC(18,2),
  currency           CHAR(3)       NOT NULL,
  close_date         DATE,
  forecast_category  VARCHAR(32),
  owner_id           VARCHAR(32)   NOT NULL,
  is_closed          BOOLEAN       NOT NULL,
  is_reconstructed   BOOLEAN       NOT NULL DEFAULT FALSE,  -- seeded from field history
  extracted_at       TIMESTAMP WITH TIME ZONE NOT NULL,
  PRIMARY KEY (snapshot_date, opportunity_id)
);

-- The loader can insert and delete whole days. Nobody gets UPDATE.
GRANT SELECT, INSERT, DELETE ON analytics.opportunity_snapshot TO snapshot_loader;
GRANT SELECT ON analytics.opportunity_snapshot TO forecast_readers;

-- Any past forecast call, rebuilt: open pipeline by category for the quarter
-- each snapshot_date falls in. Closed deals drop out, so report wins separately.
CREATE VIEW analytics.forecast_as_of AS
SELECT
  snapshot_date,
  forecast_category,
  currency,
  COUNT(*)    AS deals,
  SUM(amount) AS amount
FROM analytics.opportunity_snapshot
WHERE NOT is_closed
  AND close_date >= CAST(DATE_TRUNC('quarter', snapshot_date) AS DATE)
  AND close_date <  CAST(DATE_TRUNC('quarter', snapshot_date) + INTERVAL '3 months' AS DATE)
GROUP BY snapshot_date, forecast_category, currency;

The load replaces one day inside a transaction, so a rerun leaves the table exactly as one run would. It reads the extract landed at the cutoff, never the live CRM, which by noon would bake the morning’s edits into the 6 a.m. snapshot.

-- Daily load for one snapshot_date, bound by the scheduler.
-- staging.opportunity_extract holds what the extract pulled at that day's
-- cutoff: every open deal, plus deals that closed since the previous cutoff.
BEGIN;

DELETE FROM analytics.opportunity_snapshot
WHERE snapshot_date = CAST(:snapshot_date AS DATE);

INSERT INTO analytics.opportunity_snapshot (
  snapshot_date, opportunity_id, stage, amount, currency, close_date,
  forecast_category, owner_id, is_closed, is_reconstructed, extracted_at
)
SELECT
  e.snapshot_date, e.opportunity_id, e.stage, e.amount, e.currency, e.close_date,
  e.forecast_category, e.owner_id, e.is_closed, FALSE, e.extracted_at
FROM staging.opportunity_extract AS e
LEFT JOIN (
  -- each deal's latest row before this day
  SELECT
    opportunity_id,
    is_closed,
    ROW_NUMBER() OVER (PARTITION BY opportunity_id ORDER BY snapshot_date DESC) AS rn
  FROM analytics.opportunity_snapshot
  WHERE snapshot_date < CAST(:snapshot_date AS DATE)
) AS prev
  ON prev.opportunity_id = e.opportunity_id
 AND prev.rn = 1
WHERE e.snapshot_date = CAST(:snapshot_date AS DATE)
  AND (
    e.is_closed = FALSE                          -- every open deal
    OR COALESCE(prev.is_closed, FALSE) = FALSE   -- a closed deal, once, on the day it closes
  );

COMMIT;

Comparing each day with the day before

The change facts line each row up with the same deal’s previous row and emit one row per change: created, stage moved, amount changed, close date moved, forecast category changed, owner changed, won and lost. Plus reopened, because lost deals come back. Every row carries the old and new values and how much it moved the quarter’s open commit total.

When several fields change on one day, the commit effect is applied in a fixed order (close date, then category, then amount), so one deal’s rows for a day add up exactly to its change that day. A win or loss takes the whole effect for its day. I keep it as a view, so a rerun of any day flows straight through.

-- One row per change per deal per day, with old and new values and the change's
-- effect on the quarter's open commit total. 'Commit' and 'Closed Won' are your
-- CRM's values; the definitions registry should say which ones count.
CREATE VIEW analytics.opportunity_change AS
WITH paired AS (
  SELECT
    s.*,
    LAG(s.snapshot_date)     OVER (PARTITION BY s.opportunity_id ORDER BY s.snapshot_date) AS prev_snapshot_date,
    LAG(s.stage)             OVER (PARTITION BY s.opportunity_id ORDER BY s.snapshot_date) AS prev_stage,
    LAG(s.amount)            OVER (PARTITION BY s.opportunity_id ORDER BY s.snapshot_date) AS prev_amount,
    LAG(s.close_date)        OVER (PARTITION BY s.opportunity_id ORDER BY s.snapshot_date) AS prev_close_date,
    LAG(s.forecast_category) OVER (PARTITION BY s.opportunity_id ORDER BY s.snapshot_date) AS prev_forecast_category,
    LAG(s.owner_id)          OVER (PARTITION BY s.opportunity_id ORDER BY s.snapshot_date) AS prev_owner_id,
    LAG(s.is_closed)         OVER (PARTITION BY s.opportunity_id ORDER BY s.snapshot_date) AS prev_is_closed,
    LAG(s.is_reconstructed)  OVER (PARTITION BY s.opportunity_id ORDER BY s.snapshot_date) AS prev_is_reconstructed,
    CAST(DATE_TRUNC('quarter', s.snapshot_date) AS DATE)                       AS q_start,
    CAST(DATE_TRUNC('quarter', s.snapshot_date) + INTERVAL '3 months' AS DATE) AS q_end
  FROM analytics.opportunity_snapshot AS s
),
flagged AS (
  SELECT
    p.*,
    p.prev_snapshot_date IS NOT NULL                                                AS has_prev,
    p.prev_snapshot_date IS NOT NULL AND NOT p.is_closed AND NOT p.prev_is_closed   AS stayed_open,
    COALESCE(p.prev_close_date >= p.q_start AND p.prev_close_date < p.q_end, FALSE) AS prev_in_q,
    COALESCE(p.close_date >= p.q_start AND p.close_date < p.q_end, FALSE)           AS now_in_q,
    p.is_reconstructed OR COALESCE(p.prev_is_reconstructed, FALSE)                  AS reconstructed
  FROM paired AS p
),
commit_effect AS (
  -- Open commit for this quarter before the day, after the close-date step,
  -- after the category step, and after everything.
  SELECT
    f.*,
    CASE WHEN f.has_prev AND NOT f.prev_is_closed
              AND f.prev_forecast_category = 'Commit' AND f.prev_in_q
         THEN COALESCE(f.prev_amount, 0) ELSE 0 END AS commit_before,
    CASE WHEN f.prev_forecast_category = 'Commit' AND f.now_in_q
         THEN COALESCE(f.prev_amount, 0) ELSE 0 END AS commit_after_date,
    CASE WHEN f.forecast_category = 'Commit' AND f.now_in_q
         THEN COALESCE(f.prev_amount, 0) ELSE 0 END AS commit_after_category,
    CASE WHEN NOT f.is_closed AND f.forecast_category = 'Commit' AND f.now_in_q
         THEN COALESCE(f.amount, 0) ELSE 0 END AS commit_after
  FROM flagged AS f
)
SELECT snapshot_date AS changed_on, opportunity_id, currency, 'created' AS change_type,
       CAST(NULL AS VARCHAR(64)) AS old_value, stage AS new_value,
       commit_after AS commit_delta, FALSE AS slipped_out_of_quarter, reconstructed
FROM commit_effect
WHERE NOT has_prev
  -- the first live load sees every deal for the first time; that isn't creation
  AND snapshot_date <> (SELECT MIN(snapshot_date)
                        FROM analytics.opportunity_snapshot
                        WHERE NOT is_reconstructed)
UNION ALL
SELECT snapshot_date, opportunity_id, currency, 'stage_moved',
       prev_stage, stage, 0, FALSE, reconstructed
FROM commit_effect
WHERE stayed_open AND stage IS DISTINCT FROM prev_stage
UNION ALL
SELECT snapshot_date, opportunity_id, currency, 'close_date_moved',
       CAST(prev_close_date AS VARCHAR(64)), CAST(close_date AS VARCHAR(64)),
       CASE WHEN stayed_open THEN commit_after_date - commit_before ELSE 0 END,
       COALESCE(stayed_open AND prev_in_q AND close_date >= q_end, FALSE),  -- the slip rule
       reconstructed
FROM commit_effect
WHERE has_prev AND close_date IS DISTINCT FROM prev_close_date
UNION ALL
SELECT snapshot_date, opportunity_id, currency, 'forecast_category_changed',
       prev_forecast_category, forecast_category,
       CASE WHEN stayed_open THEN commit_after_category - commit_after_date ELSE 0 END,
       FALSE, reconstructed
FROM commit_effect
WHERE has_prev AND forecast_category IS DISTINCT FROM prev_forecast_category
UNION ALL
SELECT snapshot_date, opportunity_id, currency, 'amount_changed',
       CAST(prev_amount AS VARCHAR(64)), CAST(amount AS VARCHAR(64)),
       CASE WHEN stayed_open THEN commit_after - commit_after_category ELSE 0 END,
       FALSE, reconstructed
FROM commit_effect
WHERE has_prev AND amount IS DISTINCT FROM prev_amount
UNION ALL
SELECT snapshot_date, opportunity_id, currency, 'owner_changed',
       prev_owner_id, owner_id, 0, FALSE, reconstructed
FROM commit_effect
WHERE has_prev AND owner_id IS DISTINCT FROM prev_owner_id
UNION ALL
SELECT snapshot_date, opportunity_id, currency,
       CASE WHEN NOT is_closed THEN 'reopened'
            WHEN stage = 'Closed Won' THEN 'won'
            ELSE 'lost' END,
       prev_stage, stage, commit_after - commit_before, FALSE, reconstructed
FROM commit_effect
WHERE has_prev AND is_closed <> prev_is_closed;

The words in that SQL aren’t engineering’s to define. “Slip” above all. I’ve coded it as a close date moved out of the current quarter, but some teams count any push of more than two weeks, and some ignore moves in the quarter’s last week. Sales ops should own those definitions, versioned, for the same reason a data agent shouldn’t get to guess what revenue means. “Commit” and “stale” belong on the same list.

A Monday with $500K missing

Picture a fictional sales org carrying 2,400 open opportunities. On a Monday morning the quarter’s open commit reads $3.6M. A week earlier it read $4.1M. Its CRM keeps history on stage and amount but not forecast category, so finding the $500K means clicking through deals and hoping you open the right ones.

The rollup takes one query.

-- What moved the quarter's open commit between two forecast calls.
-- :since is last Monday's snapshot_date and :as_of is today's, in the same quarter.
SELECT
  CASE WHEN slipped_out_of_quarter THEN 'slipped_out_of_quarter'
       ELSE change_type END         AS change_type,
  currency,
  COUNT(DISTINCT opportunity_id)   AS deals,
  SUM(commit_delta)                AS commit_change
FROM analytics.opportunity_change
WHERE changed_on >  CAST(:since AS DATE)
  AND changed_on <= CAST(:as_of AS DATE)
  AND commit_delta <> 0
GROUP BY 1, 2
ORDER BY commit_change;
change_type currency deals commit_change
slipped_out_of_quarter USD 3 -310000.00
amount_changed USD 1 -120000.00
forecast_category_changed USD 1 -70000.00

Three commit deals had their close dates pushed past the quarter’s end. One lost $120K when the customer recounted seats, and one moved from commit to best case. The lines add up to exactly $500K, and they should. If the sum ever disagrees with the gap between the two days in forecast_as_of, a deal left the pipeline without a change row. The usual suspect is a deleted record or a merged duplicate.

Filter the same view to close-date moves, count them per deal, and one of the three slipped deals stands out: six moves in eight weeks. I’d spend more of the call on that deal than on the total.

Daily rows over validity ranges

The textbook alternative is a type 2 slowly changing dimension, one row per version of a deal with valid-from and valid-to dates. It’s far smaller, since most deals don’t change most days. But every as-of question becomes a range condition, and a day-by-day trend becomes a join against a calendar. Each new version also has to update the previous row’s valid-to, so rows get edited after they’re written. Rerunning one bad day turns into surgery on ranges instead of a delete and an insert.

At 2,400 open deals, that’s under a million rows a year, which costs almost nothing. With a few hundred thousand open records, I’d redo the arithmetic.

Tie it out to the CRM every morning

Right after each load, compare the new snapshot with the CRM’s own figures: the open deal count and the quarter’s open commit total. Pull them in the same job, as a user who can see every record, because the live total starts moving once people log in. A mismatch holds the publish. The usual cause: the extract’s user can’t see some records, so a whole team’s deals drop out of the forecast without an error anywhere.

History you can’t get back

Real history starts with the first snapshot. I’d still seed what the CRM’s field history holds, walking each tracked field back from today’s value, and mark every one of those rows reconstructed. Untracked fields carry today’s values, which is a guess. Reconstructed rows are fine for spotting a deal that slipped four times last year. They shouldn’t feed a number anyone has to defend.

Stale deals come free. An open deal with no change rows in 45 days is stale, and the list is an anti-join between today’s snapshot and the last 45 days of change facts. Forty-five days is my default. Like “slip,” the real number belongs to sales ops.