CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
Mark Ginsburg
Mark Ginsburg

Reputation: 2269

How to replicate the GA field Visits in Big Query

In a typical GA session, after picking a View ID and a date range,

We can get a week's worth of data like this:

Users
146,207

New Users
124,582

Sessions
186,191

The question is, what BQ field(s) to query in order to get this Users value?

Here is an example query with 2 methods (the 2nd method is commented out).

SELECT      
        count(DISTINCT(CONCAT(CAST(visitID as STRING),cast(visitNumber as 
STRING)))) as visitors,   

 --  count(DISTINCT(fullVisitorId)) as visitors

I noticed the FVID method was fairly close to what I see in GA (with Users being a little understated by a 3% in BQ) and if I use the commented out method, I get a value that is about 15% overstated as compared to GA. Is there a more reliable method in BQ to acquire the Users value in GA?

Upvotes: 0

Views: 552

Answers (1)

colintemple
colintemple

Reputation: 421

The COUNT(DISTINCT fullVisitorId) method is the most correct method, but it won't match what Analytics 360 reports by default. Since last year, Google Analytics 360 by default uses a different calculation for the Users metric than it previously did. The old calculation, which is still used in unsampled reports, is more likely to match what you get out of BigQuery. You can verify this by exporting your report as an unsampled report, or using the unsampled reporting features in the Management API.

If you want the numbers to match exactly, you can turn off the new calculation by using the instructions here. The new calculation's precise details are not public, so duplicating that value in BigQuery is quite difficult.

There are still some reasons you might see different numbers, even with the old calculation. One is if the site has implemented User ID, in which case the GA number will be lower than BigQuery for fullVisitorId. Another is sampling, though that's unlikely in Analytics 360 at the volumes you're talking about.

Upvotes: 1

Related Questions