BigQuery : Returning Visits Count Mismatch with google Analytics data

Question

I am importing google Analytics data into bigquery session_streaming table using Owox BI. I have a requirement to count returning visits count using this data but result is not matching. Business logic: If newVisits is null then its a returning visitor Date Range: 10th june 2018

Source : Google

Medium: CPC

BigQuery Result: 136 Returning Visits

GA Account: 95(TotalUsers-New Users)

SELECT
  count(distinct clientId ) as returningvisits
FROM `test.Test.session_streaming_20180610` where trafficSource.medium ='cpc' and trafficSource.source ='google' and newVisits is null

Schema of session streaming table

user RECORD NULLABLE

user.id STRING NULLABLE

user.phone STRING NULLABLE

user.email STRING NULLABLE

clientId STRING NULLABLE

date STRING NULLABLE

sessionId STRING NULLABLE

visitNumber INTEGER NULLABLE

newVisits INTEGER NULLABLE

There are few more fields.

Could you please help me whats wrong with this query ?

Hi - when you say streaming, what frequency are you importing? Are you imported daily aggregated data, or in-day data which is not fully aggregated? When you sum total visits, regardless of your source filters, are the results correct? — Ben P
I am importing in-day data which is not fully aggregated.it is based on user session data.Yes, sum of total visits is matching with the ga datset — Mayank
It could be the way you are counting the sessions (this is a good read: stackoverflow.com/questions/30419771/…) but if you are matching back to GA then it is more likely that your streaming data is missing the traffic source and medium. Also, have you checked the frequency? GA is usually about 2 hours behind, how does this match the frequency of your import? Could this be a cause for the mismatch? — Ben P

Yevheniia Chernenko Yevheniia Chernenko · Accepted Answer · 2018-06-22T11:54:38

Mayank! You've already contacted our support service and we're replied in the support chat. Just to double check everything, we're sending you the reply here.

First of all, it's not the best idea to count new users in GA like Total Users - Returned Users.

Because a single user can be a New Visitor and a Returning Visitor in the same time period. New Users are the first time visitors to your website who have initiated a session for the first time for a given date range. If the same users return back to your website on the same day or any other day then they’ll be considered as returning visitors in GA.

We recommend you to use the same logic of counting users in the segment, e.g. count New users in both GA and BQ. And use GROUP BY instead of COUNT(DISTINCT ...), it gives a more accurate result in most cases.

BigQuery : Returning Visits Count Mismatch with google Analytics data

1 Answers