BigQuery的Google Analytics(分析)数据中的页面组合 [英] Page combinations in BigQuery's Google Analytics data

问题描述

使用Google BigQuery中的Google Analytics(分析)数据，我可以得出包含/confirm/页面上包含匹配项的会话数:

Using Google Analytics data in Google BigQuery, I can derive the number of sessions that include a hit on the /confirm/ page with:

#standardSQL
SELECT date AS Date, COUNT(Date) AS Sessions
FROM (
  SELECT date
  FROM `123456789.ga_sessions_20161202`
  CROSS JOIN UNNEST(hits) as hit
  WHERE hit.type = 'PAGE' AND REGEXP_CONTAINS(hit.page.pagePath, '/confirm/$')
  GROUP BY VisitId, fullVisitorId, date
)
GROUP BY Date
ORDER BY Date ASC, Sessions ASC;

如果我想显示在/confirm/页面和/payment/页面上同时点击 的会话数，该怎么办?我的SQL应该是什么样的?

How about if I wanted to show the number of sessions that hit both the /confirm/ page and the /payment/ page? What should my SQL look like?

推荐答案

尝试以下方法，应该给您一个想法

Try below, should give you an idea

#standardSQL
SELECT DATE, COUNT(1) AS Sessions
FROM `123456789.ga_sessions_20161202`
WHERE (SELECT COUNT(1) FROM UNNEST(hits) AS hit WHERE hit.type = 'PAGE' 
                 AND REGEXP_CONTAINS(hit.page.pagePath, '/confirm/$') ) > 0
AND (SELECT COUNT(1) FROM UNNEST(hits) AS hit WHERE hit.type = 'PAGE' 
                 AND REGEXP_CONTAINS(hit.page.pagePath, '/payment/$') ) > 0
GROUP BY DATE
ORDER BY DATE ASC, Sessions ASC;

可以如下进一步优化

#standardSQL
SELECT DATE, COUNT(1) AS Sessions
FROM `123456789.ga_sessions_20161202`
WHERE (SELECT COUNTIF(REGEXP_CONTAINS(hit.page.pagePath, '/confirm/$')) *
          COUNTIF(REGEXP_CONTAINS(hit.page.pagePath, '/payment/$'))
        FROM UNNEST(hits) AS hit WHERE hit.type = 'PAGE') > 0
GROUP BY DATE
ORDER BY DATE ASC, Sessions ASC;

这篇关于BigQuery的Google Analytics(分析)数据中的页面组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！