SQL for Beginners: How to Calculate DAU, WAU, MAU

EN
How to count active users per day, week and month using SQL and what commands to use
SQL for Beginners: How to Calculate DAU, WAU, MAU
Published
29.07.2021
|
devtodev

In the previous article, we talked about what SQL is, what kind of specialists need it, and also tried the basic commands using a simple query as an example. Now let's calculate the user activity metrics.

Let’s say you have already released your project. Its audience will grow daily. The first time users install the app, some of them will continue to use it regularly or from time to time, and some will quickly lose interest. However, these user segments are considered active because they had at least one session in the app at a certain time. To assess the active audience of an app and analyze their requests, you will need metrics such as DAU, WAU, MAU. 

  • DAU (Daily Active Users) – the number of unique users per day;
  • WAU (Weekly Active Users) – the number of unique users per week;
  • MAU (Monthly Active Users) – the number of unique users per month.

Read more: Main Metrics. Active Users (DAU, WAU, MAU)

Let's use SQL to calculate the number of unique users in the last three months. Log in to your devtodev account and find the SQL Report in the demo project.

In devtodev, all app metrics are calculated automatically, also a huge variety of filters can be applied to them without using SQL.

DAU

We’ll try to calculate DAU for 3 in a row. iOS app (project ID p102968) using the sessions table. 

from p102968.sessions is the path to the table we’ll be working with. The command where eventtime> = current_date - interval '3 month' determines the time interval from which we’ll receive data.

Let’s convert the execution time of each event (eventtime) from this interval into a string using the to_char (eventtime, 'Month - dd') function. Here you can add any delimiters within the date, write the year in full 'YYYY' or only the last two digits 'YY', the month in lower case in abbreviated format 'mon', as a numeric designation 'mm' or the full 'Month'.

select to_char(eventtime, 'Month - dd') as day
, count(distinct devtodevid) as "DAU"   
from p102968.sessions
where eventtime>=current_date - interval '3 month' 
and eventtime < current_date  
and tester is false  
and cheater is false  
group by 1
order by 1 asc

How to count DAU
Query result. Screenshot from devtodev DEMO

Use count(distinct devtodevid), where devtodevid is the unique user ID, to count the number of unique users for each of these days.

devtodev pricing

MAU

We’ll use the same commands to calculate MAU, but replace eventtime, 'Month - dd' with eventtime, 'yyyy-mm' inside the to_char function, so the grouping will take place by month.

select to_char(eventtime, 'yyyy-mm') as month
, count(distinct devtodevid) as "MAU"   
from p102968.sessions
where eventtime>=current_date - interval '3 month' 
and eventtime < current_date  
and tester is false  
and cheater is false  
group by 1
order by 1 asc

How to count MAU
Query result. Screenshot from devtodev DEMO

In this case, you will probably get the result for 4 months, only two of which will reflect the real MAU. Let's say you make a request on June 10th. Then the farthest month (March) will show users only for the last 20 days of the month, and the current month will show only those who managed to complete sessions in the last 10 days. This can be easily checked by adding a count of unique days to select

count(distinct(to_char(eventtime, 'yyyy-mm-dd'))) as daysInMonth

Let's fix this by changing the conditions to get MAU for full months only.

where eventtime>=ut('2021-04-01')and date_trunc('month',current_date

The first part of the time limit eventtime> = ut ('2021-04-01') is set directly. We don’t recommend using such conditions in widgets and dashboards. It is much better to use floating dates depending on the current date or the time of the event. 

date_trunc ('month', current_date) rounds current_date to a month, leaving ‘2021-06-01 00:00:00.0’ as a result (if the request was made in June 2021). You can leave data only for full months by adding this function to where.

select to_char(eventtime, 'YYYY - mm') as month
, count(distinct devtodevid) as "MAU"   
from p102968.sessions
where eventtime>= date_trunc('month',current_date) - interval '2 month'
and eventtime < date_trunc('month',current_date)
and tester is false  
and cheater is false  
group by 1
order by 1 asc

How to count MAU
Query result. Screenshot from devtodev DEMO

The date_trunc function, along with the interval operator, can take completely different parameters. It can be 'year', 'month', 'day', 'hour', 'minute', 'second'. 

WAU

This time, we’ll complicate the task a little. Let's calculate the number of unique users per week, but only for those who have been using the app for more than a week.

select to_char(date_trunc('week',eventtime), 'YYYY Mon dd')
, count(distinct devtodevid) as "WAU"   
from p102968.sessions
where eventtime>= date_trunc('week',current_date) - interval '10 week'
and eventtime date_trunc('week',current_date
and (eventtime-created) interval '7 day'
and tester is false  
and cheater is false  
group by 1
order by 1 asc

How to count WAU
Query result. Screenshot from devtodev DEMO

Now you need to filter the result received from the table with sessions. Add the condition (eventtime-created)> interval '7 day'. Thus, for each row in the table, the difference between the session event eventtime and the date the application was installed by the user created is calculated. If the difference is more than 7 days, then the data on such a session will be included in our selection.

Now add to_char (date_trunc ('week', eventtime), 'YYYY Mon dd') to the query select. The date_trunc function will round the date to a week, the to_char function will make the data look nice.

We have talked about ways to calculate user activity metrics. In the next article, you’ll learn how to use SQL to get audience monetization data.

Read more