Is google analytics new user affected by lookback window?
up vote
1
down vote
favorite
I recently found out that visitNumber in big query google analytics export starts over at 1 if a user has not visited the site in 183 days or more. I am now trying to understand if the same lookback window is applied when google analytics defines new users?
google-analytics 
google-bigquery asked Nov 7 at 10:00
Ichta
956
add a comment |
up vote
1
down vote
favorite
I recently found out that visitNumber in big query google analytics export starts over at 1 if a user has not visited the site in 183 days or more. I am now trying to understand if the same lookback window is applied when google analytics defines new users?
google-analytics google-bigquery asked Nov 7 at 10:00
Ichta
956
I would be curious to see this answered, but I don't think it falls into the realm of "specific programming problems" that are considered on-topic for this site.
– Eike Pierstorff
Nov 7 at 12:43
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I recently found out that visitNumber in big query google analytics export starts over at 1 if a user has not visited the site in 183 days or more. I am now trying to understand if the same lookback window is applied when google analytics defines new users?
google-analytics google-bigquery asked Nov 7 at 10:00
Ichta
956
I recently found out that visitNumber in big query google analytics export starts over at 1 if a user has not visited the site in 183 days or more. I am now trying to understand if the same lookback window is applied when google analytics defines new users?
google-analytics google-bigquery google-analytics google-bigquery asked Nov 7 at 10:00
Ichta
956
asked Nov 7 at 10:00
Ichta
956
asked Nov 7 at 10:00
Ichta
956
asked Nov 7 at 10:00
Ichta
956
asked Nov 7 at 10:00
Ichta
956
956
I would be curious to see this answered, but I don't think it falls into the realm of "specific programming problems" that are considered on-topic for this site.
– Eike Pierstorff
Nov 7 at 12:43
add a comment |
I would be curious to see this answered, but I don't think it falls into the realm of "specific programming problems" that are considered on-topic for this site.
– Eike Pierstorff
Nov 7 at 12:43
I would be curious to see this answered, but I don't think it falls into the realm of "specific programming problems" that are considered on-topic for this site.
– Eike Pierstorff
Nov 7 at 12:43
I would be curious to see this answered, but I don't think it falls into the realm of "specific programming problems" that are considered on-topic for this site.
– Eike Pierstorff
Nov 7 at 12:43
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
The result of SUM(totals.newVisits) in bigquery is exactly the same as the new user count reported in Google Analytics Audience report for a day in my exported data that has users marked as new visitors eventhough they have visited our site earlier. I therefore conclude that google analytics also uses the same lookback window.
I found that in order to count new users depending on their actual first visit (cookie creation date) it's possible to use the last part of the client id. As an example, this query finds the number of new users for 20181025:
#StandardSQL
SELECT SUM(CASE WHEN cookie_date = '2018-10-25' THEN 1 ELSE 0 END) AS new_visitors,
count(*) AS all_visitors
FROM (SELECT clientId,
DATE(TIMESTAMP_ADD("1970-01-01 00:00:00 UTC", INTERVAL min(CAST(REGEXP_EXTRACT(clientId, r'[0-9]*$') AS INT64)) SECOND), "Europe/Berlin") as
cookie_date
FROM `xxx.ga_sessions_20181025`
GROUP BY clientId)
answered Nov 7 at 14:48
Ichta
956
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
The result of SUM(totals.newVisits) in bigquery is exactly the same as the new user count reported in Google Analytics Audience report for a day in my exported data that has users marked as new visitors eventhough they have visited our site earlier. I therefore conclude that google analytics also uses the same lookback window.
I found that in order to count new users depending on their actual first visit (cookie creation date) it's possible to use the last part of the client id. As an example, this query finds the number of new users for 20181025:
#StandardSQL
SELECT SUM(CASE WHEN cookie_date = '2018-10-25' THEN 1 ELSE 0 END) AS new_visitors,
count(*) AS all_visitors
FROM (SELECT clientId,
DATE(TIMESTAMP_ADD("1970-01-01 00:00:00 UTC", INTERVAL min(CAST(REGEXP_EXTRACT(clientId, r'[0-9]*$') AS INT64)) SECOND), "Europe/Berlin") as
cookie_date
FROM `xxx.ga_sessions_20181025`
GROUP BY clientId)
answered Nov 7 at 14:48
Ichta
956
add a comment |
up vote
1
down vote
accepted
The result of SUM(totals.newVisits) in bigquery is exactly the same as the new user count reported in Google Analytics Audience report for a day in my exported data that has users marked as new visitors eventhough they have visited our site earlier. I therefore conclude that google analytics also uses the same lookback window.
I found that in order to count new users depending on their actual first visit (cookie creation date) it's possible to use the last part of the client id. As an example, this query finds the number of new users for 20181025:
#StandardSQL
SELECT SUM(CASE WHEN cookie_date = '2018-10-25' THEN 1 ELSE 0 END) AS new_visitors,
count(*) AS all_visitors
FROM (SELECT clientId,
DATE(TIMESTAMP_ADD("1970-01-01 00:00:00 UTC", INTERVAL min(CAST(REGEXP_EXTRACT(clientId, r'[0-9]*$') AS INT64)) SECOND), "Europe/Berlin") as
cookie_date
FROM `xxx.ga_sessions_20181025`
GROUP BY clientId)
answered Nov 7 at 14:48
Ichta
956
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
The result of SUM(totals.newVisits) in bigquery is exactly the same as the new user count reported in Google Analytics Audience report for a day in my exported data that has users marked as new visitors eventhough they have visited our site earlier. I therefore conclude that google analytics also uses the same lookback window.
I found that in order to count new users depending on their actual first visit (cookie creation date) it's possible to use the last part of the client id. As an example, this query finds the number of new users for 20181025:
#StandardSQL
SELECT SUM(CASE WHEN cookie_date = '2018-10-25' THEN 1 ELSE 0 END) AS new_visitors,
count(*) AS all_visitors
FROM (SELECT clientId,
DATE(TIMESTAMP_ADD("1970-01-01 00:00:00 UTC", INTERVAL min(CAST(REGEXP_EXTRACT(clientId, r'[0-9]*$') AS INT64)) SECOND), "Europe/Berlin") as
cookie_date
FROM `xxx.ga_sessions_20181025`
GROUP BY clientId)
answered Nov 7 at 14:48
Ichta
956
The result of SUM(totals.newVisits) in bigquery is exactly the same as the new user count reported in Google Analytics Audience report for a day in my exported data that has users marked as new visitors eventhough they have visited our site earlier. I therefore conclude that google analytics also uses the same lookback window.
I found that in order to count new users depending on their actual first visit (cookie creation date) it's possible to use the last part of the client id. As an example, this query finds the number of new users for 20181025:
#StandardSQL
SELECT SUM(CASE WHEN cookie_date = '2018-10-25' THEN 1 ELSE 0 END) AS new_visitors,
count(*) AS all_visitors
FROM (SELECT clientId,
DATE(TIMESTAMP_ADD("1970-01-01 00:00:00 UTC", INTERVAL min(CAST(REGEXP_EXTRACT(clientId, r'[0-9]*$') AS INT64)) SECOND), "Europe/Berlin") as
cookie_date
FROM `xxx.ga_sessions_20181025`
GROUP BY clientId)
answered Nov 7 at 14:48
Ichta
956
answered Nov 7 at 14:48
Ichta
956
answered Nov 7 at 14:48
Ichta
956
answered Nov 7 at 14:48
Ichta
956
956
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53187173%2fis-google-analytics-new-user-affected-by-lookback-window%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I would be curious to see this answered, but I don't think it falls into the realm of "specific programming problems" that are considered on-topic for this site.
– Eike Pierstorff
Nov 7 at 12:43