While using BigQuery, the user_pseudo_id values are not consistent across different queries of same data. Is there something I'm missing?

user_13191938 0
Viewed 290

Title says it all. The user_pseudo_id value even completely change formatting. So strange. See the two different queries and example results respectively.

SELECT
  -- to count our events, we will use our event_name, and count USERS concatenated with a timestamp
  -- this gives us each event per user, or all events sorted by event, date, and user id
  user_pseudo_id,
  event_date,
  event_timestamp,
  event_name,
  ANY_VALUE(param.key) as Param_Key,
  ANY_VALUE(param.value.string_value) as Param_String_Value,
  ANY_VALUE(param.value.int_value) as Param_Int_Value,
  ANY_VALUE(param.value.double_value) as Param_Double_Value,
  ANY_VALUE(param.value.float_value) as Param_Float_Value
FROM
  `##PRIVACY FILLER##-platform.analytics_223493244.events_*`, UNNEST(event_params) as param -- modify to your project
WHERE
  _TABLE_SUFFIX BETWEEN '20210110' AND '20210111' AND (event_name="view_content" OR event_name = "view_streamboard_screen" OR event_name = "view_brand_detail")
GROUP BY 
1,
2,
3,
4

An example user_pseudo_id from the saved results looks like following:

enter image description here

The following is BigQuery query 2:


SELECT
  -- to count our events, we will use our event_name, and count USERS concatenated with a timestamp
  -- this gives us each event per user, or all events sorted by event, date, and user_id
  user_pseudo_id,
  event_date,
  event_name,
  ANYVALUE(param),
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'app_open' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS app_open,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'app_updatebrand_screen_on_share_press' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS brand_screen_on_share_press,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'app_view' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS app_view,
  COUNT(DISTINCT
    CASE
    WHEN event_name = 'app_update' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS app_update,
      --WHEN event_name = 'app_update' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS app_update
    COUNT(DISTINCT
    CASE
    WHEN event_name = 'app_update' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS app_update,
      --WHEN event_name = 'app_update' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS app_update
    COUNT(DISTINCT
    CASE
    WHEN event_name = 'brand_view_on_website_press' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS brand_view_on_website_press,
      --WHEN event_name = 'app_update' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS brand_view_on_website_press
    COUNT(DISTINCT
    CASE
    WHEN event_name = 'copy_content' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS copy_content,
      --WHEN event_name = 'app_update' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS copy_content
    COUNT(DISTINCT
    CASE
    WHEN event_name = 'copy_favorite_list' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS copy_favorite_list,
      --WHEN event_name = 'app_update' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS copy_favorite_list
    COUNT(DISTINCT
    CASE
    WHEN event_name = 'create_favorite_list' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS create_favorite_list,
      --WHEN event_name = 'app_update' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS create_favorite_list
    COUNT(DISTINCT
    CASE
    WHEN event_name = 'created_discussion' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS created_discussion,
      --WHEN event_name = 'app_update' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS created_discussion
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'discussion_new_message' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS discussion_new_message,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'dynamic_link_app_open' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS dynamic_link_app_open,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'dynamic_link_first_open' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS dynamic_link_first_open,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'edit_interests' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS edit_interests,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'edit_profile' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS edit_profile,
 COUNT(DISTINCT
    CASE
      WHEN event_name = 'edit_profile' AND param.key = "edit_avatar" AND param.value.int_value = 1 THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS edit_profile_avatar,
 COUNT(DISTINCT
    CASE
      WHEN event_name = 'edit_profile' AND param.key = "edit_baner" AND param.value.int_value = 1 THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS edit_profile_baner,
 COUNT(DISTINCT
    CASE
      WHEN event_name = 'edit_profile' AND param.key = "edit_blur" AND param.value.int_value = 1 THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS edit_profile_blur,
 COUNT(DISTINCT
    CASE
      WHEN event_name = 'edit_profile' AND param.key = "edit_name" THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS edit_profile_name,
 COUNT(DISTINCT
    CASE
      WHEN event_name = 'edit_profile' AND param.key = "edit_title" AND param.value.int_value = 1 THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS edit_profile_title,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'editorial_screen_on_share_press' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS editorial_screen_on_share_press,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'enter_login_screen' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS enter_login_screen,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'enter_sign_up_screen' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS enter_sign_up_screen,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'enter_sign_up_with_email_screen' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS enter_sign_up_with_email_screen,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'event_answer_quiz_card' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS event_answer_quiz_card,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'event_user_search' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS event_user_search,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'favorite_press' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS favorite_press,
 COUNT(DISTINCT
    CASE
      WHEN event_name = 'favorite_press' AND param.key = "card_type" AND (param.value.string_value = "product" OR param.value.string_value = "brand") THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS favorite_product_or_brand,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'first_open' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS first_open,
 COUNT(DISTINCT
    CASE
      WHEN event_name = 'unfollow' AND param.key = "type" AND param.value.string_value = "brand" THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS unfollow_brand,
 COUNT(DISTINCT
    CASE
      WHEN event_name = 'unfollow' AND param.key = "type" AND param.value.string_value != "brand" THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS unfollow_social,
 COUNT(DISTINCT
    CASE
      WHEN event_name = 'follow' AND param.key = "type" AND param.value.string_value = "brand" THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS follow_brand,
 COUNT(DISTINCT
    CASE
      WHEN event_name = 'follow' AND param.key = "type" AND param.value.string_value != "brand" THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS follow_social,
 COUNT(DISTINCT
    CASE
      WHEN event_name = 'invite_friend' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS invite_friend,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'join_discussion' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS join_discussion,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'login' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS login,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'os_update' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS os_update,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'press_content' AND param.key = "card_type" AND param.value.string_value != "product" THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS press_content,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'press_content' AND param.key = "card_type" AND param.value.string_value = "product" THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS press_content_product,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'press_instagramstory' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS press_instagramstory,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'product_buy_on_website' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS product_buy_on_website,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'product_screen_on_share_press' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS product_screen_on_share_press,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'screen_view' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS screen_view,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'session_start' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS session_start,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'setting_on_about_GH_click' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS setting_on_about_GH_click,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'setting_on_edit_interests_click' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS setting_on_edit_interests_click,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'setting_on_feed_back_click' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS setting_on_feed_back_click,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'setting_on_help_center_click' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS setting_on_help_center_click,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'setting_on_invite_a_friend_click' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS setting_on_invite_a_friend_click,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'setting_on_sign_out_click' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS setting_on_sign_out_click,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'setting_on_update_phone_click' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS setting_on_update_phone_click,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'share_content' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS share_content,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'share_favorite_list' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS share_favorite_list,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'sign_up' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS sign_up,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'sign_up' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS sign_out,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'streamboard_on_discussion_press' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS streamboard_on_discussion_press,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'streamboard_on_share_press' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS streamboard_on_share_press,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'ugc_content_submitted' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS ugc_content_submitted,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'unfavorite_press' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS unfavorite_press,
COUNT(DISTINCT
    CASE
      WHEN event_name = 'unfavorite_press' AND param.key = "card_type" AND (param.value.string_value = "product" OR param.value.string_value = "brand") THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS unfavorite_product_or_brand,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'user_profile_on_chat_press' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS user_profile_on_chat_press,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'view_brand_detail' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS view_brand_detail,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'view_content' AND param.key = "card_type" AND param.value.string_value != "product" THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS view_content,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'view_content' AND param.key = "card_type" AND param.value.string_value = "product" THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS view_content_product,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'view_curator_profile' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS view_curator_profile,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'view_editorial_screen' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS view_editorial_screen,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'view_favorites_screen' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS view_favorites_screen,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'view_product_detail' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS view_product_detail,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'view_streamboard_screen' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS view_streamboard_screen,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'view_user_profile' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING)) END ) AS view_user_profile


FROM
  `##PRIVACY FILLER##-platform.analytics_223493244.events_*`, UNNEST(event_params) as param -- modify to your project
WHERE
  _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY 
1,
2,
3

And the following are the results of this query:

enter image description here

So yeah. Hopefully you will see the difference in format from user_psuedo_id values is 9685 type compared to F3B6029A6C074CAD9F14D7A7F2697E24 type.

Does anyone understand the reason for this? I thought that Firebase's user_pseudo_id is a constant value.

firebase google-bigquery
1 Answers

I should've updated this earlier, but this was a mislabeling issue on my part and I was unable to recreate the issue.

There are the lengthier user_pseudo_id (for example F3B6029A6C074CAD9F14D7A7F2697E24) that Firebase will automatically assign to a user. This will identify the user unless the user reinstalls the app, then he/she will be assigned a new user_pseudo_id.

There is also a typically shorter user_id (for example 9685) which can be assigned to a user if the developers specifically set this. This example is from a module from an iOS app. This user id is typically used to track users and events in a company's database that is apart from anything stored by Google using Firebase and Google Analytics.

user_131919380
Related