This weekend, I implemented a metrics dashboard for our iOS, Android and web app Sporzer. One metric that I needed on this dashboard is an improved version of the regular Monthly Active Users (MAUs) metric. Read my previous article to learn what I mean by improved Monthly Active Users.

The problem I ran into is that I can't extract an improved metric from Mixpanel with the regular exports or API. There is a way to extract the simple and dumb Monthly Active Users number (read that previous article!). But to get to my Improved Monthly Active Users, I had to use Mixpanel's Custom Query feature to get to the metric.

The desired result

Here's a screenshot of the result I wanted to achieve on Sporzer's internal metrics dashboard. Take a look at that first trend bar chart and the two numeric metric widgets to the right about Monthly Active Users.

Running a Custom Query on Mixpanel

First, I built my custom query via the Custom Query builder in Mixpanel. You can get to that feature via "Applications > Custom query" as shown in the following screenshot:

There, I constructed the following query:

<script src=“https://gist.github.com/jansn/7b2b407866b765f87159593b956d93fb.js"></script>

This query will look at the users that performed the event Viewed timeline in the data period you set with "from_date" and "to_date" to the top of the query.

Then, I use the .groupByUser() function in Mixpanel's custom query API to go trough the user's event for that month and only increase the count variable when I find an event that is at least 7 days apart from the initial event.

Then the .filter() I apply, removes all the users where the count is lower than 2, meaning that they either only did one event, or didn't do a second event that was seven days apart from the initial event of the month.

Finally, the reduce() just gives me a single integer by counting the full list of users that get returned by all the querying and filtering done by the query. (Basically just saying: tell me how many users I got).

Importing the data into our dashboard

The last step was to get this query automatically run by our metrics dashboard so it displays the data in nice bar charts and metrics widgets.

I wrote a bit of code in our dashboard that loops over the past 12 months and calls a Ruby class that executes the query via the Mixpanel API. Here it is (I left out the irrelevant parts)

Here's the code that generates the data for the graphs on the dashboard:

And here's the full code from that MixpanelMonthlyActiveUsers class:

Questions or comments?

Did you enjoy this post? If you have any questions or comments, please let me know! You can reach me on Twitter via @michiels or send me an email at mailto:michiel@firmhouse.com.

‍