How to Calculate Monthly Active Users (MAU) in MySQL

Last updated on May 17th, 2020 at 06:36 am

Monthly Active Users (MAU) is a useful KPI for every business. It helps you understand how many people are actually using your product/service at least once a month. Here’s how to calculate monthly active users (MAU) in MySQL.

How to Calculate Monthly Active Users (MAU) 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-02 10:00:00 |
|  4 | ...                 |
+----+---------------------+

Here’s a simple SQL query to calculate monthly active users (MAU) in MySQL for preceding month

SELECT COUNT(DISTINCT id)
    FROM users
    WHERE date_joined > NOW() - INTERVAL 1 MONTH;
+--------------------+
|         MAU        |
+--------------------+
|        1243        |
+--------------------+

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

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 MAU
    FROM users
    WHERE date_joined > NOW() - INTERVAL 1 MONTH and status=4;
+--------------------+
|        MAU         |
+--------------------+
|        314         |
+--------------------+

Also check out How to Calculate Daily Active Users (DAU) in MySQL

If you want to calculate monthly MAU, that is, monthly active users every month, then here’s the SQL query to calculate monthly active users (MAU) in MySQL.

SELECT LEFT(date_joined, 7) AS Month,
       COUNT(id) AS MAU
    FROM users
    GROUP BY LEFT(date_joined, 7);

+---------+--------+
| Month   |  MAU   |
+---------+--------+
| 2019-01 |  30100 |
| 2019-02 |  45213 |
| 2019-03 |  55301 |
| 2019-04 |  60210 |
| 2019-05 |  ...   |
+---------+--------+

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