基于指定BigQuery GA导出Schema，如何提取含自定义维度的Hits数据？

Got it, let's work through this query together—dealing with the nested arrays in Google Analytics' BigQuery export can be confusing at first, but once you get the hang of UNNEST it's straightforward.

First, a quick reminder about the GA export schema: both session-level customDimensions and hit-level customDimensions are nested arrays, so you can't just join them directly like flat tables. You need to use UNNEST to "unpack" these arrays into rows, then filter for the specific index (100 in your case) you care about.

Here's a corrected, working SQL query that pulls the session info, hit data, and the custom dimension value (index 100) from both session and hit levels:

SELECT
  s.visitId,
  s.visitStartTime,
  -- Session-level custom dimension (index 100) - use IFNULL to handle missing values
  IFNULL(session_cd.value, 'No Value') AS session_custom_dim_100,
  -- Replace these with the actual hit fields you need
  h.hitNumber,
  h.page.pagePath,
  h.eventInfo.eventCategory,
  h.eventInfo.eventAction,
  -- Hit-level custom dimension (index 100)
  IFNULL(hit_cd.value, 'No Value') AS hit_custom_dim_100
FROM
  `your-project.your-dataset.session_data` s
-- Unpack the hits array to get individual hit records
LEFT JOIN UNNEST(s.hits) AS h
-- Unpack session-level custom dimensions and filter for index 100
LEFT JOIN UNNEST(s.customDimensions) AS session_cd
  ON session_cd.index = 100
-- Unpack hit-level custom dimensions and filter for index 100
LEFT JOIN UNNEST(h.customDimensions) AS hit_cd
  ON hit_cd.index = 100
-- Optional: Add date filters if using GA's standard ga_sessions_* table naming
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'

Key Explanations: #

• UNNEST for nested arrays: Both hits and customDimensions are repeated fields (arrays). UNNEST converts each element in the array into a separate row, so you can access individual values like value or index.
• LEFT JOIN instead of INNER JOIN: This ensures you don't lose sessions or hits that don't have the custom dimension (index 100) set. Use IFNULL to replace NULL values with a readable placeholder like 'No Value'.
• Table naming note: If you're using the official GA export, your table is likely named ga_sessions_YYYYMMDD (with daily partitions). Replace session_data with ga_sessions_* and use _TABLE_SUFFIX to filter by date, as shown in the WHERE clause.

Handling Multiple Custom Dimensions (Avoiding Row Bloat) #

If you need to pull more than one custom dimension (e.g., index 100 and 101), using multiple UNNEST calls can cause unintended row duplication (cartesian product). Instead, use conditional aggregation to keep all dimensions in a single row:

SELECT
  s.visitId,
  s.visitStartTime,
  -- Get multiple session-level custom dimensions without row duplication
  MAX(CASE WHEN session_cd.index = 100 THEN session_cd.value END) AS session_cd_100,
  MAX(CASE WHEN session_cd.index = 101 THEN session_cd.value END) AS session_cd_101,
  h.hitNumber,
  h.page.pagePath,
  -- Get multiple hit-level custom dimensions
  MAX(CASE WHEN hit_cd.index = 100 THEN hit_cd.value END) AS hit_cd_100,
  MAX(CASE WHEN hit_cd.index = 102 THEN hit_cd.value END) AS hit_cd_102
FROM
  `your-project.your-dataset.ga_sessions_*` s
LEFT JOIN UNNEST(s.hits) AS h
LEFT JOIN UNNEST(s.customDimensions) AS session_cd
LEFT JOIN UNNEST(h.customDimensions) AS hit_cd
GROUP BY
  s.visitId, s.visitStartTime, h.hitNumber, h.page.pagePath
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'

This way, you avoid extra rows and keep all relevant dimensions aligned with their session/hit.

内容的提问来源于stack exchange，提问作者Oleg