Need to calculate daily/monthly ratio. How to combine two date ranges in one card?

user02464 · July 2018

Hi all,

I need to calculate a daily users / monthly users ratio, but I am stumped as to how to build the Beast Mode to indicate the following logic: "COUNT(DISTINCT `user id`) for every day and divide by COUNT(DISTINCT `user id`) for the whole month" This will be calculating this ratio over time.

I don't know if this can be built as a beast mode or if it shoud be done in the ETL, but mostly I am confused as to how to indicate both counts with different date ranges, one daily and one monthly in the same card.

Any help is much appreciated! Thanks.

user09465 · July 2018

Hello,

I think you can do it using a formula like this (for current month) :

COUNT(DISTINCT 'user_id')/COUNT(DISTINCT CASE WHEN YEAR(`your_date`) = YEAR(CURRENT_DATE()) AND MONTH(`your_date`) = MONTH(CURRENT_DATE()) THEN 'user_id')

Maybe there is a easier solution but I think it will do the job !

rado98 · August 2018

It also depends on how you want to present the informantion. If it was me I would do the following on Magic ETL:

Basically generate a table with the following fields (one row per date)

"Date", "Daily Distinct Users", "Monthly Distinc Users"

Create a beast mode as either Daily/Monthly (or AVG Daily/ AVG Monthly for Summary Numbers and so on)

You then use Date as your date grain, so you can trend it accross months, get averages and so on.

In more detail:

Create a Month of Date column on the original table
Create a Year of Date column on the original table
Create a YearMonth column on the original table
Group as Distinct Count on YearMonth
Group as Distinct Count on Date and YearMonth
Join the two new tables
Select the columns you want to keep

user000051 · June 2020

Hi Rado98,

Thanks for the explanation, I am building DAU/MAU ratio and your post has been the most useful?

However, would you be so kind to explain in more details the processes required, in particular:

- How do I generate a table with "Date", "Daily Distinct Users", "Monthly Distinct Users"?

- I am using Date operation to extract Year of Date and Month of Date then I will use combine to get YearMonth Column, but I don't understand the next steps and how to join the two tables.

Thanks for your help.

G

rado98 · June 2020

Hi

As I recall:

Step 4.

Using the ETL Group function to generate the table.

Group by MonthYear and Count Distinct Users, call new column "distinct Monthly Users"

Step 5

Using the ETL Group function to generate the table.

Group by Date and MonthYear and Count Distinct Users, call new column "distinct Daily Users"

Join on MonthYear

jaeW_at_Onyx · June 2020

Have a good think about what you want the data to mean and display.

I recommend that instead of JOINing the data as recommended by @rado98 that you UNION the two sets of data after adding a column Aggregation Level with constant values 'Daily' and 'Monthly'. This way you can avoid crazy math in your beast modes when you're calculating your ratio.

SUM( CASE WHEN AggLevel = 'daily' then CntDstnctMeasure END )/ SUM( CASE WHEN AggLevel = 'month' then CntDstnctMeasure END )

Have a think about how / if you want your report to respond to filters as you're defining your GROUP BY clause and aggregate function. Any columns that you eliminate means you can't use in filters.

I did a longer tutorial video that's lays out how I'd approach your problem while avoiding COUNT DISTINCT to give maximum functionality in dashboards. It might seem like a stretch for your use case but it'll work.

https://www.youtube.com/watch?v=Xb4QgKYgaqg

Need to calculate daily/monthly ratio. How to combine two date ranges in one card?

Comments

Categories