问题描述
我们的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如何/为什么的建议不同的报告是值得欢迎的。干杯。
更新：
我们有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（分析）的用户数量不匹配大查询“用户数量”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！