VisitorID in BigQuery doesn't match Sessions Google Analytics

3
votes

I've looked around the web and I keep getting the same answer: to count sessions in BigQuery, take count(distinct concat(fullvisitorID, string(visitID))). But in some cases, that's not even getting me close to the sessions in Google Analytics. Is there any other way to count sessions better? Here's what I'm trying to do:

SELECT hits.customdimensions.value val,
count(*) as pageviews,
exact_count_distinct(CONCAT([fullVisitorId], STRING([visitid]))) sessions
FROM [xxx.ga_sessions_20150619]
where hits.customdimensions.index = 7 and lower(hits.type) = 'page'
group by val
order by pageviews desc
LIMIT 1000

For some custom dimension values, that gets close to GA, but others are off by twice the amount. Is there any way to get a better session count in BQ?

1
sessiongoogle-analyticsgoogle-bigquery

1 Answers

0
votes

Well, I can't really speak to your GA data itself (of course, check to make sure you're not sampling the data at all), but if you run the following query, you'll pull the sum of each of the session counts per fullVisitorId:

SELECT SUM(sessionsPerUser)
  FROM (SELECT fullVisitorId, COUNT(visitNumber) AS sessionsPerUser
    FROM [xxx.ga_sessions_2017yyzz]
    GROUP BY fullVisitorId)