From tracking code to boardroom dashboard — analytics enterprises can trust.

GA4 custom event — instrumented beyond the defaults

Registered as a custom dimension so zero-result searches become queryable — the GA4 UI alone can't answer "what do users search for that we don't have?"

event_name:            search_results_view
event_params:
  search_term:         "gluten free menu"
  search_results_count: 0
  page_type:           "search"
  ga_session_id:       1737455512

Staging — flatten the raw export (production code)

The daily events_* export nests everything in arrays and has no session table. Staging unnests it once — and the incremental filter only scans a 3-day window instead of full history, which cut daily processing cost dramatically.

-- definitions/staging/stg_ga4_events.sqlx (sanitized)
SELECT
  PARSE_DATE('%Y%m%d', event_date)  AS event_date,
  TIMESTAMP_MICROS(event_timestamp) AS event_timestamp,
  user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(event_params)
   WHERE key = 'ga_session_id')      AS session_id,
  COALESCE(
    (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'),
    ecommerce.purchase_revenue
  ) AS event_value
FROM `client_project.analytics_XXXXXX.events_*`
WHERE ${when(incremental(),
  `_TABLE_SUFFIX BETWEEN
     FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
     AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())`)}
-- ⭐ was missing: pipeline scanned full history daily before this fix

Deduplication — a deterministic key, enforced by the build (production code)

Every event gets an MD5 identity key; the incremental merge on uniqueKey makes duplicates structurally impossible — replayed exports and double-fired tags can't create them.

config {
  type: "incremental",
  uniqueKey: ["event_key"],
  bigquery: {
    partitionBy: "event_date",
    clusterBy: ["event_name", "user_pseudo_id"]
  }
}

-- deterministic event identity: same event can never land twice
TO_HEX(MD5(CONCAT(
  CAST(event_date AS STRING),
  IFNULL(user_pseudo_id, ''),
  IFNULL(user_id, ''),
  CAST(IFNULL(custom_timestamp, 0) AS STRING),
  CAST(event_timestamp AS STRING)
))) AS event_key

Dataform sessionization — the session table GA4 never gives you (production code)

First-touch attribution resolved inside the session, geo edge cases handled so BI visuals never show blank rows, partitioned and clustered for cheap queries.

-- definitions/intermediate/int_sessions.sqlx (sanitized)
config {
  type: "incremental", uniqueKey: ["session_key"],
  bigquery: { partitionBy: "session_date",
              clusterBy: ["channel_group", "device_category"] }
}

SELECT
  ANY_VALUE(session_key)  AS session_key,
  MIN(event_timestamp)    AS session_start_timestamp,
  -- first-touch attribution inside the session
  ARRAY_AGG(session_source ORDER BY event_timestamp LIMIT 1)[OFFSET(0)] AS session_source,
  ARRAY_AGG(channel_group  ORDER BY event_timestamp LIMIT 1)[OFFSET(0)] AS channel_group,
  -- geo fix: unresolvable IPs (VPN, IPv6) → 'Unknown', never a blank row
  COALESCE(NULLIF(TRIM(
    ARRAY_AGG(region ORDER BY event_timestamp LIMIT 1)[OFFSET(0)]
  ), ''), 'Unknown') AS region,
  COUNTIF(event_name = 'page_view') AS page_views,
  MAX(session_engaged_int) AS engaged_session_int
FROM ${ref("stg_ga4_events")}
GROUP BY user_pseudo_id, session_id

Power BI on tested marts — the number everyone finally agrees on

Semantic model reads only the marts. Every metric has one definition, in code, upstream of the BI tool. This is a production dashboard from the pipeline (client identity removed).