Export Firebase/GA events data to Bigquery - things you need to pay attention to
Published: 2023-12-13 12:39:40

Who should read this article:
You are testing/publishing your application at low cost, need to quickly use firebase to collect event data, and need to analyze the data in BI or database


1. Looking at the relationship between firebase & GoogleAnalytics & Bigquery from the perspective of events data
- firebase sdk: collect events data
- GA (google analytics): A BI system that presets many dimensions, indicators, and dashboards for events data. Of course, you can also customize them; the events data reported by firebase is also stored in GA.
- bigquery: google cloud official database, which can store original event data for further backup or processing analysis


2. events data export
- Why export events data?
(1) Otherwise you can only view your data in GA. Personally, I think that the GA experience is very poor in most cases.
(2) You need to do more complex data processing and calculations, but GA only supports very limited custom dimension and indicator settings and does not have any complex data processing capabilities.
(3) You need to back up your historical data. The standard version of GA can only help you save data for a very limited period of time, a few months.

- Setting method
(1)GA console-->Navigation bar-->Management-->Product association-->bigquery association-->Select your project-->Data configuration-->Export-->Daily/Streaming
(2) Bigquery association is your only choice to obtain the original data of events. You will never find any other way through normal channels, I am very sure.
(3) The sooner the better: All data-related settings in GA will only take effect on data newly added after the setting operation is completed. If your application has been running for a while before it is associated with biguery, then you will have no way to get it. For the previous data, there is no need to look up information, post help in the community, or contact official support, because I have tried it all. Google Cloud has been this rule for so many years, and you can only accept the fact that you have lost this data.

- Choose daily export or streaming export

[BigqueryExport](https://support.google.com/analytics/answer/9358801?hl=zh-Hans&utm_id=ad) The official document gives a vague description, but there are many strange details that are not mentioned. Only those who have been hurt by him understand (like me), you need to know the following key points

(1) Streaming export: performed in real time (every few hours), the exported data will be temporarily stored in the events_intraday_* table of your project data set in bigquery. This table will be saved for up to 7 days (sometimes 8 days, I don't know why) data. Theoretically, this method does not limit the number of logs you can report every day.

(2) Daily export: once a day at 24:00. In order to prevent the loss of delayed data reporting, there is actually a 3-day buffer period. The table of one day may be updated in the next 3 days (unless there is no delayed data reporting) ), the data exported every day is saved in the events_* table. This method is limited to exporting only 1 million logs per day (unless you upgrade to GA360). When the number of your logs exceeds this limit (very easy, it only takes 10,000 or even a few thousand DAU to achieve), Google will advance a few days. It will send you a warning email every day, asking you to reduce the number of logs (only by publishing or exporting only part of the logs) or upgrade to GA360 (very expensive, seems to be 150,000 US dollars per year), you must deal with it, otherwise it will stop exporting every day. A possible side effect is that streaming export will also be affected (in my project, the streaming export was blocked for 5 days and could not be completed), but the Google documentation will not tell you this terrible side effect.

(3) The relationship between events_intraday_* and events_*: You may think that these are two different export methods that are not related to each other, then you are totally wrong. The data of events_* actually has two parts, one part is truly complete The other part of the exported date data just "seems" to already exist. In fact, the events_* table refers to the data in events_intraday_*. For example: events_intraday_* contains January 3, January 4, and January 5. The data in the events_* table seems to have data from January 1st to January 4th, but in fact it only has data from January 1st and January 2nd. If you delete all the data in events_intraday_*, You will find that the data from January 3rd to 5th in the events_* table has automatically disappeared. On the other hand, if you query the events_* table in the report, you will find that there is data from January 5th in it, even if bigquery The table information is not displayed, which further proves that when there is data for a certain day in events_intraday_*, the events_* table actually refers to the data of this day, and events_* does not actually copy the data of this day. So when you want to delete the data in events_intraday_*, be sure to back it up carefully, because bigquery does not help you save them to events_* at all. Bigquery table deletion operations are irreversible. I think Google didn't mention this hidden logic at all until we lost data as a result.

(4) Do not enable daily export! Do not enable daily export! Do not enable daily export! This choice will not bring you any benefits, it will only make you suffer from the limit issue. The best solution is to only enable streaming export, set up scheduled tasks in bigquery, and back up the incremental data in event_intraday_* to other data tables every day. This way you don’t have to worry about quotas or the expensive GA360 fees. , only need to pay a minimal fee for BigQuery storage, writing and querying


3. It is not recommended to use GA Kanban

(1) Only supports some simple dimension and indicator definitions

(1) If you must use it, you'd better set as many custom dimensions and indicators that may be used in the future in the GA console as possible before starting the project, otherwise you will not be able to see any results before the date you set. , custom dimensions and indicators are only calculated on incremental data


The above is my experience in using firebase and bigquery. I hope it can help you!