如何在BigQuery中统计从指定搜索页跳转至商品页的用户数？

Hey there! Let's work through this problem to get you accurate counts of users who landed on a product page right after a search page with the srcql parameter.

First, Let's Address the Issues with Your Original Approach #

Your current JOIN-based query has a couple of potential pitfalls:

• It joins all search pages and product pages across the same user, regardless of session—so you might be matching a search from one session to a product view from a completely different session.
• The condition t2.hitnumber > t1.hitnumber is too broad: it counts any product view that happens after any search in the user's history, not the immediate preceding hit in the same session.

Using the LAG() function is actually the right call here—it lets you directly look at the previous page hit within the same user session, which is exactly what you need to confirm the sequence.

Optimized Query Using LAG() #

Here's a streamlined, accurate version of the query that uses window functions to track the preceding page:

WITH all_page_hits AS (
  SELECT
    ga.fullVisitorId AS GA_USER_ID,
    CONCAT(ga.fullVisitorId, CAST(ga.visitId AS STRING)) AS SessionID,
    h.hitnumber,
    (SELECT VALUE FROM h.customDimensions WHERE INDEX = 10) AS PAGETYPE,
    (SELECT VALUE FROM h.customDimensions WHERE INDEX = 8) AS ref_parameter,
    -- Get the previous page's type and ref parameter in the same session
    LAG((SELECT VALUE FROM h.customDimensions WHERE INDEX = 10)) OVER (
      PARTITION BY ga.fullVisitorId, ga.visitId 
      ORDER BY h.hitnumber ASC
    ) AS previous_pagetype,
    LAG((SELECT VALUE FROM h.customDimensions WHERE INDEX = 8)) OVER (
      PARTITION BY ga.fullVisitorId, ga.visitId 
      ORDER BY h.hitnumber ASC
    ) AS previous_ref_parameter
  FROM `emagbigquery.0` ga,
       UNNEST(hits) AS h
  WHERE
    h.type = 'PAGE'
    AND _TABLE_SUFFIX = '20190115'
)

SELECT
  COUNT(DISTINCT GA_USER_ID) AS USERS,
  COUNT(DISTINCT SessionID) AS SESSIONS
FROM all_page_hits
WHERE
  -- Current page is a product view
  PAGETYPE = 'viewproduct'
  -- Previous page was a search with srcql ref parameter
  AND previous_pagetype = 'search'
  AND previous_ref_parameter LIKE '%srcql%' -- Use LIKE if there might be extra characters, = 'srcql' if exact match

Why This Works Better #

1. Session-level context: The PARTITION BY ga.fullVisitorId, ga.visitId ensures we only look at hits within the same user session, so we don't cross-pollinate between different sessions.
2. Immediate sequence check: ORDER BY h.hitnumber ASC makes sure LAG() grabs the directly preceding page hit, not just any earlier hit.
3. Performance: This avoids joining two large CTEs, which can be slow on big datasets. Window functions are generally more efficient for this kind of sequential analysis.
4. Complete coverage: This will capture every user who had a product view immediately following a srcql search page in the same session—no gaps, no false matches.

Quick Notes #

• If your ref_parameter is exactly srcql (no extra text), replace LIKE '%srcql%' with = 'srcql' for precision.
• The all_page_hits CTE includes all page hits, but the final WHERE clause filters down to only the product views that meet your criteria.

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