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

问题描述

我只是在学习BigQuery，所以这可能是一个愚蠢的问题，但我们希望得到一些统计数据，其中一个是给定日期的总会话数。

  select sum（sessions）as total_sessions from（
从时间戳（20150519）到时间戳（20150519）之间的（table_query（[40663402]，'timestamp（right（table_id，8））中选择
 fullvisitorid，
 count（distinct visitid）作为会话，
 timestamp（20150519）'））
每组通过fullvisitorid 
）
  

（我正在使用 table_query ，因为稍后我们可能会增加天数的范围）

1,075,137 。

但在Google Analytics报告的受众群体概览部分中，

此报告基于1,026,641次会话（会话的100％）。

总是有粗糙的区别尽管有一天，y〜5％。所以我想知道，即使查询很简单，我们是否有任何错误？

预计会发生这种差异吗？我阅读了BigQuery的文档，但在这个问题上找不到任何东西。

在此先感谢，

解决方案

在发布问题后，我们与Google支持部门联系并发现，在Google Analytics中，只有发生事件的会话才算实际计数。

在Bigquery中，您会发现所有会话，无论它们是否有交互。

为了找到与GA相同的结果，您应该过滤在您的BQ查询（ totals.visits ）中，通过与 totals.visits = 1 进行的会话仅在会话中有1

即：

  select sum（ （table_query（[40663402]，'timestamp（right（table_id，8））中的total_sessions作为会话，
选择
 fullvisitorid，
 count（distinct visitid） ）在时间戳（20150519）和时间戳（20150519）之间））
其中totals.visits = 1 
每组通过fullvisitorid 
）
  

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.

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
)

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

This results in 1,075,137.

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

This report is based on 1,026,641 sessions (100% of sessions).

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?

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

Thanks in advance,

解决方案

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.

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

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).

That is:

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屋！