How is user_first_touch_timestamp different from first_open_time?

Question

Does "first" mean first in this run of the app (until the app terminates and restarts), or first across runs?

I thought that these fields will have only one value, but they often have two. When I run this query:

SELECT
  user_pseudo_id,
  COUNT(*) AS the_count
FROM (
  SELECT
    DISTINCT user_pseudo_id,
    user_first_touch_timestamp AS user_first_touch_timestamp
  FROM
    `noctacam.<my project>.events*`
  WHERE
    app_info.id = "<my bundle ID>"
  ORDER BY
    user_pseudo_id)
GROUP BY
  user_pseudo_id
ORDER BY
  the_count DESC

I find that 0.6% of my users have two different values for user_first_touch_timestamp. Is this a bug in Firebase?

Likewise for first_open_time:

SELECT
  user_pseudo_id,
  COUNT(*) AS the_count
FROM (
  SELECT
    DISTINCT user_pseudo_id,
    user_properties.value.int_value AS first_open_time
  FROM
    `noctacam.<my project>.events*`,
    UNNEST(user_properties) AS user_properties
  WHERE
    app_info.id = "<my bundle ID>"
    AND user_properties.key = "first_open_time"
  ORDER BY
    user_pseudo_id)
GROUP BY
  user_pseudo_id
ORDER BY
  the_count DESC

Exactly the same 0.6% of users have two different values for this field, too.

References: https://support.google.com/firebase/answer/7029846?hl=en https://support.google.com/firebase/answer/6317486?hl=en

Answer 1

I started wondering about the difference in these 2 params too and found this difference.

From User Properties:

First Open Time - The time (in milliseconds, UTC) at which the user first opened the app, rounded up to the next hour.

From BigQuery Export Schema:

user_first_touch_timestamp - The time (in microseconds) at which the user first opened the app.

In my case, the rounding was the difference. I envision that Firebase needed to have first_open_time as a User Property for some reason so they just rounded and copied user_first_touch_timestamp.

I know it still doesn't answer your whole question and doesn't explain why 0.6% of your users have 2 different values. I still thought that this may help someone here.