Question

Rolling 30 day count distinct

  • 27 July 2022
  • 1 reply
  • 64 views

Hi Lookers,

 

I am trying to get the count of unique who made a transaction in a 30 day window starting from any given date by the end user.  I followed the instruction on this (rolling 30 day) and this (mau and dau) link.

The issue with the second one is that it gives me MAU and DAU as dimensions but I need them as measures in my explore so that is not a viable solution for my need.

The first one looked promising but I think it is an old link and some information is missing for the end steps. I have the explore made with the join as described in the instructions but the result I get for the measure is identical in the daily unique users and the 30 day unique user.  

 

What I ideally need is a dimension for the date and then two measures, one for the daily active user on any selected day and the second measure for the all unique users from that day to 30 days prior to that day. 

I have tried the filters in measure but they do not work as intended. The DAU is figured out by the following measure.

  measure: dau {
type: count_distinct
sql: ${user_key} ;;
drill_fields: [active_date_date]
}

Any suggestion is welcome. 


1 reply

Hi Fizza,

If above solution aren’t working for you , you can create a derived table similar to this 
 

with dau as (
select count(distinct userid) dau,
Date(event_time) date_event
from events where Date(event_time) > '2022-07-01' group by 2
)
SELECT date_event, dau,
(SELECT count(distinct m.userid)
FROM events m
WHERE Date(event_time) BETWEEN dau.date_event - 29 AND dau.date_event
) AS mau
FROM dau

this will give you the dau and rolling mau as dimensions , you can customise it to use as a measure as 

measure: mau_rolling {
type: number
sql: ANY_VALUE(${TABLE}.mau) ;;

}


 

Reply