How to visualize stickiness (DAU / MAU) in Apache superset

user_7653486 0
Viewed 310

i am new to Apache superset i want to implement chart it should represent stickiness means ratio of Daily active users/Monthly active users. Using Druid as a datasource. column present in my database "userId","event","timestamp".

druid apache-superset
1 Answers

For DAU you just need to truncate timestamp to daily grain:

SELECT COUNT(DISTINCT userId) AS dau, FLOOR(timestamp TO DAY) AS day
FROM your_datasource
GROUP BY FLOOR(timestamp TO DAY)

You can write the query above in SQL Lab, and then click "Visualize" to build a chart out of it. Alternatively, in the explore view you can choose "day" as the time grain, and then define the metric as COUNT(DISTINCT userId).

For WAU/MAU, it's a bit more complicated. Traditionally you'd use a self join for the query, something like this (in pseudo-SQL):

SELECT ...
FROM your_datasource AS today
JOIN your_datasource AS last_month
ON last_month.timestamp BETWEEN today.timestamp AND (today.timestamp - 30 days)

But Druid only supports equijoins.

user_8071180
Related