DAU , MAU and WAU using mongodb aggregation commands

Hello ,

I have a model like blow

{
  "_id": {
    "$oid": "655f35103c80107f8f53f706"
  },
  "userId": 25,
  "institutionId": 1,
  "organizationId": 2,
  "startTime": {
    "$date": "2023-11-23T11:18:40.348Z"
  },
  "endTime": {
    "$date": "2023-11-23T11:20:31.994Z"
  },
  "day": 4,
  "hour": 11,
  "timeSpent": 67,
  "deviceInfo": "Desktop",
  "osInfo": "Mac OS",
  "browserInfo": "Chrome",
  "isGuest": false,
  "__v": 0
}

similar data will be there which usually stores user’s activity.

Was able to write a aggregation command for DAU,
need help in WAU.

Like i want WAU of each day of last 14 days filtered by organizationId.

So expected end goal is like below

{
Date:”07-10-2023”,
WAU: 23 // contains all the unique users  from 01-10-2023  to 07-10-2023
},
{
Date:”08-10-2023”,
WAU: 28 // contains all the unique users  from 02-10-2023  to 08-10-2023
},
{
Date:”09-10-2023”,
WAU: 25 // contains all the unique users  from 03-10-2023  to 09-10-2023
}

Can someone help me with some good approach or aggregate command .
also if any necessary indexing that i should do to make commands faster expecting my collection size can grow to more than 10 million records in 6 months
current indexes

organizationId_1
day_1_hour_1

Hi @Gouri_Sankar ,

As you mention your expectation for the size for the next 6 months to more than 10 million records. So I belive that a good approach is to use computed pattern. Your use case allows you to aggregate all users from a previous day and save it as a DAU computed value. On the final of a week, you can create a new WAU computed value aggregating all DAU values and finally on the end of month you can create a MAU computed value aggregating DAU or WAU.

I recommend this approach because with that you don’t need to calculate the value of active users all time that you are querying. It’s all already to be consumed. So your queries function will have a better performance than calculating value all the time.

1 Like