תפעול החברה
טיוטה: מדדי RevOps
החוזה המלא ל־MRR, תנועות, CAC, LTV, הנחות ומצבי נתון חסר.
בעמוד הזה
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 describes monthly normalization of recurring prices and annual run rate.
- ChartMogul: MRR movements describes customer-level movement categories and event-driven changes.
- ChartMogul: ARPA defines average recurring revenue per paying account.
- ChartMogul: customer churn discusses the opening-customer denominator and treatment of same-period joining, churn, and reactivation.
- Stripe: SaaS CAC describes acquisition sales/marketing cost divided by new paying customers.
- ChartMogul: LTV report 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 also discusses gross-margin-adjusted lifetime value as a business metric.
Focused verification#
Run the pure engine's financial and coverage tests:
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.