DAU/MAU calculation for segments

Question

I have written the following query to calculate DAU/MAU ratio:

WITH dau AS
(
  SELECT TRUNC(created_at) AS created_at,
         CASE
           WHEN user_agent SIMILAR TO '%(Mobile|iPhone|iPod|iPad|Android)%' THEN 'non-desktop'
           ELSE 'desktop'
         END AS trafficsource,
         COUNT(DISTINCT member_id) AS dau
  FROM table ds
  WHERE ds.created_at BETWEEN '2017-01-01' AND '2017-12-11'
  AND   member_id <> 2
  AND   member_id NOT IN (SELECT memberid FROM auth2.membersinglerole WHERE roleid = 25)
  GROUP BY TRUNC(created_at),
           trafficsource
)
SELECT created_at,
       trafficsource,
       dau,
       (SELECT COUNT(DISTINCT member_id)
        FROM table ds
        WHERE member_id <> 2
        AND   member_id NOT IN (SELECT memberid FROM auth2.membersinglerole WHERE roleid = 25)
        AND   ds.created_at BETWEEN dau.created_at - 29*INTERVAL '1 day' AND dau.created_at) AS mau,
       (dau / CAST(mau AS float)) AS "DAU/MAU",
       (dau / CAST(mau AS float))*30 AS DaysOutOf30
FROM dau
WHERE EXTRACT(dayofweek FROM created_at) NOT IN (0,6)
AND   EXTRACT(month FROM created_at) NOT IN (5,6,7)
ORDER BY created_at

This query creates 2 segments for 'desktop' and 'non-desktop'. But notably, the query returns the same 'MAU' numbers for both these segments for the same day, as follows..

created_at    trafficsource    dau    mau    DAU/MAU    DaysOutOf30
2017-01-02  desktop 4157    140834  0.02951702003777497 0.8855106011332491
2017-01-02  non-desktop 801 140834  0.005687547041197438    0.17062641123592315
2017-01-03  desktop 12610   140468  0.089771335820258   2.6931400746077396
2017-01-03  non-desktop 2891    140468  0.020581199988609505    0.6174359996582851
2017-01-04  non-desktop 4033    137516  0.029327496436778264    0.8798248931033479
2017-01-04  desktop 17902   137516  0.1301812152767678  3.9054364583030337

How do I fix the query to return 'MAU' values for the segments created? Any help would be highly appreciated.

user_139490 · Answer

You can get an accurate mau value by calculating it separately and then joining it back to dau for the final result.

Logical steps:

Create traffic with 1 row per member_id per day. Note that the range starts 30 days earlier than your final selection range.
Create dau which is mostly unchanged
Create mau by joining traffic to itself with a range join that (using your interval logic) brings in the last 30 days data. Count distinct member_id over that data.
Select the final data, joining dau and mau on created_at.

WITH traffic AS (
    SELECT member_id,
           TRUNC(created_at) AS created_at,
           CASE WHEN user_agent SIMILAR TO '%(Mobile|iPhone|iPod|iPad|Android)%' 
           THEN 'non-desktop' ELSE 'desktop' END AS trafficsource,
    FROM table ds
    WHERE ds.created_at BETWEEN '2016-12-01' AND '2017-12-11'
    AND   member_id <> 2
    AND   member_id NOT IN (SELECT memberid FROM auth2.membersinglerole WHERE roleid = 25)
    GROUP BY 1,2,3
), dau AS (
    SELECT created_at, trafficsource,
           COUNT(DISTINCT member_id) AS dau
    FROM traffic
    GROUP BY 1,2
), mau AS (
    SELECT ds.created_at, ds.trafficsource,
           COUNT(DISTINCT ds.member_id) AS mau
    FROM traffic ds
    LEFT JOIN traffic mth
        ON  ds.member_id = mth.member_id
        AND ds.created_at BETWEEN mth.created_at - 29*INTERVAL '1 day' AND mth.created_at
    GROUP BY 1,2
    )
SELECT created_at, trafficsource, dau, mau,
       (dau / CAST(mau AS float)) AS "DAU/MAU",
       (dau / CAST(mau AS float))*30 AS DaysOutOf30
FROM dau
JOIN mau    
    USING (created_at, trafficsource)
WHERE created_at BETWEEN '2017-01-01' AND '2017-12-11'
AND   EXTRACT(dayofweek FROM created_at) NOT IN (0,6)
AND   EXTRACT(month FROM created_at) NOT IN (5,6,7)
ORDER BY created_at
;

DAU/MAU calculation for segments

1 Answers