Question

Group Users by event volume

  • 30 March 2019
  • 1 reply
  • 496 views

Userlevel 2
Badge

Hi there,


I'm trying to learn JQL, but I'm stuck on a simple task I've tried to achieve. Here is the scenario:

I have events name `game_played` associated with a parameter `bet`.

I am trying to display the "avg(bet)" based on how many "game_played" users do on a single day.

---

Expected result:

{

0: null;

1: 75;

2: 88;

...

}

So, when players do 2 games per day, they bet in average 88.

---

So, my idea was to groupByUser for each day based on the sum of event :


function main() {
return Events({
from_date: "2019-02-28",
to_date: "2019-03-30",
event_selectors: [
{event: 'game_start', label: 'Played'}
]
})
// group each user's events by the number of event they triggered,
.groupByUser([mixpanel.numeric_bucket('time', mixpanel.daily_time_buckets)], mixpanel.reducer.count());
}



The output is a tuple with { userId, timestamp, total_game_day }.



Now, I want to group together users with the same total_game_day value, and extract the avg("bet") from them. I'm stuck on that step. How can I reuse the output value from the last groupByUser ?



1 reply

Badge +1

Hi Jice,


This is a pretty interesting one that requires some tricks with multiple reducers.


For each user, you'll need both the sum of their bet amounts and the count of their bets. So we will use .groupByUser like this:


.groupByUser([mixpanel.numeric_bucket('time', mixpanel.daily_time_buckets)], [mixpanel.reducer.count(), mixpanel.reducer.sum(event => event.properties.bet)])


(The first param is the group keys array, the second is a reducer array).


This will give us a collection of items that look like so:


{
    "key": [
      "c095d5ba-afcf-4203-a7bf-6420e53d5010",
      1553904000000
    ],
    "value": [
      7,
      672
    ]
}


Where key[1] is the date, value[0] is their bet count, and value[1] is their sum of bets.


Next, we need to make groups based on value[0] (the number of bets they've made) and key[1] (date), and find both the count of users with that bet count and the sum of all of their bets. So we'll use .groupBy like so:


.groupBy([item => item.key[1], item => item.value[0]], [mixpanel.reducer.count(), mixpanel.reducer.sum(item => item.value[1])])


Where the first param is the group array, and the 2nd param is an array of reducers. The output now looks like this:


{
    "key": [
      1553904000000,
      1
    ],
    "value": [
      722,
      37443
    ]
}


Where key[0] is the date, key[1] is the bet count, value[0] is the user count with that bet count, and value[1] is the sum of the bet amounts.


Finally, we need to do some math and date formatting, so we'll use .map to transform the items. The average bet is value[1] / value[0] / key[0]. I'd recommend something like this:


  .map(item => ({
    date: new Date(item.key[0]).toISOString().slice(0,10),
    bet_count: item.key[1],
    user_count: item.value[0],
    average: item.value[1] / item.value[0] / item.key[1]
  }))


Hope that helps!


Thanks,

Evan


Reply


Mixpanel