在BigQuery中展开customDimensions时GA会话数据丢失问题排查

Hey there! Let's break down why your unique session count dropped from 6,696 to 6,606 when adding user-level customDimensions to your Standard SQL query. Here are the most common culprits and fixes:

1. You're Using Cross Join Instead of Left Join for Unnesting User Dimensions #

The biggest issue here is likely how you're handling the user.customDimensions array. When you use a comma (shorthand for CROSS JOIN UNNEST), any sessions where the user has no custom dimensions (or no value for the specific index you're targeting) get dropped entirely.

Example of the Problematic Query: #

SELECT
  COUNT(DISTINCT CONCAT(fullVisitorId, '-', visitId)) AS unique_sessions
FROM
  `project.dataset.ga_sessions_*`,
  UNNEST(user.customDimensions) AS cd
WHERE
  cd.index = 4 -- Your target custom dimension index
  AND _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'

This query excludes all sessions where the user doesn't have a value for index 4, which directly causes the lower count.

Fix: Use LEFT JOIN UNNEST with a Filter on the Join #

Instead, use a LEFT JOIN UNNEST to preserve all sessions—even those without the target custom dimension. Filter the dimension index directly in the join condition to avoid irrelevant NULLs from other indices:

SELECT
  COUNT(DISTINCT CONCAT(fullVisitorId, '-', visitId)) AS unique_sessions
FROM
  `project.dataset.ga_sessions_*`
LEFT JOIN
  UNNEST(user.customDimensions) AS cd
ON
  cd.index = 4 -- Filter dimension index here
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'

This way, sessions without the custom dimension will still be included (with cd.value as NULL), so your unique session count matches the GA interface.

2. You're Accidentally Filtering Out NULL Values #

If you added a WHERE clause that excludes rows where cd.value IS NULL, that would also drop sessions without the user-level dimension. Double-check your query for conditions like:

WHERE cd.value IS NOT NULL -- This removes sessions without the dimension

Remove that condition or adjust it to include NULLs if you want to keep all sessions in your count.

3. Duplicate Rows Skewing Counts (Less Likely but Worth Checking) #

If you're grouping by additional fields from user.customDimensions, a cross join might split one session into multiple rows (e.g., if a user has multiple custom dimensions with different indices). While COUNT(DISTINCT) should still return the correct number of unique sessions, you can verify duplicates with this quick check:

-- Check for duplicate session rows
SELECT
  CONCAT(fullVisitorId, '-', visitId) AS session_id,
  COUNT(*) AS row_count
FROM
  `project.dataset.ga_sessions_*`,
  UNNEST(user.customDimensions) AS cd
GROUP BY session_id
HAVING row_count > 1

If duplicates exist, switching to the LEFT JOIN method above will resolve this.

Final Check: Verify Date Range and Segment Consistency #

Even though your simplified query matches GA, double-check that the date range, segments, and any other filters in your expanded query are identical to what you're seeing in the GA interface. Small discrepancies here can cause unexpected drops.

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