custom.sql

  -- Some sample set custom dimensions return null values
SELECT
  -- Custom Dimension XX (User)
  (
  SELECT
    value
  FROM
    UNNEST(session.customDimensions)
  WHERE
    index = 3
  GROUP BY
    1) AS Custom_Dimension_XX_User,
  -- Custom Dimension XX (Session)
  (
  SELECT
    value
  FROM
    UNNEST(session.customDimensions)
  WHERE
    index = 4
  GROUP BY
    1) AS Custom_Dimension_XX_Session,
  -- Custom Dimension XX (Hit)
  (
  SELECT
    value
  FROM
    UNNEST(hits.customDimensions)
  WHERE
    index = 2
  GROUP BY
    1) AS Custom_Dimension_XX_Hit,
  -- Custom Dimension XX (Product)
  (
  SELECT
    value
  FROM
    UNNEST(product.customDimensions)
  WHERE
    index = 10
  GROUP BY
    1) AS Custom_Dimension_XX_Product,
  -- Custom Metric XX (Hit)
  SUM((
    SELECT
      value
    FROM
      UNNEST(hits.customMetrics)
    WHERE
      index = 1)) AS Custom_Metric_XX_Hit,
  -- Custom Metric XX (Product)
  SUM((
    SELECT
      value
    FROM
      UNNEST(product.customMetrics)
    WHERE
      index = 2)) AS Custom_Metric_XX_Product
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS session,
  UNNEST(hits) AS hits,
  UNNEST(product) AS product
WHERE
  _table_suffix BETWEEN '20160101'
  AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  AND totals.visits = 1
GROUP BY
  1,
  2,
  3,
  4
ORDER BY
  2 DESC
LIMIT
  10