CAC LTV Payback Dashboard for Subscription Apps

Start with decisions, not charts

List the decisions before you open a BI tool. For a subscription app they usually include raising or cutting spend by channel, pausing a creative family, changing a price or trial length, and approving a hire. Write each one as a sentence with a threshold, such as: cut a campaign when its cohort sits below the target payback line at day 14.

Then map each chart to one decision and one owner. The paid acquisition lead owns the channel and creative views, the product lead owns plan and price views, and the founder or finance lead owns the blended view. A chart with two owners tends to be read by neither.

Apply one test to every chart you add: if this number moved, what would someone do differently on Monday? If the answer is nothing, remove the chart. A short dashboard that triggers decisions beats a long one that gets scrolled past.

The core metrics and their definitions

CAC is spend divided by new paying customers, grouped by cohort week. Count paying customers in the denominator, since free installs earn nothing in a subscription model. Illustrative example: you spend $2,000 in a cohort week and 40 people start paying, so CAC is $2,000 / 40 = $50.

LTV is modeled revenue per payer over a stated horizon, net of store and payment processing fees. Print the horizon on the chart, because 12-month LTV and 24-month LTV are different numbers. Use observed revenue for cohorts old enough to have it, and a retention curve fitted on older cohorts for the rest, with the modeled part marked as a forecast.

Payback is the number of days until cumulative cohort revenue passes cohort spend. Illustrative example: a cohort spent $1,000, and its cumulative revenue is $350 at day 30 and $1,050 at day 75. Between those days revenue grows $1,050 - $350 = $700 over 45 days, about $15.56 per day. The cohort still needs $1,000 - $350 = $650 after day 30, which takes $650 / $15.56, about 42 days. Payback lands near day 72 by straight-line interpolation.

I owned budgeting, forecasting and P&L reporting on CAC, revenue, ROAS and payback. Write these definitions down once and use the same ones in marketing and finance reports.

The cohort payback curve

Draw one line per weekly cohort. The x axis is days since the cohort started, and the y axis is cumulative revenue divided by cohort spend. A line that crosses 1.0 has paid back. Draw cohorts that have not reached full age with a dashed line, so nobody reads a young cohort as a weak one.

Read the slope early. Day 7 and day 14 usually predict payback better than totals, because a cohort's total mostly reflects its age. Compare each new cohort with older cohorts at the same day. If the new cohort sits below them at day 14, expect a later payback and act before it arrives.

Overlay a target payback line: a horizontal line at 1.0 and a marker at your target day. The target depends on your cash position, margin and vertical, so set it with finance rather than copying a figure from another company.

Cuts that reveal problems

A blended number hides the cause of a problem. Cut the same payback view along these axes:

• Channel, campaign and creative family: shows which groups pull a cohort below target and which carry it.
• Country tier and platform: price paid and retention often differ between Tier-1 and lower tiers, and between iOS and Android, so a fine blended CAC can hide a segment that loses money.
• Plan type, annual against monthly: annual plans bring cash forward, monthly plans spread it out.

Illustrative example of the plan effect: an annual plan costs $60 paid on day 0, and a monthly plan costs $10 per month. With no churn, the monthly payer reaches $60 only after the sixth payment, around day 150, while the annual payer paid all $60 on day 0. A cohort that skews annual shows faster payback without telling you anything about retention, so check the plan mix before you judge a channel.

Real revenue vs platform-reported

Show two revenue lines: revenue from your payment records (Stripe, store payout data or your subscription tool) and revenue reported by the ad platform or attribution tool. Add a reconciliation gap column, platform-reported minus payment-based, by channel and week.

Gaps have ordinary causes. Refunds and chargebacks reduce real revenue after a platform has counted the purchase. Renewals can fail and retry, so a cohort looks weaker at day 30 than it ends up. Attribution windows and modeled conversions can also credit a purchase to a channel that the payment record does not link to.

Set a tolerance and an alert, for example a flag on any channel where the gap stays above your threshold for two weeks in a row. Use payment-based ROAS for budget decisions and treat platform numbers as a faster, rougher signal.

Layout for a weekly read

Use three rows. The top row holds spend, new payers, CAC and blended ROAS for the last full week next to the prior week. The middle row holds the cohort payback curves with the target line. The bottom row lists exceptions: campaigns past a CAC limit, cohorts behind target at day 14, and channels whose reconciliation gap is over tolerance.

Use one time zone and one currency on every chart, and state both in the header. Convert at a documented rate per period, since exchange rate moves can look like performance changes.

Build a mobile view for the founder with the top row and the exceptions list, and put the curves one tap away. The weekly read should take a few minutes, and the exceptions row sets the agenda.

Common mistakes

• Mixing install-date and purchase-date cohorts. Pick one cohort key for spend, payers and revenue, and state it on the dashboard.
• Reporting LTV with no horizon. A figure without a horizon cannot be compared with CAC or with last quarter's number.
• Hiding refunds. Show gross and net revenue, and let refunds reduce the cohort they belong to.

Next step: build version one from three tables

Start with three tables: spend by day and campaign, payments by customer and date, and a customer table with acquisition date and source. Join them on cohort week, compute CAC, cumulative revenue and payback, and chart one payback line per cohort. Google Sheets, Tableau, Looker or SQL on a warehouse can all carry this first version.

Review the first result with finance to confirm the definitions, then add the cuts one at a time, starting with channel.