Connecting Google Analytics clientId to BigQuery fields

Question

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.

Answer 1

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.   |
-------------------------------------------------------------------------