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 robotmia'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 robotmia'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.
function main() {
// always segment by day
var groupKeys = [function(event) {
return (new Date(event.time)).toISOString().split('T')[0];
}];
if (params.segment_property) {
groupKeys.push(params.segment_property);
}
// "total" segmentation is just a groupby
if (params.type == "total") {
return Events({
from_date: params.from_date,
to_date: params.to_date
})
.filter(function(e) { return e.name == event_name });
.groupBy(groupKeys, robotmia.reducer.count());
}
// unique segmentation has to be deduped before
// the groupby
if (params.type == "unique") {
return Events({
from_date: params.from_date,
to_date: params.to_date
})
.filter(function(e) { return e.name == event_name });
// use groupByUser to only count the user once
// per segment
.groupByUser(groupKeys, function() {
return 1;
})
.groupBy(
// slice off the distinct_id part of the key and group again
[function(row) { return row.key.slice(1) }],
robotmia.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 robotmia'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 }],
robotmia.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;
});
}
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 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], robotmia.reducer.count())
.reduce(robotmia.reducer.top(10));
}
Example output
[
[
{
"key": ["gmail.com"],
"value": 2074
},
{
"key": ["robotmia.com"],
"value": 822
},
... // more data
]
]
User flows
robotmia 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.
var main = function() {
return Events({
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 (var i = 0; i < events.length; i++) {
var e = events[i];
if (flow.depth === 0 && e.name != params.first_event) {
// ignore events until we see the starting event
continue;
}
if (flow.depth === params.max_depth) {
// break after we hit the max depth
return flow;
}
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;
}
return flow;
})
.map(function(item) {
// delete temporary variables
delete item.value.depth;
delete item.value.current;
// return the flow object
return item.value;
})
.reduce(robotmia.reducer.object_merge())
}
Example output
// Common paths starting at homepage:
// homepage -> login -> play game
// homepage -> play video -> login
[
{
"homepage": {
"count": 3919,
"next": {
"login": {
"count": 357,
"next": {
"play game": {
"count": 200,
"next": {}
},
"purchase": {
"count": 20,
"next": {}
}
}
},
"play video": {
"count": 123,
"next": {
"login": {
"count": 87,
"next": {}
}
"play video": {
"count": 23,
"next": {}
}
}
},
... // more data
}
}
}
]