UTM Normalization - SourceMedium

UTM fields are used across SourceMedium for segmentation, channel mapping, and conversion analysis. In many places, SourceMedium already normalizes these fields (lowercase, trimmed, and cleaned).

If you’re joining or filtering on user-supplied strings (e.g., config sheet inputs, custom uploads), normalize on the fly: LOWER(TRIM(value)).

Canonical UTM fields

You’ll commonly see:

sm_utm_source
sm_utm_medium
sm_utm_campaign
sm_utm_content
sm_utm_term
sm_utm_source_medium (combined convenience field)

These fields appear in order tables and funnel tables, depending on data availability and attribution source.

Recommended query pattern (categorical-safe)

SELECT
  COALESCE(NULLIF(LOWER(TRIM(sm_utm_source)), ''), '(unknown)') AS sm_utm_source,
  COALESCE(NULLIF(LOWER(TRIM(sm_utm_medium)), ''), '(unknown)') AS sm_utm_medium,
  COUNT(*) AS order_count,
  SUM(order_net_revenue) AS net_revenue
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND order_processed_at_local_datetime >= DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 30 DAY)
GROUP BY 1, 2
ORDER BY net_revenue DESC
LIMIT 200;