---
id: casos/cohort-e-ltv
area: Product
usage: common
effort: medium
grain: 1 row per vintage x month since entry, built on a base of 1 row per customer x month
---

# Cohort and LTV by acquisition vintage

How much each vintage of customers pays and how long it stays, comparing only mature vintages and with the same vintage's CAC alongside.

## Executive summary

**For:** Growth, finance and CS teams that need to know what a new customer is worth and whether recent vintages are better or worse than older ones.

**What it solves**

- Each vintage is compared with the others at the same point in its life, so a new vintage does not look bad just for being new.
- LTV sits next to the CAC of the same vintage, and acquisition spend is judged by both together.
- This month's cancellations get context: did the customer leave early or after staying the expected time?

**Why it matters:** Total revenue hides whether new customers stay less or pay less than older ones. A recent vintage always looks worse because it has not had time yet, and an old vintage looks great because only the ones who stayed are left. Without this yardstick, the acquisition budget is decided by CAC alone, which almost always goes up.

**Business questions it answers**

- Which vintage of the last two years has the best 12-month LTV, comparing only mature vintages?
- What percentage of the customers who joined in January are still active 6 months later?
- Is this year's vintages' LTV/CAC better or worse than last year's?
- Did the customers who cancelled this month leave early or were they already mature?
- Which source channel brings customers who stay longer and pay more?

**What you need**

- Billing, ERP or sales platform connected to Nekt, with payment history
- A unique customer key (CNPJ root, CPF or account id)
- Monthly media spend, if you want CAC per vintage

**What you get**

- A customer x month table with vintage, status and recurring revenue
- A vintage x month matrix with customer retention and cumulative revenue
- Realized LTV, expected LTV and LTV/CAC per vintage, with a maturity flag
- An AI agent that answers about vintages without comparing immature ones

## Detailed

### The problem

The typical cohort sets the vintage by signup date, counts the same customer once per branch or per product, and shows 0% retention in months the vintage has not lived yet. Every new vintage looks worse than the old ones, and average LTV mixes customers with 2 months of life and customers with 3 years. Without the same vintage's CAC alongside, the conversation becomes just "CAC went up".

### Business rules

1. **The vintage is the month of the first confirmed payment.** 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. (Avoid: Vintage by signup date or billing cycle date.)
2. **The customer is the entity, not the billing line.** 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. (Avoid: Counting CNPJ plus product, or each branch, as a customer.)
3. **Only those who had time enter the denominator.** 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. (Avoid: Dividing by the whole vintage and showing 0% in months not yet reached.)
4. **Compare mature vintages only.** 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. (Avoid: Putting last month's vintage on the same 12m LTV chart as the old ones.)
5. **Ratios are recomputed, never summed.** 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. (Avoid: Averaging the monthly rates.)
6. **Only the product's recurring revenue counts.** 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. (Avoid: Summing all of a customer's billing as LTV.)
7. **Realized LTV and expected LTV are different numbers.** 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. (Avoid: Calling LTV sometimes the cash received, sometimes the projection.)
8. **CAC and vintage in the same universe.** 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. (Avoid: Customers from every channel and spend from a single media account.)
9. **Early churn by a fixed tenure scale.** 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. (Avoid: Premature is anyone who left before the average lifetime.)

### Which date sets the vintage in each model

Always the first time the customer really paid, in the source that records the payment.

- **Subscription (SaaS, club, app)** → First paid charge, never the cycle start (`charge succeeded, invoice.amount_paid > 0`)
- **Digital course with guarantee** → Sale accrual date, mature only after the guarantee
- **Education and tuition** → First paid tuition of the enrollment
- **Service contract (agency, consulting)** → Start of the customer's first contract
- **B2B retail and distribution** → Entity's first invoiced sale (CNPJ root)
- **Lending and cash advance** → First disbursement, with months on book (MoB)

### Definitions

| Metric | Definition |
|---|---|
| Vintage | month of the entity's first confirmed payment, frozen once written |
| Month since entry (k) | whole months between the vintage and the reference month; k = 0 is the entry month itself |
| Eligible at k | vintage customers with vintage + k less than or equal to the last closed month |
| Customer retention at k | vintage customers still active at k / eligible at k; NULL when there is no eligible customer |
| Cumulative revenue at k | sum of net recurring revenue received from the vintage's customers from month 0 to month k / vintage customers |
| 12m LTV | revenue received within 365 days of the first sale / vintage customers; valid only for vintages 13 months or older |
| Expected LTV | month's ARPU x expected lifetime in months (sum of the retention curve, or 1 / monthly customer churn) |
| Vintage CAC | media spend in the vintage month / new customers in the vintage, with the same channel scope |
| 12m LTV/CAC | sum of the vintage's 12m revenue / sum of the vintage's media spend |

### Pitfalls

- **Billing rewrote the activation date.** The cycle start date moved forward when a subscription recovered from a failed charge. Activations already earned were undone and customers migrated to later vintages. How to avoid: Anchor the vintage on the first successful payment transaction and write the date only once.
- **New vintage with 0% retention.** Recent vintages showed 0% in months they had not reached yet, and the chart suggested a drop that did not exist. How to avoid: Eligible denominator and a NULL cell when nobody has had time to get there.
- **A premature scale with no power to discriminate.** Comparing the tenure of those who left with the expected lifetime (about 13 months) marked almost every churn as premature. The number did not change from one month to the next. How to avoid: Fixed tenure bands, with expected lifetime only as a reference.
- **Events inflated LTV.** Event tickets and sponsorship came in the same billing category as the monthly fee and were counted as recurring revenue. How to avoid: Reclassify that revenue into a separate line, outside cohort, LTV and churn.
- **CAC with media missing.** The online store's customers counted in the vintage, but the store's media account was not in the spend. The vintages' CAC was lower than reality for years. How to avoid: Check that every media account for the vintage's channels is in the spend before comparing.
- **Mass cancellation erased the end.** When an installment contract was cancelled early, the future installments never came to exist in the ERP, not even as cancelled. The last revenue month was wrong. How to avoid: Use the cancelled installments to find the last revenue month and store each customer's end of life.

### Acceptance test

- [ ] Every entity with at least one confirmed payment is in exactly one vintage, and the sum of customers across vintages equals the total of those entities.
- [ ] At k = 0, customer retention is 100% in every vintage.
- [ ] No cell with k greater than the closed months since the vintage has a value: all are NULL, none is 0%.
- [ ] Summing the revenue of all vintages in a calendar month matches finance's recurring revenue for that month, within 1%.
- [ ] No 12m LTV or LTV/CAC comparison includes a vintage whose complete-window flag is false.

### Context document

```text
Cohort and LTV by vintage
- The vintage is the month of the entity's first confirmed payment. Do not use signup, contract or billing cycle start dates.
- The customer is the entity: CNPJ root (8 digits) for companies, full CPF for individuals. A customer with several products counts once.
- In month k since entry, the denominator is only the customers who have had time to reach k. With no eligible customer, the cell is NULL, never 0%.
- Compare LTV, repeat purchase and LTV/CAC only across vintages with a complete window. Say when a vintage is still immature.
- Retention, LTV per customer and LTV/CAC are ratios: to combine periods, sum the parts and divide again. Never average rates.
- Only the product's recurring revenue counts. Events, sponsorship, onboarding fees and one-off sales stay out.
- Realized LTV is what the vintage has already paid. Expected LTV is ARPU x expected lifetime. Always say which one you are using.
- Vintage CAC uses the same channel scope as the customers and the revenue.
- Early churn follows fixed tenure bands: early below 1 month, premature from 1 to 3, mature from 3 on.
Client exceptions:
- <client>: refund guarantee of <N> days on product <product>; sales within the guarantee do not enter a mature vintage.
- <client>: pipeline <name> excluded from the cohort because it is not a recurring product.
```

### Variations

- **Repeat purchase in retail:** Without a contract, retained at k means bought again within the window; track 12-month repeat rate and orders per vintage.
- **Lending vintage:** Vintage by first disbursement, with revenue, risk and persistence by months on book (MoB).
- **Funnel cohort:** Of the people who entered in a month, how many bought within 7, 30, 60 and 90 days, with the complete window flagged.