Dynamic User Retention Using BigQuery (Firebase Project)

Oct 17 · 4 min read

If you are mobile app analyst, you might be really interested to know whether users are returning to app or are they uninstalling the app within the first few days. User retention refers to the ability of the mobile app to retain users.

This article is not about the What is user retention?, but about a way to get the user retention of a mobile app using BigQuery functions. And the advantages of this method are, we can measure the user retention for a given date range and drill down the user retention based on user properties.

Below section will provide the logic behind this method step by step, by the end of this article I will provide the complete query to get the first 7 day user retention.

Get the user who download the app (Step 1):

Query 1

See the query results…

Query result 1

Above query represent the users who download my app during the given time period. day_0 column contains the date when each user downloaded the app.

Get the users who engaged with the app (Step 2):

In this step I get the dates which users have engaged with app for a given date range.I have increase the date range by 7 days to make it more sensible for the users who downloaded the app on last date of the date range.

Query 2

See the query results for a single user…

Query result 2

Now I have the above data for every user.

For this users day_0 is the day of app downloaded, and day_1 will be engage column’s 2nd row and day_2 will be engage column’s 3rd row. Now we can take the difference of day_0 and engage columns, and that represent the gap between dates.

When difference is equal to 0 it represents the downloaded date, for the difference of 1 it represents the one day after the day_0, for the difference of 2 it represents the two day after the day_0 and so on…

Filter out the users who engaged for each day (Step 3):

Now I will get the day_0 and day_1 for each user,

Query 3

See the query results

Query result 3

Now we can get the users who engaged with the app on day_1, and we can get users who engaged on day_2, day_3 and further by changing the DATE DIFF function’s value of the above query.

Since we have all the users who engaged with the mobile app on any particular day, we can calculate the user count for each day.

As I explained the logic behind the dynamic user retention in the above section, here I have provided you with the full SQL query to get the dynamic user retention. I did some alteration to the query for better visualization and accessibility.

Final query

See the query results…

Query result final
User retention graph

Now we have results which we were waiting for, if you want to drill down this results further based on user properties, just need to add the relevant parameters to the query, also you can extend this for first 30 days or break down the results by weekly basis and get the user retention for the mobile app.

It is critical to define the user engagement with the correct event, here I have used the user_engagement event as the event, but it can be user login or performing a specific action. So it is up to you to define the meaningful user engagement for your mobile app to measure the user retention.

 by the author.


Written by

Product Analyst @ Reapdigital

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade