AnalyticsCAC, LTV, ROAS and marginIntermediateAnalyses 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.

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 performance-marketing finance analyst. Using ONLY the inputs below, work out break-even ROAS and judge the campaign data against it. Inputs: average order value 80 USD; product cost (COGS) 28; shipping and fulfilment per order 7; payment fees 2.5; returns or refunds assumption 8% of revenue refunded; other variable costs per order 1.5. Campaign data (campaign | spend | revenue | orders): Campaign | Spend | Revenue | Orders Prospecting | 5000 | 16000 | 200 Retargeting | 2000 | 12000 | 150 Tasks: 1. List assumptions and flag ambiguities (is revenue before or after returns, are shipping fees charged to customers, are taxes included). 2. Compute contribution margin per order and as a percentage of AOV, showing every step. 3. Compute break-even ROAS = 1 / contribution margin % (state the formula and the assumption that margin is constant), then target ROAS for a desired profit margin of 20% on ad spend, showing the math. 4. Compare each campaign's ROAS with break-even and show the implied profit or loss using the data only. 5. Recalculate and verify the results; point out any sensitivity (for example how break-even ROAS shifts if returns change). 6. List what the analysis excludes (fixed costs, repeat purchase value, assisted conversions) and the data needed to include it. Rules: do not invent costs, margins or benchmarks. If any input is missing or not provided, stop and ask. Treat ROAS as an observed ratio, not proof that ads caused the revenue.

Customize

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

Average order value.

Currency.

COGS per order.

Shipping and handling cost per order.

Payment processing per order.

Your assumption, stated explicitly.

Any other per-order cost.

Profit target; or write 'none'.

Paste your table. The default is small fictional sample data so the preview works; replace it with your own.

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.

Contribution margin per order and in percent with the arithmetic, break-even and target ROAS with formulas, each campaign's implied profit or loss, sensitivity to returns and the excluded items. (Illustrative.)

Expected format: Assumptions, margin and ROAS calculations, campaign comparison, sensitivity, exclusions.

How to use it

  1. Get cost inputs from finance.
  2. Recompute in a spreadsheet.
  3. Compare with the ROAS calculator.

Limitations

  • Break-even ROAS ignores repeat purchases and assisted conversions.
  • Margins vary by product mix; averages can mislead.

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-028. 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

    Google Ads campaign diagnostic against CPA or ROAS targets

    Compares campaigns against your target, separates volume, efficiency and conversion-rate drivers, and highlights what the data cannot explain.

    Google Ads · Any capable chat model (ChatGPT, Claude, Gemini, others)

  • AnalyticsIntermediateAnalyses your data

    Budget reallocation scenarios with saturation caveats

    Builds transparent what-if scenarios for moving budget between channels, with simple assumptions about diminishing returns clearly labelled.

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

  • AnalyticsIntermediateAnalyses 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.

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