google-bigquery - BigQuery 中带有会话 ID 的 Google Analytics（分析）交易 ID

Question

我正在使用 BigQuery 中的 Google Analytics（分析）数据，并尝试将会话 ID 与事务 ID 结合起来。我面临一些困难，因为我还不明白如何将会话 ID（与我totals.visits = 1在 WHERE 子句中使用的 GA UI 匹配）与事务 ID ( UNNEST(hits)) 结合起来。条件：数据应与 GA UI 匹配。

期望的结果

日期 | 会话 ID | 交易 ID
2019-06-18 00:00:00.000 UTC | 400101010101 | AAAAAAA
2019-06-18 00:00:00.000 UTC | 400202010102 | BBBBBBB
2019-06-18 00:00:00.000 UTC | 400303010103 | 中国交建

检索时间戳和 SessionIds 的当前查询

#standardSQL
SELECT
TIMESTAMP(PARSE_DATE('%Y%m%d', date)) AS Date,
CONCAT(fullVisitorId, CAST(visitId AS STRING) ) AS SessionID 
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitId AS STRING) )) AS Sessions, /* verify match with GA UI*/
FROM
`ga-xxxxx.yyyyyyyy_sessions_20*`
WHERE
_TABLE_SUFFIX = '180618'
AND totals.visits = 1
GROUP BY
1,2

使用 TransactionID 进行的当前查询

当使用查询扩展时hits.transaction.transactionId，UNNEST(hits)我得到的记录比在 GA UI 中更多。似乎查询正在计算 SessionID 和 TransactionID。

#standardSQL
SELECT
TIMESTAMP(PARSE_DATE('%Y%m%d', date)) AS Date,
CONCAT(fullVisitorId, CAST(visitId AS STRING) ) AS SessionID, 
hits.transaction.transactionId AS transactionId, 
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitId AS STRING) )) AS Sessions, /* verify match with GA UI*/
FROM
`ga-xxxxx.yyyyyyyy_sessions_20*`,                                  
 UNNEST(hits) AS hits
WHERE
_TABLE_SUFFIX = '180618'
AND totals.visits = 1
GROUP BY
1,2,3

如何在数据SessionID与hits.transaction.transactionIdGA UI 匹配的条件下进入查询？

希望有人可以帮助我。

谢谢。

标签： google-bigquery

Answer 1

要将 GA UI 中的会话数与 BigQuery 匹配，您可以像这样计算会话数：

CONCAT(CAST(fullVisitorId AS STRING),CAST(visitId AS STRING)) AS sessions

然后，如果您想查看会话 ID 和事务 ID，可以执行以下操作：

    SELECT Date,
    CONCAT(CAST(fullVisitorId AS STRING),CAST(visitId AS STRING)) AS visit_ref,
    hits.transaction.transactionID AS transaction_id
    FROM `xxx.xxx.ga_sessions_20180619`
      CROSS JOIN UNNEST(hits) AS hits
    WHERE hits.transaction.transactionID IS NOT NULL
    GROUP BY Date, visit_ref, transaction_id

请注意，我已经删除了所有未在上面进行交易的会话，但WHERE如果您希望它们存在，您可以删除此子句。

如果您使用此方法获得更多行，那么请考虑您可能有用户放置多个订单的会话，您可以通过使用STRING_AGG在会话 ID 旁边列出所有事务 ID 来了解这一点，在下面的示例中我'也计算了它们并按此计数排序：

   SELECT Date,
   CONCAT(CAST(fullVisitorId AS STRING),CAST(visitId AS STRING)) AS visit_ref,
   STRING_AGG(hits.transaction.transactionID) AS transaction_id, 
   COUNT(DISTINCT hits.transaction.transactionId) AS orders
   FROM `xxx.xxx.ga_sessions_20180619`
     CROSS JOIN UNNEST(hits) AS hits
   WHERE hits.transaction.transactionID IS NOT NULL
   GROUP BY Date, visit_ref
   ORDER BY orders DESC