calculate daily active users (DAU) in MySQL

How to Calculate Daily Active Users (DAU) in MySQL

Daily Active Users (DAU) is a useful KPI for every business. It helps you understand how many people are actually using your product/service everyday. Here’s how to calculate daily active users (DAU) in MySQL.

 

How to Calculate Daily Active Users (DAU) in MySQL

 

You will mainly need 2 columns for this purpose – user id and timestamp(when user account was created). Let’s say you have stored user information in the following table users(id, date_joined)

 

mysql> create table users (
id int(11) NOT NULL auto_increment,
date_joined timestamp,
PRIMARY KEY (id)
);


mysql> insert into users values
(1, '2019-12-01 2:00:00'),
(2,'2019-12-01 2:30:00'),
(3,'2019-12-02 10:00:00'),
...;

mysql> select * from users;
+----+---------------------+
| id | date_joined         |
+----+---------------------+
|  1 | 2019-12-01 02:00:00 |
|  2 | 2019-12-01 02:30:00 |
|  3 | 2019-12-01 10:00:00 |
|  4 | ...                 |
+----+---------------------+


Here’s a simple SQL query to calculate daily active users (DAU) in MySQL for preceding day

 

SELECT COUNT(DISTINCT id)
    FROM users
    WHERE date_joined > NOW() - INTERVAL 1 DAY;
+--------------------+
|         DAU        |
+--------------------+
|        120         |
+--------------------+

You can plot the above DAU as a single number chart on your dashboard.

dau single value

 

Also check out How to Calculate Weekly Active Users (WAU) in MySQL.

 

If you want to add some kind of filter (e.g status=4) to count only specific users, then you can add the condition to WHERE clause in the above SQL query

SELECT COUNT(DISTINCT id) as DAU
    FROM users
    WHERE date_joined > NOW() - INTERVAL 1 DAY and status=4;
+--------------------+
|        DAU         |
+--------------------+
|        31          |
+--------------------+

 

Also read: How to Calculate Monthly Active Users (MAU) in MySQL

 

If you want to calculate daily DAU, that is, daily active users every day, then here’s the SQL query to calculate daily active users (DAU) in MySQL.

 

SELECT DATE(date_joined) AS DAY,
       COUNT(id) AS DAU
    FROM users
    GROUP BY DATE(date_joined);

+------------+--------+
|   DAY      |  DAU   |
+------------+--------+
| 2019-01-01 |  70    |
| 2019-01-02 |  77    |
| 2019-01-03 |  71    |
| 2019-01-04 |  70    |
| 2019-01-05 |  ...   |
+------------+--------+

You can use a charting tool to plot the daily DAU value in a line chart to get the growth trend for your business. Here’s an example of a line chart created using Ubiq

dau line chart

 

You can easily customize the above queries to calculate DAU for most recent month, or get daily DAU values and plot them on a line chart.

If you want to create charts, dashboards & reports from MySQL database, you can try Ubiq. We offer a 14-day free trial.

mm

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!