SiSu RevOps

M6  /  L6.2  /  about 12 minutes

Movement that reconciles, and the week that did not happen

"What changed this week" sounds like the easiest question in the pack, and it goes wrong the most quietly. Nobody reads "12 deals advanced" and thinks "that cannot be right". They think "fine" and move on. So the table needs a proof attached: arithmetic that either closes or does not.

By the end of this lesson you can: specify a movement table with fixed change types and a reconciliation line, tell additive rows from non-additive ones, and refuse a zero-movement week until you have run the check. Everything this lesson uses came in the practice pack from the course page and was copied into 10-data/ in L6.1; if you have not downloaded the pack, do that first.

Fix the change types, or you will get different ones every week

The change types, closed set, in this order:

Change type Definition, between snapshot week W-1 and W
created opportunity_id present in W, absent in W-1
advanced stage index in W greater than in W-1
slipped a stage stage index in W lower than in W-1
close date pushed close_date in W later than in W-1
close date pulled in close_date in W earlier than in W-1
amount increased amount in W greater than in W-1, converted
amount decreased amount in W lower than in W-1, converted
closed won present in W-1, absent in W, and the opportunity file says Closed Won
closed lost present in W-1, absent in W, and the opportunity file says Closed Lost
left the file, other present in W-1, absent in W, and neither of the above

That last row exists because "an opportunity leaves the snapshot file the week after it closes" (DATA-DICTIONARY.md in your pack's data folder). A deal that leaves for any other reason is something you want to see, not something quietly absorbed into "closed lost".

Two kinds of row, and only one kind adds up

Most hand-built movement tables get this wrong:

  • Some rows change the total. created, closed won, closed lost, left-other, amount increased, amount decreased. These carry a delta.
  • Some rows do not. advanced, slipped, close date pushed, close date pulled in. A deal that advances a stage is worth the same money it was worth last week.

Print one amount column and add it up, and you double count every deal that both advanced and grew. So the table prints the deal value for non-additive rows, the delta for additive rows, labels which is which, and reconciles on the additive ones only:

opening amount
  + created
  - closed won
  - closed lost
  - left the file, other
  + amount increases
  - amount decreases
  = closing amount

And the same for counts:

opening rows + created - all exits = closing rows

The reconciliation is not decoration. It catches a snapshot join done wrong, the most common silent failure in this workflow.

One currency, and one authority for it

pipeline_snapshots.amount is in the deal's billing currency (DATA-DICTIONARY.md). Convert before you compare anything, using the fixed rates in the data dictionary (EUR 1.08, GBP 1.27), and say in the brief that the account is the authority for the currency, exactly as in Module 5.

A live example: OPP-006697 is labelled USD in opportunities.csv while its account ACC-03045 bills in EUR, a documented defect (answer-keys/PLANTED-ISSUES.md in your pack, class SUBTLE currency-mismatch, which you open only after attempting the lab), and the same deal is labelled EUR in all 26 of its snapshot rows. So snapshot totals and opportunity totals disagree on this one deal, and the account file settles it. Not a trick question. A Tuesday.

Try this now

Run the movement section of the brief for real:

OUTCOME
Produce the week-over-week pipeline movement table for the week ending
2026-07-27.

INPUTS
10-data/pipeline_snapshots.csv, weeks 2026-07-20 and 2026-07-27 only.
10-data/opportunities.csv, used only to resolve the closed stage of deals that
left the file.
10-data/accounts.csv, used only to read accounts.currency.

OUTPUT SHAPE
30-output/pipeline/review-2026-W30.md, section "Movement".
One table. Columns: change_type | count | amount_usd | additive.
Amounts converted to USD at EUR 1.08, GBP 1.27, USD 1.00.
For additive rows amount_usd is the delta. For non-additive rows it is the
current value of the deals concerned, and the additive column reads "no".
Below the table, two reconciliation lines, each stating pass or fail:
  rows:    opening + created - exits = closing
  amount:  opening + created - exits + increases - decreases = closing

CONSTRAINTS
Stage order is Qualification, Discovery, Solution Review, Technical Validation,
Proposal, Negotiation.
accounts.currency is the authority where it disagrees with the snapshot.
Before computing anything, compare the two snapshot weeks on row count and on a
hash of the ordered rows. If they are identical, stop and report it.

DEFINITION OF DONE
Both reconciliation lines print and both say pass, or the run reports why not.

What you should see, from the shipped data:

Week ending 2026-07-27. Opening 59 open deals, 6,126,525 USD. Closing 60 open deals, 6,200,285 USD.

change_type count amount_usd additive
created 2 +82,560 yes
closed won 1 -27,000 yes
closed lost 0 0 yes
left the file, other 0 0 yes
amount increased 2 +18,200 yes
amount decreased 0 0 yes
advanced 10 537,640 no
slipped a stage 0 0 no
close date pushed 2 322,000 no
close date pulled in 0 0 no

Reconciliation, rows: 59 + 2 - 1 = 60. Pass. Reconciliation, amount: 6,126,525 + 82,560 - 27,000 + 18,200 = 6,200,285. Pass.

This gives your Monday meeting what "pipeline is up 73,760" does not: ten deals advanced worth 537,640, two close dates out on 322,000 of pipeline, one deal closed won at 27,000. The total went up and the quarter got harder, both at once.

When this goes wrong

The break. Run the same movement analysis across the whole snapshot history and trust the file. Specifically, run the week ending 2026-04-20.

The tell. Every single cell is zero.

change_type count amount_usd
created 0 0
advanced 0 0
slipped a stage 0 0
close date pushed 0 0
amount increased 0 0
amount decreased 0 0
closed won 0 0
closed lost 0 0

Opening 50 deals, 4,821,045 USD. Closing 50 deals, 4,821,045 USD.

Reconciliation, rows: 50 + 0 - 0 = 50. Pass. Reconciliation, amount: 4,821,045 + 0 - 0 = 4,821,045. Pass.

Stop on that, because it is the whole lesson. The reconciliation passes. Both lines say pass. The week is fiction.

The 2026-04-20 snapshot is a byte-for-byte copy of 2026-04-13, all 50 rows, identical apart from the date: the weekly load failed and the previous file was reused (PLANTED-ISSUES.md, class SUBTLE broken-etl). In a business that advanced between 4 and 17 deals a stage in every other week of the file (a median of 11 or 12 across the other 24 week pairs), nobody touched anything across 50 open deals. Not a quiet week. A missing week.

The damage spreads, too. The following week absorbs two weeks of movement into one:

week advanced closed won closed lost created
2026-04-06 to 04-13 10 1 1 5
2026-04-13 to 04-20 0 0 0 0
2026-04-20 to 04-27 13 5 4 4

A manager reading that sequence sees a stall and then a surge, and will explain both. Neither happened.

What catches it. A consistency check is not a verification. Arithmetic on a duplicated file is still correct arithmetic, so what catches it sits outside the arithmetic: compare each snapshot week against the previous one on row count and on a hash of the ordered rows, before any movement runs. Two consecutive weeks that hash identically did not happen. If the hash is unavailable, fall back to checking that at least one of stage, amount or close_date differs somewhere between the two weeks.

The guard. Three things, in this order, permanently in the brief:

  1. Snapshot integrity test runs first. Any week identical to its predecessor is reported and the run stops.
  2. A zero-movement week is never accepted silently. If the integrity test passes and movement is still zero, that is a finding and it goes in the pack in words.
  3. The affected week is a judgment, not a patch. Exclude it and say so in the exclusions section: "the week of 2026-04-20 is excluded because the snapshot is a duplicate of 2026-04-13; the week of 2026-04-27 therefore contains two weeks of movement." Do not smooth it, interpolate it, or quietly drop it. The reader is about to draw a trend line through it, and they need to know.

What you keep

  • 20-briefs/pipeline-review.brief.md, a brief you start this lesson from templates/M6/pipeline-review.brief.md in your practice pack, with the movement section, the change type list, the reconciliation lines and the snapshot integrity test.
  • The first 30-output/pipeline/review-YYYY-WW.md with a movement table that reconciles.

Check yourself

  1. Your movement table says opening plus changes does not equal closing, and the gap is exactly one large deal. Name the two most likely causes and the check that separates them.
  2. Why is "advanced" excluded from the amount reconciliation? What breaks if you include it?
  3. Your integrity test flags two identical weeks. Your manager wants the pack in ten minutes. What goes in the pack?
  4. A colleague fixes the duplicated week by averaging the movement across the two affected weeks so the trend looks smooth. Name what that costs the reader.

Where this goes next

L6.3 turns "which deals are at risk" into named rules that emit evidence, and produces the first deal card.