CODEWITHSUNDEEP
sqlgoogle-analyticsgoogle-bigquery
Robyn F
Robyn F

Reputation: 93

Google Analytics 'User Count' not Matching Big Query 'User Count'

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.

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.

GA

BQ

*(Statistically) significant discrepancies begin in Week 11

Upvotes: 4

Views: 5884

Answers (3)

Martin Weitzmann
Martin Weitzmann

Reputation: 4736

In standard SQL use COUNT(DISTINCT fullVisitorId) Google Analytics shows an approximation for users, Big Query is exact. You can test this with unsampled reports in Google Analytics - numbers will match.

Also: GA uses all available data to count users, even where totals.visits is NULL! In contrast GA counts sessions only where totals.visits = 1!

Upvotes: 1

Rachel
Rachel

Reputation: 181

I have this problem before. The way I fixed it was by using COUNT(DISTINCT FULLVISITORID) for total_users.

Upvotes: 1

Robyn F
Robyn F

Reputation: 93

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:

Fixed GA View

Looks to me like GA has now fixed the issue.

Without actually admitting there ever was one.

::shrug::

Upvotes: 2

Related Questions