Create Custom KPI Dashboard using Mixpanel

How to generate reports with your own data by using Mixpanel Custom Report

Since Magic Mirror v1.2 released, I have got the fundamental features up and running. It’s time to be more serious on product growth and visualising data for me to check day to day will be important to understand your audience and what you’ve done right or wrong.

I am using Mixpanel on my website, but only added a few days ago. The problem is that I don’t have it with the complete set of data. However, I do have the numbers in my own backend.

Using Mixpanel Charts for Custom Data

A dashboard like Mixpanel would be ideal for me. And it happened that Mixpanel actually provides a way for you to present custom data on their website, or even better, to build on your own environment by using Custom Report.

There’re two main styles of representation:

MPChart

MPTable

So how do we feed our own data? Referencing from the API doc, we just need to provide a simple data structure like this, then hand it over to one of the MPTable or MPChart api.

var data = { 
'template1': {
'2015-09-01': 10,
'2015-09-02': 11,
'2015-09-03': 8
},
'template2': {
'2015-09-01': 1,
'2015-09-02': 18,
'2015-09-03': 17
},
'template3': {
'2015-09-01': 32,
'2015-09-02': 37,
'2015-09-03': 30
}
};

Backend Setup

Lets look at my setup for tracking downloads of my sketch templates. Every download link on my website points to the API server, and deduce the resolved link, works somewhat like an URL shortener.

A sample JSON response from the API server might look like this:

GET http://api-server/download/template1.json
200 OK {
target: "http://file-server/template.sketch"
}

In the database, there are two tables: Links table indexed by the keyword and contains the real link.

| Links | 
|-------|
|LinkId |
|Source |
|Target |
| id | Source    |               Target                | 
| 1 | template1 | http://file-server/template1.sketch |
| 2 | template2 | http://file-server/template2.sketch |
| 3 | template3 | http://file-server/template3.sketch |

The Downloads table serve as a log, so that I can always query the download record for any period of time later.

|Downloads| 
|---------|
|LinkId |
|CreatedAt|
|Count |
| id | LinkId |     CreatedAt    | Count |
| 1 | 1 | 2015-09-01 01:00 | 1 |
| 2 | 1 | 2015-09-01 02:00 | 1 |
| 3 | 3 | 2015-09-01 03:40 | 1 |
| 4 | 2 | 2015-09-01 09:41 | 1 |
| 5 | 3 | 2015-09-02 03:40 | 1 |
| 6 | 2 | 2015-09-03 01:41 | 1 |
| 7 | 3 | 2015-09-03 02:40 | 1 |
| 8 | 3 | 2015-09-03 03:41 | 1 |
| 9 | 2 | 2015-09-03 04:40 | 1 |

So the remaining is how to create the data structure that can be read by the Mixpanel framework.

Creating the Datastructure

As seen in the example above, we know we wanted to create a hash of keywords, and each keyword is also a hash of dates.

My first attempt creates a data structure like this and actually creates a weird layout:

var incomplete_data = {
'template1': {
'2015-09-01': 2,
},
'template2': {
'2015-09-01': 1,
'2015-09-03': 2,
},
'template3': {
'2015-09-01': 1,
'2015-09-02': 1,
'2015-09-03': 2,
}
};

The reason is that Mixpanel Custom Report depends on us to create the correct number of rows in each section. Noticing that ‘template1’ and ‘template2’ do not have sufficient rows, we will have to manually patch it in our application level.

We should be able to start by fetching the oldest record, then loop it all the way to the current day.

And here’s the correct version of our data structure:

var complete_data = { 
'template1': {
'2015-09-01': 2,
'2015-09-02': 0,
'2015-09-03': 0,
},
'template2': {
'2015-09-01': 1,
'2015-09-02': 0,
'2015-09-03': 2,
},
'template3': {
'2015-09-01': 1,
'2015-09-02': 1,
'2015-09-03': 2,
}
};

Getting The Code

I put it all together for Active Record in rails Controller and View. I am not a SQL guru to know enough on how might I do it in a single SQL query, so I fetch the results and post process it. Please feel free to let me know if you have any ideas to improve the code.

dashboard_controller.rb

class DashboardController < ApplicationController 
before_action :authenticate
def mixpanel
@data = report
end
private
def report
oldest_date = Download.all.order(“created_at ASC”).first.created_at
start_date = oldest_date
end_date = Date.today
@downloads = Download.select(“date(created_at) as ordered_date, sum(count) as download_count, link_id”).group(“date(created_at), link_id”)
@links = Link.all.order(“id DESC”)
data = {}
downloads_hash = {}
@downloads.each do |download|
datecount = downloads_hash[download.link_id]
unless datecount
datecount = {}
oldest_date.to_date.upto(Date.today) do |f|
datecount[“#{f}”] = 0
end
end
datecount[“#{download.ordered_date}”] = download.download_count
downloads_hash[download.link_id] = datecount
end
@links.each do |link| 
data[“#{link.source}”] = downloads_hash[“#{link.id}”]
end
data.to_json
end
end

mixpanel.erb.html

<!DOCTYPE html> 
<html>
<head>
<meta charset=”utf-8">
<! — <link rel=”stylesheet” type=”text/css” href=”https://cdn.mxpnl.com/libs/mixpanel-platform/css/reset.css"> →
<link rel=”stylesheet” type=”text/css” href=”https://cdn.mxpnl.com/libs/mixpanel-platform/build/mixpanel-platform.v0.latest.min.css">
<script src=”https://cdn.mxpnl.com/libs/mixpanel-platform/build/mixpanel-platform.v0.latest.min.js"></script>
<script>
var data = <%= raw @data %>
function plotChart(data, classname) {
var lineChart = $(‘<div></div>’).appendTo(classname).MPChart({chartType: ‘line’, highchartsOptions: {
tooltip: {
backgroundColor: ‘#fffce7’ // Make tooltip background yellow
}
}}); // Create a line chart
lineChart.MPChart(‘setData’, data); // Set the chart’s data
}
function plotTable(data, classname) {
$(‘<div></div>’).appendTo(classname).MPTable({ // create table; try scrolling horizontally over demo below
data: data,
showPercentages: true,
firstColHeader: ‘Downloads / Template’
});
}
$(document).ready(function() {
plotChart(data, “.chart”)
plotTable(data, “.table”)
});
</script>
</head>
<body class=”mixpanel-platform-body”>
<div class=”chart”></div>
<div class=”table”></div>
<script>
// Run queries and display results here
</script>
</body>
</html>

And this is the results I wanted!

Conclusion

Mixpanel’s Custom Report is definitely very useful and easy to use. The framework can be used separately with no extra dependancies on the platform nor the data on Mixpanel. Keep it in your tool chain :)

If you liked my post, checkout the Magic Mirror Plugin for Sketch I’m working on, to create Perspective Mockups easily just like Smart Objects for Photoshop.


Originally published at james.ooo on September 3, 2015.

Like what you read? Give James Tang a round of applause.

From a quick cheer to a standing ovation, clap to show how much you enjoyed this story.