Create a Mixpanel Dashboard with Google Apps Scripts

Maria Cerase
Jul 6 · 4 min read

If I had a penny for every time my manager asked to know about my monthly active users I wouldn’t be a millionaire, but I would have definitely gotten more shoes in my closet.

Even as a Mixpanel premium user, I cannot give access to all company board members and executives, and even if I could, they would’t bother to login and look at the dashboard, let alone dig through the data. If you are in the same situation, and can write simple JavaScript, you can pick the key metrics your manager is looking for, and publish them on a Google Sheet, without the need of giving Mixpanel access to the entire company.

Google Apps Script Authentication to Mixpanel

First you’ll need to get some project essentials right from your Mixpanel account:

  • your API secret
  • your project id

This will be necessary to call a simple generic authentication function that you can reuse wherever in your Google Apps Script, just by passing the apiSecret variable as parameter.

//auth function
function Auth(apiSecret) {
var headers = {"Authorization": "Basic " + apiSecret};
return headers
}
//test function
var headers = Auth('myverylongandfancyapisecret)';
Logger.log(headers);

Getting your Events

The most common use case is to get event count from Mixpanel, such as Login count, or the count of a user completing a specific action in a funnel. Take note of your exact event names, we will retrieve them later thanks to the generic function getMixpanelEvents that uses the formatted data api. In this example I am using a helper function called objToParams that takes a JSON object as input, and simply breaks it into a long string of url parameters, separated by &.

It’s now time for you to get the event you want out of the big lot. Some event responses require you to do some parsing work, here is an example of how to use the getMixpanelEvents function and then filter the result for your specific event. Here I am using another helper function called prepParameters, which is basically making sure we are giving the Mixpanel api all those special inputs it likes so much. See documentation.

In the parseEvent function, I expect four different inputs, then the parser sifts through the response and picks only the most recent entry of that event. If you had selected ‘hourly’ as a unit, you will get the latest hour, and so forth. You can then use this function directly in Google Sheets (you need to give permission to the sheet the first time you use the custom function).

Counting your Users

Using the events endpoint of the Mixpanel API is fairly easy, but when you are not dealing with events, and need to count for example, how many users have logged in more than once, the events API is not sufficient anymore. In these cases you actually need to POST some pretty long request with unintelligible parameters and then retrieve the response. There was no pretty way to do this: you first need to test what you want right in the user panel.

Then, open an Inspect Element panel in your browser, go to the ‘Network’ tab and refresh the page to send again the request. You will see something like this, POSTed to an endpoint called engage.

Copy that request Json and get ready for some more Google Apps Scripting, where you will have to POST that payload, together with more url parameters. Below I would pass the payload as stringified data. You can then use the postMixpanelEvents function in a parser: the result will be different depending on each response type, so you will have to develop a specialised parser function, of the same type as parseEvents.

Putting it all together

So, in general using the Formatted Data API of Mixpanel is pretty easy to do with Google Apps Script, and by parametizing the key inputs, you can create a fairly simple Google Sheet Dashboard. If you need to count users based on their properties or behaviours, you need to sweat a little more, as you need to reverse engineer the request: it’s more effort, but you will be glad once you stop getting those constant requests from management and keep sending them the link to a working Google Sheet with live data.

The Startup

Medium's largest active publication, followed by +481K people. Follow to join our community.

Maria Cerase

Written by

Eternal searcher, sample of Italian madness, Wine&Food lover, Internet & Movie addicted. Author of https://www.khaleesicode.com

The Startup

Medium's largest active publication, followed by +481K people. Follow to join our community.