Introducing JQL: A Query Language for Analytics

Today, we are excited to announce the official release of JQL (JavaScript Query Language), a powerful new way to access your Mixpanel data.

By letting you write queries using JavaScript, JQL is flexible enough to answer any question about your data while remaining familiar and intuitive.

Although JQL is a general-purpose tool, it is designed to make it easy to express typical analytics queries about customer behavior. It has a functional programming design, centered around streaming primitives like map, groupBy and reduce. By composing these elements, it is easy to write queries that scan over user activity streams, compute aggregates, or slice and dice the dataset on multiple dimensions.

The aim of this post is to explain the purpose of JQL, the motivations behind its design, and our plans for its future.

Why create JQL?

We created JQL so that our customers could query their Mixpanel data with maximum flexibility. We started working on JQL over a year ago after it became increasingly apparent that our customers had a subset of questions that were very complex and could not be answered using our built-in reports. Before JQL, the only solution to this problem was to export the data and run the required analysis in another tool. This was often a laborious process that could take hours, and it lacked the real-time, interactive quality of our built-in reports. Now, you can open the JQL Console app in Mixpanel, compose a query, and get the answer in seconds.

How did we design and create it?

A great deal of time and effort went into the design of JQL. Mixpanel is built on top of our own database, Arb, which has evolved over the past five years to support a set of dedicated query types that power our built-in reports. To achieve the flexibility we wanted, we needed to add a general-purpose query interface to Arb. Our goal at the outset of the project was to choose a query language that was simple, familiar, powerful and fast.

We arrived at JQL after many iterations and experiments with different languages. To evaluate languages, we used them to write the queries for our existing reports, like funnelsand retention.

We started with SQL, the default choice used by most databases. However, we soon realized that SQL was very verbose and awkward for engagement analytics queries. For instance, a multi-step funnel, segmented on a couple of columns, would extend to several screenfuls of complex SQL, which is cumbersome to write and difficult to reason about. We wanted something simpler that felt purpose-built for analytics. We experimented with our own declarative language but were reluctant to require users to learn an entirely new language.

A month into the process, we built a prototype that embedded the V8 JavaScript engine into Arb. It was immediately clear that this was a powerful combination. JavaScript is easy to learn and familiar to many programmers. Plus, since it’s a feature-rich programming language, it gave us the flexibility to do interesting and complex analysis of our data.

Adopting this functional paradigm allowed us to express queries as a pipeline of fundamental primitives, like map, filter and reduce. These would serve as the basic building blocks of the language. This also allowed us to make optimizations like hooking into the V8 runtime to replace these functions with native code and parallelizing various stages of the pipeline efficiently across an Arb cluster.

Next, we added some features to make it easier to do common tasks. For instance, groupByallows you to segment your data and groupByUser allows you to analyze each user’s activity history. Segmentation and per-user activity histories are the building block of most engagement analytics. By virtue of these functions, they are baked into JQL as a fundamental concept. We also added builtins for common aggregations. Returning to our original litmus test of writing queries for our own reports, we found that using JQL resulted in terse, understandable code.

What happens next?

From the beginning, JQL was intended to be used both by customers and our own teams.

Internally, among many use cases, JQL offers exciting possibilities for our engineering efficiency. By having a generic, flexible interface to Arb, we will be able to iterate on new products and features quickly. Our intent is therefore to continue to invest in the language. Current plans include another round of optimizations and the addition of a couple more primitives.

As we add these, we hope to make them composable, orthogonal features that interact predictably. We have laid a powerful, flexible foundation, which we will continue to extend. As we move forward, JQL will serve as the internal platform for all of our standard reports and tools.

Externally, we’re seeing more and more Mixpanel customers executing JQL queries. By choosing to provide a new query API, even one based on JavaScript, we know we face a difficult challenge in terms of adoption. However, we’ve surprised ourselves with how quickly it has become a commonly used tool at Mixpanel. Every day, more people find new uses for JQL, and more analytics questions get answered. Similarly, we’re blown away by the adoption among our beta customers.

If you know some JavaScript, you can be productive in JQL after about an hour of working through the Getting Started Guide. We hope you’ll give it a try and find it as useful and fun to work with as we do!