Maximize Your Mobile Application Analytics using Firebase
If you are working with mobile applications, chances are you know one or two things about Firebase. For those of you who are not yet familiar with Firebase, it is essentially a collection of services that are mainly used in app development. I recommend reading this great article by Doug Stevenson:
Like any other Google products, Firebase has seamless integration with many other Google products such as Google Analytics, Google Ads, and BigQuery.
I am (well, was) a Data Analyst for a mobile application, and almost 100% of the data I work on a day-to-day basis comes from Firebase. When I first worked with the product, we only utilized 2 Firebase products for analytics. But now, after realizing how powerful Firebase is for mobile app analytics, we have been using data from 4 Firebase products in our analytics tech stack, with more to come.
Without further ado, let’s see what the products are!
Firebase Analytics
What is Firebase Analytics?
At the heart of Firebase is Firebase Analytics. Those of you who have used Google Analytics 4 might already know that Firebase Analytics uses the same event-based schema, where each event in-app is recorded as one row of data along with its many customizable parameters.
If you’re not familiar with event-based data and how to work with them, I suggest you read this article by Todd Kerpelman:
You don’t need any additional setups on Firebase to use this feature, as Firebase will collect the data automatically for you. These includes common metrics such as DAU/MAU to more specific metrics such as Engagement Time and User Retention.
Similar to Google Analytics, you can implement custom event tracking using Firebase Analytics to track a specific part/feature of your app.
How I use Firebase Analytics data as a Data Analyst
Use Case 1. Breakdown number of users by device and OS version — Firebase has already provided this data on the console. However, they use the device model name, instead of the more common marketing name — think Xiaomi Redmi Note 10S instead of M2003J158C. Using BigQuery, you can access the device.mobile_marketing_name field. FYI, the data on Firebase console use device.mobile_model_name instead.
This simple query is all you need to run
SELECT
PARSE_DATE('%Y%m%d',event_date) AS event_date,
CONCAT(
device.mobile_brand,
' ',
COALESCE(device.mobile_marketing_name, device.mobile_model_name)
) AS device,
COUNT(DISTINCT user_pseudo_id) AS total_user,
FROM `firebase-project.analytics_123456789.events_*`
GROUP BY 1,2Use Case 2. Implement custom event tracking for specific features —PMs will usually want to know how their newly-deployed feature fares against the users. A common method to gauge a new feature’s performance is to measure how many users are using it. Also, depending on the type of app your team is developing, you can also create a user journey funnel.
An interesting use case for custom event tracking using Firebase Analytics is to track whenever a user encounters an error page or basically whatever UI component (pop-up dialogs, toasters, etc.) the error is shown in. Why is this important you ask? Well, who wants to use an app that is constantly showing you “Sorry, an unexpected error happened” pages again and again?
Suppose that we have two important pages — Dashboard and Account Page, in which we want to track the errors. We might implement a custom event with the following specification:
- Event name is
error_show - Parameter
error_pagewhose value contains the current page - Parameter
error_codewhose value contains the error code
Using these 2 simple parameters is enough for us to run this query
SELECT
PARSE_DATE('%Y%m%d',event_date) AS event_date,
ep.value.string_value AS error_page,
ec.value.string_value AS error_code,
COUNT(*) AS total_occurences,
COUNT(DISTINCT user_pseudo_id) AS total_users,
FROM `firebase-project.analytics_123456789.events_*`
CROSS JOIN UNNEST(event_params) ep ON key = 'error_page'
CROSS JOIN UNNEST(event_params) ec ON key = 'error_code'
WHERE event_name = 'error_show'Or you can take it further and query this instead
WITH
error_session AS (
SELECT
PARSE_DATE('%Y%m%d',event_date) AS event_date,
CONCAT(s.value.int_value,user_pseudo_id) AS session_id,
ep.value.string_value AS error_page
FROM `firebase-project.analytics_123456789.events_*`
CROSS JOIN UNNEST(event_params) s ON key = 'ga_session_id'
CROSS JOIN UNNEST(event_params) ep ON key = 'error_page'
WHERE event_name = 'error_show'
),
all_session AS (
SELECT
PARSE_DATE('%Y%m%d',event_date) AS event_date,
CONCAT(s.value.int_value,user_pseudo_id) AS session_id,
fsc.value.string_value AS page_name
FROM `firebase-project.analytics_123456789.events_*`
CROSS JOIN UNNEST(event_params) s ON key = 'ga_session_id'
CROSS JOIN UNNEST(event_params) fsc ON key = 'firebase_screen_class'
WHERE event_name = 'screen_view'
)
SELECT
a.event_date,
a.page_name,
SAFE_DIVIDE(
COUNT(DISTINCT e.session_id),
COUNT(DISTINCT a.session_id)
) AS dirty_session
FROM all_session a
LEFT JOIN error_session e
ON a.event_date = e.event_date
AND a.page_name = e.error_page
AND a.session_id = e.session_idIn the above query, we use one of the many automatically collected events by Firebase Analytics, which is the screen_view event, to calculate dirty session which is the percentage of sessions of a specific page that contains at least one error occurrence. A little caveat here, is that you have to make sure that the value tracked on error_page parameter have to be the same as the value on firebase_screen_class parameter.
Firebase Cloud Messaging
What is Firebase Cloud Messaging?
Firebase Cloud Messaging (FCM) is mainly used to send notifications to the user’s app across platforms. Often, push notifications are sent to users with one of two goals in mind: information and engagement. In case of information, we want to make sure that the push notification is delivered and that the users have seen them. In case of engagement, we want them to act on the push notification after they received it.
How I use FCM data as a Data Analyst
Use Case 1. Track push notification funnel — FCM handles the sending of the notification, but it doesn’t guarantee that it will be delivered successfully to the recipient’s device (due to many factors such as inactive device, etc.). The low delivery rate might indicate that most of the targeted devices are inactive which means redundant effort.
We also might want to know the CTR (click-through rate) of varying types of push notifications. The same push notification worded differently might just be a more effective lead generator, resulting in more users clicking the push notification.
Let’s say that we’re building an e-commerce app, and we want to send a push notification to a predefined customer segment, that a Black Friday campaign is ongoing and they’re getting a discount coupon that they can use on their next purchase.
A Simple Funnel might be able to answer this question :
How many of our targeted push notifications are actually delivered to the users?
We can compute the delivery rate of our push notification using this query
WITH
sent AS (
SELECT
DATE(event_timestamp) AS event_date,
COUNT(DISTINCT CONCAT(message_id,instance_id)) AS total_sent
FROM `firebase-project.firebase_messaging.data`
WHERE
/* Filter for sent messages only */
event = 'MESSAGE_ACCEPTED'
/* Filter for the associated push notification only */
AND analytics_label = 'BLACK_FRIDAY'
GROUP BY 1
),
delivered AS (
SELECT
DATE(event_timestamp) AS event_date,
COUNT(DISTINCT CONCAT(message_id,instance_id)) AS total_delivered
FROM `firebase-project.firebase_messaging.data`
WHERE
/* Filter for delivered messages only */
event = 'MESSAGE_DELIVERED'
/* Filter for the associated push notification only */
AND analytics_label = 'BLACK_FRIDAY'
GROUP BY 1
)
SELECT
event_date,
total_sent,
total_delivered,
total_delivered / total_sent AS delivery_rate
FROM sent
FULL JOIN delivered
USING(event_date)You might notice that I’ve added a filter using the analytics_label field, which contains a custom value that we can add when we are sending the request to FCM API. More details about adding analytics label to your messages can be read here.
The Simple Funnel works fine, until the marketing team comes and asked us this question :
Is the push notification effective enough? How many users actually made a purchase because of the push notification?
This is a tricky question. We can never be sure that someone made a purchase because of the push notification. We can, however just make an assumption that if they use the discount coupon and is on the predefined customer segment group, then they made the purchase already aware of the push notification.
To answer the question above, we will create the Advanced Funnel. For the Advanced Funnel, we added 2 more funnel steps — Clicked and Converted. In the above case, Converted means the user have purchased a product using the discount coupon mentioned in the push notification.
However, FCM data only identifies a push notification up until the Delivered step. Luckily, we have our friend Firebase Analytics! Again we will use one of the automatically collected events which is the notification_open event, that we can use for our Clicked step.
For the Converted step, we will utilize a table taken from our transaction database that is already available on our BigQuery.
We can run another query to add the two funnel steps
WITH
sent AS (
SELECT
DATE(event_timestamp) AS event_date,
COUNT(DISTINCT CONCAT(message_id,instance_id)) AS total_sent
FROM `firebase-project.firebase_messaging.data`
WHERE
/* Filter for sent messages only */
event = 'MESSAGE_ACCEPTED'
/* Filter for the associated push notification only */
AND analytics_label = 'BLACK_FRIDAY'
GROUP BY 1
),
delivered AS (
SELECT
DATE(event_timestamp) AS event_date,
COUNT(DISTINCT CONCAT(message_id,instance_id)) AS total_delivered
FROM `firebase-project.firebase_messaging.data`
WHERE
/* Filter for delivered messages only */
event = 'MESSAGE_DELIVERED'
/* Filter for the associated push notification only */
AND analytics_label = 'BLACK_FRIDAY'
GROUP BY 1
)
clicked AS (
SELECT
PARSE_DATE('%Y%m%d',event_date) AS event_date,
COUNT(*) AS total_clicked
FROM `firebase-project.analytics_123456789.events_*`
CROSS JOIN UNNEST(event_params) p ON key = 'label'
WHERE
/* Filter for the notification_open event */
event_name = 'notification_open'
/* Filter for the associated push notification only */
p.value.string_value = 'BLACK_FRIDAY'
GROUP BY 1
),
converted AS (
SELECT
t.trx_date AS event_date,
COUNT(DISTINCT t.trx_id) AS total_converted
FROM `your-project.transaction.transaction_detail` AS t
LEFT JOIN `your-project.customer.customer_detail` AS c
USING(customer_id)
WHERE
/* Filter for the coupon code associated with the campaign only */
t.discount_coupon_code = 'BLKFRDY'
/* Filter for customer in the predefined segment only */
AND c.customer_segment = 'Predefined Segment - Black Friday'
)
SELECT
event_date,
total_sent,
total_delivered,
total_clicked,
total_converted,
total_delivered / total_sent AS delivery_rate,
total_clicked / total_delivered AS click_through_rate,
total_converted / total_clicked AS conversion_rate
FROM sent
FULL JOIN delivered
USING(event_date)
FULL JOIN clicked
USING(event_date)
FULL JOIN convereted
USING(even_date)The converted subquery part in the above query might be different in your case, but you got the point.
Use Case 2. Measure latency in push notification delivery — In some cases, we might want to deliver information to the users in an instant. I’ve heard many users complaining that they don’t receive any push notification when they receive an in-app message. Turns out it is delayed by several minutes. Measuring time to deliver push notification might help to identify potential problems.
We can use this query to measure overall latency in delivery time for each type of push notification.
WITH
sent AS (
SELECT
message_id,
instance_id,
analytics_label,
event_timestamp AS sent_timestamp
FROM `firebase-project.firebase_messaging.data`
WHERE event = 'MESSAGE_ACCEPTED'
),
delivered AS (
SELECT
message_id,
instance_id,
analytics_label,
event_timestamp AS delivered_timestamp
FROM `firebase-project.firebase_messaging.data`
WHERE event = 'MESSAGE_DELIVERED'
)
SELECT
analytics_label,
AVG(
TIMESTAMP_DIFF(delivered_timestamp,sent_timestamp,MILLISECOND)
) AS avg_latency_in_ms
FROM sent
FULL JOIN delivered
USING(message_id,instance_id)
GROUP BY 1So, that’s it folks!
But wait… you said it was 4 products, but you only shown 2 products! Well, find out in my next post!
