Calculate DAU/MAU from mongodb events

Question

Here's what it looks like so far:

  collection.aggregate(
    [
      {
        $match: {
          ct: {$gte: dateFrom, $lt: dateTo },
        }
      },
      {
        $group: { 
          _id: '$user'
        }
      }
    ]
  ).toArray((err, result) => {
    callback(err, result.length)
  });

This gets me a list of users like this which I can count for DAU/MAU:

But I think this is not efficient, what's the correct way of doing this?

Answer 1

You could use below aggregation for unique active users over day and month wise. I've assumed ct as timestamp field.

db.collection.aggregate(
[
  {"$match":{"ct":{"$gte":dateFrom,"$lt":dateTo}}},
  {"$facet":{
    "dau":[
      {"$group":{
        "_id":{
          "user":"$user",
          "ymd":{"$dateToString":{"format":"%Y-%m-%d","date":"$ct"}}
        }
      }},
      {"$group":{"_id":"$_id.ymd","dau":{"$sum":1}}}
    ],
    "mau":[
      {"$group":{
        "_id":{
          "user":"$user",
          "ym":{"$dateToString":{"format":"%Y-%m","date":"$ct"}}
        }
      }},
      {"$group":{"_id":"$_id.ym","mau":{"$sum":1}}}
    ]
  }}
])

DAU

db.collection.aggregate(
[
  {"$match":{"ct":{"$gte":dateFrom,"$lt":dateTo}}},
  {"$group":{
     "_id":{
        "user":"$user",
        "ymd":{"$dateToString":{"format":"%Y-%m-%d","date":"$ct"}}
      }
   }},
   {"$group":{"_id":"$_id.ymd","dau":{"$sum":1}}}
])

MAU

db.collection.aggregate(
[
  {"$match":{"ct":{"$gte":dateFrom,"$lt":dateTo}}},
  {"$group":{
     "_id":{
       "user":"$user",
       "ym":{"$dateToString":{"format":"%Y-%m","date":"$ct"}}
     }
  }},
  {"$group":{"_id":"$_id.ym","mau":{"$sum":1}}}
])