AnalyticsCAC, LTV, ROAS and marginIntermediateAnalyses your data

LTV model sanity check from cohort data

Stress-tests an LTV figure against cohort retention data, exposing hidden assumptions about lifespan, margin and discounting.

For: Performance marketers, Marketing analysts, Growth marketers · Works with: Any capable chat model (ChatGPT, Claude, Gemini, others), Google Sheets / Excel

Public beta. This prompt was drafted with AI assistance and checked by automated rules, but it has not been reviewed or tested by a person yet. Treat it as a starting point and check the output. It is hidden from search engines while in beta. Use the “Was this prompt useful?” box to tell us what works.

Your prompt

Act as a skeptical analyst. Review the LTV calculation below using ONLY the data I provide. LTV method I used: average monthly margin divided by monthly churn. Result: 480 USD. Cohort data (cohort | month 0 | month 1 | month 2 | ... customers or revenue retained): Cohort | M0 | M1 | M2 | M3 Jan | 1000 | 620 | 520 | 470 Feb | 900 | 540 | 450 | 400 Mar | 1100 | 700 | 590 | n/a Margin per customer-month (if known): 20. Observed horizon: 3 months. Tasks: 1. State the assumptions behind my method (for example constant churn, no discounting, revenue vs margin) and test them against the cohort data. 2. Compute retention by month for each cohort from the raw data and check whether churn is roughly constant, declining or changing by cohort. 3. Recompute a conservative LTV using only the observed horizon, with no extrapolation, and separately show an extrapolated version only if you state the extrapolation assumption and how uncertain it is. 4. Compare my figure with both; explain the gap and which assumptions drive it. 5. List the biggest risks to using this LTV for decisions (for example young cohorts, discounting, margin changes). 6. List data missing from my analysis. Rules: do not invent churn rates, margins or benchmarks. If data is missing or not provided, say so. Verify and recompute every figure. Describe differences between cohorts as observations (correlation, not causation); do not claim why they differ.

Customize

The fields start with example values so you can see how the prompt works. Everything stays in your browser.

How you computed it.

Your figure.

Currency.

Customers active by month. The default is small fictional sample data so the preview works; replace it with your own.

If known.

How long you have data.

Example of what to expect

Illustrative only. It describes the kind of result this prompt aims for; real output varies by tool, model and run.

Assumptions tested against retention curves, a conservative observed-horizon LTV, a separate clearly-uncertain extrapolation, an explanation of the gap to your figure and risks. (Illustrative.)

Expected format: Assumption tests, retention table, conservative vs extrapolated LTV, risks.

How to use it

  1. Use cohorts, not just averages.
  2. Prefer conservative LTV for decisions.
  3. Refresh as cohorts mature.

Limitations

  • LTV beyond the observed horizon is a forecast, not a fact.
  • Young cohorts understate long-term behaviour.

Platform notes

Any capable chat model (ChatGPT, Claude, Gemini, others)

Official docs read · checked 2026-10-11

Works in any capable chat model. Paste a small anonymised sample and recompute the key numbers yourself: chat models can misread columns or miscalculate.

General notes on Any capable chat model (ChatGPT, Claude, Gemini, others)
  • These prompts are plain text and work in any chat assistant. For analytics prompts, paste a small, anonymised export; a chat model can misread columns or miscalculate, so recompute key numbers yourself.

Google Sheets / Excel

Not verified: third-party guidance only · checked 2026-10-11

Formula syntax is shared across Google Sheets and Excel for the functions used here, but argument separators and function availability depend on your locale and version; test on a small range first.

General notes on Google Sheets / Excel
  • Formulas are written for common functions in both products. Locale settings (decimal and argument separators) can change what you must type.

Source, license and attribution

Origin
Original by MarketerTools
Publisher
MarketerTools
License
Original work by MarketerTools, free to copy and use. Informed by the linked documentation; no third-party text is reproduced.
Platform assumptions checked
2026-10-11

Documentation and references behind this prompt

These informed the structure and the platform notes. A reference is not a license, and no third-party prompt text is copied here.

Spotted an attribution error, or are you a source owner with a request? Use the chat button at the bottom right and quote prompt ID ana-027. We will correct or remove it.

Related prompts

  • AnalyticsIntermediateAnalyses your data

    CAC and payback calculation with validation

    Calculates CAC and payback from your inputs, shows every formula, and checks that definitions are consistent before the number is quoted.

    Any capable chat model (ChatGPT, Claude, Gemini, others) · Google Sheets / Excel

  • AnalyticsIntermediateAnalyses your data

    Cohort retention analysis and interpretation

    Turns a cohort table into retention curves and a careful read of what changed between cohorts, with hypotheses clearly separated from findings.

    Any capable chat model (ChatGPT, Claude, Gemini, others) · Google Analytics 4 · Google Sheets / Excel

  • AnalyticsIntermediateAnalyses your data

    App retention diagnostics: D1, D7 and D30 by acquisition source

    Compares day-N retention across acquisition sources with maturity and sample-size checks, so weak channels are flagged only when the data supports it.

    Any capable chat model (ChatGPT, Claude, Gemini, others) · Google Sheets / Excel

  • AnalyticsIntermediateAnalyses your data

    ROAS vs contribution margin: break-even and target ROAS

    Translates ROAS into profit terms using margin and variable costs, so a campaign is judged against break-even rather than a generic ROAS target.

    Any capable chat model (ChatGPT, Claude, Gemini, others) · Google Sheets / Excel