BigQuery GoogleSQL Pipe Syntax (|>) & GROUP BY ALL Blueprint

-- Google BigQuery Modern Pipe Syntax (|>) Analytics Blueprint (2025-2026 GA) -- Every prefix up to any '|>' line is a valid standalone query for instant debugging! -- 1. Linear Funnel & Revenue Attribution Pipeline Without Nested CTE Boilerplate FROM `analytics_prod.events_partitioned` -- Step A: Prune partition + cluster columns immediately to minimize scanned bytes |> WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND event_name IN ('page_view', 'checkout_started', 'purchase') |> SELECT user_pseudo_id, event_date, event_name, traffic_source.source AS utm_source, ecommerce.purchase_revenue_usd -- Step B: Derive computed columns inline via EXTEND (immediately referenceable in next pipe!) |> EXTEND COALESCE(NULLIF(LOWER(utm_source), ''), 'direct') AS channel -- Step C: Aggregate per user-day first |> AGGREGATE COUNTIF(event_name = 'checkout_started') AS checkouts, COUNTIF(event_name = 'purchase') AS purchases, SUM(COALESCE(purchase_revenue_usd, 0)) AS user_revenue_usd GROUP BY event_date, channel, user_pseudo_id -- Step D: Roll up channel KPIs and filter top-performing channels in one linear flow |> AGGREGATE COUNT(DISTINCT user_pseudo_id) AS active_users, COUNTIF(purchases > 0) AS converting_users, ROUND(SAFE_DIVIDE(COUNTIF(purchases > 0) * 100.0, COUNT(DISTINCT user_pseudo_id)), 2) AS conversion_pct, ROUND(SUM(user_revenue_usd), 2) AS gross_revenue_usd GROUP BY event_date, channel |> WHERE active_users >= 25 |> ORDER BY event_date DESC, gross_revenue_usd DESC |> LIMIT 100;