The Triple-Counted Deal

RevOps Mystery

~1 hour to resolution

An AE pings Slack: "Why does this opp show $450K ARR?" The deal is only $150K/year. The ARR field is fine on most opps, so what's different about this one? This is the story of how a single wrong number uncovered a formula + Flow design flaw that had been silently tripling revenue on every ramping contract in the org for 14 months — $2.1M in phantom pipeline.

The Slack Message

AE — Enterprise10:42 AM

Hey — quick one. On the Meridian Health opp, the ARR field shows $450K but I'm pretty sure the deal is only like $150K/yr. Most opps look fine so IDK what's different about this one. Can you take a look?

RevOps Lead10:44 AM

Don't have time to dig into this rn. Forwarding to Claude — it'll figure out what's going on with the OLIs and the rollup. If it's a formula issue it'll fix it.

The Prompt

Copy-pasted the Slack thread into Claude Code. Two minutes of effort.

Hey — quick one. On the Meridian Health opp (006Dn000003xK9PIAU), the ARR field shows $450K but I'm pretty sure the deal is only like $150K/yr. Most opps look fine so IDK what's different about this one. Can you take a look and figure out what's going on? If it's broken, fix it.

The RevOps Panic Moment

"Most opps look fine" is the scariest phrase in RevOps. It means the bug is conditional — it only fires on certain deal structures, which means it's been hiding in plain sight. When the number is 3x, the question isn't "is this one opp wrong?" — it's "how many board decks have we sent with phantom revenue?"

How Claude Code Handles This

Inspect

Pull the Opportunity and its line items to see what the data actually looks like

Trace the Formula

Read the OLI formula field and the rollup to understand how ARR is calculated

Find the Pattern

Query the org for other opps with the same multi-line stacking pattern

Trace the Flow

Pull the ARR calculation Flow and find where it blindly trusts the rollup

Fix Formula + Flow

New OLI formula for current-year detection, redesigned Flow for smart grouping

Backfill & Prevent

Recalculate all affected opps, add a validation rule to catch future stacking

After you paste this prompt, Claude Code:

"ARR shows $450K on a deal that should be ~$150K/yr. Let me pull the opp and its line items to see what's actually in there..."

Running

$ sf data query --query "SELECT Id, Name, ARR__c, ARR__c, Amount, \ Contract_Term_Months__c FROM Opportunity \ WHERE Id = '006Dn000003xK9PIAU'" --json

Meridian Health Systems — Platform Enterprise. ARR__c: $450,000 (inflated). Amount: $450,000. Contract_Term_Months__c: 36.

Running

$ sf data query --query "SELECT Id, Product2.Name, UnitPrice, Quantity, \ TotalPrice, Service_Term_Months__c, Annualized_Amount__c, ServiceDate \ FROM OpportunityLineItem \ WHERE OpportunityId = '006Dn000003xK9PIAU' \ ORDER BY ServiceDate" --json

3 line items found: Line 1: Platform Enterprise, $140,000, Term: 12mo, ServiceDate: 2025-03-01, Annualized: $140,000 Line 2: Platform Enterprise, $150,000, Term: 12mo, ServiceDate: 2026-03-01, Annualized: $150,000 Line 3: Platform Enterprise, $160,000, Term: 12mo, ServiceDate: 2027-03-01, Annualized: $160,000

"There it is. The rep quoted this as three annual line items — one per year with a ramp — instead of a single line with a 36-month term. Each line has Term = 12 months, so the annualization formula treats each one as a standalone annual subscription. The rollup sums all three: $140K + $150K + $160K = $450K. That's the full TCV, not the ARR."

"The OLI formula is Annualized_Amount__c = TotalPrice * 12 / Service_Term_Months__c. When Term = 12, it just passes through TotalPrice. Correct per-line, but the rollup treats sequential annual lines as concurrent subscriptions. Now the question: is this one rep, or a pattern?"

Running

$ sf data query --query "SELECT OpportunityId, Product2Id, \ Product2.Name, COUNT(Id) LineCount, SUM(TotalPrice) TotalValue \ FROM OpportunityLineItem \ GROUP BY OpportunityId, Product2Id, Product2.Name \ HAVING COUNT(Id) > 1" --json

23 opportunities with duplicate product lines found. Total over-reported value: $2.1M across 8 reps.

"23 opportunities. $2.1M in over-reported pipeline. This has been happening silently. Let me check how long..."

Running

$ sf data query --query "SELECT MIN(CreatedDate) FirstOccurrence, \ MAX(CreatedDate) LatestOccurrence \ FROM OpportunityLineItem \ WHERE OpportunityId IN ( \ SELECT OpportunityId FROM OpportunityLineItem \ GROUP BY OpportunityId, Product2Id HAVING COUNT(Id) > 1 \ )" --json

First occurrence: 2024-11-18. Latest: 2026-01-29. Pattern has been active for 14 months.

"14 months of triple-counting. The pattern started right after the new quoting template was rolled out in November 2024. The template encouraged reps to break multi-year ramps into annual lines for finance visibility — but nobody checked what that does to the ARR rollup. Now I need to understand the full calculation chain before I fix it."

The Anatomy of a Triple-Count

How it should work

1 OLI: Platform Enterprise

TotalPrice: $450,000

Term: 36 months

Formula: $450K × 12 / 36

Annualized: $150,000

Rollup ARR: $150,000

What the rep did

OLI 1: Year 1 — $140K, Term: 12mo

OLI 2: Year 2 — $150K, Term: 12mo

OLI 3: Year 3 — $160K, Term: 12mo

Formula per line: correct (12/12 = pass-through)

Rollup ARR: $140K + $150K + $160K

Result: $450,000 (3x actual ARR)

The formula is correct per-line. The rollup can't distinguish sequential years from concurrent subscriptions. The Flow blindly trusts the rollup.

After you paste this prompt, Claude Code:

Running

$ sf project retrieve start \ --metadata CustomField:OpportunityLineItem.Annualized_Amount__c \ --metadata CustomField:Opportunity.ARR__c \ --target-org prod

Retrieved 2 field definitions.

OLI formula: Annualized_Amount__c = TotalPrice * 12 / Service_Term_Months__c — annualizes each line independently. Correct per-line, blind to sibling lines.

Opportunity rollup: ARR__c = SUM(OpportunityLineItem.Annualized_Amount__c) — sums ALL line items. Cannot distinguish concurrent vs. sequential lines.

Running

$ sf project retrieve start \ --metadata Flow:Calculate_Opportunity_ARR \ --target-org prod

Retrieved Flow: Calculate_Opportunity_ARR (Record-Triggered, on Opportunity update).

Flow logic: When ARR__c changes → propagate to Account.Total_ARR__c with SUM of child Opportunity ARR values. The Flow blindly trusts the rollup. No grouping, no date filtering, no stacking detection.

"The full chain: OLI formula annualizes each line → rollup sums them all → Flow propagates inflated ARR to Account. Three components, and the bug lives between the formula and the rollup. The formula can't see sibling OLIs (Salesforce limitation — no cross-object OLI-to-OLI references in formulas). The rollup can't filter by date. So the fix has to live in the Flow."

The Root Cause

Salesforce OLI formulas cannot reference sibling line items — each OLI is an island. The Annualized_Amount__c formula works perfectly for its own line, but it has no way to know that two other lines represent Year 2 and Year 3 of the same contract. The rollup summary field sums everything. The Flow propagates the inflated sum to the Account. Three components, each correct in isolation, producing a 3x overcount when the deal structure changes. The quoting template change in November 2024 encouraged reps to break ramps into annual lines for finance visibility — but nobody checked what that does downstream.

After you paste this prompt, Claude Code:

"Two-part fix. First: a new formula on OLI to flag which line is the current-year line. Second: redesign the Flow to group OLIs by product, detect stacked annuals, and only count current-year amounts."

New OLI formula: Is_Current_Year_Line__c

IF(

AND(

ServiceDate <= TODAY(),

ServiceDate + (Service_Term_Months__c * 30.44) > TODAY()

TRUE,

FALSE

)

Flags the OLI whose service window includes today.

New OLI formula: Effective_ARR__c

IF(

Is_Current_Year_Line__c,

Annualized_Amount__c,

)

Only the current-year line contributes to ARR.

For single-line deals (Term=36), the original formula

already annualizes correctly and this passes it through.

Redesigned Flow: Calculate_Opportunity_ARR

Trigger: OLI create/update/delete (moved from Opportunity trigger)

1. Get Records: all OLIs on parent Opportunity

2. Loop + Decision: group by Product2Id

3. For each product group:

- If only 1 line → use Annualized_Amount__c (standard case)

- If multiple lines → SUM only where Is_Current_Year_Line__c = TRUE

4. Sum across product groups → Opportunity.ARR__c

5. Roll up to Account.Total_ARR__c

Validation Rule: Stacked_Annual_Lines_Warning

Fires on OLI insert when another OLI with the same

Product2Id already exists on the Opportunity.

Soft warning (not blocking): "This product already has

a line item on this opportunity. If this is a multi-year

ramp, consider using a single line with the full contract

term instead."

Running

$ sf project deploy start \ --source-dir force-app/main/default/objects/OpportunityLineItem/fields/Is_Current_Year_Line__c.field-meta.xml \ --source-dir force-app/main/default/objects/OpportunityLineItem/fields/Effective_ARR__c.field-meta.xml \ --source-dir force-app/main/default/flows/Calculate_Opportunity_ARR.flow-meta.xml \ --source-dir force-app/main/default/objects/OpportunityLineItem/validationRules/Stacked_Annual_Lines_Warning.validationRule-meta.xml \ --test-level RunLocalTests \ --target-org prod

Deployed 4 components. All tests passing. Zero errors.

Formula + Flow + validation rule deployed to production.

After you paste this prompt, Claude Code:

"Flow is live for new changes. Now I need to recalculate ARR on all 23 affected opportunities and their parent Accounts..."

Running

$ sf apex run --file scripts/backfill_stacked_arr.apex --target-org prod

23 opportunities recalculated. 14 Account ARR values corrected.

Running

$ sf data query --query "SELECT Id, Name, ARR__c, ARR__c \ FROM Opportunity \ WHERE Id = '006Dn000003xK9PIAU'" --json

Meridian Health Systems: ARR__c: $140,000 (current year, corrected). TCV unchanged at $450,000.

Running

$ sf data query --query "SELECT Name, Total_ARR__c \ FROM Account \ WHERE Id IN ( \ SELECT AccountId FROM Opportunity \ WHERE Id IN ('006Dn000003xK9PIAU', ...) \ )" --json

All 14 parent Accounts verified. ARR values corrected. Net reduction: $2.1M in previously over-reported ARR.

Backfill complete. All 23 opportunities and 14 accounts corrected. Pipeline ARR reduced by $2.1M to accurate numbers.

What Shipped

Is_Current_Year_Line__c

OLI Formula · New

Flags the line item whose service window includes today. Enables the Flow to distinguish "this year's revenue" from future-year lines on the same opp.

Effective_ARR__c

OLI Formula · New

Zeroes out non-current-year lines so only active revenue contributes to ARR. Single-line deals pass through unchanged.

Calculate_Opportunity_ARR

Record-Triggered Flow · Redesigned

Moved trigger from Opportunity to OLI. Groups lines by product, detects stacked annuals, sums only current-year amounts. Propagates corrected ARR to Account.

Stacked_Annual_Lines_Warning

Validation Rule · New

Soft warning when a rep adds a duplicate product line. Suggests using a single line with the full contract term instead. Doesn't block — just educates.

Before & After

$2.1M

Over-reported ARR

Affected opportunities

Months undetected

Components deployed

Pipeline ARR accuracy

$2.1M overCorrect

Reps affected

8 unawareAll warned

The RevOps Win

A "quick one" from an AE turned into $2.1M of phantom pipeline that had been inflating ARR reports for 14 months. The bug was invisible on standard deals and only fired on ramping contracts — exactly the kind of deal structure you want your reps creating. The OLI formula was correct per-line. The rollup was doing what rollups do. The Flow trusted both. Three correct components producing a wrong answer. Diagnosed, fixed, backfilled, and prevented — all from a forwarded Slack message.

Try This Workflow

Next time a number looks wrong, forward the Slack message. Let Claude trace it.

View Quickstart Guide More Playbooks