Customer lifetime value dashboard by cohort

A customer lifetime value dashboard should separate contribution already earned from future contribution estimated under stated assumptions. Compare acquisition cohorts at the same age, then place acquisition cost beside each estimate. A large projected value is useful only when the reader can see what was measured and what was assumed.

Start with customer IDs and equal observation windows

Bring four exports: customer acquisition dates, invoice and credit rows, customer service costs, and acquisition spending. Match them using a stable customer ID. Agree how direct costs and shared acquisition spending are assigned before comparing cohorts.

The dashboard below belongs to a US business-services company. All January customers were acquired January 1, 2026, and are measured through June 30; all April customers were acquired April 1 and are measured through September 30. Both have six observed months. An active customer has a paid service agreement still in force at that cutoff.

The LTV calculation guide owns the broader formulas. Here the job is arranging inputs, assumptions, and checks so the dashboard can support a decision. For businesses without contractual renewals, use the ledger-based lifetime value approach to define activity before building the view.

Build the observed section first

Group revenue and costs by acquisition cohort, with each customer's first six months as the filter. In a Transactions sheet, suppose B is cohort, C is completed customer month, and D is net revenue. With the cohort label in A2, sum eligible revenue using:

=SUMIFS(Transactions!$D$2:$D$1000,Transactions!$B$2:$B$1000,A2,Transactions!$C$2:$C$1000,">=1",Transactions!$C$2:$C$1000,"<=6")

Google's SUMIFS documentation describes summing a range subject to multiple criteria. Apply the same cohort and age filters to direct and service costs.

Put the resulting inputs in a Data sheet, columns A:G:

Enter these formulas in H2, I2, J2, and K2 respectively, then fill down to row 3:

=D2-E2-F2
=H2/B2
=G2/B2
=C2/B2

H is observed contribution, I contribution per starting customer, J acquisition cost per customer, and K active share at cutoff. The displayed results follow:

Calculate total per-customer figures from the summed dollars divided by 40 customers. This matters when cohorts differ in size. The LTV-to-CAC guide covers ratio interpretation; keep the underlying dollars visible here.

Put future assumptions beside the estimate

Use an explicit horizon rather than presenting an indefinite lifetime as observed fact. This example adds two annual future contribution payments after the six-month cutoff. Management supplies the following assumptions; they are not inferred from the short history.

Assume departures happen before each annual payment and survivors generate the stated contribution. Enter these formulas in E2, F2, and G2 respectively for future contribution per original acquired customer:

=B2*C2*D2
=B2*C2^2*D2
=NPV(10%,E2:F2)

E and F are the future payments; G is their present value at the cutoff. The assumed discount rate is 10% per year. Google's NPV documentation specifies periodic cash flows and a discount rate for one period, so annual payments require an annual rate.

Keep unrounded inputs in cells; display dollars to two decimals. April's second payment is 214.5825 before rounding. Add G2 to Data!I2 for total contribution value, then subtract Data!J2 for value after acquisition cost.

This combines undiscounted historical contribution with discounted future contribution. Label it that way: it is a finite-horizon dashboard estimate, not a complete discounted lifetime valuation. The distinction between customer profitability and lifetime value prevents an estimate from being mistaken for current earnings.

Check what could change the decision

April's acquisition cost is higher, although its baseline value after acquisition cost is also higher. Change only April's assumed annual retention to 75%. Its future payments become $222.75 and $167.0625 per starting customer; discounted future value becomes $340.57. Total value falls to $760.57, leaving $460.57 after acquisition cost.

That scenario is close to January's $461.49 baseline. It shows why acquisition spending should not expand solely because the dashboard's central estimate looks attractive.

Reconcile observed contribution: $52,000 minus $29,800 minus $6,600 equals $15,600. Check cohort customers against the acquisition register and acquisition spending against the approved expense population. Finance owns these ties; sales or customer success owns the active-account definition and review of retention assumptions.

Where it goes wrong

• Unequal ages. Twelve months of revenue will usually exceed six; show customer age beside each cohort.
• Survivor-only denominators. Dividing by active customers drops lost accounts from acquisition economics. Use starting customers for cohort value.
• Hidden horizons. A short estimate and an indefinite estimate cannot share an unlabeled column.
• Double-counted spending. Deduct acquisition cost once, and keep it out of operating service costs if shown separately.
• Assumptions presented as measurements. Keep future retention and annual contribution visibly separate from actual results.

Build the comparison from your own files in Covirage

Upload a sample of your own customer, financial, and acquisition exports with the assumptions clearly identified. Covirage maps the columns; its tools compute and check the supplied cohort measures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring your own files after setting up your business.

See Covirage for customer profitability for the supporting approach. Covirage is not affiliated with Google.