在BigQuery标准SQL中展平GA会话与命中层级字段的优化问询

Your current query works, but those repeated subqueries that unnest hits over and over get messy quickly—especially as you add more custom fields. This approach is also inefficient because BigQuery has to process the hits array multiple times for each session. Let's refactor this to unnest hits only once, making the query cleaner, more maintainable, and faster.

Here's the optimized version:

WITH combined_ga_data AS (
  -- Combine desktop and mobile properties in one reusable CTE
  SELECT * FROM `abcdefgh.12345678.ga_sessions_*` desktopProperty 
  WHERE _TABLE_SUFFIX BETWEEN '20180122' AND '20180122'
  UNION ALL
  SELECT * FROM `abcdefgh.12345678.ga_sessions_*` mobileProperty 
  WHERE _TABLE_SUFFIX BETWEEN '20180122' AND '20180122'
),
hit_level_aggregates AS (
  -- Unnest hits ONCE, then compute all hit-level metrics/dimensions here
  SELECT
    -- Session-level fields (keep all needed for final grouping)
    date,
    fullVisitorId,
    visitId,
    visitNumber,
    TIMESTAMP_SECONDS(visitStartTime) AS visitStartTime,
    totals.visits,
    device.deviceCategory,
    totals.hits AS total_session_hits,
    totals.newVisits,
    totals.pageviews,
    totals.timeOnSite,
    trafficSource.adContent,
    trafficSource.campaign,
    trafficSource.keyword,
    trafficSource.medium,
    trafficSource.referralPath,
    trafficSource.source,
    channelGrouping,
    device.browser,
    device.browserSize,
    device.browserVersion,
    device.mobileDeviceInfo,
    device.mobileDeviceModel,
    device.operatingSystem,
    device.mobileDeviceBranding,
    geoNetwork.country,
    geoNetwork.city,
    -- Session custom dimension: Get userId (index 20) with conditional aggregation
    MAX(IF(cd.index = 20 AND REGEXP_CONTAINS(cd.value, r"\d"), cd.value, NULL)) OVER(PARTITION BY fullVisitorId, visitId) AS userId,
    -- Hit-level custom metrics: Sum booking value (index 28) for this hit
    (SELECT SUM(IF(hcm.index = 28, hcm.value, 0)) FROM UNNEST(h.customMetrics) hcm) AS hit_total_booking,
    -- Hit-level custom dimension: Count occurrences of index 20 for this hit
    (SELECT COUNT(IF(hcd.index = 20, hcd.value, NULL)) FROM UNNEST(h.customDimensions) hcd) AS hit_cd20_count,
    -- Product custom dimensions: Count occurrences per index for this hit's products
    (SELECT COUNT(IF(hpc.index = 1, hpc.value, NULL)) FROM UNNEST(h.product) hp, UNNEST(hp.customDimensions) hpc) AS hit_pc1_count,
    (SELECT COUNT(IF(hpc.index = 2, hpc.value, NULL)) FROM UNNEST(h.product) hp, UNNEST(hp.customDimensions) hpc) AS hit_pc2_count,
    (SELECT COUNT(IF(hpc.index = 3, hpc.value, NULL)) FROM UNNEST(h.product) hp, UNNEST(hp.customDimensions) hpc) AS hit_pc3_count,
    (SELECT COUNT(IF(hpc.index = 4, hpc.value, NULL)) FROM UNNEST(h.product) hp, UNNEST(hp.customDimensions) hpc) AS hit_pc4_count,
    (SELECT COUNT(IF(hpc.index = 5, hpc.value, NULL)) FROM UNNEST(h.product) hp, UNNEST(hp.customDimensions) hpc) AS hit_pc5_count,
    (SELECT COUNT(IF(hpc.index = 6, hpc.value, NULL)) FROM UNNEST(h.product) hp, UNNEST(hp.customDimensions) hpc) AS hit_pc6_count,
    (SELECT COUNT(IF(hpc.index = 7, hpc.value, NULL)) FROM UNNEST(h.product) hp, UNNEST(hp.customDimensions) hpc) AS hit_pc7_count,
    (SELECT COUNT(IF(hpc.index = 8, hpc.value, NULL)) FROM UNNEST(h.product) hp, UNNEST(hp.customDimensions) hpc) AS hit_pc8_count,
    (SELECT COUNT(IF(hpc.index = 9, hpc.value, NULL)) FROM UNNEST(h.product) hp, UNNEST(hp.customDimensions) hpc) AS hit_pc9_count
  FROM combined_ga_data,
       UNNEST(hits) h, -- Unnest hits once per session
       UNNEST(customDimensions) cd -- Unnest session custom dimensions to fetch userId
)
-- Aggregate hit-level metrics up to session level
SELECT
  date,
  fullVisitorId,
  visitId,
  visitNumber,
  visitStartTime,
  totals.visits,
  deviceCategory,
  total_session_hits,
  newVisits,
  pageviews,
  timeOnSite,
  adContent,
  campaign,
  keyword,
  medium,
  referralPath,
  source,
  channelGrouping,
  browser,
  browserSize,
  browserVersion,
  mobileDeviceInfo,
  mobileDeviceModel,
  operatingSystem,
  mobileDeviceBranding,
  country,
  city,
  userId,
  -- Sum all hit-level aggregates to get session totals
  SUM(hit_total_booking) AS totalBooking,
  SUM(hit_cd20_count) AS h_cd1,
  SUM(hit_pc1_count) AS h_pc1,
  SUM(hit_pc2_count) AS h_pc2,
  SUM(hit_pc3_count) AS h_pc3,
  SUM(hit_pc4_count) AS h_pc4,
  SUM(hit_pc5_count) AS h_pc5,
  SUM(hit_pc6_count) AS h_pc6,
  SUM(hit_pc7_count) AS h_pc7,
  SUM(hit_pc8_count) AS h_pc8,
  SUM(hit_pc9_count) AS h_pc9
FROM hit_level_aggregates
GROUP BY
  date, fullVisitorId, visitId, visitNumber, visitStartTime, totals.visits, deviceCategory, total_session_hits, newVisits, pageviews, timeOnSite, adContent, campaign, keyword, medium, referralPath, source, channelGrouping, browser, browserSize, browserVersion, mobileDeviceInfo, mobileDeviceModel, operatingSystem, mobileDeviceBranding, country, city, userId
ORDER BY date;

Key Improvements: #

• Single Unnest of Hits: Instead of unnesting hits in every subquery, we do it once in the hit_level_aggregates CTE. This cuts down on redundant processing and speeds up the query.
• Modular Structure: CTEs split the query into logical chunks—combining data, processing hits, then aggregating to sessions. Adding new fields later will be much simpler.
• Cleaner Session Custom Dimensions: We use a window function to fetch the session-level userId instead of a subquery, making the code more readable and maintainable.
• Scalable Field Addition: To add a new custom metric/dimension, just add one line in the hit-level CTE and one line in the final aggregation—no need to write an entirely new subquery.

Bonus Tips: #

• If you have dozens of product custom dimensions, you could use dynamic SQL to generate those lines, but for a fixed set, this static approach is perfectly fine.
• Use LEFT JOIN UNNEST instead of comma-separated unnest if you want to retain hits that don't have custom metrics/dimensions/products (though comma-separated works well here since we're counting occurrences).

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