Getting Number Of Downloads Of Android and IOS and Distinct Unique Users

Question

I am using the below query to get the number of downloads for IOS and Android using Firebase,

SELECT
    COUNT(DISTINCT user_pseudo_id)
    , app_info.firebase_app_id
FROM
    `xxx.analytics_xxx.events_*`
WHERE
    event_name = "first_open" 
    AND (app_info.firebase_app_id = "1:xxx:android:xxx" OR app_info.firebase_app_id = "1:xxx:ios:xxxx")
GROUP BY app_info.firebase_app_id

If I remove event_name = "first_open" then the count is difference (around 200-300). So, why distinct unqiue user count is different using first_open and not using first_open?

Answer 1

Assuming the count is higher when you remove the WHERE event_name = "first_open" clause. Your BigQuery export most likely does not contain the first_open event for those users. Perhaps the BigQuery export was enabled after the users had already installed and opened the app.

You can try the following simplified query to provide similar results:

SELECT
    device.operating_system,
    COUNT(DISTINCT user_pseudo_id) AS user_count
FROM `xxx.analytics_xxx.events_*`
GROUP BY 1