1

Yesterday, when I ran the query,

SELECT
  Count(*)
FROM 
  `analytics_xxx.events_*`
 
WHERE 
  event_name = 'first_open'

It return 571 but today when I ran the same query it was 560. How come the count become less in one day. Is Firebase deleting the first_open event from the database?

Imran Qadir Baksh - Baloch
  • 32,612
  • 68
  • 179
  • 322
  • I suggest you to toggle Default Reporting Identity” from “By User-ID and service” to “By device only” and check whether the discrepancy still persists or not. Please refer to this [document](https://support.google.com/analytics/answer/9213390?hl=en#zippy=%2Cin-this-article) regarding how to toggle the configuration in detail. To me, this setting can just toggle a different view on the same dataset where one looks at device id and the other looks at user_ids (configured in the app), Google signals (if enabled), then device id. – Priyashree Bhadra Oct 29 '21 at 14:20
  • 1
    For clarification, that is NOT a Firebase query. It's a BigQuery query. There may be a duplicate question to this question [BigQuery to Data Studio discrepancy for event 'first_open'](https://stackoverflow.com/questions/58414296/bigquery-to-data-studio-discrepancy-for-event-first-open). Also see [this](https://stackoverflow.com/questions/38598819/firebase-analytics-first-open-from-google-analytics#38602085). There are a number of factors that can impact first_open so I am not sure this question is answerable. – Jay Oct 30 '21 at 13:14
  • @Jay I think you don't get the question. Let's say you have table which only allow insertion and you query the table daily. One day it returns 10 records and next day it returns 9 records. How come this possible if table is only allow insertion and no deletion. – Imran Qadir Baksh - Baloch Nov 01 '21 at 08:52
  • I do get the question, but the title states "Firebase" and your query is not a [Firebase Query](https://firebase.google.com/docs/firestore/query-data/queries) - additionally, the 'first_open' is not a Firebase *database* parameter either, it's analytics. Firebase database results will always be exact - the the same every time. Whereas it could be different with BigQuery. Please read through the links which explains *why* the results in BigQuery could vary due to reinstalling the app, updates etc. See things that affect [first-open](https://support.google.com/firebase/answer/9234069?hl=en) – Jay Nov 01 '21 at 17:03
  • @Jay do you have links that says count deducted because of reinstall? – Imran Qadir Baksh - Baloch Nov 02 '21 at 19:11
  • That's launching after re-installing. It's in the link I already provided in my above comment? *the first time a user launches an app after installing or re-installing it* – Jay Nov 02 '21 at 19:39

1 Answers1

1

From the SQL query you provided, this would return just the daily export tables ('events_*').Further, you have stated that you set the BQ table to allow appends manually, however in BQ if the default expiration is set, these tables would be removed as per this documentation.

I would ask you to try to query a specific time period that is known not to expire and monitor that. An example for a query like this could be;

SELECT
    Count(*)
FROM 
      `analytics_xxx.events_2021102*`
WHERE 
      event_name = 'first_open'

This should give a consistent number of results as these tables should fall within the default retention period. If you can identify a specific set of tables where there is a reduction in the count of records, even after this, we can look into it. Otherwise, given the broad scope of the selection ('events_*'), it is difficult to provide a definite answer however the table retention is the likely cause.

Have a look at how the exports follow this, in which it is mentioned how the tables are named.

Priyashree Bhadra
  • 3,182
  • 6
  • 23
  • Default table expiry is Never – Imran Qadir Baksh - Baloch Nov 02 '21 at 19:01
  • Priya, what else we can try to let know how the count become less? – Imran Qadir Baksh - Baloch Nov 02 '21 at 19:09
  • Given what you said on already setting this on all tables, you should not lose any record nor, based on the configuration, update a record (remember, the count is only for a specified field of data). Only appends should be allowed, so the count shouldn't change (i.e, the number of records with event_name = 'first_open'. Given the count is changing, this means that there are likely some records being updated. You would need to determine specific examples, i.e. which records are affected. Another extra thing is to count the number of records per table and see if this changes. – Priyashree Bhadra Nov 05 '21 at 08:51
  • You should point to a record, show how in one day this had that field, and then in the following day it is gone. As far as I am aware, it doesn't gets modified, but maybe there is a consolidation but am not sure. – Priyashree Bhadra Nov 05 '21 at 08:51
  • Hi Priya, you were right some tables were deleted because of table expiration of one of our project. Can you restore these expired table as we were not aware of the table expiration? – Imran Qadir Baksh - Baloch Nov 25 '21 at 06:41