דילוג לתוכן
מרכז התיעוד
לאתר LinkNodes Energy
LinkNodesENERGYתיעוד

טוענים את החיפוש…

    חיפוש גם בתוך המאמרים ↑ ↓ לניווט Esc לסגירה

    לאתר החברה

    תפעול החברה

    טיוטה: מדדי 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:

    IntervalExact mrrTwelfthsMinorMonthly minor-unit display
    Monthlynet_minor × 12net_minor
    Annualnet_minorround_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 stateIncluded in MRR?
    active with subscription payment evidenceYes
    past_due with previous subscription payment evidenceYes, until observed termination
    canceled, cancelAtPeriodEnd=true, with payment evidence and a future service endYes, through that service period
    Canceled paid service whose known end has passedNo
    pending_payment, trialing, expiredNo
    Any state without subscription payment evidenceNo

    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:

    TransitionClassification
    No MRR → positive MRR, lifetime history known and never previously payingNew
    No MRR → positive MRR, previously payingReactivation
    No MRR → positive MRR, earlier history unknownUnclassified activation
    Positive MRR increasesExpansion
    Positive MRR decreases but remains positiveContraction
    Positive MRR → zero MRRChurn

    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:

    StatusMeaning
    availableRequired sources support the reported value, including a real zero
    missing_dataA required source or amount is absent
    incomplete_dataSource coverage, ledger consistency, or the cost period is incomplete
    new_baselineThe required historical boundary precedes observation coverage
    unknown_historyAn activation cannot be proven first-ever or returning
    insufficient_historyThe LTV model lacks six usable consecutive months
    zero_denominatorDivision would require a nonzero account, revenue, MRR, or CAC base
    zero_churnThe 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.

    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.