AnalyticsSQL and BigQueryAdvancedWrites SQL / queries

Write BigQuery SQL: sessions and conversions by source/medium (GA4 export)

Gets a correct, cost-aware GA4 export query by giving the model your table path, session definition, window and required columns.

For: Performance marketers, Marketing analysts, Growth marketers · Works with: BigQuery / SQL, Google Analytics 4, 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 against my GA4 BigQuery export. Table pattern: my-project.analytics_123456789.events_* (daily tables events_*). Date range: 2026-09-01 to 2026-09-30, using _TABLE_SUFFIX to limit the scan. Goal: compare sessions, engaged sessions and purchases by source/medium. Definitions I want: a session is identified by user_pseudo_id + the ga_session_id event parameter. Source and medium come from collected_traffic_source (manual_source and manual_medium) for the session's first event. A conversion is event_name = 'purchase'. Output columns: source, medium, sessions, engaged_sessions, purchases, purchase_rate. Sort by sessions descending. Requirements: - Use UNNEST on event_params with typed value fields where needed (and tell me which value type you assumed for each parameter). - Add a comment on every step and explain in plain English what each CTE does. - Mention how I can estimate scan cost before running (for example the dry-run bytes estimate) and any way to reduce it. - List all assumptions and any field names you are unsure exist in my schema; do not invent columns. If a field you need is missing or not provided in my export, tell me and propose an alternative. - Include a validation query I can run to check your result, for example comparing session totals with a simple distinct count, and note that results may differ from the GA4 interface.

Customize

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

Replace with your project, dataset and wildcard table.

YYYY-MM-DD.

YYYY-MM-DD.

What the query should answer.

Which field(s) to use; confirm they exist in your export.

Event name.

Columns you need.

Order.

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 BigQuery query with CTEs that unnest event_params, a note on assumed parameter types, a dry-run cost tip, assumptions, and a validation query. (Illustrative.)

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

How to use it

  1. Replace the table path and dates with yours.
  2. Run a dry run first to see bytes scanned.
  3. Run the validation query and compare with a known figure.

Limitations

  • The model cannot see your schema; wrong field names are possible, so check against the export schema.
  • Results rarely match the GA4 interface exactly.

Platform notes

BigQuery / SQL

Official docs read · checked 2026-10-11

GA4's BigQuery export has daily events_YYYYMMDD and intraday tables; event_params is a repeated typed record that usually needs UNNEST, and intraday tables lack some fields (per Google's export documentation).

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.

Google Analytics 4

Official docs read · checked 2026-10-11

GA4's BigQuery export has daily events_YYYYMMDD and intraday tables; event_params is a repeated typed record that usually needs UNNEST, and intraday tables lack some fields (per Google's export documentation).

General notes on Google Analytics 4
  • The BigQuery export has daily events_YYYYMMDD tables and intraday tables; intraday lacks some fields, and late-arriving data can update a daily table for up to 3 days.
  • Exported data and the GA4 interface can differ; the export documentation links to separate comparison articles that we did not read.

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

Related prompts

  • 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)

  • 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

    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)