# טיוטה: מדדי RevOps

> טיוטת PR #69 — לא מוזגה. מקור: docs/admin-revops-metrics.md · 34bdd4e · 2026-11-21.

This document defines methodology version `1` implemented by
`lib/admin/revops-calculations.ts`. Its public input and report types live in
`lib/admin/revops-types.ts`. The calculation is pure: it reads no database, provider,
credentials, or clock. `calculateRevOps(input)` uses the supplied atomic snapshot
and `asOf` time. There is no production-data fallback to synthetic data.

## Scope, units, and source coverage

The seven views are the whole company, B2C (`personal` + `family`), B2B
(`business` + `enterprise`), and the four individual subscription pricing tiers.
The tier comes from each observed priced subscription, rather than retroactively
applying today's customer plan or current catalogue prices.

An account is a tenant. Paying account counts are distinct tenant IDs, not seats,
membership rows, invoices, or subscription count. An account with simultaneous
subscriptions in two tiers is included once in each relevant filtered population
and only once company-wide. Consequently, filtered account counts are not always
additive. Tier MRR amounts are additive because each subscription amount belongs
to exactly one tier. Acquisition counts use the first paying activation's tier(s),
not the customer's later tier.

All monetary inputs are integer **ILS minor units**. VAT-exclusive subscription
prices are already net. For VAT-inclusive prices, the engine first removes VAT
using the existing `ev1_private.billing_split_price` policy:

`net_minor = round_half_up(gross_minor × 10000 / (10000 + vat_rate_bps))`

This operation uses integer arithmetic and matches the billed net price, including
half-unit rounding. Only then is the annual price normalized. The internal unit is
one twelfth of a minor unit:

| Interval | Exact `mrrTwelfthsMinor` |      Monthly minor-unit display |
| -------- | -----------------------: | ------------------------------: |
| Monthly  |         `net_minor × 12` |                     `net_minor` |
| Annual   |              `net_minor` | `round_half_up(net_minor / 12)` |

MRR totals and movements are summed in exact units before display rounding. They
are never reconstructed by summing independently rounded labels. Every MRR metric
returns `exactTwelfthsMinor` alongside `value`; `value` is rounded minor units.
ARR in minor units equals exact MRR twelfths, since ARR is MRR × 12. All finance
amounts are checked for safe integer bounds; malformed or unsafe source data is
rejected rather than approximately added. Ratios are returned as fractions, so
`0.05` means 5%.

Reporting uses **Asia/Jerusalem calendar months**, including Israel's daylight
saving changes. A period is `[start, end)`. Its opening state is immediately before
`start`; events exactly at `start` belong to that month, and events at `end` belong
to the next month. A current month ends at the supplied observation time and is
marked incomplete. A future month is rejected. The historical table includes the
selected month and the previous six months; `complete` denotes calendar completion,
while each metric's availability separately describes data coverage.

## What counts as observed recurring MRR

Version 1 follows observed recurring subscription service, rather than recognized
accounting revenue, cash collections, energy sales, card verification, or forecast
contracted revenue. Subscription payment evidence is required: `firstPaidAt` comes
from an actual subscription obligation, including preserved evidence after refunds.
A card-verification transaction never qualifies.

| Subscription state                                                                   | Included in MRR?                 |
| ------------------------------------------------------------------------------------ | -------------------------------- |
| `active` with subscription payment evidence                                          | Yes                              |
| `past_due` with previous subscription payment evidence                               | Yes, until observed termination  |
| `canceled`, `cancelAtPeriodEnd=true`, with payment evidence and a future service end | Yes, through that service period |
| Canceled paid service whose known end has passed                                     | No                               |
| `pending_payment`, `trialing`, `expired`                                             | No                               |
| Any state without subscription payment evidence                                      | No                               |

This treatment reflects the actual LinkNodes billing contract:
`lne_billing_cancel_subscription` immediately stores `status='canceled'` and returns
`access_until=current_period_end`. A canceled trial can retain trial access, so
status and period end alone are insufficient evidence of paid MRR. A never-paid
trial can also become `past_due` after its first failed renewal; it is excluded.

At a canceled paid subscription's known service end, the engine records the
effective expiry once that time has passed, even if the expiry maintenance job has
not run yet. Resuming before that time cancels the derived expiry. Changes at the
same observed timestamp are applied together for each account, so a simultaneous
replacement subscription does not create a spurious intermediate churn. No future
expiry is counted before it occurs. A canceled paid subscription that lacks its
required service end is invalid source data, not evidence of zero MRR.

An active/past-due subscription's expected billing period end does not by itself
terminate recurring service. Refunds do not automatically terminate the contract
or change its recurring price; the billing state must record that change.

## Movement bridge and first-paying history

The engine aggregates all subscriptions on an account before classifying an
economic movement:

| Transition                                                                | Classification          |
| ------------------------------------------------------------------------- | ----------------------- |
| No MRR → positive MRR, lifetime history known and never previously paying | New                     |
| No MRR → positive MRR, previously paying                                  | Reactivation            |
| No MRR → positive MRR, earlier history unknown                            | Unclassified activation |
| Positive MRR increases                                                    | Expansion               |
| Positive MRR decreases but remains positive                               | Contraction             |
| Positive MRR → zero MRR                                                   | Churn                   |

The movement report sums all observed changes in the period, including churn and
reactivation within the same month. Those two movements do not disappear merely
because the account has similar MRR at the month's start and end. Monotonic decimal
ledger IDs order changes sharing an observed timestamp without converting IDs to
floating-point numbers. The engine groups the account's changes at the same
JavaScript timestamp precision (milliseconds) and validates each before-state
against the preceding stored after-state.

For a tier migration, the retained recurring amount transfers between scopes.
Only an account's net economic increase or decrease becomes expansion or
contraction. For example, moving from Personal at ₪100 MRR to Business at ₪150
produces a ₪100 transfer out of Personal/B2C, a ₪100 transfer into Business/B2B,
and ₪50 expansion. Company-wide it is ₪50 expansion, with no new customer, churn,
or reclassification amount. A Personal → Family transfer has no transfer at B2C
level. Multiple simultaneous subscriptions use deterministic tier-order matching
after retaining overlapping amounts within each tier.

All movement values except `net` are positive magnitudes. Each complete scope
must reconcile, using exact twelfth-minor units:

`closing = opening + new + expansion + reactivation + unclassified_activation`

`          - contraction - churn + reclassification_in - reclassification_out`

`net = closing - opening`

The code rejects an available bridge that does not reconcile. Displayed rounded
minor values can differ by a rounding unit; `exactTwelfthsMinor` is authoritative.

An initial migration establishes a baseline, not new business. Existing tenants
start with unknown lifetime history. Preserved earlier paid evidence can establish
reactivation; absence of earlier evidence does not prove first-ever acquisition.
Newly created tenants after coverage begins have known pre-coverage history.
When activation history is unknown, the amount goes into the explicit unclassified
bucket. Full new/reactivated account counts and CAC are unavailable for affected
scopes rather than treating those customers as new or silently omitting uncertainty.

## Counts, churn, growth, and ARPA

`payingAccounts` counts distinct accounts with positive closing MRR in the scope.
`newPayingAccounts` counts distinct first-ever paying acquisitions during the month,
including those who subsequently churned in that month. `reactivatedAccounts`
counts distinct returning accounts, not return events. One account can have both
a first acquisition and a later reactivation in the same month.

Customer churn uses the opening paying-account cohort:

`customer_churn = opening_scope_accounts_no_longer_paying_company_wide_at_close`

`                 / opening_scope_paying_accounts`

Newly acquired customers who also churn in the month are not in this denominator.
An opening customer who churns and returns by closing is not lost. A customer who
moves to another tier or B2C/B2B segment is not company churn. For segmented LTV,
this measures company-level losses from the segment's opening cohort, not departure
from that particular product tier. A segment's net revenue/customer totals can
therefore change due to transfers even when its customer churn rate is zero.

MRR growth is `(closing_exact_mrr - opening_exact_mrr) / opening_exact_mrr`.
It includes the filter's transfers; the company has none. An opening value of zero
produces an undefined relative growth rate, not 0% or infinity.

ARPA is closing net MRR divided by closing paying accounts. It is calculated from
unrounded MRR, then rounded for the monetary display. No paying accounts means ARPA
is undefined.

## CAC and cost attribution

CAC is `attributable acquisition spend / first-ever new paying accounts` for the
same **completed calendar month and scope**. Costs must be explicitly attributed
to pricing tiers by a trusted finance source. Shared costs must have a documented
allocation before import; the engine does not invent an allocation from current
customer counts. Acquisition spend should include acquisition sales/marketing labor,
campaigns, tools, and contractors allocated to that period. Service delivery,
support, and product-development costs belong outside the acquisition numerator.

All included tiers must have complete acquisition cost coverage. A row with an
explicit, verified zero is valid; a missing row, null amount, or partial coverage
is not zero. No new paying customers means CAC is undefined. Reactivations and
tier migrations do not increase its denominator. Unknown first-paying history
prevents a complete CAC calculation.

CAC is unavailable for the current partial month even if a monthly cost snapshot
is present: the cost contract has no independently proven month-to-date cutoff.
The UI offers the last complete month. This avoids dividing a full-month cost by
a partial-month acquisition count.

## LTV model, assumptions, and unavailable states

Version 1 is an explicit **gross-profit-adjusted estimate**:

`LTV = selected_period_closing_ARPA × trailing_service_gross_margin`

`      / mean(trailing_six_monthly_customer_churn_rates)`

The trailing window is the six completed, consecutive calendar months immediately
before the selected month, even when the selected month itself is already complete.
Every month needs a complete opening/closing customer cohort and a nonzero opening
paying population. An empty month is not silently treated as 0% churn or skipped.

Gross margin is revenue-weighted over those same six months:

`gross_margin = (sum(net_service_revenue) - sum(attributable_service_cost))`

`               / sum(net_service_revenue)`

Service revenue and direct service costs must have complete, aligned finance
coverage for each included tier. They are separate inputs from MRR and from gross
energy turnover. Zero service cost is valid when explicitly complete; zero service
revenue makes gross margin undefined. Negative margin and negative gross-profit
LTV remain visible instead of being clamped to zero.

The estimate assumes selected-period ARPA continues and the trailing window's
margin and customer churn remain representative. It is not realized cohort profit,
discounted cash flow, accounting revenue recognition, or a promise about a new
customer's lifetime. Small cohorts and changing pricing/customer mix make it
volatile. Segment migrations are retained company customers under the cohort rule
above, which is an explicit interpretation for this product.

LTV is null for insufficient observed history, missing/partial service finances,
no paying accounts, zero service revenue, zero observed churn, or arithmetic outside
the safe range. Zero churn cannot support a finite estimate from this model. The
LTV/CAC ratio uses unrounded estimates; it is undefined when CAC is zero or either
underlying estimate is unavailable.

Each metric exposes `value`, `status`, and a displayable `reason`:

| Status                 | Meaning                                                                  |
| ---------------------- | ------------------------------------------------------------------------ |
| `available`            | Required sources support the reported value, including a real zero       |
| `missing_data`         | A required source or amount is absent                                    |
| `incomplete_data`      | Source coverage, ledger consistency, or the cost period is incomplete    |
| `new_baseline`         | The required historical boundary precedes observation coverage           |
| `unknown_history`      | An activation cannot be proven first-ever or returning                   |
| `insufficient_history` | The LTV model lacks six usable consecutive months                        |
| `zero_denominator`     | Division would require a nonzero account, revenue, MRR, or CAC base      |
| `zero_churn`           | The LTV model cannot estimate a finite lifetime from zero observed churn |

## Source-only persistence and access integration

The migration is `supabase/migrations/20261121145421_company_admin_revops_ledger.sql`.
It is source code; adding it does not apply it to a hosted database or create any
new Supabase resource. Subscription and payment-evidence triggers capture financial
state changes automatically once the migration is deliberately applied. Append-only
private baseline/event/account/cost sources are exposed through the service-only,
security-invoker `lne_admin_revops_read(date)` atomic read.

Reads are bounded: 5,000 current subscription states, 5,000 account-history entries,
and 10,000 ledger events. An overflow returns an empty affected array with its
coverage flag false. The engine does not interpret that array as a complete empty
company. Complete current MRR can be computed independently of truncated historical
events. Historical movement totals require a complete ledger, before/after
continuity, and agreement with an independently complete current snapshot.

The source-only service cost-import function is
`lne_admin_revops_import_cost_month(source_key, month, entries_jsonb)`. It accepts
four explicitly attributed tier snapshots, validates completeness/null handling,
and uses an idempotent source key. A corrected snapshot uses a new key and replaces
the previous month's effective snapshot rather than being added to it. No expense
provider is connected by this change. Reading the Admin API never ingests a cost
record or sends a message.

`/api/admin/revops` requires staff `revops.read` authorization before constructing a
privileged database client. Its report contains aggregate metrics and coverage,
not contact details, raw event payloads, or account IDs. The separate development
preview uses explicitly synthetic fixtures and never bypasses live API authorization.

## Primary references and deliberate product decisions

These sources informed the definitions; the implementation's timing, coverage, and
gross-margin policy above are explicit LinkNodes decisions, not claims of identical
configuration to another analytics provider.

- [Stripe: understanding MRR and ARR](https://support.stripe.com/questions/understanding-monthly-recurring-revenue-%28mrr%29-and-annual-recurring-revenue-%28arr%29)
  describes monthly normalization of recurring prices and annual run rate.
- [ChartMogul: MRR movements](https://help.chartmogul.com/article/163-understanding-mrr-movements)
  describes customer-level movement categories and event-driven changes.
- [ChartMogul: ARPA](https://help.chartmogul.com/article/209-chart-average-revenue-per-account)
  defines average recurring revenue per paying account.
- [ChartMogul: customer churn](https://help.chartmogul.com/article/203-chart-customer-churn-rate)
  discusses the opening-customer denominator and treatment of same-period joining,
  churn, and reactivation.
- [Stripe: SaaS CAC](https://stripe.com/resources/more/cac-in-saas)
  describes acquisition sales/marketing cost divided by new paying customers.
- [ChartMogul: LTV report](https://help.chartmogul.com/article/208-chart-customer-lifetime-value-ltv)
  uses prior-six-month average customer churn, excluding the current month, and
  explains why zero churn cannot produce its estimate. Its report formula is
  revenue-based; LinkNodes adds a measured service gross-margin multiplier.
- [ChartMogul: subscription analytics glossary](https://help.chartmogul.com/article/166-subscription-analytics-glossary)
  also discusses gross-margin-adjusted lifetime value as a business metric.

## Focused verification

Run the pure engine's financial and coverage tests:

```bash
node node_modules/vitest/vitest.mjs run lib/admin/revops-calculations.test.ts
```

The suite covers canonical VAT rounding, annual fractional minor units, Israel
calendar/DST boundaries, simultaneous account activation, first-payment evidence,
paid cancellation/resumption/expiry, unknown lifetime history, all seven transfer
bridges, bounded/broken source coverage, complete versus missing/zero cost inputs,
six-month LTV, zero churn, weighted gross margin, and retained service losses.
It includes a deterministic multi-account transition sequence that verifies exact
opening-plus-movements-equals-closing for every scope.
