AnalyticsReporting and spreadsheetsIntermediateWrites formulas

Spreadsheet formulas for marketing KPIs (Sheets and Excel)

Gets working formulas for KPIs from your actual column layout, with error handling, a test case and explanations you can audit.

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

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 spreadsheet expert for Google Sheets. Write formulas for these KPIs: CTR, CPC, CPA, ROAS, month-over-month change in spend. My layout (sheet, columns and rows; use exactly these references): Sheet 'Ads': A=Date, B=Campaign, C=Spend, D=Impressions, E=Clicks, F=Conversions, G=Revenue; data in rows 2 to 500 Locale or separator notes: comma argument separators, dot decimals. Requirements: - Give one formula per KPI with the exact cell references from my layout, and explain each in one sentence. - Handle errors sensibly (for example divide by zero returns blank), and say how. - Prefer simple, auditable formulas over clever ones; avoid volatile functions unless needed, and tell me if you used one. - Provide a tiny worked test case with made-up numbers clearly labelled as test data, and the result each formula should return, so I can check them. - List assumptions (for example how dates are stored, whether currency is in text) and any column in my layout that is ambiguous; ask rather than guess. - If a KPI cannot be calculated from the columns I gave you, say that it is missing the required input. Do not invent column names, and recompute the test results by hand to verify they are right.

Customize

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

Google Sheets or Excel.

What to calculate.

Describe your columns and rows.

Separators matter.

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.

One formula per KPI with cell references (for example CTR = IFERROR(E2/D2,"")), a one-line explanation, a test case with expected results and assumptions. (Illustrative.)

Expected format: Formula list, explanations, test case, assumptions.

How to use it

  1. Describe your real layout.
  2. Test with the provided test case.
  3. Lock ranges where needed.

Limitations

  • Locale and version differences can break formulas; test on a small range.
  • Large datasets may need pivot tables or SQL instead.

Platform notes

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.

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.

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-024. We will correct or remove it.

Related prompts

  • AnalyticsIntermediateBuilds a checklist

    Blended efficiency (MER) and dashboard specification

    Designs a small, honest dashboard spec for blended efficiency metrics, with metric definitions, data sources and the questions each chart answers.

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

  • AnalyticsIntermediateWrites a report

    Executive marketing report narrative with variance analysis

    Writes a concise executive narrative from your KPI table: what moved, by how much, plausible reasons labelled as hypotheses, and decisions needed.

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