CODEWITHSUNDEEP
sqldatabasegoogle-analyticsgoogle-bigquerybigdata
Awais Kazi
Awais Kazi

Reputation: 85

Bigquery Active User count not accurate (Google Analytics)

I have Google Analytics integrated to Bigquery and I'm trying to write a query to fetch Active Users that should match with the number on GA Portal.

Here's the query I've written;

SELECT 
date(date) as date,
EXACT_COUNT_DISTINCT(fullVisitorId) as daily_active_users,
FROM TABLE_DATE_RANGE([<project_id>:<dataset>.ga_sessions_],
TIMESTAMP('2018-01-01'),
TIMESTAMP(CURRENT_DATE()))
group by date
order by date desc

The numbers I get in response are somehow related to the ones Google Analytics shows me, but they aren't a 100% accurate.

The numebers I get in return are slightely higher than the ones on the portal and I assume I need to put a where clause to filter a property GA might be filtering on the portal.

Upvotes: 0

Views: 943

Answers (1)

Pol Ferrando
Pol Ferrando

Reputation: 673

Your query looks fine to me. Assuming that you're looking at the same GA view as the one linked to BigQuery, I think that the problem could be sampling.

Even if the GA UI says that "This report is based on 100% of sessions.", try to export it as an Unsampled Report and check the numbers (in my experience, the users metric sometimes doesn't match between unsampled reports and default reports without sampling).

Upvotes: 6

Related Questions