google-analytics - 如何在浏览器控制台中使用 BigQuery SQL 计算 "Exit Rate"

Question

我正在处理从 Google Analytics 自动填充的大型 BigQuery 数据集。对于这项工作，我正在尝试使用数据计算退出率。此外，作为引用，我正在仔细查看以下链接中提供的 BigQuery 导出架构 which describes the BigQuery export schema in sufficient detail.

如前所述in this post from Google regarding the exit rate ，退出率可以定义为“对于页面的所有综合浏览量，退出率是 session 中最后一次的百分比。”为了计算这个，我推测我需要在每次唯一访问时检查 hits.page.pagePath 列中的每个页面，如果发现错误的正则表达式是 前面 通过另一个不指示错误的URL，则错误路径之后的退出可以算作退出。

这看起来是一个非常明确的路径分析案例，我还不确定 BigQuery 是否可以轻松或有效地处理它。通常，我有兴趣查看的包含退出的 URL 使用:

REGEXP_MATCH(hits.page.pagePath, r'/[^/]+error\.aspx')

例如，我开始在以下上下文中使用它作为试验:

SELECT hits.page.pagePath AS Page_Path
FROM [XXXXXXXX.ga_sessions_20140711]
WHERE REGEXP_MATCH(hits.page.pagePath, r'/[^/]+error\.aspx') OR REGEXP_MATCH(hits.page.pagePath, r'/[^/]+genericerror\.aspx')

任何可以指出我的建议或示例，以突出某人如何成功使用 BigQuery 来计算退出率，我们将不胜感激。

添加了更多详细信息(2014 年 7 月 14 日):

这里有问题的数据集是 XXXXXXXX。以下是添加的一些进一步细节。将执行的整个查询将创建一个具有以下输出的表:

日期页面(FROM CASE - 每天有多个案例，因此对于各种案例，每天将有 7 或 8 行)，度量 1，度量 2(跳出)，度量 3(退出)

该查询具有以下总体规范:

描述: ^/XXX/[^/]+error.aspx 或 ^/XXX/[^/]+genericerror.aspx 的浏览量和唯一浏览量

指标:浏览量、唯一浏览量、 session 、用户、跳出率(跳出)、退出率(退出)

案例陈述值(value) (页面维度值):Homepage 2.0、Homepage 1.0、Inbound Search 2.0、Inbound Search 1.0、Outbound Search 2.0、Outbound Search 1.0、Review Itinerary 2.0、Review Itinerary 1.0、旅行者信息 2.0、旅行者信息 1.0、座位选择器 2.0、座位选择器1.0, 支付信息 2.0, 支付信息 1.0, 数字 2.0 其他, 数字 1.0 其他

案例陈述:

Case when previous page = "^/XXX/[^/]+/default\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Homepage 2.0" when previous page ="^/web/[^/]+/default\.aspx" and landing page = "^/web/[^/]+/default\.aspx" then "Homepage 1.0" when previous page="^XXX/[^/]+/apps/booking?flight/(searchresult1|search(rt|ow|md))\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Inbound Search 2.0" when previous page="^web/[^/]+/apps/booking?flight/(searchresult1|search(rt|ow|md))\.aspx" and landing page="^/web/[^/]+/default\.aspx" then "Inbound Search 1.0" when previous page="^/XXX/[^/]+/apps/booking/flight/searchResult2\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Outbound Search 2.0" when previous page="^/web/[^/]+/apps/booking/flight/searchResult2\.aspx" and landing page="^/web/[^/]+/default\.aspx" then "Outbound Search 1.0" when previous page="^/XXX/[^/]+/apps/booking/flight/reviewRevenue\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Review Itinerary 2.0" when previous page="^/web/[^/]+/apps/booking/flight/reviewRevenue\.aspx" and landing page="^/web/[^/]+/default\.aspx" then "Review Itinerary 1.0"  when previous page="^/XXX/[^/]+/apps/booking/flight/traveler\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Traveler info 2.0" when previous page="^/web/[^/]+/apps/booking/flight/traveler\.aspx" and landing page="^/web/[^/]+/default\.aspx" then "Traveler info 1.0"  when previous page="^/XXX/[^/]+/apps/booking/flight/seatSelector\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Seat selector 2.0" when previous page="^/web/[^/]+/apps/booking/flight/seatSelector\.aspx" and landing page="^/web/[^/]+/default\.aspx" then "Seat selector 1.0" when previous page="^/XXX/[^/]+/apps/booking/flight/billingRevenue\.aspx" and landing page="^/XXX/[^/]+/default\.aspx" then "Payment info 2.0" when previous page="^/web/[^/]+/apps/booking/flight/billingRevenue\.aspx" and landing page="^/web/[^/]+/default\.aspx" then "Payment info 1.0" when landing page ="^/XXX/[^/]+/default\.aspx" then "digital 2.0 other" else "digital 1.0 other" end as Page

尺寸:日期、页面、设备类别、浏览器、自定义变量(值 04)

过滤器:正则表达式:page=^/XXX/[^/]+/error.aspx 或 ^/XXX/[^/]+/genericerror.aspx

分组/订购:日期、页面

此处的最终查询必须是在指定天数内执行 Ad Hoc 的单个查询，它会生成上面提到的简单表(对各种值求和)。退出率看起来是手动计算的。首先，我使用下表生成各种点击的 URL:

SELECT date, CONCAT(fullVisitorId, STRING(visitId)) AS unique_visit_id, visitId, visitNumber, fullVisitorId, totals.pageviews, totals.bounces, 
hits.page.pagePath, hits.page.pageTitle, device.deviceCategory, device.browser, device.browserVersion, hits.customVariables.index,
hits.customVariables.customVarName, hits.customVariables.customVarValue, hits.time
FROM (FLATTEN([XXXXXXXX.ga_sessions_20140711], hits.time))
WHERE hits.customVariables.index = 4
ORDER BY unique_visit_id DESC, hits.time ASC
LIMIT 1000;

我现在使用 lag 函数查看之前为执行此案例而提供的 hits.page.pagePath 以及退出率的计算时遇到了问题。

Answer 1

我将这些放在一起，为您提供一个额外的列，说明页面是否是 session 中的最后一次点击。在此检查命中类型很重要。

如果页面是最后一次点击，则添加列以声明

SELECT 
  unique_visit_id,
  page,
  hit_number,
  hit_type,
  max_hit,
  IF(hit_number = max_hit, 'yes', 'no') as last_page
FROM (SELECT CONCAT(fullVisitorId, STRING(visitId)) AS unique_visit_id, hits.hitNumber AS hit_number, hits.type AS hit_type, hits.page.pagePath AS page, MAX(hit_number) OVER (PARTITION BY unique_visit_id) AS max_hit
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE hits.type = 'PAGE'
GROUP BY unique_visit_id, hit_number, hit_type, page
ORDER BY unique_visit_id, hit_number)

获取浏览量、退出和退出率

然后，这将为您提供实际计算

SELECT 
  page,
  COUNT(page) as pageviews,
  SUM(IF(hit_number = max_hit, 1, 0)) as exits,
  (SUM(IF(hit_number = max_hit, 1, 0))/COUNT(page)) * 100 AS exit_rate
FROM (SELECT CONCAT(fullVisitorId, STRING(visitId)) AS unique_visit_id, hits.hitNumber AS hit_number, hits.type AS hit_type, hits.page.pagePath AS page, MAX(hit_number) OVER (PARTITION BY unique_visit_id) AS max_hit
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE hits.type = 'PAGE'
GROUP BY unique_visit_id, hit_number, hit_type, page
ORDER BY unique_visit_id, hit_number)
GROUP BY page
ORDER BY pageviews DESC