应用电子商务操作筛选器时，大查询和Google Analytics UI不匹配

Question

我们正在验证Big Query中的查询，无法获得与google analytics UI匹配的结果。类似的问题可以在here中找到，但在我们的例子中，不匹配只发生在我们对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个具有产品详细信息视图的唯一会话，而使用上面的查询在Big Query中获得了3.684M个唯一会话。

几个问题: 1)我们的印象是，购物行为报告“带有产品视图的会话”的细分是基于ecommerce_action.actiontype过滤器的。这是真的吗? 2)有没有一个.totals预聚合表，UI可以从中拉出？

Answer 1

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

此查询对包含所有isProduct为null或false的action_type='2‘的所有会话求和：

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 2

听起来问题在于，在使用遗留SQL、as noted in the migration guide时，COUNT(DISTINCT ...)是近似值，因此计数不准确。可以改用standard SQL (首选)，也可以将EXACT_COUNT_DISTINCT与遗留SQL一起使用。

Answer 3

除了在使用遗留SQL时COUNT(DISTINCT ...)是近似值之外，可能还有一些会话只有非交互式的命中，在Google Analytics UI中不会被计入会话，但COUNT(DISTINCT ...)和EXACT_COUNT_DISTINCT(...)都会对它们进行计数，因为在您的查询中，它们会计入访问id。

使用SUM(totals.visits)应该会得到与在UI中相同的结果，因为SUM没有考虑totals.visits的空值(对应于其中只有非交互式点击的会话)。