Google Analytics（分析）的用户数量不匹配大查询“用户数量” [英] Google Analytics 'User Count' not Matching Big Query 'User Count'

问题描述

我们的Google Analytics（分析）用户数与我们的大查询用户数不匹配。

我是否正确计算？

通常情况下，GA和BQ非常接近......尽管并非如此。

最近，GA中的用户数与vs .BQ不协调。

• 我们的'每用户会话数'通常有一个非常正常的
  
  分布。


• 在过去的4周里，'每用户会话数'（GA）与
  有一些偏差。


• 在BQ中同一时间段内交叉检查数据时，无法复制此偏差

不同之处在于用户数量。

我希望有人能够回答的是：

我是否至少使用了正确的SQL语法在BQ中得到答案？

这是我在BQ中运行的查询：

SELECT

Week Week，
Week AS Date_Week，
Total_Sessions，
Total_Users，
Total_Pageviews，
（Total_Time_on_Site / Total_Sessions）AS Avg_Session_Duration，
（Total_Sessions / Total_Users）AS Sessions_Per_User，
（Total_Pageviews / Total_Sessions ）AS Pageviews_Per_Session

FROM

（

SELECT

FORMAT_UTC_USEC（UTC_USEC_TO_WEEK （date，1））AS Week，
COUNT（DISTINCT CONCAT（STRING（fullVisitorId），STRING（VisitID）），1000000）AS Total_Sessions，
COUNT（DISTINCT（fullVisitorId），1000000）AS Total_Users，
SUM（totals.pageviews）As Total_Pageviews，
SUM（totals.timeOnSite）AS Total_Time_on_Site，


FROM

（
TABLE_DATE_RANGE（[zzzzzzzzz.ga_sessions_]，
TIMESTAMP（'2015-02-09'），
TIMESTAMP（'2015-04-12'））
）

GROUP BY星期

）


按周，日期，周日，总计次数，总用户数，总计日历，Avg_Session_Duration，会话次数_用户，Pageviews_Per_Session
ORDER BY星期ASC


我们拥有100万用户/会话/ etc一周。

<100>将1,000,000放入计数区别子句应该防止对BQ的任何抽样部分。

我是否正确地做这项工作？

如果是这样，任何关于GA如何/为什么的建议不同的报告是值得欢迎的。

干杯。

解决方案 / div>

更新：

我们有Premium Analytics，正如@ Pentium10所建议的。所以，我接触到了他们的付费支持。

现在，当我从GA获取完全相同的数据时，我得到了这个结果：

外表对我来说，GA现在已经解决了这个问题。

:: shrug ::

Our Google Analytics 'User Count' is not matching our Big Query 'User Count.'

Am I calculating it correctly?

Typically, GA and BQ align very closely…albeit, not exactly.

Recently, User Counts in GA vs.BQ are incongruous.

• Our number of ‘Sessions per User' typically has a very normal
  distribution.
• In the last 4 weeks, 'Sessions per User' (in GA) has been several deviations from the norm.
• I cannot replicate this deviation when cross-checking data from the same time period in BQ

The difference lies in the User Counts.

What I'm hoping someone can answer is:

Am I at least using the correct SQL syntax to get to the answer in BQ?

This is the query I’m running in BQ:

SELECT

WEEK(Week) AS Week, 
Week AS Date_Week,
Total_Sessions,
Total_Users,
Total_Pageviews,
( Total_Time_on_Site / Total_Sessions ) AS Avg_Session_Duration,
( Total_Sessions / Total_Users ) AS Sessions_Per_User,
( Total_Pageviews / Total_Sessions ) AS Pageviews_Per_Session

FROM

( 

    SELECT

    FORMAT_UTC_USEC(UTC_USEC_TO_WEEK (date,1)) AS Week,
    COUNT(DISTINCT CONCAT(STRING(fullVisitorId), STRING(VisitID)), 1000000) AS Total_Sessions,
    COUNT (DISTINCT(fullVisitorId), 1000000) AS Total_Users,
    SUM(totals.pageviews) As Total_Pageviews,
    SUM(totals.timeOnSite) AS Total_Time_on_Site,


    FROM

          (
            TABLE_DATE_RANGE([zzzzzzzzz.ga_sessions_], 
                           TIMESTAMP('2015-02-09'),
                           TIMESTAMP('2015-04-12'))
          )                

     GROUP BY Week

  )


GROUP BY Week, Date_Week, Total_Sessions, Total_Users, Total_Pageviews,     Avg_Session_Duration, Sessions_Per_User, Pageviews_Per_Session
ORDER BY Week ASC

We have well under 1,000,000 users/sessions/etc a week.

Throwing that 1,000,000 into the Count Distinct clause should be preventing any sampling on BQ’s part.

Am I doing this correctly?

If so, any suggestion on how/why GA would be reporting differently is welcome.

Cheers.

*(Statistically) significant discrepancies begin in Week 11

解决方案

Update:

We have Premium Analytics, as @Pentium10 suggested. So, I reached out to their paid support.

Now when I pull the exact same data from GA, I get this:

Looks to me like GA has now fixed the issue.

Without actually admitting there ever was one.

::shrug::

这篇关于Google Analytics（分析）的用户数量不匹配大查询“用户数量”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！