AnalyticsSQL and BigQueryAdvancedWrites SQL / queries

Write SQL for CAC by channel from your own schema

Produces a transparent CAC-by-channel query once you describe spend and customer tables, with attribution rules stated explicitly rather than assumed.

For: Performance marketers, Marketing analysts, Growth marketers · Works with: BigQuery / SQL, 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

Write a BigQuery Standard SQL query that calculates customer acquisition cost (CAC) by marketing channel by month. My schema (use only these tables and columns; do not invent others): ad_spend(date, channel, spend) customers(customer_id, first_order_date, acquisition_channel) Definitions: a "new customer" is a customer whose first_order_date falls in the period. Channel is assigned using acquisition_channel as stored in customers. Spend to include: media spend only, excluding agency fees and tools. Currency: USD. Requirements: - Show spend, new customers and CAC = spend / new customers per channel and period, with safe handling of zero new customers (return NULL rather than dividing by zero). - Comment each CTE and explain the attribution rule in plain English, including its limits (for example last-touch vs first-touch, and unattributed customers). - Include a row or section for customers with no channel, so totals reconcile. - State all assumptions and list anything in my definitions that is ambiguous; ask me instead of guessing. - Provide a validation query: total new customers and total spend must equal the source totals for the same period. - Note that CAC by channel depends on attribution and can be misleading for channels that assist rather than close; do not treat the result as causal. If a needed column is missing or not provided, say so and propose an alternative.

Customize

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

BigQuery, PostgreSQL, Snowflake, etc.

Day, week or month.

Tables and columns you really have.

Your definition.

How channels are assigned.

What spend includes.

Currency.

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 commented query with spend, new customers and CAC per channel and month, a no-channel bucket, NULL-safe division, a validation query and a list of assumptions. (Illustrative.)

Expected format: SQL in a code block, explanation, assumptions, validation query.

How to use it

  1. Describe only tables and columns that exist.
  2. Run the validation query before trusting the output.
  3. Review attribution rules with your team.

Limitations

  • CAC by channel depends on attribution; do not over-interpret it.
  • Models may invent columns; check against your schema.

Platform notes

BigQuery / SQL

Official docs read · checked 2026-10-11

State your SQL dialect and give the real table and column names; models invent columns that do not exist, so always check against your schema and run a dry run for cost where your warehouse supports it.

General notes on BigQuery / SQL
  • GA4's event_params is a repeated key/value record with typed value fields, so queries typically UNNEST it. Always state the SQL dialect and let the model see your real table and column names.

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

  • AnalyticsAdvancedWrites SQL / queries

    Review and optimise a marketing SQL query

    A code-review prompt for existing SQL: correctness first, then cost and readability, with changes explained and a test for equivalence.

    BigQuery / SQL · Any capable chat model (ChatGPT, Claude, Gemini, others)

  • AnalyticsAdvancedWrites SQL / queries

    Write BigQuery SQL: ordered funnel from GA4 events

    A prompt for a user- or session-level funnel query that respects event order and time windows, with explicit decisions about what counts as a step.

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