Key App Metrics with BigQuery Part 1: DAU/MAU
This post is Part 1 of a series inspired by the a16z podcast episode Metrics and Mindsets for Retention & Engagement. The episode covers the metrics that come after acquisition including engagement metrics like the DAU/MAU, and the power user curve (L7, L28). Inspired, I thought it would be a good idea to put some theory into practice using our Rainbow App.
For this article, I highly encourage listening to the podcast episode and reading this post by Andrew Chen before proceeding.
The Setup
Our app is a plant-based food journal. The hope is that users will engage with it on a daily basis and so the DAU/MAU would be a relevant metric for us.
The DAU/MAU will tell us what percentage of monthly users use our app on a daily basis. A perfect and impossible score here would be a DAU/MAU of 100%, but more importantly, we know that a strong sign of an app finding its product/market fit is a DAU/MAU of 40%.
Reviewing the history of our apps growth to date, in May of 2019 we had about 650 monthly active users (MAU) which grew to 2000 MAU by February of 2020.
Before we dive in, let’s make some educated guesses about our app using the Firebase analytics dashboard, our app rating and feedback as inputs.
- Hypothesis 1: Our app has not found product/market fit. Since our Week-1 retention is about 20%, this means 80% of users drop out after the first week. We can answer this by calculating the DAU/MAU.
- Hypothesis 2: There may be a handful of power users. This is based on enthusiastic app reviews and a 90-day retention rate of 9%. Let’s answer this by calculating the Power User Curve (in Part 2).
For Part 1 of our tutorial, let’s answer our first hypothesis: Our app has not found product/market fit. To do that I’ll use BigQuery to extract metrics from our event logs in Firebase. Note that I’ve already connected BigQuery to our Firebase project, which I don’t cover in this tutorial.
Calculating the DAU/MAU
In order to calculate the DAU/MAU, we use a rolling 30-day window to calculate the respective MAU value for a given DAU value. For example:
To calculate the DAU/MAU for Feb 29 2020:- First, calculate the DAU for Feb 29, 2020
- Second, calculate the MAU for Jan 31 2020 to Feb 29, 2020
- Finally, apply the math DAU/MAU
The below query calculates the daily DAU/MAU values for 30 days pivoting on a start date. In our case NDAYS=30 and STARTDATE=2020–03-01.
-- Special mention to Felipe Hoffa for inspiring and cleaning up this query
-- Replace the fields PROJECT and DATABASE
-- As well as the table name: 'events_202001*'DECLARE STARTDATE DATE DEFAULT '2020-03-01';
DECLARE NDAYS INT64 DEFAULT 30;WITH data AS (
SELECT * FROM (
SELECT *, PARSE_DATE('%Y%m%d', '2020'||_TABLE_SUFFIX) day, user_pseudo_id user_id
FROM `PROJECT.DATABASE.events_2020*`
) WHERE day <= STARTDATE
), daysN AS (
SELECT * FROM data
WHERE day >= DATE_ADD(STARTDATE, INTERVAL -NDAYS DAY)
), days60 AS (
SELECT * FROM data
WHERE day >= DATE_ADD(STARTDATE, INTERVAL -60 DAY)
), stopdays AS (
SELECT DISTINCT day stopday FROM data
WHERE day >= DATE_ADD(STARTDATE, INTERVAL -NDAYS DAY)
), maus AS (
SELECT stopday, COUNT(DISTINCT(user_pseudo_id)) mau
FROM days60 JOIN stopdays
ON day BETWEEN DATE_ADD(stopday, INTERVAL -NDAYS DAY) AND stopday
GROUP BY stopday
)
SELECT day, dau, mau, 100*dau/mau daumau
FROM (
SELECT day, COUNT(DISTINCT user_pseudo_id) dau
, (SELECT mau FROM maus WHERE a.day=maus.stopday) mau
FROM daysN a
GROUP BY day
)
ORDER BY dayResults
After averaging the days values into a single monthly metric we can plot it on a line graph, as shown below.
The result reveals a uniform distribution across the 9 months we have data. It also tells us that despite the volume of users growing over time, the app is not increasing in daily engagement. This makes sense because nothing fundamentally changed within the app during this period.
Take away
I’ve also not seen a 10% DAU/MAU product, through sheer effort, become 40% DAU/MAU. — Andrew Chen
Getting from a DAU/MAU of 9% to 40% will be an immense challenge. The next step from here is to develop strategies to increase daily retention rates. For our app we could consider these:
- Top of the funnel (quick win): If we brand our app storefront to target plant-based journal seekers, we can increase our daily engagement by being more targeted. At the moment there may be some ambiguity in our messaging as voiced by some users. This is a quick win but a short-term fix.
- Leverage power users (incremental improvements): Get feedback from our power users and A/B test new features optimizing daily engagement. This is what Andrew had warned us against. My instincts tell me incremental improvements will lead us down a dead end.
- Product pivot (big effort): Getting from 9% to 40% will likely require a product pivot. If we do the work to understand our power user cohort we may uncover a hidden use-case that leads to a successful pivot. The alternative is to appeal to the masses by becoming a calorie tracking app, and that doesn’t align with our mission.
Closing Thoughts
The data suggests that our original hypothesis was correct. The 2000 monthly-active user metric is more vanity than substance. That said the DAU/MAU value of 9% while not strong is our new baseline. We can aim to improve it from here.
In the next part of this series, I’ll cover the Power User Curve. For an early-stage app like ours, figuring out who our power users are will help us navigate to product/market fit 🤞. If we’re successful this should also result in a lift to our daily engagement metric.
Posts in this Series
- Key App Metrics with BigQuery Part 1: DAU/MAU (this post)
- Key App Metric with BigQuery Part 2: Power User Curve
Get in Touch!
References
- a16z — Metrics and Mindsets for Retention & Engagement
- Andrew Chen — DAU/MAU is an important metric to measure engagement, but here’s where it fails
- Felipe Hoffa on StackOverflow — DAU/MAU
- Google Support — Sample Queries
