Skip to main content

Calculate monthly active users

Take great care when modifying and running queries in your database. Ensure you understand what the queries do and double check that your query is correct.

Incorrect queries can cause irrecoverable data loss.

We recommend you familiarize yourself with Transactions. That way, changes are not immediately written and you can undo any errors.

  1. Connect to your Synapse database
  2. Get the UNIX timestamps in milliseconds for the time frame you are interested in. You want the time set to 00:00:00 GMT. https://www.epochconverter.com/ is a great tool to convert to/from UNIX timestamps.
    a. If you are interested in the current MAU number, pick the date 30 days ago. Note that if you have MAU metrics enabled, this information is also available in Grafana (or your metrics system of choice)
    b. If you want a specific month, get the timestamps for 1st of that month and 1st of the following month
  3. Modify and run the appropriate query below

Get your current MAU number. This uses the timestamp for 30 days ago. For example, if you're running this on January 7, 2025, you would use December 8 2024. This is similar to the query used by Synapse to calculate user count for phone-home stats.

SELECT COUNT(*) FROM (
    SELECT user_id
    FROM user_ips
    WHERE
        last_seen >= 1733616000000 AND -- Sunday, 8 December 2024 00:00:00 GMT
        user_id NOT IN (
            SELECT name
            FROM users
            WHERE user_type = 'support'
        )
    GROUP BY user_id
) AS temp;

For reference, this is equal to

SELECT COUNT(*) FROM (
    SELECT
        user_id,
        MAX(timestamp) AS timestamp
    FROM user_daily_visits
    WHERE
        timestamp >= 1733616000000 AND -- Sunday, 8 December 2024 00:00:00 GMT
        user_id NOT IN (
            SELECT name
            FROM users
            WHERE user_type = 'support'
        )
    GROUP BY user_id
) AS temp;

To get retrospective statistics, use this query instead

SELECT COUNT(*) FROM (
    SELECT
        user_id,
        MAX(timestamp) AS timestamp
    FROM user_daily_visits
    WHERE
        timestamp >= 1730419200000 AND -- Friday, 1 November 2024 00:00:00 GMT
        timestamp < 1733011200000 AND -- Sunday, 1 December 2024 00:00:00 GMT
        user_id NOT IN (
            SELECT name
            FROM users
            WHERE user_type = 'support'
        )
    GROUP BY user_id
) AS temp;
Back to top