Friday, December 11, 2015

RESTful services, Part 2 - Mixpanel

In the last blog entry in this series, we introduced our integration to connect to any REST API's querying using a SQL-like syntax and generating custom visualizations from it. The example used a live Parse account and we've since had some requests for the same for Mixpanel. 

This outlines connecting and querying Mixpanel data using our REST API interface to build custom visualizations from it. Whereas Parse API  is simple to connect, query and track, Mixpanel API's are not particularly easy to work with. Partly this is due to the way in which authentication is handled, where any parameters to the API must be signed using MD5 in alphabetical order. 

The high level steps are:
  1. Determine your API Key and API secret. This can be found under the Accounts --> Projects section of your Mixpanel account. 
  2. Determine what you'd like to query from Mixpanel from their documentation:
  3. Build the URL, and generate a signature with the parameters.
  4. Query and post process the nested data using Cloud9QL.

Let's take a live Mixpanel demo account to make it easier to follow along. This example determines the count of events over the past day. The API documentation for this can be found here:

1. Visit Enter as the host. 
(If you an existing user, this is also accessible within the REST datasource section within your account) 

2. Leave the Headers section empty. For end point, set to events/top.

3. Now comes the tricky part. To build the URL, we need to build a MD5 hash for the alphabetically ordered parameters. 

Let's build up the URL with basic params first: api_key=a8d3995557ce4cea1ae92ec03314a620&type=unique

Determine the epoch expiry date for the request. For 1st, Dec  2016, this would be 1480550400 ( Adding this to the URL, we get: api_key=a8d3995557ce4cea1ae92ec03314a620&type=unique&expire=1480550400

Next comes the sig field, which is the MD5 hash for the request:
  • Build a string with the url parameters in ascending form concatenated together without the & separator: api_key=a8d3995557ce4cea1ae92ec03314a620expire=1480550400type=unique
  • Concatenate the Mixpanel API  secret to it:  
  • api_key=a8d3995557ce4cea1ae92ec03314a620expire=1480550400type=unique6a2532b187735ea4d531f4b7dfc7a7d2
  • MD5 this string ( to generate a hash. This will be the sig field for the request. MD5 Result: befa480e75440ef4b8141f2664ba6753
  • Putting it all together, the resulting URL parameter looks like this: api_key=a8d3995557ce4cea1ae92ec03314a620&type=unique&expire=1480550400&sig=befa480e75440ef4b8141f2664ba6753
4. Add the above into the URL params field at

5. Now we are ready to query Mixpanel to fetch the results. The return data looks something like this:

{"events": [{"amount": 2,
             "event": u"funnel",
             "percent_change": -0.35635745999582824},
            {"amount": 75,
             "event": u"pages",
             "percent_change": -0.20209602478821687},
            {"amount": 2, "event": u"projects", "percent_change": 1.0}],
 "type": u"unique"}

To unwind the events nested array above, add the following Cloud9QL into the the Cloud9QL Query: 

select expand(events);

Click on Show me to visualize the results.