How to retrieve a parameter from Array (Google Analytics table with hits in BigQuery)?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I am trying to get new table with EventInfo from my GA view such as EventAction, EventCategory and EventLabel. But I face the following problem
Cannot access field eventInfo on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>> at [2:6]
Even after trying to compose query as mentioned above, I am getting an error.
ARRAY>
Could you please let me know how to fix it?
sql google-bigquery
edited Jan 4 at 19:27
Mikhail Berlyant
63.8k44176
asked Jan 4 at 18:44
Elena EpshteinElena Epshtein
153
add a comment |
I am trying to get new table with EventInfo from my GA view such as EventAction, EventCategory and EventLabel. But I face the following problem
Cannot access field eventInfo on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>> at [2:6]
Even after trying to compose query as mentioned above, I am getting an error.
ARRAY>
Could you please let me know how to fix it?
sql google-bigquery
edited Jan 4 at 19:27
Mikhail Berlyant
63.8k44176
asked Jan 4 at 18:44
Elena EpshteinElena Epshtein
153
What error are you getting? And can you show us the query?
– mypetlion
Jan 4 at 19:24
add a comment |
I am trying to get new table with EventInfo from my GA view such as EventAction, EventCategory and EventLabel. But I face the following problem
Cannot access field eventInfo on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>> at [2:6]
Even after trying to compose query as mentioned above, I am getting an error.
ARRAY>
Could you please let me know how to fix it?
sql google-bigquery
edited Jan 4 at 19:27
Mikhail Berlyant
63.8k44176
asked Jan 4 at 18:44
Elena EpshteinElena Epshtein
153
I am trying to get new table with EventInfo from my GA view such as EventAction, EventCategory and EventLabel. But I face the following problem
Cannot access field eventInfo on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>> at [2:6]
Even after trying to compose query as mentioned above, I am getting an error.
ARRAY>
Could you please let me know how to fix it?
sql google-bigquery
sql google-bigquery
edited Jan 4 at 19:27
Mikhail Berlyant
63.8k44176
asked Jan 4 at 18:44
Elena EpshteinElena Epshtein
153
edited Jan 4 at 19:27
Mikhail Berlyant
63.8k44176
asked Jan 4 at 18:44
Elena EpshteinElena Epshtein
153
edited Jan 4 at 19:27
Mikhail Berlyant
63.8k44176
edited Jan 4 at 19:27
Mikhail Berlyant
63.8k44176
edited Jan 4 at 19:27
Mikhail Berlyant
63.8k44176
63.8k44176
asked Jan 4 at 18:44
Elena EpshteinElena Epshtein
153
asked Jan 4 at 18:44
Elena EpshteinElena Epshtein
153
asked Jan 4 at 18:44
Elena EpshteinElena Epshtein
153
153
What error are you getting? And can you show us the query?
– mypetlion
Jan 4 at 19:24
add a comment |
What error are you getting? And can you show us the query?
– mypetlion
Jan 4 at 19:24
What error are you getting? And can you show us the query?
– mypetlion
Jan 4 at 19:24
What error are you getting? And can you show us the query?
– mypetlion
Jan 4 at 19:24
add a comment |
1 Answer
1
active
oldest
votes
Below example for BigQuery Standard SQL
#standardSQL
SELECT visitId, visitNumber, hit.eventInfo.*
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
UNNEST(hits) hit
WHERE NOT eventInfo IS NULL
with result as something like below
visitId visitNumber eventCategory eventAction eventLabel eventValue
1501607501 1 Enhanced Ecommerce Quickview Click YouTube Men's Vintage Henley null
1501633019 1 Enhanced Ecommerce Quickview Click YouTube Custom Decals null
answered Jan 4 at 19:02
Mikhail BerlyantMikhail Berlyant
63.8k44176
Thank you very much for your help!
– Elena Epshtein
Jan 4 at 19: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%2f54044430%2fhow-to-retrieve-a-parameter-from-array-google-analytics-table-with-hits-in-bigq%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
Below example for BigQuery Standard SQL
#standardSQL
SELECT visitId, visitNumber, hit.eventInfo.*
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
UNNEST(hits) hit
WHERE NOT eventInfo IS NULL
with result as something like below
visitId visitNumber eventCategory eventAction eventLabel eventValue
1501607501 1 Enhanced Ecommerce Quickview Click YouTube Men's Vintage Henley null
1501633019 1 Enhanced Ecommerce Quickview Click YouTube Custom Decals null
answered Jan 4 at 19:02
Mikhail BerlyantMikhail Berlyant
63.8k44176
Thank you very much for your help!
– Elena Epshtein
Jan 4 at 19:27
add a comment |
Below example for BigQuery Standard SQL
#standardSQL
SELECT visitId, visitNumber, hit.eventInfo.*
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
UNNEST(hits) hit
WHERE NOT eventInfo IS NULL
with result as something like below
visitId visitNumber eventCategory eventAction eventLabel eventValue
1501607501 1 Enhanced Ecommerce Quickview Click YouTube Men's Vintage Henley null
1501633019 1 Enhanced Ecommerce Quickview Click YouTube Custom Decals null
answered Jan 4 at 19:02
Mikhail BerlyantMikhail Berlyant
63.8k44176
Thank you very much for your help!
– Elena Epshtein
Jan 4 at 19:27
add a comment |
Below example for BigQuery Standard SQL
#standardSQL
SELECT visitId, visitNumber, hit.eventInfo.*
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
UNNEST(hits) hit
WHERE NOT eventInfo IS NULL
with result as something like below
visitId visitNumber eventCategory eventAction eventLabel eventValue
1501607501 1 Enhanced Ecommerce Quickview Click YouTube Men's Vintage Henley null
1501633019 1 Enhanced Ecommerce Quickview Click YouTube Custom Decals null
answered Jan 4 at 19:02
Mikhail BerlyantMikhail Berlyant
63.8k44176
Below example for BigQuery Standard SQL
#standardSQL
SELECT visitId, visitNumber, hit.eventInfo.*
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
UNNEST(hits) hit
WHERE NOT eventInfo IS NULL
with result as something like below
visitId visitNumber eventCategory eventAction eventLabel eventValue
1501607501 1 Enhanced Ecommerce Quickview Click YouTube Men's Vintage Henley null
1501633019 1 Enhanced Ecommerce Quickview Click YouTube Custom Decals null
answered Jan 4 at 19:02
Mikhail BerlyantMikhail Berlyant
63.8k44176
answered Jan 4 at 19:02
Mikhail BerlyantMikhail Berlyant
63.8k44176
answered Jan 4 at 19:02
Mikhail BerlyantMikhail Berlyant
63.8k44176
answered Jan 4 at 19:02
Mikhail BerlyantMikhail Berlyant
63.8k44176
63.8k44176
Thank you very much for your help!
– Elena Epshtein
Jan 4 at 19:27
add a comment |
Thank you very much for your help!
– Elena Epshtein
Jan 4 at 19:27
Thank you very much for your help!
– Elena Epshtein
Jan 4 at 19:27
Thank you very much for your help!
– Elena Epshtein
Jan 4 at 19: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%2f54044430%2fhow-to-retrieve-a-parameter-from-array-google-analytics-table-with-hits-in-bigq%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
What error are you getting? And can you show us the query?
– mypetlion
Jan 4 at 19:24