Identifying Unique Records in BigQuery GA4 Events Data

[James]

Sure, let’s take a look at these fields together. As stated in the Google Analytics Documentation:

For the user fields, these provide unique information about the user who is associated with the event. For example, we have ‘user_id’ which is the user ID set via the setUserId API and ‘user_pseudo_id’, which is the pseudonymous ID (like a specific app instance ID) referencing the user. Another interesting one is the ‘user_first_touch_timestamp’, which tells you the exact time (down to microseconds) when the user first opened the app or visited the site.

Now, let’s dive right into those event fields. These provide unique information for each specific event. ‘event_date’ marks the exact date (in YYYYMMDD format) when the event was logged in the registered timezone of your app, while ‘event_timestamp’ indicates the precise time (in microseconds, UTC) when the event was logged on the client. The ‘event_name’ – as you probably guessed – just indicates the name of the event.

A noteworthy field is ‘event_value_in_usd’ where you can find out the event’s “value” parameter, converted into USD. And then, there’s ‘event_bundle_sequence_id’ – this is the sequential ID of the bundle these events were part of when they were uploaded. Finally, ‘event_server_timestamp_offset’ marks the timestamp offset between the actual collection time and the upload time in microseconds.

I hope this clarifies a lot about the different fields!

[Oliver]

The combination of the fields event_name, event_timestamp, user_pseudo_id, and event_bundle_sequence_id are used to uniquely identify each record in the GA4 BigQuery event export table. However, it seems from your results that there are instances of multiple rows having the same values in these columns but different event parameters. What could be happening is that the event_params field, which is nested and JSON-formatted, may contain varying information across multiple events even if the identifying fields are the same. The assumption that related event_params values would always appear in one row with the same identifying record could be incorrect. There might be a mix-up of event_params data from different rows into one. A potential workaround for this could be to add a hash or fingerprint of the entire event_params object to the four mentioned fields, although there currently seems to be no surefire way of identifying unique records as of April 2023.