0

When counting total users in Firebase Console and Bigquery export for project, we have a massive difference in users count:

OS Firebase (Total Users) BigQuery (Users) Difference
Windows (Web) 38k 23k 39%
Android 21k 24k 15%
Ios 10k 12k 20%
other platforms (Web) 1,5k 1k ~50%
Total 70k 60k 15%

BigQuery query:

SELECT
  count(distinct user_pseudo_id),
  device.operating_system
FROM
  `{project}.events_*`
  where _TABLE_SUFFIX between "20210614" and "20210620"
GROUP BY
  device.operating_system;

I've read about Firebase approximation and HLL, but it shouldn't give that much difference?

Both platorms have filter on date, so it queries 1 week old data (14 june - 20 june).

Any suggestions, what can cause this difference and how to fix it?

useless dinosaur
  • 649
  • 10
  • 17
  • How are you adding the data to Firestore and Big Query? Are you sure the data is being updated din both the places? – Dharmaraj Jun 28 '21 at 08:21
  • @Dharmaraj It's Firebase Analytics, not Firestore. Firebase collects data from mobile apps and web, we use Analytics SDK. Then, we set up BigQuery integration like this: https://support.google.com/firebase/answer/6318765?hl=en#zippy=%2Cin-this-article – useless dinosaur Jun 28 '21 at 08:24
  • Sorry for mistyping that. Have you checked [this](https://stackoverflow.com/a/62652193/13130697) ? – Dharmaraj Jun 28 '21 at 08:52
  • @Dharmaraj yep. But I don't think this is the case, because BQ have somewhat similar numbers for Android and Web, but Firebase has totally different numbers, and this repeats in any time range – useless dinosaur Jun 28 '21 at 08:58

0 Answers0