的用户数量不匹配大查询

的用户数量不匹配大查询

本文介绍了Google Analytics(分析)的用户数量不匹配大查询“用户数量”的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我们的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一周。



将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 beenseveral 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(分析)的用户数量不匹配大查询“用户数量”的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-26 08:11