Question

Is it possible to combine two JQL event queries?

  • 31 January 2019
  • 3 replies
  • 748 views
T
Badge

One of our key metrics is the proportion of active users who do a certain action at least X times a week.


I have two JQL queries:


  1. One that uses a certain event as a proxy for an "active user" and counts the number of "active users" per week.

  2. The other that counts number of users who complete the action X times or more per week.

Both queries return a collection of timestamps and counts. The timestamps are from the builtin "mixpanel.weekly_time_buckets", so they marry up between the two queries.


What I want to be able to do is to join the two collections, using the timestamp as the key. For each timestamp (i.e. for each week) I want to be able to report:


  • Count of active users (count from query A)
  • Count of users meeting our criteria (count from query B)
  • Proportion of active users meeting criteria (count from B divided by count from A)


The two approaches I've used have both been dead ends:

  • When I've tried doing a map on A to set a property to the value from a filter of B, I get an error: "Attempting to apply multiple transformations to an intermediate collection."
  • When I've tried aggregating both queries into a plain old JS collection, it complains that "main() must return a chain of transformations applied to a data source"


Any ideas?


3 replies

E
Rank
Badge +1

Hi tdwright,


Good question. We can definitely get to the answer you want in a single query by using multiple reducers. First, we'll need an event count for each user/week:


.groupByUser([mixpanel.numeric_bucket('time', mixpanel.weekly_time_buckets)], mixpanel.reducer.count())


This will give us the following output for users:


{

    "key": [

      "00ba56d3-2733-4653-b39c-254f124bcac6",

      1552089600000

    ],

    "value": 8

}


Where key[1] is the week and value is the event count for that user/week.


Then we need to group into weeks and use two reducers, one to count the users (query A), and one to only count the users if they're above a threshold (query 😎.


  .groupBy([user => user.key[1]], [

    mixpanel.reducer.count(),

    mixpanel.reducer.sum(function(user) {

      if (user.value >= eventThreshold)

        return 1;

      else

        return 0;

    })

  ])


The first parameter of the groupBy makes week-piles, then the 2nd param is two reducers. The first is a simple count, but the 2nd is a tricky way to only count certain users. Basically if the user is over the threshold, they count as 1, otherwise they count as 0. mixpanel.reducer.sum() will handle the rest. You can also do it in a single line like this:

mixpanel.reducer.sum(user => user.value >= eventThreshold? 1: 0)


Finally, we need to do some math and transform the date back to a readable format, so we'll use .map()


  .map(item => ({

    'date': new Date(item.key[0]).toISOString().slice(0,10),

    'active users': item.value[0],

    'threshold users': item.value[1],

    'ratio': item.value[1] / item.value[0]

  }))


Hope that helps!


Thanks,

Evan


T
Badge

Thanks Evan.

Haven't tried this, but from reading it I think it'll give me the count (and therefore ratio) of all users who've done the event at least once?

The problem with this that the number I care about it proportion of all active users, where "active" is defined using a different event. I.e. we use event A as a proxy for "activity", and of those users we want to know the proportion who've done event B three times or more, per week.

Or have I misunderstood your solution?

Cheers,

Tom


E
Rank
Badge +1

Hi Tom,


If the events are different, you'll probably need multiple reducers in the groupByUser() as well. One to count event A and one to count event B. You can use the mixpanel.reducer.sum() trick for this:


.groupByUser([mixpanel.numeric_bucket('time', mixpanel.weekly_time_buckets)], [ mixpanel.reducer.sum(event => event.name == eventA? 1: 0), mixpanel.reducer.sum(event => event.name == eventB? 1: 0)

])

In the next groupBy(), you'll need to use value[1] to represent the event count in the mixpanel.reducer.sum() instead of just value.


Thanks,

Evan


Reply