Missing user_pseudo_id's & ga_session_id's in GA4 BigQuery table

[MrNelsch]

Hi all,

Since 2 days ago my company implemented a cookie window in our website.

Since then my GA4 BigQuery table contains events with nulls in both ga_session_id and user_psuedo_id. For all these events the privacy_info fields contain "no".  I just want to make sure if this is the correct behaviour for the GA4 datamodel to put those two values to null.

[oakinlaja]

Hello, I understand you are looking for some answers about your Google Analytics data Export to BigQuery via Firebase. I think your question may be better supported by the Google Analytics Community[0]. I could not find any such information[1] about the expected behavior of GA4 data model.

I encourage you to reach out to the Google Analytics community, I believe you will get more assistance there.

Thank you.

[0]https://support.google.com/analytics/community?hl=en

[1]https://support.google.com/firebase/answer/7029846?hl=en

[JovaniKing]

Were you able to figure this out? We are looking into our GA4 BigQuery table containing our events with nulls in both ga_session_id and user_psuedo_id. What preferences make this the case? We are trying to reduce the nulls if possible. Thanks!

[louise20]

Has anyone found a solution for this?

[JovaniKing]

I have not but still interested in solving the problem.

[louise20]

It seems challenging. One suggestion that has been given to me is to do our own modelling on the data so we can try replicate how the Google models the data for GA4 reports

[louise20]

I've created a new thread here as well https://support.google.com/analytics/thread/186986520?hl=en

[JovaniKing]

Denying Consent Mode and AD Blockers will no longer assign a proper value to user_pseudo_id or ga_session_id fields within the raw GA4 data export that we send into BigQuery.

Unfortunately, I've been continuing to roll up the data into null values and excluding them from analysis and reporting. I use the following SQL query to check daily.

SELECT
  TIMESTAMP(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'[0-9]+'))) session_date,
  ( SELECT value.int_value FROM UNNEST(events.event_params) WHERE key = "ga_session_id") ga_session_id,
  ( SELECT value.int_value FROM UNNEST(events.event_params) WHERE key = "ga_session_number") ga_session_number,
  events.user_pseudo_id,
  TIMESTAMP(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'[0-9]+')))||( SELECT value.int_value FROM UNNEST(events.event_params) WHERE key = "ga_session_id")||( SELECT value.int_value FROM UNNEST(events.event_params) WHERE key = "ga_session_number")||events.user_pseudo_id sl_key,
  COUNT(*)
FROM
  `X.Y.events_*` events
WHERE
  TIMESTAMP(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'[0-9]+'))) >= TIMESTAMP('2022-01-01 00:00:00')
GROUP BY
  1,
  2,
  3,
  4,
  5
ORDER BY
  6 DESC