AnalyticsReporting and spreadsheetsIntermediateBuilds 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.

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 an analytics lead. Design a one-page marketing efficiency dashboard specification for a subscription meal-kit company, used by the marketing and finance leads to make monthly budget allocation and channel mix. Data sources available: Shopify orders, Meta and Google Ads spend exports, GA4. Refresh frequency: daily. Deliver: 1. A metric dictionary: for each metric (include marketing efficiency ratio = total revenue / total marketing spend, blended CAC, and the other metrics suited to my context), give its exact definition and formula, source tables or reports, and known caveats. 2. A layout: 6-8 visuals, each with the question it answers and the decision it informs; specify chart type, dimensions and comparisons, and avoid decoration. 3. Data-quality checks to display (freshness, completeness, reconciliation to finance totals). 4. Interpretation guardrails: what each metric can and cannot tell you, and a note that blended metrics hide channel differences and are not causal. 5. A list of open questions and missing data. Rules: do not invent benchmark targets or data. If a metric needs data I did not list, mark it as missing. State assumptions, and check that every formula uses only sources I listed.

Customize

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

What you sell.

Who uses it.

What the dashboard informs.

What you have.

How often it updates.

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.

A metric dictionary with formulas and caveats, 6-8 specified visuals each tied to a decision, data-quality checks, interpretation guardrails and open questions. (Illustrative.)

Expected format: Metric dictionary, layout spec, quality checks, guardrails, open questions.

How to use it

  1. Share the spec with stakeholders before building.
  2. Build a first version in your BI tool or a spreadsheet.
  3. Reconcile totals with finance.

Limitations

  • A spec is not a dashboard; it needs building and testing.
  • Blended efficiency can hide channel-level problems.

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

Related prompts

  • 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

  • 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

    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

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

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