Solved

Is it possible to sum user property after group by in jql ?

  • 18 September 2020
  • 3 replies
  • 89 views

I am grouping users by their earliest event (Month) .I first apply group by users and see the first time of their event then again apply group by [month],and count the number of users.I want to show total money spent by those users in that month.How do i do that,considering amount spent is user property?Can I join two queries?

icon

Best answer by ian 8 October 2020, 16:59

Hi @Vin ,

You’re almost there! You can do a join() function on Events() and People() which will allow you to link the user props with the event props (on distinct id).

That said, I have two follow-ups: 

  1. The amount spent being a user property will show the total amount spent and not the amount spent for a specific month.  User properties show the current state and unless you have separate properties for each respective month or have the spend amount attached to an event as an event property, then your query won’t get you the monthly spend.
  2. Have you checked out our first time ever filter?  You can check out the doc here but you can essentially apply this filter on Insights and break it down by Date (month) and then by the Spend amount.  

Best.

Ian

View original

3 replies

function main() {
  return Events({
    from_date: '2020-07-01',
    to_date:   '2020-09-17',
  })
  .groupByUser(function(state, events) {
    state = state || {first_time: 0};
    if(!state.first_time) {
      state.first_time = new Date(events[0].time).toISOString().split('T')[0];
    }
    return state;
    
  }
    
    ) .groupBy([getMonth], [mixpanel.reducer.count(),mixpanel.reducer.sum("properties.cashCollection")]);
    
}
 function getMonth(event) {
  return new Date(event.value.first_time).toISOString().split('T')[0].split('-')[1];
}
 

Userlevel 6
Badge +3

 

@Vin ,

Tagging in our JQL gurus @ian @glencwillis & @Emmett to see if they can help you out here!

Userlevel 1
Badge +2

Hi @Vin ,

You’re almost there! You can do a join() function on Events() and People() which will allow you to link the user props with the event props (on distinct id).

That said, I have two follow-ups: 

  1. The amount spent being a user property will show the total amount spent and not the amount spent for a specific month.  User properties show the current state and unless you have separate properties for each respective month or have the spend amount attached to an event as an event property, then your query won’t get you the monthly spend.
  2. Have you checked out our first time ever filter?  You can check out the doc here but you can essentially apply this filter on Insights and break it down by Date (month) and then by the Spend amount.  

Best.

Ian

Reply