sql - Google Analytics session 范围字段返回多个值

Question

我发现 BigQuery 中的某些 GA“ session ”范围字段对于相同的 fullVisitorId 和 visitId 字段具有多个值。请参阅下面的示例:

对字段进行分组也没有帮助。在 GA 中，我检查了用户数量与不同设备划分的用户数量。用户数量不同:

这解释了发生了什么，用户将被分组到多个设备下。我的结论是，在用户 session 期间的某个时刻，他们的浏览器用户代理会发生变化，并且在随后的点击中，会在 GA 中设置新的设备类型。

我希望 GA 会使用第一个或最后一个值来避免这种情况，但我想他们不会。我的问题是，我是否认为这是 GA 中的一个“缺陷”。我宁愿选择一个值。从以下查询中选择最后一个或第一个设备值的最佳方法是什么:

SELECT
  fullVisitorId,
  visitId,
  device.deviceCategory
FROM (
  SELECT
    *
  FROM
    `project.dataset.ga_sessions_*` desktop
  UNION ALL
  SELECT
    *
  FROM
    `project.dataset.ga_sessions_*` mobile ) table

我尝试进行子选择并使用STRING_AGG()，尝试按hits.time排序并限制为一个值，但仍然会创建另一行。

我测试过，发现以下字段都有同样的问题:

visitNumber
totals.hits
totals.pageviews
totals.timeOnSite
trafficSource.campaign
trafficSource.medium
trafficSource.source
device.deviceCategory
totals.sessionQualityDim
channelGrouping
device.mobileDeviceInfo
device.mobileDeviceMarketingName
device.mobileDeviceModel
device.mobileInputSelector
device.mobileDeviceBranding

更新

请参阅以下有关此特定 fullVisitorId 和 visitId 的查询 - UNION 已被删除:

添加了

visitStartTime:

添加了

visitStartTime 和 hits.time:

Answer 1

嗯，从表面上看，我认为你有 3 个选择:

1 - 按 fullVisitorId、visitId 分组；并使用最大或最小设备类别。这应该可以防止设备切换器被重复计算，这有点随意，但 GA 数据也是如此。

2 - 选项二类似，但是，如果 deviceCategory 结果可以是任何内容(即结果中不限制为仅有效的 deviceCategory 成员)，您可以使用 CASE 来检查 MAX(deviceCategory) = MIN(deviceCategory) )，如果不同，则返回“多个设备”

3 - 您可以更进一步，计算使用的不同设备的数量，构建以某种方式列出它们的串联，等等。

我要为你写第二个。在您的问题中，您有 2 个不同的查询:一个包含 [日期]，另一个不包含 - 我将同时提供这两个查询。

没有[日期]:

SELECT
    fullVisitorId,
    visitId,
    case when max(device.deviceCategory) = min(device.deviceCategory) 
       then max(device.deviceCategory) 
       else 'Multiple Devices' 
    end as deviceCategory,
    {metric aggregations here}
FROM 
    (SELECT *
        FROM `project.dataset.ga_sessions_*` desktop
    UNION ALL
    SELECT *
        FROM `project.dataset.ga_sessions_*` mobile 
    ) table
GROUP BY fullVisitorId, visitId

[日期]:

SELECT
    [date],
    fullVisitorId,
    visitId,
    case when max(device.deviceCategory) = min(device.deviceCategory) 
       then max(device.deviceCategory) 
       else 'Multiple Devices' 
    end as deviceCategory,
    {metric aggregations here}
FROM 
    (SELECT *
        FROM `project.dataset.ga_sessions_*` desktop
    UNION ALL
    SELECT *
        FROM `project.dataset.ga_sessions_*` mobile 
    ) table
GROUP BY [date], fullVisitorId, visitId

我在这里假设您提供的 Selects 和 Union 是正确的。

另外，我应该指出，这些{metric Aggregations} 应该是 SUM 以外的东西，否则您仍然会重复计算。

我希望这会有所帮助。