3

I wanted to resurface this question since I couldn't find a good answer.

How do you match the dot-separated clientId (see link google analytics section) in Google Analytics to fields in BigQuery? I'd expect the clientId to be the fullVisitorId, but it isn't. The clientId is in the form 1234567890.9876543210 where the last ten digits are the visitId in BigQuery.

Community
  • 1
  • 1
Feynman27
  • 3,049
  • 6
  • 30
  • 39
  • Have you managed to get any further with this? I have this link https://www.cardinalpath.com/ga-basics-the-structure-of-cookie-values/ with say you should combine the clientId with the initial visit time to get the unique visitor id and then is @Pol F. comment is true you should just need to hash it... – Andy Long Aug 25 '16 at 16:34

1 Answers1

9

A while ago I asked the same question to the GAP support team and their answer was that the fullVisitorId in BigQuery is a hash form of Client ID, so the Client ID from the _ga cookie isn't represented directly in BigQuery.

In other words, a single Client ID will map to a single fullVisitorId, but you can't get the client ID in BigQuery (unless you capture it as a custom dimension).

Update:

Starting on May 24, 2018, Google began exporting the Google Analytics Client ID to BigQuery. The schema addition details are:

-------------------------------------------------------------------------
| Field Name | Type   | Description                                     |
-------------------------------------------------------------------------
| clientId   | STRING | Unhashed version of the Client ID for a given   |
|            |        | user associated with any given visit/session.   |
-------------------------------------------------------------------------
Pol Ferrando
  • 663
  • 4
  • 11
  • I see. Is there a way to apply the same hash function to the GA client ID so that I can map it to the corresponding BigQuery field -- as Google does? – Feynman27 Aug 17 '16 at 14:04
  • @Feyman27 I've been looking into the same issue and found that google have not documented how they hash the Client Id, we've tried a couple of techniques and ask quite a few other partner companies who work with GA about how this could be done and no one seems to know. So I think placing the Client ID in a Custom Dimension/Variable would be the way to go. – Andy Long Aug 26 '16 at 15:38