Examples

JQL Examples

Here are some example queries to help understand what you can get from JQL. We'll include sample output, but you should try running the queries yourself.

Segmentation Query

You may be familiar with Mixpanel's Segmentation report, which allows you to filter and segment your events based on their properties. This is useful for understanding how different user segments behave, and what trends look like over time.

This is one of Mixpanel's core features, and its biggest selling point is its flexibility. We can reimplement most of the segmentation backend with only a few lines of code using JQL.

var params = {
  from_date: '2018-10-18',
  to_date: '2018-10-19',
  event_name: 'Complete Chat', //event to analyze
  segment_property: 'Platform', //property to group by
  type: 'unique' //type of query -- can be 'unique' or 'total'
};

var groupKeys = [function(event) {return (new Date(event.time)).toISOString().split('T')[0];}]; //always segment by day

if (params.segment_property)
  //add another grouping for the selected property
  groupKeys.push(function(event) {return event.properties[params.segment_property];}); 

function main() {
  //define the collection
  var collection = Events({
    from_date: params.from_date,
    to_date: params.to_date
  })
  .filter(function(event) {return event.name == params.event_name});

  // total segmentation is just a groupby
  if (params.type == 'total')
    return collection
    .groupBy(groupKeys, mixpanel.reducer.count());

  // unique segmentation has to be deduped before the groupby
  else if (params.type == 'unique')
    return collection
    .groupByUser(groupKeys, mixpanel.reducer.null()) //change the collection into unique groups of keys/users
    .groupBy([mixpanel.slice('key', 1)], mixpanel.reducer.count());
}
// "homepage" segmented by day and "referrer"
[
  {
    "key": ["2015-10-01", "google.com"],
    "value": 3487
  },
  {
    "key": ["2015-10-01", "$direct"],
    "value": 432
  },
  {
    "key": ["2015-10-01", "reddit.com"],
    "value": 876
  },
  {
    "key": ["2015-10-02", "google.com"],
    "value": 4298
  },
  ... // more data
]

Funnel Analysis

Here's a pretty simple implementation of Funnels, another of Mixpanel's core features. Funnels help you understand how users flow through a series of steps that you define.

In this example, we process each user individually to figure out how far they got in the funnel, then we count the number of users that ended at each step. From there, we can get the final funnel: the number of users who completed each step of the funnel.

var funnel = params.funnel || ["homepage", "signup", "purchase"];

function main() {
  return Events({
    from_date: params.from_date,
    to_date: params.to_date
  })
  // get the last step seen for each user
  .groupByUser(function(current_step, events) {
    if (current_step === undefined) { current_step = -1 }
    _.each(events, function(e) {
      if (e.name == funnel[current_step + 1]) {
        current_step++;
      }
    });
    return current_step;
  })
  // filter out users who did not enter the funnel
  .filter(function(item) { return item.value > -1 })
  // count the number of users who ended at each step
  .groupBy(
    [function(item) { return item.value }],
    mixpanel.reducer.count()
  )
  // do some math to add the step N users to the previous step(s)
  // this is converting us from "users who ended at each step"
  // into "users who were ever present at each step"
  .reduce(function(accumulators, steps) {
    var funnel_steps = Array(funnel.length);
    for (var i = 0; i < funnel_steps.length; i++) {
      funnel_steps[i] = 0;
    }
    _.each(steps, function(step) {
      // the group key was the step the user ended on
      var step_idx = step.key[0];
      // increment each previous step by the number of
      // users who ended at this step.
      while (step_idx > -1) {
        funnel_steps[step_idx] += step.value;
        step_idx--;
      }
    });
    // if there are a LOT of steps we might have
    // processed some of them previously, so we have
    // to add the previously processed counts together.
    _.each(accumulators, function(accumulator) {
      _.each(accumulator, function(step_count, i) {
        funnel_steps[i] += step_count;
      });
    });

    return funnel_steps;
  });
}
// step counts for a 5 step funnel
[
  [
    2538,
    437,
    354,
    274,
    214
  ]
]

Grouping By a Dynamic Key

The most interesting thing about groupBy is that it makes it very easy to group by a dynamically computed key -- instead of just doing a simple "group by these two properties", we can write a function to calculate a key.

In this example, we ask the question "what are the top 10 most common email domains of our users?". We don't have the email domain as a property, but we have the email, so we can compute it.

// helper function to pluck out the domain
function getEmailDomain(user) {
  var email = user.properties["$email"];
  if (!email) { return undefined; }
  pos = email.indexOf('@');
  if (pos < 0) { return undefined; }
  return email.substr(pos + 1);
}

function main() {
  return People()
  .groupBy([getEmailDomain], mixpanel.reducer.count())
  .reduce(mixpanel.reducer.top(10));
}
[
  [
    {
      "key": ["gmail.com"],
      "value": 2074
    },
    {
      "key": ["mixpanel.com"],
      "value": 822
    },
    ... // more data
  ]
]