Understanding Customer Mobile App Journey Using Firebase Events and BigQuery.

As our customer base grows, we start seeing the importance of understanding our customer's journey inside our products. Before this, we rely heavily on Google Analytic Web to understand our customer journey. And then we noticed more and more of our new customers start to use our Mobile app over our Website to make their booking.

While Firebase itself did provide a visualization dashboard, it is pretty basic and could not tell us the flow between events and pages. For example, if we want to know if how many time a single customer goes back and forth between Vehicle Selection Page and Extra Service Page inside our mobile app, or how long that a user spends on a particular page.

Thus, to get the granular details of the customer events, we have to connect Firebase to Bigquery.

How to connect Firebase to Bigquery

You can basically refer to this straight-forward document, but if you having a hard time understanding it, here is the UI flow.

1. First, click on the ‘Project Setting’ inside Firebase.

2. Click on the ‘Integration’, find ‘BigQuery’, and click ‘Manage’.

3. Switch on Exported Integration, ‘Google Analytics’

And you are done! that's it.

Firebase needs around 24 hours before it starts sending data to BigQuery. To check if there Firebase Data is already there, go to the project name inside GCP that you link your Firebase account to.

Under your project name, try to find node call ‘analytic__XXXXX’,

All your daily firebase event can be found inside table called ‘events_(XX)’ (the number inside the parentheses represent total daily data that has been extracted from Firebase)

You can either choose any date you want for ‘event_(XX)’ table.

You can use SQL DML to get a certain date range that you want. For example, if you want to get the whole data for March 2021, you can simply add ‘*’ after the month inside your query or you can use table suffix.

Automatic Logged Event

Firebase has more than 20 default events tracked automatically for you. Examples of these events such as when the user first opens your app or when did they delete the app. These default events also really helpful for us to understand our customer behavior that uses our apps. For example, when did they first open our apps or when did they remove the app?. You can learn more about the events automatically collected by firebase here.

Custom Event

You can also create custom events to track additional user data and activities. For example for an on-demand logistic company like us, we can record what type of vehicle that the customer chooses even if they did not complete the booking, how many letters that the customer key in inside the pickup address form before they click the autocomplete option, to the price that the customer sees after choosing the vehicle type, location, and extra service that they need.

In TheLorry, our tech team is the one who is responsible for setting up the custom event (both on the website and mobile app) inside Firebase. While the Data team will do the heavy analysis based on the data extracted. You can refer to this guide if you are interested in setting up Firebase tracking in your mobile app or website.

Everything is good and ready, now let query our Firebase’s event inside BigQuery.

The table structure of Firebase Event

The event data from Firebase uses a nested structure. Users' data points such as user_params and event _params are nested within each event. So it is pretty common we need to flatten or create rows for each event. You can refer to this comprehensive article to understand more about the Firebase data structure inside Bigquery.

Understand User UI navigation Behaviour

We use custom events is to understand how mobile users navigate each of our mobile UI pages. The reason why? Custom events help us to answer questions such as:

1. What is our customer's typical booking flow journey?
2. Did they log in through email or using their social media account?
3. How long did they spend their time in a particular UI and why?
4. How many clicks they made in a particular UI

And so on.

Let simplify the use case for this article as we only like to understand how long did our users spend their time in a particular Mobile app UI and what sequence of event/pages that they have gone through. Nothing less nothing more. Our custom events for this case study can be categorized into 2.

1. UI_event_name — event that represents the UI page (one event can represent one UI page)
2. UI_running_time — the duration that the user spends on that particular UI page.

We will take the first 4 pages of our mobile app UI (1. UI Date & Time>> 2. UI addressSelection>>3. UI Map >> 4. UI Vehicle) and try to understand:

1. The date and time when a unique user lands on these UI pages.
2. The sequence of UI flow they make (are they going back and forth, or straight forward?).
3. How long did they spend on each UI page?

Now let's find out the total unique users and the average time that users spend in each UI event for the first day of January 2018. In BigQuery SQL editor,you can simply write:

select
event_name,
count(distinct user_pseudo_id) as total_users,
round(avg(CAST(e.value.string_value as FLOAT64)/1000),2) as avg_UI_running_time
FROM `thelorry-consumer-app-v3.analytics_XXXXXX.events_20180301`
unnest(event_params) as e
where e.key ='Extra_UIRunningTime'
group by 1
order by 2 desc

and here is the result

We can see that as the user going from the first UI page (UI_DateAndTime) to the last one (Vehicle Selection), there were drop off (user exit the app) happened which is pretty common in any conversion funnel, that is the first sign that our event tracking works properly.

A quick glimpse showed that on average Users spend a longer time on Address Selection Page and Vehicle Selection Page compare to the Data&Time Page.

Now let's pick some samples of our users and investigate their page flow. We want to find out if the customer journey between pages is straightforward in one flow or do they go back and forth between pages. We can use the query below.

select
user_pseudo_id,
event_date,
event_timestamp,
platform,
event_name,
round(CAST(e.value.string_value as FLOAT64)/1000,2) as UI_running_time -- convert into seconds
FROM `project_id.analytics_XXXXXX.events_20180101`,
unnest(event_params) as e
where e.key ='Extra_UIRunningTime'
and event_name i ('UI_DateAndTime','UI_AddressSelection')
order by 1,2,3

the result of this query will be:

So for our first user (pseudo_id = 0092e841546a825117858c8f03febafc), we can see how long that this user spend on each UI page. And we also found out that the user also went back and forth between the ‘UI_Map’ event and ‘UI_AddressSelection’ a couple of times.

And of course, this is just one user. When we have thousands of users, we can see more significant patterns and the statistical distribution of our users' behaviors.

For example, our users spend much longer time inside the ‘VehicleSelection’ Page compare to another UI page. Is it because the user having a hard time navigating the page or they just having deep thought on which vehicle to select?

Another example, we saw a lot of back-and-forth activities between the ‘UI_Map’ and ‘AddressSelection’ Page, is it because the user having a hard time pinpointing their pickup address, or is it because of something else?

With this kind of information, we could further raise further questions or develop another hypothesis altogether and then conduct further tests to understand our customers' behavior inside the product. Subsequently, would help us to iterate and improve our product.

You are not limited to analyze your data inside BigQuery. From here you can also connect to Data Studio.

To connect to data studio, we need to save our query as View inside BigQuery. View act as a virtual table based on the result-set of an SQL statement.

and name your View.

Also, you have to make sure that Your Data Studio has the access to BigQuery. You can refer to this documentation.

Finally, simply select your data source inside Big

Inside Data Studio, you can simply choose any visualization of your liking to represent your data.

I hope you benefit from my article, thank you for reading up to this point :)