Can't match GA advanced segment and BigQuery results
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I'm using a simple GA advanced segment: 
And it returns 10,440 sessions in GA interface for a single day.
When I try to do something similar in BigQuery using Standard SQL I get different results:
SELECT fullVisitorId, visitStartTime
FROM `project.id.ga_sessions_20181001`
where EXISTS(
SELECT 1 FROM UNNEST(hits) h
WHERE h.type = 'PAGE'
AND REGEXP_CONTAINS(page.pagePath, r'/signup/.*'));
This returns 10,137 sessions for the same day. And this:
SELECT fullVisitorId, visitStartTime
FROM `project.id.ga_sessions_20181001`
where EXISTS(
SELECT 1 FROM UNNEST(hits) h
WHERE AND REGEXP_CONTAINS(page.pagePath, r'/signup/.*'));
Returns 10,564 sessions for the same day. How to get them to match the GA interface?
google-analytics google-bigquery
asked Nov 15 '18 at 14:14
maggietomaggieto
50335
add a comment |
I'm using a simple GA advanced segment:
And it returns 10,440 sessions in GA interface for a single day.
When I try to do something similar in BigQuery using Standard SQL I get different results:
SELECT fullVisitorId, visitStartTime
FROM `project.id.ga_sessions_20181001`
where EXISTS(
SELECT 1 FROM UNNEST(hits) h
WHERE h.type = 'PAGE'
AND REGEXP_CONTAINS(page.pagePath, r'/signup/.*'));
This returns 10,137 sessions for the same day. And this:
SELECT fullVisitorId, visitStartTime
FROM `project.id.ga_sessions_20181001`
where EXISTS(
SELECT 1 FROM UNNEST(hits) h
WHERE AND REGEXP_CONTAINS(page.pagePath, r'/signup/.*'));
Returns 10,564 sessions for the same day. How to get them to match the GA interface?
google-analytics google-bigquery
asked Nov 15 '18 at 14:14
maggietomaggieto
50335
Don't you need to count the distinct number offullVisitorId? There have been some past similar questions if you do a bit of searching, too, which may be useful.
– Elliott Brossard
Nov 15 '18 at 14:20
Thanks, but I'm interested in sessions, not users. Why should I count the users?
– maggieto
Nov 15 '18 at 15:09
stackoverflow.com/questions/30419771/… hth!
– Martin Weitzmann
Nov 16 '18 at 7:57
add a comment |
I'm using a simple GA advanced segment:
And it returns 10,440 sessions in GA interface for a single day.
When I try to do something similar in BigQuery using Standard SQL I get different results:
SELECT fullVisitorId, visitStartTime
FROM `project.id.ga_sessions_20181001`
where EXISTS(
SELECT 1 FROM UNNEST(hits) h
WHERE h.type = 'PAGE'
AND REGEXP_CONTAINS(page.pagePath, r'/signup/.*'));
This returns 10,137 sessions for the same day. And this:
SELECT fullVisitorId, visitStartTime
FROM `project.id.ga_sessions_20181001`
where EXISTS(
SELECT 1 FROM UNNEST(hits) h
WHERE AND REGEXP_CONTAINS(page.pagePath, r'/signup/.*'));
Returns 10,564 sessions for the same day. How to get them to match the GA interface?
google-analytics google-bigquery
asked Nov 15 '18 at 14:14
maggietomaggieto
50335
I'm using a simple GA advanced segment:
And it returns 10,440 sessions in GA interface for a single day.
When I try to do something similar in BigQuery using Standard SQL I get different results:
SELECT fullVisitorId, visitStartTime
FROM `project.id.ga_sessions_20181001`
where EXISTS(
SELECT 1 FROM UNNEST(hits) h
WHERE h.type = 'PAGE'
AND REGEXP_CONTAINS(page.pagePath, r'/signup/.*'));
This returns 10,137 sessions for the same day. And this:
SELECT fullVisitorId, visitStartTime
FROM `project.id.ga_sessions_20181001`
where EXISTS(
SELECT 1 FROM UNNEST(hits) h
WHERE AND REGEXP_CONTAINS(page.pagePath, r'/signup/.*'));
Returns 10,564 sessions for the same day. How to get them to match the GA interface?
google-analytics google-bigquery
google-analytics google-bigquery
asked Nov 15 '18 at 14:14
maggietomaggieto
50335
asked Nov 15 '18 at 14:14
maggietomaggieto
50335
asked Nov 15 '18 at 14:14
maggietomaggieto
50335
asked Nov 15 '18 at 14:14
maggietomaggieto
50335
asked Nov 15 '18 at 14:14
maggietomaggieto
50335
50335
Don't you need to count the distinct number offullVisitorId? There have been some past similar questions if you do a bit of searching, too, which may be useful.
– Elliott Brossard
Nov 15 '18 at 14:20
Thanks, but I'm interested in sessions, not users. Why should I count the users?
– maggieto
Nov 15 '18 at 15:09
stackoverflow.com/questions/30419771/… hth!
– Martin Weitzmann
Nov 16 '18 at 7:57
add a comment |
Don't you need to count the distinct number offullVisitorId? There have been some past similar questions if you do a bit of searching, too, which may be useful.
– Elliott Brossard
Nov 15 '18 at 14:20
Thanks, but I'm interested in sessions, not users. Why should I count the users?
– maggieto
Nov 15 '18 at 15:09
stackoverflow.com/questions/30419771/… hth!
– Martin Weitzmann
Nov 16 '18 at 7:57
Don't you need to count the distinct number of
fullVisitorId? There have been some past similar questions if you do a bit of searching, too, which may be useful.– Elliott Brossard
Nov 15 '18 at 14:20
Don't you need to count the distinct number of
fullVisitorId? There have been some past similar questions if you do a bit of searching, too, which may be useful.– Elliott Brossard
Nov 15 '18 at 14:20
Thanks, but I'm interested in sessions, not users. Why should I count the users?
– maggieto
Nov 15 '18 at 15:09
Thanks, but I'm interested in sessions, not users. Why should I count the users?
– maggieto
Nov 15 '18 at 15:09
stackoverflow.com/questions/30419771/… hth!
– Martin Weitzmann
Nov 16 '18 at 7:57
stackoverflow.com/questions/30419771/… hth!
– Martin Weitzmann
Nov 16 '18 at 7:57
add a comment |
1 Answer
1
active
oldest
votes
Sessions as a metric in GA and BigQuery is a pre-aggregated metric to allow for faster processing. To match GA, you need to use SUM(totals.visits), rather than the COUNT(DISTINCT CONCAT(fullVisitorId,CAST(visitId as STRING))) . Visits to your site where a user only triggers non-interaction events (and no pageviews) will result in a visitId and a record in BigQuery with a totals.visits value of zero, but can be queried and reported on using segments for users, or Unique Dimension Combinations.
BigQuery Query View:
Google Analytics Dasboard View:
answered Nov 15 '18 at 21:50
tombtomb
35528
Thanks so much for your explanation! Any idea why segment condition event category = User login and event action matches RegEx [0-9] won't match EXISTS(SELECT 1 FROM UNNEST(hits) h WHERE eventInfo.eventCategory = 'User login' AND REGEXP_CONTAINS(eventInfo.eventAction, r'^[0-9]') )
– maggieto
Nov 16 '18 at 12:31
Hi, sorry about the delay and thanks for accepting my answer. I'd imagine the BigQuery result will be counting total events, where the other is going to count visits that include the event. I can help debug if you still haven't figured this out?
– tomb
Jan 14 at 2:28
It seems combining event category and event action in advanced segment as condition and as sequence returns different results. I managed to match the sequence one with the BQ query, but can't match the condition one.
– maggieto
Jan 15 at 9:27
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
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%2f53321389%2fcant-match-ga-advanced-segment-and-bigquery-results%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sessions as a metric in GA and BigQuery is a pre-aggregated metric to allow for faster processing. To match GA, you need to use SUM(totals.visits), rather than the COUNT(DISTINCT CONCAT(fullVisitorId,CAST(visitId as STRING))) . Visits to your site where a user only triggers non-interaction events (and no pageviews) will result in a visitId and a record in BigQuery with a totals.visits value of zero, but can be queried and reported on using segments for users, or Unique Dimension Combinations.
BigQuery Query View:
Google Analytics Dasboard View:
answered Nov 15 '18 at 21:50
tombtomb
35528
Thanks so much for your explanation! Any idea why segment condition event category = User login and event action matches RegEx [0-9] won't match EXISTS(SELECT 1 FROM UNNEST(hits) h WHERE eventInfo.eventCategory = 'User login' AND REGEXP_CONTAINS(eventInfo.eventAction, r'^[0-9]') )
– maggieto
Nov 16 '18 at 12:31
Hi, sorry about the delay and thanks for accepting my answer. I'd imagine the BigQuery result will be counting total events, where the other is going to count visits that include the event. I can help debug if you still haven't figured this out?
– tomb
Jan 14 at 2:28
It seems combining event category and event action in advanced segment as condition and as sequence returns different results. I managed to match the sequence one with the BQ query, but can't match the condition one.
– maggieto
Jan 15 at 9:27
add a comment |
Sessions as a metric in GA and BigQuery is a pre-aggregated metric to allow for faster processing. To match GA, you need to use SUM(totals.visits), rather than the COUNT(DISTINCT CONCAT(fullVisitorId,CAST(visitId as STRING))) . Visits to your site where a user only triggers non-interaction events (and no pageviews) will result in a visitId and a record in BigQuery with a totals.visits value of zero, but can be queried and reported on using segments for users, or Unique Dimension Combinations.
BigQuery Query View:
Google Analytics Dasboard View:
answered Nov 15 '18 at 21:50
tombtomb
35528
Thanks so much for your explanation! Any idea why segment condition event category = User login and event action matches RegEx [0-9] won't match EXISTS(SELECT 1 FROM UNNEST(hits) h WHERE eventInfo.eventCategory = 'User login' AND REGEXP_CONTAINS(eventInfo.eventAction, r'^[0-9]') )
– maggieto
Nov 16 '18 at 12:31
Hi, sorry about the delay and thanks for accepting my answer. I'd imagine the BigQuery result will be counting total events, where the other is going to count visits that include the event. I can help debug if you still haven't figured this out?
– tomb
Jan 14 at 2:28
It seems combining event category and event action in advanced segment as condition and as sequence returns different results. I managed to match the sequence one with the BQ query, but can't match the condition one.
– maggieto
Jan 15 at 9:27
add a comment |
Sessions as a metric in GA and BigQuery is a pre-aggregated metric to allow for faster processing. To match GA, you need to use SUM(totals.visits), rather than the COUNT(DISTINCT CONCAT(fullVisitorId,CAST(visitId as STRING))) . Visits to your site where a user only triggers non-interaction events (and no pageviews) will result in a visitId and a record in BigQuery with a totals.visits value of zero, but can be queried and reported on using segments for users, or Unique Dimension Combinations.
BigQuery Query View:
Google Analytics Dasboard View:
answered Nov 15 '18 at 21:50
tombtomb
35528
Sessions as a metric in GA and BigQuery is a pre-aggregated metric to allow for faster processing. To match GA, you need to use SUM(totals.visits), rather than the COUNT(DISTINCT CONCAT(fullVisitorId,CAST(visitId as STRING))) . Visits to your site where a user only triggers non-interaction events (and no pageviews) will result in a visitId and a record in BigQuery with a totals.visits value of zero, but can be queried and reported on using segments for users, or Unique Dimension Combinations.
BigQuery Query View:
Google Analytics Dasboard View:
answered Nov 15 '18 at 21:50
tombtomb
35528
answered Nov 15 '18 at 21:50
tombtomb
35528
answered Nov 15 '18 at 21:50
tombtomb
35528
answered Nov 15 '18 at 21:50
tombtomb
35528
35528
Thanks so much for your explanation! Any idea why segment condition event category = User login and event action matches RegEx [0-9] won't match EXISTS(SELECT 1 FROM UNNEST(hits) h WHERE eventInfo.eventCategory = 'User login' AND REGEXP_CONTAINS(eventInfo.eventAction, r'^[0-9]') )
– maggieto
Nov 16 '18 at 12:31
Hi, sorry about the delay and thanks for accepting my answer. I'd imagine the BigQuery result will be counting total events, where the other is going to count visits that include the event. I can help debug if you still haven't figured this out?
– tomb
Jan 14 at 2:28
It seems combining event category and event action in advanced segment as condition and as sequence returns different results. I managed to match the sequence one with the BQ query, but can't match the condition one.
– maggieto
Jan 15 at 9:27
add a comment |
Thanks so much for your explanation! Any idea why segment condition event category = User login and event action matches RegEx [0-9] won't match EXISTS(SELECT 1 FROM UNNEST(hits) h WHERE eventInfo.eventCategory = 'User login' AND REGEXP_CONTAINS(eventInfo.eventAction, r'^[0-9]') )
– maggieto
Nov 16 '18 at 12:31
Hi, sorry about the delay and thanks for accepting my answer. I'd imagine the BigQuery result will be counting total events, where the other is going to count visits that include the event. I can help debug if you still haven't figured this out?
– tomb
Jan 14 at 2:28
It seems combining event category and event action in advanced segment as condition and as sequence returns different results. I managed to match the sequence one with the BQ query, but can't match the condition one.
– maggieto
Jan 15 at 9:27
Thanks so much for your explanation! Any idea why segment condition event category = User login and event action matches RegEx [0-9] won't match EXISTS(SELECT 1 FROM UNNEST(hits) h WHERE eventInfo.eventCategory = 'User login' AND REGEXP_CONTAINS(eventInfo.eventAction, r'^[0-9]') )
– maggieto
Nov 16 '18 at 12:31
Thanks so much for your explanation! Any idea why segment condition event category = User login and event action matches RegEx [0-9] won't match EXISTS(SELECT 1 FROM UNNEST(hits) h WHERE eventInfo.eventCategory = 'User login' AND REGEXP_CONTAINS(eventInfo.eventAction, r'^[0-9]') )
– maggieto
Nov 16 '18 at 12:31
Hi, sorry about the delay and thanks for accepting my answer. I'd imagine the BigQuery result will be counting total events, where the other is going to count visits that include the event. I can help debug if you still haven't figured this out?
– tomb
Jan 14 at 2:28
Hi, sorry about the delay and thanks for accepting my answer. I'd imagine the BigQuery result will be counting total events, where the other is going to count visits that include the event. I can help debug if you still haven't figured this out?
– tomb
Jan 14 at 2:28
It seems combining event category and event action in advanced segment as condition and as sequence returns different results. I managed to match the sequence one with the BQ query, but can't match the condition one.
– maggieto
Jan 15 at 9:27
It seems combining event category and event action in advanced segment as condition and as sequence returns different results. I managed to match the sequence one with the BQ query, but can't match the condition one.
– maggieto
Jan 15 at 9:27
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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%2f53321389%2fcant-match-ga-advanced-segment-and-bigquery-results%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
Don't you need to count the distinct number of
fullVisitorId? There have been some past similar questions if you do a bit of searching, too, which may be useful.– Elliott Brossard
Nov 15 '18 at 14:20
Thanks, but I'm interested in sessions, not users. Why should I count the users?
– maggieto
Nov 15 '18 at 15:09
stackoverflow.com/questions/30419771/… hth!
– Martin Weitzmann
Nov 16 '18 at 7:57