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

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 BigQuery Standard SQL for an ordered funnel using my GA4 export at my-project.analytics_123456789.events_*, from 2026-09-01 to 2026-09-30 (limit the scan with _TABLE_SUFFIX). Funnel steps in order: session_start -> view_item -> add_to_cart -> begin_checkout -> purchase. Counting unit: users identified by user_pseudo_id. A step counts only if it happens after the previous step, within 24 hours of the previous step. Users may repeat steps; count each user once per step. Output: for each step, users reaching it, step-to-step conversion rate, and overall conversion from step 1; ordered by step. Requirements: explain the logic in plain language and comment the SQL; state the assumptions you make about event names and parameters; do not invent column names, tell me which fields you assume exist, and say so if any needed field is missing or not provided. Explain how late-arriving data and intraday tables could affect results. Provide a validation query (for example, confirm step 1 users equal a simple distinct count of the first event) and tell me to check results against a separate source before relying on them. Do not present funnel drop-off as caused by any particular factor; list only what the data can show.

Customize

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

Your GA4 export tables.

YYYY-MM-DD.

YYYY-MM-DD.

Event names in order.

Users or sessions.

Maximum gap between steps.

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 funnel query with per-step users and conversion rates, assumptions about event names, notes on late data, and a validation query. (Illustrative.)

Expected format: SQL, explanation, assumptions, validation query.

How to use it

  1. Confirm event names in your export before running.
  2. Dry-run to check cost.
  3. Compare step 1 to a known number.

Limitations

  • Funnels from raw events depend on how events are named and fired.
  • Results will not match the GA4 Funnel exploration 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-007. We will correct or remove it.

Related prompts

  • AnalyticsIntermediateAnalyses your data

    Conversion funnel drop-off analysis with hypotheses and tests

    Finds the biggest relative and absolute drop-offs in a funnel table and turns them into prioritised, testable hypotheses.

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

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