Using Mixpanel Custom Queries

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.

Sporzer metrics dashboard with 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:

function main() {  
  // get events from january
  return Events({
    from_date: "2016-03-01",
    to_date: "2016-03-29",
    event_selectors: [{event: "Viewed timeline"}]
  })
  // look for users that have performed the event at least two times with 7 days apart
  .groupByUser(function(count, events) {
    var first_event = events[0];
    count = count || 1;
    for (var i = 1; i < events.length - 1; i++) {
      var check_event = events[i];
      var time_difference = check_event.time - first_event.time;
      var one_week = 604800000;

      if (time_difference >= one_week) {
        count += 1;
      } 
    }
    return count;
  })
  // filter for users who we counted the correct event occurrences for
  .filter(function(user) {
    return user.value >= 2;
  })
  // count how many users that is
  .reduce(mixpanel.reducer.count());
}

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:

class Admin::DashboardController  
  def index
    monthly_active_users = {}

    12.upto(0) do |m|
      month = m.months.ago

      mixpanel_report = MixpanelMonthlyActiveUsers.new(month)

      monthly_active_users[month] =
      mixpanel_report.active_users
    end
  end
end  

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

class MixpanelMonthlyActiveUsers  
  MIXPANEL_CUSTOM_QUERY_URL = "https://mixpanel.com/api/2.0/custom-query".freeze

  def initialize(month)
    @from_date = month.beginning_of_month.to_date
    @to_date = month.end_of_month.to_date
  end

  def active_users
    req_params = {}
    req_params[:script] = <<-EOF
    function main() {
      return Events({
        from_date: #{@from_date.to_json},
        to_date: #{@to_date.to_json},
        event_selectors: [{event: "Viewed timeline"}]
      })
      // count the number of events sent by each user
      .groupByUser(function(count, events) {
        var first_event = events[0];
        count = count || 1;
        for (var i = 1; i < events.length - 1; i++) {
          var check_event = events[i];
          var time_difference = check_event.time - first_event.time;
          var one_week = 604800000;

          if (time_difference >= one_week) {
            count += 1;
          }
        }
        return count;
      })
      // filter for users who sent more than 2 events
      .filter(function(user) {
        return user.value >= #{minimum_event_occurance};
      })
      // count how many users that is
      .reduce(mixpanel.reducer.count());
    }
    EOF

    query_uri = URI(MIXPANEL_CUSTOM_QUERY_URL)
    query_uri.query = URI.encode_www_form(req_params)
    req = Net::HTTP::Get.new(query_uri)
    req.basic_auth ENV["MIXPANEL_API_SECRET"], ""

    res = Net::HTTP.start(query_uri.hostname, query_uri.port, use_ssl: true) do |http|
      http.request(req)
    end

    JSON.parse(res.body).first
  end

  private

  def first_week_of_current_month?
    current_month? && Time.zone.today.to_date < @from_date.days_since(7)
  end

  def current_month?
    @from_date == Time.zone.today.beginning_of_month.to_date
  end

  def minimum_event_occurance
    return 1 if first_week_of_current_month?

    2
  end
end

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 michiel@firmhouse.com.