How to Calculate Monthly Active Users (MAU) in MySQL

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.

mau 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 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.

mau line chart

 

You can easily customize the above queries to calculate MAU for most recent month, or get monthly MAU 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 dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build dashboards & reports for your business. Try it for free today!