大查询和谷歌分析UI在应用电子商务操作过滤器时不匹配

Question

我们正在验证大查询中的查询，无法获得与谷歌分析UI匹配的结果。类似的问题可以在这里找到，但在我们的例子中，不匹配只发生在我们对 ecommerce_action.action_type 应用特定过滤器时。

下面是查询：

SELECT COUNT(distinct fullVisitorId+cast(visitid as string)) AS sessions
FROM (
  SELECT
    device.browserVersion,
    geoNetwork.networkLocation,
    geoNetwork.networkDomain,
    geoNetwork.city,
    geoNetwork.country,
    geoNetwork.continent,
    geoNetwork.region,
    device.browserSize,
    visitNumber,
    trafficSource.source,
    trafficSource.medium,
    fullvisitorId,
    visitId,
    device.screenResolution,
    device.flashVersion,
    device.operatingSystem,
    device.browser,
    totals.pageviews,
    channelGrouping,
    totals.transactionRevenue,
    totals.timeOnSite,
    totals.newVisits,
    totals.visits,
    date,
    hits.eCommerceAction.action_type
  FROM
    (select * 
     from TABLE_DATE_RANGE([zzzzzzzzz.ga_sessions_], 
    <range>) ))t
WHERE
 hits.eCommerceAction.action_type = '2' and <stuff to remove bots>
  )

在使用内置购物行为报告的 UI 中，我们获得了 3.836M 个具有产品详细信息视图的唯一会话，而使用上述查询的大查询中则获得了 3.684M 个唯一会话。

几个问题：1( 我们的印象是，购物行为报告"具有产品视图的会话"细分基于 ecommerce_action.actiontype 过滤器。这是真的吗？2( 是否有 UI 可能从中提取的 .totals 预聚合表？

Answer 1

听起来问题是，如迁移指南中所述，使用旧版 SQL 时COUNT(DISTINCT ...)是近似值，因此计数不准确。要么改用标准 SQL(首选(，要么将EXACT_COUNT_DISTINCT与旧版 SQL 一起使用。

Answer 2

您在查询中包含产品列表视图。如 https://support.google.com/analytics/answer/3437719 中所述，您需要确保没有产品isImpression = TRUE，因为这意味着它是一个产品列表视图。

此查询对包含任何 action_type='2' 的所有会话求和，其中所有 isProduct 均为 null 或 false：

SELECT
  SUM(totals.visits) AS sessions
FROM
  `project.123456789.ga_sessions_20180101` AS t
WHERE
  (
  SELECT
    LOGICAL_OR(h.ecommerceaction.action_type='2')
  FROM
    t.hits AS h
  WHERE
    (SELECT LOGICAL_AND(isimpression IS NULL OR isimpression = FALSE) FROM h.product))

对于 legacySQL，您可以调整文档中的示例。

Answer 3

除了使用旧版 SQL 时COUNT(DISTINCT ...)是近似值这一事实之外，可能还有一些会话中只有非交互式点击，这些会话在 Google Analytics UI 中不会计为会话，但它们按COUNT(DISTINCT ...)和EXACT_COUNT_DISTINCT(...)计算，因为在您的查询中它们会计算访问 ID。

使用 SUM(totals.visits) 应该获得与 UI 中相同的结果，因为 SUM 不考虑 totals.visits(对应于只有非交互式命中的会话(的 NULL 值。