GA4 BigQuery: sessions with last-click source and key events

-- GA4 BigQuery export: sessions per day with last-click session source, and key events per session.
-- Schema: events_YYYYMMDD; session_traffic_source_last_click record (added Jul 2024); "key events" = GA4's renamed conversions (Mar 2024).
DECLARE start_date STRING DEFAULT '20260801';
DECLARE end_date   STRING DEFAULT '20260831';
WITH base AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS day,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    event_name,
    session_traffic_source_last_click.manual_campaign.source AS ss_source,
    session_traffic_source_last_click.manual_campaign.medium AS ss_medium,
    session_traffic_source_last_click.google_ads_campaign.campaign_name AS ads_campaign
  FROM `PROJECT.analytics_PROPERTYID.events_*`
  WHERE _TABLE_SUFFIX BETWEEN start_date AND end_date
),
sessions AS (
  SELECT day, user_pseudo_id, ga_session_id,
    ANY_VALUE(COALESCE(ads_campaign, ss_source)) AS source_or_campaign,
    ANY_VALUE(ss_medium) AS medium,
    COUNTIF(event_name = 'session_start') > 0 AS has_start,
    COUNTIF(event_name IN ('purchase', 'generate_lead')) AS key_events   -- edit: your key events
  FROM base
  WHERE ga_session_id IS NOT NULL
  GROUP BY day, user_pseudo_id, ga_session_id
)
SELECT day, source_or_campaign, medium,
  COUNT(*) AS sessions,
  COUNTIF(has_start) AS sessions_with_start,
  SUM(key_events) AS key_events,
  SAFE_DIVIDE(SUM(key_events), COUNT(*)) AS key_events_per_session
FROM sessions
GROUP BY day, source_or_campaign, medium
ORDER BY day, sessions DESC;