BigQuery 与 Google Analytics 报告中的总会话数 [英] Total Sessions in BigQuery vs Google Analytics Reports

问题描述

我刚刚学习 BigQuery，所以这可能是一个愚蠢的问题，但我们想在那里获得一些统计数据，其中之一是给定日期的总会话数.

I'm just learning BigQuery so this might be a dumb question, but we want to get some statistics there and one of those is the total sessions in a given day.

为此，我在 BQ 中进行了查询:

To do so, I've queried in BQ:

select sum(sessions) as total_sessions from (
  select
    fullvisitorid,
    count(distinct visitid) as sessions,
    from (table_query([40663402], 'timestamp(right(table_id,8)) between timestamp("20150519") and timestamp("20150519")'))
    group each by fullvisitorid
)

(我使用 table_query 因为稍后我们可能会增加天数范围)

(I'm using the table_query because later on we might increase the range of days)

结果是 1,075,137.

但在我们的 Google Analytics 报告中，在受众概览"部分，当天的结果:

But in our Google Analytics Reports, in the "Audience Overview" section, the same day results:

此报告基于 1,026,641 个会话(会话的 100%).

尽管如此，总有大约 5% 的差异.所以我想知道，即使查询很简单，我们是否犯了任何错误?

There's always this difference of roughly ~5% despite of the day. So I'm wondering, even though the query is quite simple, is there any mistake we've made?

这种差异会发生吗?我通读了 BigQuery 的文档，但没有找到有关此问题的任何信息.

Is this difference expected to happen? I read through BigQuery's documentation but couldn't find anything on this issue.

提前致谢，

推荐答案

在发布问题后，我们联系了 Google 支持人员，发现在 Google Analytics 中，只有触发事件"的会话才会被实际计算在内.

After posting the question we got into contact with Google support and found that in Google Analytics only sessions that had an "event" being fired are actually counted.

>

在 Bigquery 中，您将找到所有会话，无论它们是否有交互.

In Bigquery you will find all sessions regardless whether they had an interaction or not.

为了找到与 GA 中相同的结果，您应该在 BQ 查询中按 totals.visits = 1 的会话进行过滤(totals.visits 仅为 1用于触发事件的会话).

In order to find the same result as in GA, you should filter by sessions with totals.visits = 1 in your BQ query (totals.visits is 1 only for sessions that had an event being fired).

即:

select sum(sessions) as total_sessions from (
  select
    fullvisitorid,
    count(distinct visitid) as sessions,
    from (table_query([40663402], 'timestamp(right(table_id,8)) between timestamp("20150519") and timestamp("20150519")'))
    where totals.visits = 1
    group each by fullvisitorid
)

这篇关于BigQuery 与 Google Analytics 报告中的总会话数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！