Tutorial: Building an Application with JQL

Integrating a JQL query and third-party data-visualization library (D3) with a custom report

Mixpanel Applications allow developers to build visualizations, reports, and tools using the formatted data endpoints Mixpanel provides for predefined analysis (like Segmentation, Funnels, etc.). Adding a JQL query to an Application unlocks the power to visualize any custom analysis of raw Mixpanel data.

Click the "dropped" section of any of the above events to view paths those users took after completing the previous funnel step (i.e., what the users did when they didn't convert).

Mixpanel Funnels are very powerful - they allow anyone in your business to see where users drop off in important flows in your application and get granular insight into each of the funnel steps you define. But what happened to those users who didn't move on to step 3? Where did they go? What did they do instead?

In this tutorial we'll walk through an advanced example of a JQL Application that allows you to aggregate the most common paths users follow after completing a certain event if they do not convert to the next event in any given funnel. We will incorporate a D3 visualization to visualize these paths in a Mixpanel Platform report.

Let's break down all of the steps required to build this Application:

  1. Build and test your JQL query
  2. Set up the UI using Mixpanel Platform
  3. Format the data
  4. Pass your JQL query into your Application
  5. Add finishing touches

To help you follow along, you will find a link to a GitHub gist at the end of each tutorial section - this will show you the code you should have up to your current point in the tutorial.


Build the JQL query

First, we'll use the JQL Console in the Applications menu to build a query that aggregates the most common paths users take after each event in a funnel.

Set up parameters

from_date and to_date are required parameters for any JQL Query.

In our case, we also need two other pieces of information:

1. The events to include in our funnel (EVENT_LIST), and

2. the number of events (PATH_LENGTH) we want to collect for each user when they drop out of the funnel.

Let's add these parameters to our JQL query, including backup values in case they're not provided (be sure to edit the event names in the backup event list to include events in your project - include as many funnel steps as you'd like):

var EVENT_LIST = params.event_list || ['App Install', 'Game Played', 'In-App Purchase'];
var PATH_LENGTH =  params.path_length || 3;

function main() {
    return Events({
        from_date: params.from_date,
        to_date: params.to_date
    })
}

This function will return all events between the given from_date and to_date.

Collect user funnels and drop-off paths

We'll want to group all of our events by user so that we can loop through users' events in order. This is key in finding funnel steps and drop-off paths for each user.

Using .groupByUser(), JQL analyzes a few events at a time (in guaranteed chronological order) for one user at a time. We need to loop through those events, then save state on the user to loop through the next batch of events (do this inside the main() function, before the closing }):

    ...
}) // end of Events function - add code after this
.groupByUser(function(state, events) {
    // collect the funnel step and drop-off event path for each user, if they entered the funnel
    // use the current user state if events have already been analyzed for that user, or initialize user state
    state = state || {
        current_step: -1,
        exit_path: []
    };
    _.each(events, function(e) {
        // loop through each event in the current event batch
        if (e.name == EVENT_LIST[state.current_step + 1]) {
            // if the event name matches the next event in the funnel
            // reset the dropoff path (because they did not drop off if they converted to this event)
            // and move current step forward
            state.exit_path = [];
            state.current_step += 1;
        } else if (state.exit_path.length < PATH_LENGTH) {
            // record the n events after the last converted event
            state.exit_path.push(e.name);
        }
    });

    return state;
})

You may be wondering what the strange-looking _.each function on line 38 is. At Mixpanel we use Underscore.js to improve our code readability and consistency. While it's not necessary to know Underscore to use JQL or Mixpanel Platform, knowing a few basic Underscore functions will help in understanding this tutorial (and in our opinion, make your JavaScript cleaner).

_.each iterates over a list of elements, yielding each in turn to an iteratee function.

After we write each step in our JQL query, we should run the query we have so far to make sure it returns what we want (we'll use a small sample for readability):

[
    {
        "value":{
            "current_step":1,
            "exit_path":[
                "App Open"
            ]
        },
        "key":[
            "17e2d29b-f48f-48d2-945e-0ef37cd96eee"
        ]
    },
    {
        "value":{
            "current_step":-1,
            "exit_path":[
                "App Open",
                "Character Created",
                "Level Completed"
            ]
        },
        "key":[
            "3ff1465b-3b58-4c96-ad32-8e7d9896fcd8"
        ]
    },
    {
        "value":{
            "current_step":-1,
            "exit_path":[
                "App Open",
                "Game Played",
                "In-App Purchase"
            ]
        },
        "key":[
            "06899718-15c7-45f7-b8f3-f4985bab78fa"
        ]
    },
    {
        "value":{
            "current_step":-1,
            "exit_path":[
                "App Open",
                "Character Created",
                "Game Played"
            ]
        },
        "key":[
            "9a1c835c-5777-41fb-a338-46f217fca051"
        ]
    },
    {
        "value":{
            "current_step":2,
            "exit_path":[]
        },
        "key":[
            "79e6c031-3443-46a5-a377-e6ba11755075"
        ]
    },
    {
        "value":{
            "current_step":0,
            "exit_path":[
                "App Open",
                "App Open",
                "Registration Complete"
            ]
        },
        "key":[
            "eae2dd03-bca2-4cd8-9b18-d45f95eee2c3"
        ]
    },
    {
        "value":{
            "current_step":-1,
            "exit_path":[
                "App Open",
                "Character Created",
                "Game Played"
            ]
        },
        "key":[
            "e57174e2-0d0f-411d-867e-4c3eb070c874"
        ]
    }
]

Filter users

We need to take our result and filter out any users that did not enter the funnel at all so that we analyze only the applicable user paths (again, add the following code inside the main() function, before the closing }):

    ...
}) // end of .groupByUser() function - add code after this
.filter(function(item) {
    // filter out users who did not enter the funnel
    return item.value.current_step &gt; -1;
})

After running the query again, we get only the users with current_step > -1:

[
    {
        "value":{
            "current_step":0,
            "exit_path":[
                "App Open",
                "App Open",
                "Registration Complete"
            ]
        },
        "key":[
            "eae2dd03-bca2-4cd8-9b18-d45f95eee2c3"
        ]
    },
    {
        "value":{
            "current_step":1,
            "exit_path":[
                "App Open"
            ]
        },
        "key":[
            "17e2d29b-f48f-48d2-945e-0ef37cd96eee"
        ]
    },
    {
        "value":{
            "current_step":2,
            "exit_path":[
                "App Open",
                "App Open",
                "Registration Complete"
            ]
        },
        "key":[
            "79e6c031-3443-46a5-a377-e6ba11755075"
        ]
    }
]

Aggregate all users' paths

Let's consider the input format we have from our previous code and the output format we need for our Platform report:

Input:

[{ value: { current_step: 1, exit_path: ["e1", "e2", "e3"] }, key: [...]}, { value: ..., key: [..]}, ... ]

Output:
[
  {
      step: "event name 1",
      count: 12345,
      next: {
          "event name 2": {
              count: 123,
              next: {}
          }
      }
  },
]

Now we need to write a .reduce function that will take us from A to B. Again, we will take in batches of "A" inputs, so we'll need to save our state of "B" (previous_outputs in this case) as we work through each batch (place inside the main() function once again):

    ...
}) // end of .filter() function - add code after this
.reduce(function(previous_outputs, user_states) {
    // aggregate total users at each step in the funnel and
    // most common exit paths after dropping out of the funnel

    // initialize our output object for each event in the funnel
    var output = [];
    _.each(EVENT_LIST, function(e) {
        output.push({ step: e, count: 0, next: {} });
    });

    _.each(user_states, function(state) {
        // loop through each user object
        state = state.value;
        // include the user in the count for each step of the funnel they completed
        var step = 0;
        while (step &lt; state.current_step) {
            output[step].count++;
            step++;
        }
        if (step != state.current_step) { throw "you got a bug" }

        output[step].count++;
        var flow = output[step].next;
        _.each(state.exit_path, function(e) {
            // loop through each event in the user's exit path, and
            // add the event to an existing path or create a new one
            flow[e] = flow[e] || { count: 0, next: {}};
            flow[e].count++;
            flow = flow[e].next;
        });
    });

    _.each(previous_outputs, function(previous_output) {
        // merge our previous output object with the one from this batch of user objects
        merge(output, previous_output);
    });

    return output;
});

To merge the output of each "batch" we process above, we need to write two merge functions.

1. recursiveSumMerge allows us to merge objects together by adding all values at each level of the objects. Add this function at the end of your query, outside of the main() function:

    ...
} // end of entire main() function - add code after this

function recursiveSumMerge(d1, d2) {
    for (var key in d2) {
        if (d2.hasOwnProperty(key)) {
            var v1 = d1[key],
                v2 = d2[key];

            if (!(key in d1)) {
                d1[key] = v2;
            } else if (typeof v1 === "number" && typeof v2 === "number") {
                d1[key] += v2;
            } else if (typeof v1 === "object" && typeof v2 === "object") {
                recursiveSumMerge(v1, v2);
            } else {
                throw "mismatched types for key: " + key;
            }
        }
    }
}

2. merge merges event counts and uses recursiveSumMerge to merge our aggregated paths for each output object (add after all of the code we've written so far):

function merge(output1, output2) {
    for (var i = 0; i &lt; output2.length; i++) {
        output1[i].count += output2[i].count;
        recursiveSumMerge(output1[i].next, output2[i].next);
    }
}

Let's put it all together and test our final query result (using a slightly higher sample size):

[
    [
        {
            "count":34,
            "step":"App Install",
            "next":{
                "App Open":{
                    "count":11,
                    "next":{
                        "Registration Complete":{
                            "count":3,
                            "next":{
                                "Tutorial Exited":{
                                    "count":1,
                                    "next":{

                                    }
                                },
                                "Message Sent":{
                                    "count":2,
                                    "next":{

                                    }
                                }
                            }
                        },
                        "App Open":{
                            "count":3,
                            "next":{

                            }
                        }
                    }
                }
            }
        },
        {
            "count":27,
            "step":"Game Played",
            "next":{
                "Character Created":{
                    "count":13,
                    "next":{
                        "Level Complete":{
                            "count":5,
                            "next":{
                                "Tutorial Exited":{
                                    "count":1,
                                    "next":{

                                    }
                                },
                                "Message Sent":{
                                    "count":2,
                                    "next":{

                                    }
                                }
                            }
                        },
                        "App Open":{
                            "count":5,
                            "next":{

                            }
                        }
                    }
                },
                "Session End":{
                    "count":3,
                    "next":{
                        "App Open":{
                            "count":1,
                            "next":{

                            }
                        }
                    }
                },
                "Game Played":{
                    "count":25,
                    "next":{
                        "App Open":{
                            "count":3,
                            "next":{

                            }
                        },
                        "Level Complete":{
                            "count":15,
                            "next":{
                                "Session End":{
                                    "count":6,
                                    "next":{

                                    }
                                },
                                "Game Played":{
                                    "count":4,
                                    "next":{

                                    }
                                }
                            }
                        }
                    }
                }

            }
        },
        {
            "count":12,
            "step":"In-App Purchase",
            "next":{

            }
        }
    ]
]

Looks good!

General debugging tips

  • As you work through a query, check the result after each step (as we did above) so that 1) you can see if it worked as expected, and 2) you know what the input will look like for your next function.
  • Filter for a small number of users (perhaps even one) if you're having trouble starting with the bigger picture.
  • Try to return smaller pieces of your function so you can see and understand how the function is working at a granular level.
  • Reach out to support@mixpanel.com if you need help!

Here is the current state of the code. Use this as a reference in case you get lost.


Set up the Platform UI

Now our query is working and it looks awesome -- to us. But it's not exactly ready to send out to your whole company. In this step we'll start to set up a nice user interface so that we can later pull in our JQL results and visualize them in a helpful way.

Create an HTML template

Create a new Application within the "Applications" menu of the Mixpanel interface by selecting "Build an app." Name your report, and choose the "Blank Slate" option when prompted.

Let's set up all of the pieces of our UI before building each of them out. Remove the <h1>Hello, World!</h1> code from your report and replace it with the following:

<div class="mixpanel-platform-section">
    <div id="dates"></div>
    <div class="events">
        <div class="label">Funnel Steps:</div>
        <input id="event_list" value="App Install,Game Played,In-App Purchase">
        <div class="label">Alt Path Length:</div>
        <input id="path_length" value="3">
        <div id="run">RUN</div>
    </div>
    <div id="graph"></div>
</div>
<div class="mixpanel-platform-section flow">
    <div class="header">Alternative Paths</div>
    <div id="path">
        <div class="description">Click the "dropped" section of any of the above events to view paths those users took after completing the previous funnel step (i.e., what the users did when they didn't convert).</div>
    </div>
</div>

Replace the events in the value attribute of the first input (#event_list) to include the events you included in the default EVENT_LIST of your JQL query (in line 1). Include commas but no spaces in between the event names.

Set up query inputs and global variables

We have four parameters in our JQL query, each of which will be determined by user input. We added inputs for the event_list and path_length in our HTML above, but need to grab the values in order to pass them to our query. We also need to add a datepicker (Mixpanel provides one out-of-the-box) so users can dynamically change the date range of the data (from_date and to_date parameters) returned in the report. Let's add the following code inside the <script> tags in your report to set all of this up:

// Set a global variable for the JQL result
var jqlResult;

// We'll need to convert dates to strings to include them in our query
function date_to_string(d) {
    return d.toISOString().split('T')[0];
}

var datepicker = $('#dates').MPDatepicker();
var event_list_input;
var path_length_input;

function runQuery() {
    event_list_input = $('#event_list').val().split(',');
    path_length_input = parseInt($('#path_length').val());
    if (isNaN(path_length_input)) {
        alert('Error: Alternative path length must be a number');
        return;
    }

    var dates = datepicker.val();
}

Notes:

1. We defined event_list_input and path_length_input as global variables (we'll need to reference them in later functions), but set them inside a runQuery() function. The runQuery() function will be called whenever we run our JQL query, and we'll want to grab the most up-to-date values of our inputs each time it is run.

2. We need to check that path_length_input can be parsed as an integer before using it in our query.

Add a funnel graph

Mixpanel Platform also provides an out-of-the-box stacked bar chart visualization that we can use to plot our funnel. We can set up the options for that chart now. Inside the script tags of your Platform report, add the following code before the runQuery() function:

var graph = $('#graph').MPChart({
    // chart options
    chartType: 'bar',
    stacked: true,
    highchartsOptions: {
        colors: ['#cccccc', '#65afe7'],
        plotOptions: {
            series: {
                cursor: 'pointer',
                point: {
                    events: {
                        click: function () {
                            if (this.series_name == 'dropped') {
                                // enable clicking on the dropoff portion of the funnel bar to see the alternative paths for users
                                var clicked_event = this.category;
                                var index = event_list_input.indexOf(clicked_event);
                                // show dropoff paths originating from the previous event
                                // we'll define this function later
                                mapFlow(index-1);
                            }
                        }
                    }
                }
            }
        }
    }
});

Set up D3

D3 is a data-visualization tool used for rendering your data in the browser using SVG. If you find you want to display data in a way not covered by one of the Mixpanel Platform charts, the combination of Mixpanel Platform and D3 will give you almost limitless flexibility.

The only setup required for D3 is adding its minified JavaScript to the head of your report. Add this <script> tag after the others in the <head> section of the html:

<script src="https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.5/d3.min.js"></script>

D3 uses SVG to draw basic shapes on the page that represent your data. In order to render our path visualization, we'll first need to set up the SVG element that will act as a canvas for D3 to draw on. Add this code at the bottom of your report, just before the closing </script> tag:

// D3 tree setup
var margin = {top: 20, right: 40, bottom: 20, left: 40},
    width = 960 - margin.right - margin.left,
    height = 920 - margin.top - margin.bottom;

var i = 0,
    duration = 750,
    treeRoot;

var tree = d3.layout.tree()
    .size([height, width]);

var diagonal = d3.svg.diagonal()
    .projection(function(d) { return [d.y, d.x]; });

var svg = d3.select("#path").append("svg")
    .attr("width", width + margin.right + margin.left)
    .attr("height", height + margin.top + margin.bottom)
    .append("g")
    .attr("transform", "translate(" + margin.left + "," + margin.top + ")");

d3.select(self.frameElement).style("height", height + "px");

function createFlow(flowObject) {
    treeRoot = flowObject;

    // initialize path flow with the root event
    treeRoot.x0 = height / 2;
    treeRoot.y0 = 0;

    function collapse(d) {
        if (d.children) {
            d._children = d.children;
            d._children.forEach(collapse);
            d.children = null;
        }
    }

    treeRoot.children.forEach(collapse);
    update(treeRoot);
    $('#path svg').show();
}

function update(source) {
    // Update the tree from a source node

    var flow_length = path_length_input + 1;
    // Compute the new tree layout
    var nodes = tree.nodes(treeRoot).reverse(),
        links = tree.links(nodes);

    // Normalize for fixed-depth
    nodes.forEach(function(d) { d.y = width / flow_length * d.depth; });

    // Find max count
    var maxCount = 0;
    nodes.forEach(function(d) { if (d.depth &gt; source.depth) maxCount = d.count &gt; maxCount ? d.count : maxCount; });

    var ratio = calculateFlowLine(maxCount);

    // Update the nodes
    var node = svg.selectAll("g.node")
        .data(nodes, function(d) { return d.id || (d.id = ++i); });

    // Enter any new nodes at the parent's previous position
    var nodeEnter = node.enter().append("g")
        .attr("class", function(d) {return d.parent ? "node" : "root node"; })
        .attr("transform", function(d) { return "translate(" + source.y0 + "," + source.x0 + ")"; })
        .style("-webkit-clip-path", "polygon(-3px 0, " + width / flow_length + "px 0, " + width / flow_length + "px 100%, -3px 100%")
        .style("clip-path", "polygon(-3px 0, " + width / flow_length + "px 0, " + width / flow_length + "px 100%, -3px 100%")
        .style("cursor", "pointer")
        .on("click", click);

    nodeEnter.append("circle")
        .attr("r", 1e-6)
        .style("stroke", "steelblue")
        .style("stroke-width", "1.5px")
        .style("fill", function(d) { return d.children ? "#fff" : "lightsteelblue"; });

    nodeEnter.append("text")
        .attr("dx", 10)
        .attr("dy", 3)
        .attr("text-anchor", "start")
        .text(function(d) { return d.name; })
        .style("fill-opacity", 1e-6)
        .style("font", "10px sans-serif")
        .style("text-overflow", "ellipsis")
        .style("overflow", "hidden")
        .style("white-space", "nowrap");

    nodeEnter.append("title")
        .text(function(d) { return d.name; });

    // Transition nodes to their new position
    var nodeUpdate = node.transition()
        .duration(duration)
        .attr("transform", function(d) { return "translate(" + d.y + "," + d.x + ")"; });

    nodeUpdate.select("circle")
        .attr("r", 4.5)
        .style("fill", function(d) { return d.children ? "#fff" : "lightsteelblue"; });

    nodeUpdate.select("text")
        .style("fill-opacity", 1)
        .style("font-weight", function(d) {return d.children ? "bold" : "normal"; });

    // Transition exiting nodes to the parent's new position
    var nodeExit = node.exit().transition()
        .duration(duration)
        .attr("transform", function(d) { return "translate(" + source.y + "," + source.x + ")"; })
        .remove();

    nodeExit.select("circle")
        .attr("r", 1e-6);

    nodeExit.select("text")
        .style("fill-opacity", 1e-6);

    // Update the links
    var link = svg.selectAll("path.link")
        .data(links, function(d) { return d.target.id; });

    // Enter any new links at the parent's previous position
    link.enter().insert("path", "g")
        .attr("class", "link")
        .style("stroke-width", function(d) {
            var width = d.target.count * ratio &gt; 1 ? d.target.count * ratio : 1;
            return width;
        })
        .style("fill", "none")
        .style("stroke", "#ccc")
        .style("stroke-linecap", "round")
        .attr("d", function(d) {
            var o = {x: source.x0, y: source.y0};
            return diagonal({source: o, target: o});
        });

    // Transition links to their new position
    link.transition()
        .duration(duration)
        .attr("d", diagonal);

    // Transition exiting nodes to the parent's new position
    link.exit().transition()
        .duration(duration)
        .attr("d", function(d) {
            var o = {x: source.x, y: source.y};
            return diagonal({source: o, target: o});
        })
        .remove();

    // Stash the old positions for transition
    nodes.forEach(function(d) {
        d.x0 = d.x;
        d.y0 = d.y;
    });
}

function click(d) {
    // Toggle children on click
    if (d.children) {
        d._children = d.children;
        d.children = null;
    } else {
        d.children = d._children;
        d._children = null;
    }
    update(d);
}

function calculateFlowLine(count) {
    // Calculate a ratio normalize width of path links
    count = count || 1;
    return 15/count;
}

Here is the current state of the code. Use this as a reference in case you get lost.


Format the data

We know what our JQL result looks like; we need to be able to pull applicable information from the result into each of the pieces (the funnel and the path) of our visualization.

The funnel graph will take input like so:

var funnel = {
    dropped: {
        'App Install': 0,
        'Game Played': 70,
        'In-App Purchase': 80
    },
    converted: {
        'App Install': 200,
        'Game Played': 130,
        'In-App Purchase': 50
    },
}

We thus need to add the following function to our code (again, before the closing </script> tag) to get from our JQL result to the above input:

function generateFunnel(results) {
    var funnel = {
        dropped: {},
        converted: {}
    };
    _.each(results, function(funnel_step, i) {
        // loop through each of the event objects in the JQL result
        // assign the total count to the "converted" object in our funnel object
        funnel.converted[funnel_step.step] = funnel_step.count;
        // if there is a preceding event in the funnel, assign the difference between the preceding and current step to the "dropped" object
        funnel.dropped[funnel_step.step] = results[i-1] ? results[i-1].count - funnel_step.count : 0;
    });

    return funnel;
}

The D3 path visualization input looks like this:

{
    "name": "Root",
    "count": 123,
    "children": [
      {
        "name": "Child 1",
        "parent": "Root",
        "children": [
            {
              "name": "Child 1-1",
              "parent": "Child 1"
            },
            {
              "name": "Child 1-2",
              "parent": "Child 1"
            }
        ]
      },
      {
        "name": "Child 2",
        "parent": "Root"
      }
    ]
}

so we need to add the following functions (before the closing </script> tag) to get it there:

function getChildren(parentObject, flowParent, prevFlowParent, event) {
    // loop through our result to get the children of each event and add them to the applicable parent
    var eventObject = {
        'name': flowParent.count + ': ' + event,
        'count': flowParent.count,
        'parent': prevFlowParent,
        'children': []
    };

    // find the next events completed in a path after the parent event
    var next = flowParent.next;
    if ($.isEmptyObject(next) === false) {
        // continue to get the next events in the path
        for (var eventName in next) {
            if (next.hasOwnProperty(eventName)) {
                getChildren(eventObject, next[eventName], event, eventName);
            }
        }
    }
    // add all next events as children of the parent event
    parentObject.children.push(eventObject);
}

function mapFlow(index) {
    // create the object to be passed into the D3 visualization
    // jqlResult is our JQL result (we'll assign it later)
    var flows = jqlResult[index];
    var root = event_list_input[index];
    flowObject = {
        'name': flows.count + ': ' + root,
        'count': flows.count,
        'children': [],
    };

    _.each(_.keys(flows.next), function(eventName) {
        getChildren(flowObject, flows.next[eventName], '', eventName);
    });
    createFlow(flowObject);
}

Here is the current state of the code. Use this as a reference in case you get lost.


Add the JQL Query

Let's pull it all together for our final result!

Add the JQL script

To run a JQL Query inside of a Mixpanel Application, we'll need to include the query script as a parameter in our request. The easiest way to do so is to add a new set of <script> tags (before the closing <body> tag) to your Mixpanel Application with type="text/jql" and id="jql" as such:

<script type="text/jql" id="jql">
</script>

This will allow us to reference the JavaScript inside these script tags with $('#jql').html().

Add the JQL script we wrote in step 1 inside the script tags:

<script type="text/jql" id="jql">
    var EVENT_LIST = params.event_list || ['App Install', 'Game Played', 'In-App Purchase'];
    var PATH_LENGTH =  params.path_length || 3;

    function main() {
        return Events({
            from_date: params.from_date,
            to_date: params.to_date
        })
        .groupByUser(function(state, events) {
            // collect the funnel step and drop-off event path for each user, if they entered the funnel
            // use the current user state if events have already been analyzed for that user, or initialize user state
            state = state || {
                current_step: -1,
                exit_path: []
            };
            _.each(events, function(e) {
                // loop through each event in the current event batch
                if (e.name == EVENT_LIST[state.current_step + 1]) {
                    // if the event name matches the next event in the funnel
                    // reset the dropoff path (because they did not drop off if they converted to this event)
                    // and move current step forward
                    state.exit_path = [];
                    state.current_step += 1;
                } else if (state.exit_path.length &lt; PATH_LENGTH) {
                    // record the n events after the last converted event
                    state.exit_path.push(e.name);
                }
            });

            return state;
        })
        .filter(function(item) {
            // filter out users who did not enter the funnel
            return item.value.current_step &gt; -1;
        })
        .reduce(function(previous_outputs, user_states) {
            // aggregate total users at each step in the funnel and
            // most common exit paths after dropping out of the funnel

            // initialize our output object for each event in the funnel
            var output = [];
            _.each(EVENT_LIST, function(e) {
                output.push({ step: e, count: 0, next: {} });
            });

            _.each(user_states, function(state) {
                // loop through each user object
                state = state.value;
                // include the user in the count for each step of the funnel they completed
                var step = 0;
                while (step &lt; state.current_step) {
                    output[step].count++;
                    step++;
                }
                if (step != state.current_step) { throw "you got a bug" }

                output[step].count++;
                var flow = output[step].next;
                _.each(state.exit_path, function(e) {
                    // loop through each event in the user's exit path, and
                    // add the event to an existing path or create a new one
                    flow[e] = flow[e] || { count: 0, next: {}};
                    flow[e].count++;
                    flow = flow[e].next;
                });
            });

            _.each(previous_outputs, function(previous_output) {
                // merge our previous output object with the one from this batch of user objects
                merge(output, previous_output);
            });

            return output;
        });
    }

    function recursiveSumMerge(d1, d2) {
        for (var key in d2) {
            if (d2.hasOwnProperty(key)) {
                var v1 = d1[key],
                    v2 = d2[key];

                if (!(key in d1)) {
                    d1[key] = v2;
                } else if (typeof v1 === "number" && typeof v2 === "number") {
                    d1[key] += v2;
                } else if (typeof v1 === "object" && typeof v2 === "object") {
                    recursiveSumMerge(v1, v2);
                } else {
                    throw "mismatched types for key: " + key;
                }
            }
        }
    }

    function merge(output1, output2) {
        for (var i = 0; i &lt; output2.length; i++) {
            output1[i].count += output2[i].count;
            recursiveSumMerge(output1[i].next, output2[i].next);
        }
    }
</script>

Make the JQL request

At the end of the runQuery() function we created earlier (after the variable assignments and before the closing }), add the following code:

var dates = datepicker.val(); // last line of the runQuery() function - add code after this

// clear previous result
$('#path svg').hide();

// grab the JQL script as a string
var script = $('#jql').html();
// trim all extra whitespace using jQuery trim
script = $.trim(script);
// grab the parameters from the inputs we defined
var queryParams = {
    from_date: date_to_string(dates.from),
    to_date: date_to_string(dates.to),
    event_list: event_list_input,
    path_length: path_length_input
}

MP.api.jql(script, queryParams).done(function(results) {
    jqlResult = results[0];
    var funnel = generateFunnel(jqlResult);
    // add data to the chart we set up previously
    graph.MPChart('setData', funnel);
});

Let's make sure we actually run the query when appropriate. Add the following code right after the runQuery() function.

} // end of runQuery() function - add code after this

$(document).ready(function() {
    runQuery();
});
$('#dates').on('change', function() {
    runQuery();
});
$('#run').on('click', function() {
    runQuery();
});

Here is the current state of the code. Use this as a reference in case you get lost.


Add finishing touches

Finally, we can add some styling to our report. Add all of the following just before the closing </head> tag, before the HTML body:

<style>
    body {
        color: #5c617b;
        font-weight: normal;
    }
    .label {
        font-weight: bold;
        font-size: 15px;
        margin-right: 8px;
        display: inline-block;
    }
    .description {
        font-size: 13px;
        padding: 10px;
    }
    input {
        font-size: 13px;
        color: #747d94;
        background-color: white;
        height: 20px;
        width: 300px;
        margin-right: 15px;
        padding: 5px;
        border: 1px solid #bdc7d2;
        -webkit-border-radius: 3px;
        -moz-border-radius: 3px;
        border-radius: 3px;
        position: relative;
        display: inline-block;
    }
    input:focus, input:hover {
        outline: 0;
        border-color: #5ba7e1;
    }
    #path_length {
        width: 40px;
    }
    #run {
        clear: both;
        cursor: pointer;
        color: #fff;
        text-shadow: 0 -1px 0 rgba(0,0,0,0.2);
        font-weight: bold;
        text-transform: uppercase;
        background-color: #61adf0;
        background-image: -webkit-linear-gradient(#6ab5f2,#53a0ee);
        background-image: -moz-linear-gradient(#6ab5f2,#53a0ee);
        background-image: linear-gradient(#6ab5f2,#53a0ee);
        box-shadow: inset 0 1px 1px #77bdf4,0 2px 2px -1px rgba(0,0,0,0.2);
        padding: 6px 12px;
        border: 1px solid #4d93d7;
        -webkit-border-radius: 3px;
        -moz-border-radius: 3px;
        border-radius: 3px;
        vertical-align: 1px;
        display: inline-block;
    }
    #dates {
        margin-right: 20px;
        vertical-align: middle;
        display: inline-block;
    }
    .events {
        display: inline-block;
    }
    #report {
        margin: 20px 0 55px;
    }
    .header {
        font-size: 15px;
        font-weight: bold;
        text-shadow: 0 1px 0 rgba(255,255,255,0.7);
        padding: 10px 0;
    }
    #path {
        background-color: white;
        border: 1px solid #bfcfda;
        -webkit-border-radius: 5px;
        -moz-border-radius: 5px;
        border-radius: 5px;
    }
    #path svg {
        display: none;
    }
</style>

That's it! Here is the final result for reference. Commit your work, and take a look at what you've accomplished. Your report should match the one at the beginning of the tutorial. (Is your report blank? Check to make sure the event names you defined throughout your code - in lines 100 and 428 of the final result - actually exist in your project!)

You have integrated a JQL query and a D3 visualization into a Mixpanel Platform report, and have empowered anyone in your business to gain valuable insight into alternative user paths in funnels.