AnalyticsSQL and BigQueryAdvancedWrites 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.

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

Act as a senior analytics engineer. Review and improve this BigQuery Standard SQL query. Query: SELECT channel, COUNT(*) AS orders, SUM(revenue) AS revenue FROM orders o JOIN sessions s ON o.user_id = s.user_id WHERE o.order_date >= '2026-09-01' GROUP BY channel Purpose: revenue and orders by acquisition channel for September. Table sizes and partitioning/clustering, if known: orders partitioned by order_date; sessions is large and unpartitioned. Do this in order: 1. Explain what the query currently does in plain language and list your assumptions about the schema and data. 2. Correctness issues first: wrong joins or join fan-out, double counting, filters in the wrong place, NULL handling, time-zone or date-boundary mistakes, mixed grains, divisions by zero. 3. Cost and performance issues: unnecessary scans, missing partition filters, repeated subqueries, SELECT *, avoidable cross joins. Suggest changes, and say which suggestions depend on warehouse features I should confirm. 4. Readability: CTE structure, naming and comments. 5. Provide the revised query and a short change log explaining each change and its risk. 6. Provide a test I can run to confirm the revised query returns the same results (for example row counts and sums compared with the original on a bounded date range). Rules: do not invent table or column names; if the query references something that is unclear, list it as a question. If information about data is missing or not provided, do not guess it. Verify that the revised query still answers the stated purpose.

Customize

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

Dialect.

Paste the SQL to review.

What the query should answer.

Sizes and partitioning if known.

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 plain-language explanation, correctness issues (the join on user_id can multiply rows), cost suggestions, a revised query with a change log and an equivalence test. (Illustrative.)

Expected format: Explanation, issues by priority, revised SQL, change log, equivalence test.

How to use it

  1. Run the equivalence test on a bounded date range.
  2. Check query cost with a dry run.
  3. Keep the original until the new one is validated.

Limitations

  • The model cannot see your data; suggested fixes may not apply.
  • Performance advice depends on warehouse specifics.

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

Related prompts

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

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

  • AnalyticsAdvancedWrites SQL / queries

    Google Ads Query Language (GAQL) query builder

    Gets a GAQL query for a specific report, with explicit instructions to confirm field names, unit conventions and segment compatibility.

    Google Ads · 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)