Chart from different collections

I have 2 collections,

  1. signups → holds all users data with createdAt
[{
  "userId": "494df021-0a37-444c-8e1f-92bb4a35574e",
  "createdAt": "2022-08-06T06:26:27.926Z"
}]
  1. active_history → holds user last app open date time
[{
  "userId": "494df021-0a37-444c-8e1f-92bb4a35574e",
  "openedAt": "2022-08-06T06:26:27.926Z"
},
{
  "userId": "494df021-0a37-444c-8e1f-92bb4a35574e",
  "openedAt": "2022-09-06T06:26:27.926Z"
}]

I want to show Rolling 30 day growth rate in mongo chart, in other words
Number of users signed up during last T-30 days / Number of active users T-30 days ago

where my X axis will be date and Y axis will be Number of users signed up during last T-30 days / Number of active users T-30 days ago ,

How can i achieve this? I cant think of a way to do this.

How do you count this? So as of date X how do you count how many active users there are? Or is this just the number of unique users who had some activity during the previous 30 days?

Asya

Hello,

Yes, it is just the number of unique users who had some activity during the previous 30 days.

Ok, since I couldn’t assume that there would be some sign-ups every day, I put a $unionWith towards the front to make sure every date over the last 30 was represented, combining it with signups

var start_date = ISODate("2022-08-01T06:26:27.926Z");
db.signups.aggregate([
{$set:{num:1}},  /* count up each sign-up this many times */
{$unionWith:{ /* generate a document for each date over the last 
                          30 days with 0 signups to ensure no dates are missed */
      pipeline:[
             {$documents:{$map:{
                      input:{$range:[0,30]},
                      in:{ createdAt:{$dateAdd:{
                                unit:"day", 
                                amount:"$$this",
                                startDate: start_date
                      }}}
             }}}
]}},
{$group:{
    _id:{ $dateTrunc:{date:"$createdAt",unit:"day"}},
    count:{$sum:"$num"}
}},
{$setWindowFields:{   /* count cumulative signups over last 30 days "window" */
    sortBy:{_id:1}, 
    output:{signups:{
         $sum:"$count", 
         window: {range:[-30,"current"], 
         unit:"day"
}}}}}, 
{$lookup:{
    from:"active_history",  
    let:{date:"$_id",date30:{$dateSubtract:{startDate:"$_id",amount:30, unit:"day"}}},
    as:"activity", 
    pipeline:[ 
        {$match:{$expr:{$and:[{$gte:["$openedAt","$$date30"]},{$lte:["$openedAt","$$date"]}]}}},
        {$group:{_id:"$userId"}},
        {$count:"activeUsers"}]}},
{$unwind:"$activity"})

With a small amount of sample data the output would look something like:

{ "_id" : ISODate("2022-08-02T00:00:00Z"), "count" : 0, "signups" : 0, "activity" : { "activeUsers" : 1 } }
{ "_id" : ISODate("2022-08-03T00:00:00Z"), "count" : 0, "signups" : 0, "activity" : { "activeUsers" : 1 } }
{ "_id" : ISODate("2022-08-04T00:00:00Z"), "count" : 0, "signups" : 0, "activity" : { "activeUsers" : 1 } }
{ "_id" : ISODate("2022-08-05T00:00:00Z"), "count" : 0, "signups" : 0, "activity" : { "activeUsers" : 1 } }
{ "_id" : ISODate("2022-08-06T00:00:00Z"), "count" : 2, "signups" : 2, "activity" : { "activeUsers" : 1 } }
{ "_id" : ISODate("2022-08-07T00:00:00Z"), "count" : 1, "signups" : 3, "activity" : { "activeUsers" : 3 } }
{ "_id" : ISODate("2022-08-08T00:00:00Z"), "count" : 2, "signups" : 5, "activity" : { "activeUsers" : 3 } }
{ "_id" : ISODate("2022-08-09T00:00:00Z"), "count" : 3, "signups" : 8, "activity" : { "activeUsers" : 3 } }

So for each day you have number of users who signed up that day (count), cumulative over previous 30 days (signups), and unique users who had activity over previous 30 days. Should be easy to transform that to whatever format you want.

Asya
P.S. I assumed all dates would be ISODate types.

3 Likes

Thanks for the answer suites for my use case :partying_face: .

However I’m getting

$documents’ is not allowed in user requests

Error at $unionWith pipeline stage, could not find any reference to the error in the docs.

PS: I’m trying this in compas shell.

Ah, $documents is new in version 6.0 - are you using an older version? The same thing can be done without 6.0 by generating an array of dates with same $map+$range and then an $unwind

2 Likes

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.