Calculate DAU/MAU from mongodb events

user_663447 0
Viewed 303

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?

aggregation-framework mongodb analytics
3 Answers

I made a quick test on a large database of events, and counting with a distinct is much faster than an aggregate if you have the right indexes :

collection.distinct('user', { ct: { $gte: dateFrom, $lt: dateTo } }).length
user_16146160

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}}}
])
user_26838140

you can use sum at the time of group.

collection.aggregate([
    { $match: {'date': {$gte: dateFrom, $lt: dateTo }}}, // fetch all requests from/to 
    { $group: { _id: '$user', total: { $sum: 1 }}}, // group all requests by user and sum the count of collection for a group
    { $sort: { total: -1 }}
  ], function (err, result) {
      if (err) cb(err, null);
      cb(null, result);
  });
user_64601200
Related