Cannot aggregate Firebase notification events by custom analytics label?

Question

I am using the Cloud Messaging feature of FireBase and I need to analyze the notification data stored in BigQuery.

I have two tables in BigQuery:

• <project_name>.analytics_[0-9]*.event_* which contains all the events details [event_name, event_params, event_date, etc] like "notification_open" or "notification_closed", etc
• and leavoice.firebase_messaging.data table which contains columns like message_id, analytics_label, etc

From the first table, I am able to group by result based on event_name which gives me a good overview of the different notification

event_name	event_count
notification_receive	50000
notification_dismiss	30000
notification_open	3000
notification_foreground	600

From the second table I am able to get the following details:

event_timestamp TIMESTAMP NULLABLE
project_number INTEGER NULLABLE
message_id STRING NULLABLE
instance_id STRING NULLABLE
message_type STRING NULLABLE
sdk_platform STRING NULLABLE
app_name STRING NULLABLE
collapse_key STRING NULLABLE
priority INTEGER NULLABLE
ttl INTEGER NULLABLE
topic STRING NULLABLE
bulk_id INTEGER NULLABLE
device_recently_active BOOLEAN NULLABLE
event STRING NULLABLE
analytics_label

What I want is to drill down on each different event_name for example notification_closed based on the custom analytics_label from the second table, but I am not able to join these two relations. I thought message_id contained in event_params from the first table might be the same as the message_id in the second table but they are not.

Is there a way to analyze custom analytics labels for notifications with the different notification_event categories (notification_closed, notification_open, etc)?

Answer 1

So the analytics label is also coming in the events table as a label property in the event_params column, so there isn't any need to join the two tables in the first place.