Querying Data

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.

functionmain(){// always segment by dayvargroupKeys=[function(event){return(newDate(event.time)).toISOString().split('T')[0];}];if(params.segment_property){groupKeys.push(params.segment_property);}// "total" segmentation is just a groupbyif(params.type=="total"){returnEvents({from_date:params.from_date,to_date:params.to_date}).filter(function(e){returne.name==event_name});.groupBy(groupKeys,mixpanel.reducer.count());}// unique segmentation has to be deduped before// the groupbyif(params.type=="unique"){returnEvents({from_date:params.from_date,to_date:params.to_date}).filter(function(e){returne.name==event_name});// use groupByUser to only count the user once// per segment.groupByUser(groupKeys,function(){return1;}).groupBy(// slice off the distinct_id part of the key and group again[function(row){returnrow.key.slice(1)}],mixpanel.reducer.count())}}

Example output

// "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.

varfunnel=params.funnel||["homepage","signup","purchase"];functionmain(){returnEvents({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++;}});returncurrent_step;})// filter out users who did not enter the funnel.filter(function(item){returnitem.value>-1})// count the number of users who ended at each step.groupBy([function(item){returnitem.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){varfunnel_steps=Array(funnel.length);for(vari=0;i<funnel_steps.length;i++){funnel_steps[i]=0;}_.each(steps,function(step){// the group key was the step the user ended onvarstep_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;});});returnfunnel_steps;});}

Example output

// 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 domainfunctiongetEmailDomain(user){varemail=user.properties["$email"];if(!email){returnundefined;}pos=email.indexOf('@');if(pos<0){returnundefined;}returnemail.substr(pos+1);}functionmain(){returnPeople().groupBy([getEmailDomain],mixpanel.reducer.count()).reduce(mixpanel.reducer.top(10));}

Example output

[[{"key":["gmail.com"],"value":2074},{"key":["mixpanel.com"],"value":822},...// more data]]

User flows

Mixpanel once built a product called Flow, which helped
people understand how users were flowing through a website. Unfortunately, it was not a hugely popular product, so we chose
to retire it.

However, the analysis is an interesting example of what we can do with JQL. Here you can specify a first event (such
as "logged in"), and a depth for the flow tree.

varmain=function(){returnEvents({from_date:params.from_date,to_date:params.to_date,})// use groupByUser to create a single flow object per user.groupByUser(function(flow,events){flow=flow||{depth:0};flow.current=flow.current||flow;for(vari=0;i<events.length;i++){vare=events[i];if(flow.depth===0&&e.name!=params.first_event){// ignore events until we see the starting eventcontinue;}if(flow.depth===params.max_depth){// break after we hit the max depthreturnflow;}flow.depth++;flow.current[e.name]=flow.current[e.name]||{'count':0,'next':{}};flow.current[e.name].count++;flow.current=flow.current[e.name].next;}returnflow;}).map(function(item){// delete temporary variablesdeleteitem.value.depth;deleteitem.value.current;// return the flow objectreturnitem.value;}).reduce(mixpanel.reducer.object_merge())}