DAU, WAU, MAU Example

Daily, Weekly or Monthly Active Users

DAU, WAU, and MAU are pretty easy to calculate once you define your user groups (daily, weekly...) and calculate the dates for each group. Visualizations are always recommended for this type of data.

Here is a step by step example for this type of query in PostgreSQL - you can  view it in our demo account as well.

We used the  WITH clause in this example - WITH clauses allow you to name a sub-query block, this way your query is modular (and sometimes runs faster), it can later be referred to inside the main query instead of making a large main query with many aliases, JOINs or other complexities. You can have multiple sub-queries, just be sure to have a coma between them.

    Define each group or users (daily, weekly and monthly - or any combination that suits you).

    Define the smallest time range first as you'll use it in the other time ranges - in this case it's the daily users (DAU) and you'll group them by the creation date ("age").

    Also, define what an active user is - in this case, we count an active user by the first time we saw its id in the events table.

    dau AS (
            SELECT created_at::DATE AS "date", count(distinct user_id) AS dau          	FROM events
            WHERE created_at > '2016-10-01'
            GROUP BY 1 
    Calculate the dates of each group - use relative dates and exact ones to keep your dataset tidy and your query speedy.
    SELECT "date", dau,
             (SELECT count(distinct user_id)
              FROM events
              WHERE events.created_at::DATE BETWEEN dau.date - 29 AND dau.date            	  AND created_at > '2016-10-01'
             ) AS mau,
             (SELECT count(distinct user_id)
              FROM events
              WHERE events.created_at::DATE BETWEEN dau.date - 7 AND dau.date            	  AND created_at > '2016-10-01'
             ) AS wau
      FROM dau
    Select a nifty visualization

Still need help? Contact Us Contact Us