Cohort and LTV by acquisition vintage

Set the vintage by the entity's first paid charge, not by account, contract or lead creation. Exclude orders still waiting for the first payment and use the plan in force at that first payment. Do not use the billing cycle start date: some billing systems rewrite that date forward when a subscription recovers from a failed charge, which pushes the customer into another vintage.

Define a unique key before building the vintage. For companies, use the 8-digit root of the CNPJ (company tax ID) to merge headquarters and branches; for individuals, the full CPF (individual tax ID). 8 and 11 digits never collide. A customer paying for two products counts once, with the revenue of both. Groups with several CNPJs inflated the active customer count until they were consolidated under the main CNPJ.

In month k since entry, the denominator is the vintage's customers who could already have reached k (vintage + k less than or equal to the last closed month). A cell with no eligible customer is NULL, never 0%. A customer is retained at k if the end of their life (today if active; otherwise the earlier of contract end and cancellation date) is after entry + k months.

Flag on each vintage whether the window has closed (for example, ltv_12m_completo true only for vintages 13 months or older). LTV, repeat purchase and LTV/CAC are compared only across vintages with a complete window; customers, spend and CAC are final for any vintage. A sale still inside the refund guarantee (for example, 7 days, or 30 for a product with an extended guarantee) does not enter a mature churn or refund vintage.

Retention, LTV per customer, LTV/CAC and refund rate are ratios. To roll months into a quarter or year, sum numerators and denominators and divide again (summed 12m revenue / summed spend). Monthly is noisy because small vintages swing; for trends, aggregate by recomputing from the sums.

Event tickets, sponsorship, onboarding fees, commissions and one-off sales go to a separate revenue line, outside cohort, LTV, churn and NRR. Annual and quarterly plans enter spread monthly by accrual period. Refunds leave the vintage's revenue by the sale's accrual date; if the tool has no refund date, the refund counts in the month of the sale.

Realized LTV is what the vintage's customers have actually paid to date (cash collected), divided by the vintage's customers. Expected LTV is ARPU times expected lifetime, computed as the sum of the retention curve or as 1 / customer churn. Show both under their own names and, for customers who already left, use mean and median.

The vintage's acquisition cost must cover the same channels as the vintage's customers. If marketplaces are excluded from the first sale, exclude them from revenue too. If the online store has its own media account, it goes into the cost: in a real base, the store's customers were in the denominator and its media was not, and the vintages' CAC was wrong until that was fixed.

Classify those who left by fixed tenure bands, for example: early below 1 month, premature from 1 to 3 months, mature from 3 on. Comparing tenure against expected lifetime looks elegant, but in a real base it labeled almost every churn as premature and the indicator lost its power to discriminate.