Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

DAU/MAU "stickiness": calculated metric on a rolling date period

Avatar

Avatar
Contributor
Level 3
GaMeur1
Level 3

Likes

22 likes

Total Posts

40 posts

Correct Reply

2 solutions
Top badges earned
Contributor
Shape 1
Give Back
Ignite 1
Validate 1
View profile

Avatar
Contributor
Level 3
GaMeur1
Level 3

Likes

22 likes

Total Posts

40 posts

Correct Reply

2 solutions
Top badges earned
Contributor
Shape 1
Give Back
Ignite 1
Validate 1
View profile
GaMeur1
Level 3

27-09-2019

Hi,

I'd like to calculate a "stickiness" metric for each day as DAU/MAU where DAU is the "daily active user" (i.e. uniques visitors) of a specific day and MAU is the "monthly active user" (i.e. unique visitors for the 30 last days) for the same day (so period including this day and 29 days before).

It's easy to get the DAU per day thanks to a simple table with "day" dimension as rows and "unique visitors" metric as column.

But I don't know how to get the number of unique visitors on the last 30 days for each day of a period.

I mean, it's possible one shot for a specific day, using a date range dimension, but it's not possible for each day of a period.

I can't figure out how to create a calculated metric that will give for each day of a period (day as dimension in rows) the number of unique visitors during last 30 days...

Does anyone already succeed to calculate such metric on a rolling date period?

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Coach
MVP
Andrey_Osadchuk
MVP

Likes

510 likes

Total Posts

917 posts

Correct Reply

243 solutions
Top badges earned
Coach
Contributor
Seeker
Bedrock
Boost 500
View profile

Avatar
Coach
MVP
Andrey_Osadchuk
MVP

Likes

510 likes

Total Posts

917 posts

Correct Reply

243 solutions
Top badges earned
Coach
Contributor
Seeker
Bedrock
Boost 500
View profile
Andrey_Osadchuk
MVP

03-10-2019

It's not supported at the moment as the rolling dates are linked to the current day rather than to the date displayed in the freeform table rows.

You may want to submit a feature request in the Ideas.

Answers (6)

Answers (6)

Avatar

Avatar
Contributor
Level 3
GaMeur1
Level 3

Likes

22 likes

Total Posts

40 posts

Correct Reply

2 solutions
Top badges earned
Contributor
Shape 1
Give Back
Ignite 1
Validate 1
View profile

Avatar
Contributor
Level 3
GaMeur1
Level 3

Likes

22 likes

Total Posts

40 posts

Correct Reply

2 solutions
Top badges earned
Contributor
Shape 1
Give Back
Ignite 1
Validate 1
View profile
GaMeur1
Level 3

27-09-2019

To clarify the problem:

what I would like is a table with date (days) as rows, and metrics in column « DAU », « MAU » and « DAU/MAU ratio ».

For example, for 2019-09-26, DAU would be the number of unique visitors during the day of 2019-09-26, and MAU would be the number of unique visitors during the time period of 30 days from 28/08/2019 to 26/09/2019 (included).

And for 019-09-27, DAU would be the number of unique visitors during the day of 2019-09-27, and MAU would be the number of unique visitors during the time period of 30 days from 29/08/2019 to 27/09/2019 (included).

Avatar

Avatar
Contributor
Level 3
GaMeur1
Level 3

Likes

22 likes

Total Posts

40 posts

Correct Reply

2 solutions
Top badges earned
Contributor
Shape 1
Give Back
Ignite 1
Validate 1
View profile

Avatar
Contributor
Level 3
GaMeur1
Level 3

Likes

22 likes

Total Posts

40 posts

Correct Reply

2 solutions
Top badges earned
Contributor
Shape 1
Give Back
Ignite 1
Validate 1
View profile
GaMeur1
Level 3

03-10-2019

Ok, that's what I was afraid of...

Thanks for your time and your answer.

Avatar

Avatar
Contributor
Level 3
GaMeur1
Level 3

Likes

22 likes

Total Posts

40 posts

Correct Reply

2 solutions
Top badges earned
Contributor
Shape 1
Give Back
Ignite 1
Validate 1
View profile

Avatar
Contributor
Level 3
GaMeur1
Level 3

Likes

22 likes

Total Posts

40 posts

Correct Reply

2 solutions
Top badges earned
Contributor
Shape 1
Give Back
Ignite 1
Validate 1
View profile
GaMeur1
Level 3

03-10-2019

Yes, DAU is easy to get (as I said in my first post). What's difficult to get is MAU!!!

I can't get unique visitors on last 30 days for each day of a specific past period.

Avatar

Avatar
Coach
MVP
Andrey_Osadchuk
MVP

Likes

510 likes

Total Posts

917 posts

Correct Reply

243 solutions
Top badges earned
Coach
Contributor
Seeker
Bedrock
Boost 500
View profile

Avatar
Coach
MVP
Andrey_Osadchuk
MVP

Likes

510 likes

Total Posts

917 posts

Correct Reply

243 solutions
Top badges earned
Coach
Contributor
Seeker
Bedrock
Boost 500
View profile
Andrey_Osadchuk
MVP

03-10-2019

Just build a table with the day dimension and you will get DAU for each day.

Avatar

Avatar
Contributor
Level 3
GaMeur1
Level 3

Likes

22 likes

Total Posts

40 posts

Correct Reply

2 solutions
Top badges earned
Contributor
Shape 1
Give Back
Ignite 1
Validate 1
View profile

Avatar
Contributor
Level 3
GaMeur1
Level 3

Likes

22 likes

Total Posts

40 posts

Correct Reply

2 solutions
Top badges earned
Contributor
Shape 1
Give Back
Ignite 1
Validate 1
View profile
GaMeur1
Level 3

03-10-2019

Thank you Andrey but I don't think it match my needs.

This is working only if you want to have DAU and MAU for yesterday.

For example, if today is October 3rd, I'll get DAU as uniques visitors for yesterday (October 2nd) and MAU as uniques visitors during last 30 days (from Sep 4th to October 2nd).

But I won't get it for each day of a specific past period!

For example, I would like to have those figures for each day of September. How do I do?

Avatar

Avatar
Coach
MVP
Andrey_Osadchuk
MVP

Likes

510 likes

Total Posts

917 posts

Correct Reply

243 solutions
Top badges earned
Coach
Contributor
Seeker
Bedrock
Boost 500
View profile

Avatar
Coach
MVP
Andrey_Osadchuk
MVP

Likes

510 likes

Total Posts

917 posts

Correct Reply

243 solutions
Top badges earned
Coach
Contributor
Seeker
Bedrock
Boost 500
View profile
Andrey_Osadchuk
MVP

27-09-2019

GaMeur,

Build a table with two Unique Visitors metrics. Add "yesterday" time period under the first IV metric, add "last 30 days" time period under the second UV metric. This will work as "filters". The first column will return DAU, the second — MAU.

You may want to create custom time periods as per your needs and use such a technic for any type of AU.

Advanced calculated metrics will help you add the Ratio metrics. The time periods can be used as filters in the metric builder, just add them on top of the metric.