Analytics
1.8K members online now
1.8K members online now
Dive into multiple domain (Cross/Sub) tracking, implementing Ecommerce and Enhanced Ecommerce, setting up Event tracking, and Universal Analytics code.
 
Guide Me
star_border
Reply

How to get User ID in BigQuery export?

Follower ✭ ✭ ☆
# 1
Follower ✭ ✭ ☆

I am migrating one of our properties to Universal Analytics and, as part of this, we have implemented the User ID feature. One of the views in this property is linked to BigQuery (has been for years), but it is not the one that displays the User ID information, since GA forces you to create a separate view for this.

 

Does anyone know if the User ID will start showing up in our BigQuery data? In spite of the view itself not showing User IDs in the web reporting interface.

 

Or will we have to link the User ID view to BigQuery instead? And, if so, will we stop getting all of the non-User ID data?

 

Ideally, I'd like to make sure that our BigQuery exported data has all of the data (with and without User IDs), but I understand only a single view can be linked per property...

 

Any insight will be really appreciated. Thanks.

1 Expert replyverified_user

How to get User ID in BigQuery export?

A T
Participant ✭ ✭ ✭
# 2
A T
Participant ✭ ✭ ✭

user id feature needs to be enabled in GA, for GA to shoe user ids.

How to get User ID in BigQuery export?

Follower ✭ ✭ ☆
# 3
Follower ✭ ✭ ☆

Yes, it is enabled. My question refers to BigQuery export: the view that is tied to the BigQuery export is not the one that has User ID. There is a separate view for that since GA forces you to use a different view. My question is: Will I have to change the view that is being exported to BigQuery in order to get User ID flowing? If so, will this mean none of the non-User ID data will get exported any more?

How to get User ID in BigQuery export?

A T
Participant ✭ ✭ ✭
# 4
A T
Participant ✭ ✭ ✭

I didn't do it actually. but one pointer below:

 

<<the user ID will only be available in BigQuery tables that are exported from user ID enabled views in Google Analytics.>> http://www.periscopix.co.uk/blog/google-analytics-user-id-in-bigquery/

 

 

How to get User ID in BigQuery export?

Follower ✭ ✭ ☆
# 5
Follower ✭ ✭ ☆

Thanks for the link :-)

 

This seems to imply that in BigQuery you can either get (a) only the User ID visits, or (b) only the non-User ID visits; but not both. I'm hoping this is not the case, since this is a huge problem and we need all visits in BigQuery.

 

I guess I could always send the User ID in a Custom Dimension as well, but this feels clunky...

 

Does anyone else reading this thread have first-hand experience with this issue?

How to get User ID in BigQuery export?

Top Contributor
# 6
Top Contributor

Hi Ralph Andrew C,

 

I don't have direct experience with this, but my thoughts are that you may need to either expose the client ID and set that into a custom dimension so that it would be available for use in BQ, or alternatively, as clunky as it may seem, set the User ID into a custom dimension (probably better to do both). It sounds like since the view you have linked to BQ isn't the User ID enabled view, then you won't be able to export those User IDs.

 

If you unlink your current view and link the User ID view, then you would no longer get the original view's data. Note also that if you try to relink it again, in the future, to BQ, you will lose the ability to auto-export the last 13 months of data. Historical view data is only exported the very first time it is linked.

 

If you have other questions, I would suggest you reach out to your reseller or Google, since you are a 360 client.

Nicky Yuen, Google Analytics Top Contributor
Was my response helpful? If yes, please mark it as the ‘Best Answer.’ Learn how here.
Find me on: Google+ | LinkedIn

Re: How to get User ID in BigQuery export?

Follower ✭ ✭ ☆
# 7
Follower ✭ ✭ ☆

Hi Nicky: Thanks a lot for the reply. I just sent test event with a User ID to the Property/View that is linked to BQ, so I will see how it shows up in the exported data tomorrow and report back.

 

Following up on your suggestions, I was thinking there are two options to "fixing" this:

 

  1. In the existing non-User ID view, add a new Custom Dimension that has the User ID for logged in users, so that I can query it in BQ.
  2. Or, In the User ID view, start setting anonymous User IDs for logged out clients (e.g. stuffing the GA Client ID into the User ID field). So, in essence, the User ID view will end up having all visits: those of logged out and logged in users.

 

I'm leaning towards Option #1, since #2 seems like an inappropriate usage of the User ID view. It's just painful that GA can't provide a single view for both identified and un-identified users. Right now, I might have the same person logging in on 3 devices, and they will show up as 3 Users (clients) in one view and 1 user in the other. What do you think?

 

Finally, why do you suggest sending the GA Client ID as a Custom Dimension as well? Not sure what I would use this for, since the BQ schema already has unique identifiers for anonymous users (fullVisitorId) and sessions (visitId).

 

Thanks again.

How to get User ID in BigQuery export?

Top Contributor
# 8
Top Contributor

Hi, Ralph,

 

I'm going to do a lot of thinking out loud here, so hopefully there's some random tidbit that might help you.

 

I normally like exposing the CID through a custom dimension as it gives me the option of being able to tie sessions and hits to a specific user. Also, even though BQ has the "fullVisitorId", it is a hashed version of the CID and there is *no way* to unhash that fullVisitorId if you want to reconcile GA and BQ data (something that many people have desired doing). So having the CID in both GA and BQ gives you that ability to tie things together. Similarly you could definitely do this with the User ID, but I guess the problem is what to do with those non-authenticated users. You mentioned using an anonymous User ID, but would that be unique per user as well (I'm thinking not). I think the CID might be a more flexible option because it's already generated for all users (authenticated and not).

 

Hope this helps a bit.

Nicky Yuen, Google Analytics Top Contributor
Was my response helpful? If yes, please mark it as the ‘Best Answer.’ Learn how here.
Find me on: Google+ | LinkedIn

Re: How to get User ID in BigQuery export?

Follower ✭ ✭ ☆
# 9
Follower ✭ ✭ ☆

Hi Nicky,

 

Thanks for your reply. I checked BigQuery today and, as expected, the "userId" column for my test event appears null since the view that is exported to BQ is the non-User ID view. So I'm thinking of adding the following new Custom Dimensions:

 

  • User ID: Set only when a user is logged in, but scoped to "User" so that if the user logs out, their subsequent hits are still scoped to them.
  • Client ID: Set for all visitors as a User-scope dimension.

 

Do you know how I can get the Client ID for GA in Google Tag Manager? And do you think the scoping of these dimensions is correct? Or should I consider Hit or Session scope instead?

 

Thanks again for your advice.

Re: How to get User ID in BigQuery export?

Top Contributor
# 10
Top Contributor

Hi Ralph,

 

Yes, the scopes sound correct. I normally add the CID to a hit-scoped CD as well so that you can get more granular in your reporting of the User ID.

 

You can get the client ID by several methods. I think currently the best way I've tried so far is by creating a custom JS variable like this:

 

function() {
return function(model) {
var t = ga.getAll().pop();
var id = t.get('clientId');

// Set client ID into CDs
t.set('dimensionX', id); // hit scoped /cd
t.set('dimensionY', id); // user scoped CD

window['dataLayer'].push({
'event': 'e_gaClientId',
'gacid': id
});
}
}

and then calling this as the value of the "previewTask" flag in GTM for a pageview tag (I've got my reasons for doing it this way which may not be applicable to you). (You can read more about the hit tasks here https://developers.google.com/analytics/devguides/collection/analyticsjs/tasks)

Nicky Yuen, Google Analytics Top Contributor
Was my response helpful? If yes, please mark it as the ‘Best Answer.’ Learn how here.
Find me on: Google+ | LinkedIn