New Line Chart: MAU (Monthly Active Users) and WAU (Weekly Active Users) by Role

Question

New Line Chart: MAU (Monthly Active Users) and WAU (Weekly Active Users) by Role

Closed this issue 6 months ago · 29 comments

On the /health page, add a new line chart. The chart should contain 12 months of data including the current month. The chart should track the following stats:

We are trying to track the following - please implement a toggle to display each metric on the line chart:
-MAU: count unique users by role (Volunteer, Supervisor, Admin) who logged in that month
-WAU: count unique users by role (Volunteer, Supervisor, Admin) who logged in a given week on that month

Create a json endpoint for the chart data. Make sure the json contains the minimum amount of data for the chart. Make sure the endpoint is accessible to users who are logged out.

If chart creation fails, display an error to the user explaining why. Print a detailed version of the error in the console.

No testing required but prove your solution works.

See:

https://github.com/rubyforgood/casa/blob/main/app/controllers/health_controller.rb
https://github.com/rubyforgood/casa/blob/main/app/javascript/src/display_app_metric.js

We use chart.js for graphs

See: https://github.com/rubyforgood/casa/wiki/Working-with-Frontend-Assets

Answer 1 · 2024-02-14T00:36:24.000Z

Hi @bcastillo32 .. Referring to comment from this issue #5520 (comment) I would definitely like to take up this issue. Can you please assign it to me? Thanks

Answer 2 · 2024-02-14T01:01:34.000Z

Hi @bcastillo32 .. Referring to comment from this issue #5520 (comment) I would definitely like to take up this issue. Can you please assign it to me? Thanks

thank you @sarvaiyanidhi ! :) let me know if you have any questions.

Answer 3 · 2024-02-21T19:52:49.000Z

Hi @bcastillo32 ,

For below line mentioned in issue
-WAU: count unique users by role (Volunteer, Supervisor, Admin) who logged in a given week on that month
Do you want data for each week of the current month or days for the current week of the month?

Thanks,
Nidhi Sarvaiya

Answer 4 · 2024-02-24T03:49:06.000Z

Hi @bcastillo32 .. Can you please clarify on above query related to WAU data to be shown in chart?

Answer 5 · 2024-02-25T17:33:26.000Z

Hi @bcastillo32 .. Can you please clarify on above query related to WAU data to be shown in chart?

Hi @sarvaiyanidhi - this is an interesting one. I'm not sure it make sense to break it down by the week of each month since some months have 5 weeks. Seems like it makes most sense to do "in the last 7 days" for WAU (weekly active users) - would that be a more viable option you think? I also need a way to see if are seeing more WAU over a given amount of time. So over 12 month, 3 month, 1 month, and past week. Any thoughts on how we might be able to achieve this?

Answer 6 · 2024-02-27T03:52:02.000Z

Sure @bcastillo32 .. We can show data for last 7 days for weekly active users.

For other requirement of weekly, monthly, quarterly data, are you okay to have dropdown and select appropriate option to view data as shown in below chart

Answer 7 · 2024-02-27T04:20:34.000Z

Sure @bcastillo32 .. We can show data for last 7 days for weekly active users.

For other requirement of weekly, monthly, quarterly data, are you okay to have dropdown and select appropriate option to view data as shown in below chart

Hi @sarvaiyanidhi - weekly, monthly, quarterly, yearly would all be showing WAU data over those periods so we can measure if we are seeing an increase or decrease in WAUs. Same goes for MAU. So perhaps we need 2 drop downs. One to switch from MAU and WAU and one to show the time period. Does this make sense?

Answer 8 · 2024-02-28T03:40:43.000Z

Hi @bcastillo32 I am not sure I am very clear with the requirement.

As I understand from original issue description, we need to show below two charts

-MAU: count unique users by role (Volunteer, Supervisor, Admin) who logged in that month
-WAU: count unique users by role (Volunteer, Supervisor, Admin) who logged in a given week on that month

Based on this if I understand we want to have two charts showing details as mentioned below

MAU Line Chart

X axis will show 12 months
Y axis with show unique count of active users for Volunteer, Supervisor and Admin in each month

WAU Line Chart

X axis will show last 7 days
Y axis with show unique count of active users for Volunteer, Supervisor and Admin in each day

Later I thought we also need to get for period like Quarterly Active Users so I introduced dropdown to get Quarterly, Montly, Weekly active users but I am not able to understand on two dropdowns as how are we going to compare weekly users with monthly users as both are different metrics. If possible can you clarify with more details on data you want to see in chart.

Answer 9 · 2024-03-02T19:51:53.000Z

Hi @bcastillo32 I am not sure I am very clear with the requirement.

As I understand from original issue description, we need to show below two charts

-MAU: count unique users by role (Volunteer, Supervisor, Admin) who logged in that month -WAU: count unique users by role (Volunteer, Supervisor, Admin) who logged in a given week on that month

Based on this if I understand we want to have two charts showing details as mentioned below

MAU Line Chart

X axis will show 12 months

Y axis with show unique count of active users for Volunteer, Supervisor and Admin in each month

WAU Line Chart

X axis will show last 7 days

Y axis with show unique count of active users for Volunteer, Supervisor and Admin in each day

Later I thought we also need to get for period like Quarterly Active Users so I introduced dropdown to get Quarterly, Montly, Weekly active users but I am not able to understand on two dropdowns as how are we going to compare weekly users with monthly users as both are different metrics. If possible can you clarify with more details on data you want to see in chart.

Apologies for the confusion. The MAU is pretty straightforward and make sense the way you described. I am more concerned with the WAU and being able to see more than just a time period of "last 7 days". In addition to "last 7 days", I would like to see the WAUs over a month, last 3 months and 12 months if possible. Does this make sense? If it would help, I would be more than happy to explain over a quick google meet. If you need to create separate graphs for WAU and MAU that would be ok if it makes things easier

Answer 10 · 2024-03-04T21:34:45.000Z

Thank you @bcastillo32 for taking time to clarify again on the requirement.

As per last comment, we are good with MAU chart and I think chart posted in this PR ##5541 for MAU is fine.

Now, coming to WAU chart, if I understand we want to view details for more period for this chart.

I am listing out details how feature will be demonstrated as per my understanding from previous discussions

We will have two droplist for this chart
- First droplist to switch between MAU and WAU option
- Second droplist will have period - Week, Month, 3 Months, 12 Months - This droplist will be enabled only if we select in first droplist WAU option

Coming to details to be display for WAU chart as per period droplist option

WAU chart with week period option
X-axis - Last 7 days
Y-axis - Count of active users on each day for Volunteer, Supervisor and Admin
WAU chart with month period option
X-axis - Shows weeks for the months like week1, week2,... week5
Y-axis - Count of active users in each week for Volunteer, Supervisor and Admin
WAU chart with 3 months period option
X-axis - Show weeks for the last 3 months like week1, week2,... week12
Y-axis - Count of active users in each week for Volunteer, Supervisor and Admin
WAU chart with 12 months period option
X-axis - Show weeks for the last 3 months like week1, week2,... week52
Y-axis - Count of active users in each week for Volunteer, Supervisor and Admin

Let me know if you are also visualizing same details for the chart. Happy to connect over a Google meet if my understanding is still not in sync with requirement. I am available in EST timezone let me know whenever you want to connect.

Answer 11 · 2024-03-05T03:18:37.000Z

Thank you @bcastillo32 for taking time to clarify again on the requirement.

As per last comment, we are good with MAU chart and I think chart posted in this PR ##5541 for MAU is fine.

Now, coming to WAU chart, if I understand we want to view details for more period for this chart.

I am listing out details how feature will be demonstrated as per my understanding from previous discussions

We will have two droplist for this chart

First droplist to switch between MAU and WAU option

Second droplist will have period - Week, Month, 3 Months, 12 Months - This droplist will be enabled only if we select in first droplist WAU option

Coming to details to be display for WAU chart as per period droplist option

WAU chart with week period option
X-axis - Last 7 days
Y-axis - Count of active users on each day for Volunteer, Supervisor and Admin

WAU chart with month period option
X-axis - Shows weeks for the months like week1, week2,... week5
Y-axis - Count of active users in each week for Volunteer, Supervisor and Admin

WAU chart with 3 months period option
X-axis - Show weeks for the last 3 months like week1, week2,... week12
Y-axis - Count of active users in each week for Volunteer, Supervisor and Admin

WAU chart with 12 months period option
X-axis - Show weeks for the last 3 months like week1, week2,... week52
Y-axis - Count of active users in each week for Volunteer, Supervisor and Admin

Let me know if you are also visualizing same details for the chart. Happy to connect over a Google meet if my understanding is still not in sync with requirement. I am available in EST timezone let me know whenever you want to connect.

Hi @sarvaiyanidhi yes this is great! For the WAU 12 month view - if you find it difficult to show all 52 weeks on the x axis you can just put the ~4.3 weeks into a month and show 12 months. What do you think?

Answer 12 · 2024-03-05T19:14:46.000Z

Thanks @bcastillo32 for confirmation. Will go ahead with implementation and workout option for month view with the suggestion in case if there is issue with displaying 52 weeks.

Answer 13 · 2024-03-15T03:17:03.000Z

Hi @bcastillo32 I just realized that current PR and issue is closed with only MAU chart. Can I go ahead and create new issue for WAU chart which needs to be implemented as per our last discussion?

Answer 14 · 2024-03-15T04:40:48.000Z

Hi @bcastillo32 I just realized that current PR and issue is closed with only MAU chart. Can I go ahead and create new issue for WAU chart which needs to be implemented as per our last discussion?

Oh yah I just saw that too. No worries! Plz feel free to create the new issues and submit a PR for the WAU. That works perfectly fine :)

Answer 15 · 2024-04-07T19:11:57.000Z

hi @sarvaiyanidhi while I am encouraged to see such growth - can you confirm the logins are unique and we are not double counting folks? :) please see the screenshot below

Answer 16 · 2024-04-09T02:52:10.000Z

Hi @bcastillo32 ,

I am calculating details from users table to check for current_sign_in_at field grouping by month. As Users table has unique entry for each user so it won't double count users.

Below is the SQL query generated from Active Record query in the code

SELECT COUNT(*) AS "count_all", DATE_TRUNC('month', "users"."current_sign_in_at"::timestamptz AT TIME ZONE 'Etc/UTC')::date AS "date_trunc_month_users_current_sign_in_at_timestamptz_at_time_z" FROM "users" WHERE "users"."type" = 'Volunteer' AND ("users"."current_sign_in_at" IS NOT NULL) GROUP BY DATE_TRUNC('month', "users"."current_sign_in_at"::timestamptz AT TIME ZONE 'Etc/UTC')::date

Answer 17 · 2024-04-09T04:58:51.000Z

Hi @bcastillo32 ,

I am calculating details from users table to check for current_sign_in_at field grouping by month. As Users table has unique entry for each user so it won't double count users.

Below is the SQL query generated from Active Record query in the code

SELECT COUNT(*) AS "count_all", DATE_TRUNC('month', "users"."current_sign_in_at"::timestamptz AT TIME ZONE 'Etc/UTC')::date AS "date_trunc_month_users_current_sign_in_at_timestamptz_at_time_z" FROM "users" WHERE "users"."type" = 'Volunteer' AND ("users"."current_sign_in_at" IS NOT NULL) GROUP BY DATE_TRUNC('month', "users"."current_sign_in_at"::timestamptz AT TIME ZONE 'Etc/UTC')::date

Amazing! Thank you. We just wanted to double check since we have seen a nice uptick in usage. Thank you again for confirming :)

Answer 18 · 2024-04-10T21:20:12.000Z

@sarvaiyanidhi Looking at the data - I notice that some months dip. Can we test or confirm what is happening if a user's account has been deleted?

Current hypothesis is - when a user account is deleted, monthly sum is being decreased by 1.

Let me know what you think :)

Answer 19 · 2024-04-11T22:46:30.000Z

Hi @bcastillo32 ,

I could not see any option to delete Volunteer/Supervisor from the interface. It only allows to deactivate Volunteer/Supervisor.

If Volunteer or Supervisor had logged in a month and after that its account is deactivated still it is counted in graph as it had logged in the given month. Let me know if you want to consider only active users in the count.

Answer 20 · 2024-04-11T22:59:14.000Z

Okay I just thought further on your query, now there can be issue in data as we have only information on last_sign_in_at and current_sign_in_at information.

We are calculating count of users in a given month based on current_sign_in_at field.

So volunteer1 has logged in each month Feb, March and April so it current sign in will be updated for April so it might be showing its count only in April month.

We don't have data in database to track each user sign_in record to check sign_in in a given month or week. We just two instance of current and last signin.

Answer 21 · 2024-04-12T01:15:00.000Z

Hi @bcastillo32 ,

I could not see any option to delete Volunteer/Supervisor from the interface. It only allows to deactivate Volunteer/Supervisor.

If Volunteer or Supervisor had logged in a month and after that its account is deactivated still it is counted in graph as it had logged in the given month. Let me know if you want to consider only active users in the count.

Ok. We can count those that were deactivated. Seems like that makes sense.

Answer 22 · 2024-04-12T01:17:42.000Z

Okay I just thought further on your query, now there can be issue in data as we have only information on last_sign_in_at and current_sign_in_at information.

We are calculating count of users in a given month based on current_sign_in_at field.

So volunteer1 has logged in each month Feb, March and April so it current sign in will be updated for April so it might be showing its count only in April month.

We don't have data in database to track each user sign_in record to check sign_in in a given month or week. We just two instance of current and last signin.

Ah ok so does this mean previous months are labeling a lower MAU than there really was. Or am I misunderstanding?

Answer 23 · 2024-04-12T02:28:28.000Z

Yes, that is correct as we are not storing historic login data.
Just to give an example if we are checking for Volunteer 1 login count considering that user has logged in each month Feb, March, April but still our chart will show only count for April as we are considering current_sign_in date data to count its login which will be updated to latest April month.

I think if we want to calculate count of login of users for each month or week we will have to add in new functionality to store activity of user login in separate table may be to get correct count.

Answer 24 · 2024-04-14T02:28:34.000Z

Yes, that is correct as we are not storing historic login data. Just to give an example if we are checking for Volunteer 1 login count considering that user has logged in each month Feb, March, April but still our chart will show only count for April as we are considering current_sign_in date data to count its login which will be updated to latest April month.

I think if we want to calculate count of login of users for each month or week we will have to add in new functionality to store activity of user login in separate table may be to get correct count.

Ok I am following - so the past months showing a number is due to a user who's last log in was that month? We definitely need to explore how we can get the correct count for each month. let me ask if what you proposed would be ok for us to do before creating that ticket - would you be able to work on that? Or does it need to be someone with DB access?

Answer 25 · 2024-04-15T03:13:30.000Z

@sarvaiyanidhi are you on our slack? Feedback on how we might be able to accomplish the desired monthly sums here:
https://rubyforgood.slack.com/archives/CVB0QJGVD/p1713147570116209?thread_ts=1713136177.609439&channel=CVB0QJGVD&message_ts=1713147570.116209

Answer 26 · 2024-04-16T03:10:12.000Z

Hi @bcastillo32 Just joined slack and checked out suggested option. I can go ahead and implement feature as per suggestion. I will further evaluate from my end on both the suggested option and consider the better approach. Should I post my suggestion in slack channel for feedback from others before going ahead with issue?

Also, please note that even after implementing solution we will be able to track count only from the date feature gets deployed in production so past months we won't still see count data correctly as we don't have those data with us.

Answer 27 · 2024-04-21T20:50:19.000Z

Hi @bcastillo32 Just joined slack and checked out suggested option. I can go ahead and implement feature as per suggestion. I will further evaluate from my end on both the suggested option and consider the better approach. Should I post my suggestion in slack channel for feedback from others before going ahead with issue?

Also, please note that even after implementing solution we will be able to track count only from the date feature gets deployed in production so past months we won't still see count data correctly as we don't have those data with us.

Hi @sarvaiyanidhi thanks for the note. I mentally responded to this so apologies for my delayed reply here. How is the implementation going?

Answer 28 · 2024-04-25T22:33:05.000Z

Hi @bcastillo32 ,

Sorry for the delay as I was tied up with something, will be starting on this now and work on the implementation. Try to get it done as soon as possible.

Will create separate issue for capturing login details.

Thanks

Answer 29 · 2024-04-30T02:08:23.000Z

Hi @bcastillo32 ,

Sorry for the delay as I was tied up with something, will be starting on this now and work on the implementation. Try to get it done as soon as possible.

Will create separate issue for capturing login details.

Thanks

Hi @sarvaiyanidhi thanks for the update! I appreciate you helping with this and look forward to it being complete so we can track adoption :)