Querying GA4 data in plain language and checking it with SQL

A clear question is only the beginning

How many purchases happened yesterday? That can mean purchase events, distinct transaction identifiers, or reconciled orders. Those totals can differ. A useful analysis therefore starts with a short definition of the quantity being counted.

What Conversational Analytics provides

Google announced general availability of Conversational Analytics in BigQuery on 30 June 2026. Natural-language questions can produce queries and analyses. Analysing events from a private GA4 property requires a configured BigQuery export. The public example below does not require an export from a private property. The assistant cannot recreate missing historical events. Availability of the chosen interface and the required Cloud permissions must match the project in use.

A small measurement contract

A suitable first check counts purchase events by event_date in daily tables for past dates between two fixed dates. The agreement also names the exact dataset, property timezone and query time. This definition counts events rather than deduplicated orders. Refunds and revenue are outside the metric.

Keeping the reference simple

The SQL below counts purchase rows in the public GA4 sample dataset. Its historical date range makes it a reusable example. A private export needs its own project, dataset and dates. The query was not executed against BigQuery for this article, so no result value is claimed. The public sample contains deliberately altered data and is useful for practice rather than validating real business figures. This query returns no row for a day without a purchase event. An absent date alone therefore does not establish a data gap. An explicit row with zero for every date requires a calendar table and a LEFT JOIN.

SELECT event_date, COUNT(*) AS purchase_events
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
  AND event_name = 'purchase'
GROUP BY event_date
ORDER BY event_date;

Comparing answers to the same question

The assistant request needs the same tables, date range, event and grouping. Both the generated SQL and the output require inspection. Do the boundaries and filters match? Does an extra UNNEST of items multiply event rows? Counting distinct transaction_id values already answers a different question. A convincing explanation does not replace these checks.

Why the Analytics interface can still differ

The raw export and Analytics reports follow different processing paths. Identity, modelling, attribution and data freshness can affect comparisons. Google can update daily export tables with late events for up to three days after the event date. Recording the query time helps distinguish a changed dataset from a changed calculation.

Checking cost before execution

A dry run validates a query and estimates the data processed without calculating its result. A narrow table range and explicit columns make the workload easier to inspect. Production execution also needs a suitable cost limit and a review of the actual jobs. A LIMIT clause alone does not reliably reduce the amount of data read.

Measurement Summary

The metric is purchase event rows per event_date. The reference and AI query must share tables, dates, filters and data freshness. Checks cover missing days, duplicate output rows and daily differences. This article includes no authenticated BigQuery run or measured accuracy claim.

Tools and further reading

• GA4 BigQuery Query Builder
• BigQuery GA4 Query & UNNEST Builder
• GA4 Advisor & runReport Verifier
• BigQuery Costs for the GA4 Export: Which Tables and Columns a Query Reads and the Free Dry Run

Interactive learning path for Analytics measurement

Sources

• Google Cloud: Conversational Analytics announcement
• BigQuery: Conversational analytics overview
• GA4: BigQuery Export schema
• BigQuery: Query syntax and dry runs
• GA4: public BigQuery sample dataset