What do you do when you feel like you’ve reached the limits of what Mixpanel and/or Google Analytics can offer? Well, as a reader of this blog, I can assume you’re not the type to give up. In this fourth instalment of my series on using behavioural event data, I explore some semi-hidden features of Mixpanel, discuss Google Data Studio, and square up to a Frankensteinian stitching of Mixpanel to your ESP of choice. It’s gonna be a wild ride – buckle up!
Next steps for the keen data explorer
OK, so you’ve nailed the basics. You’re tracking the right things and you’ve got all your reports perfected. Unfortunately, if you’re anything like me, you’ll find something that irritates you pretty quickly.
If you’re ready to go off-piste, what follows is a collection of techniques that I’m in the process of exploring. Each of the three topics in this post are pretty half-baked, but that’s partly the point – let’s take a speculative look at what we could do with these tools.
Warning: I’m far from an expert in any of the below. Proceed at your own risk!
1) JQL and custom reports
JQL is Mixpanel’s Javascript Query Language and it allows you to write custom queries against your data. It’s kind of hidden away from the main functionality in Mixpanel, which I think is why it took me so long to find it. I wish I’d found it sooner though because it super-charged my ability to interrogate my data.
The syntax takes a bit of getting used to, but it’s really powerful. Starting with one or more queries, you can filter(), group(), map(), reduce(), and flatten() your data to your heart’s content.
The output of these JQL scripts is JSON, optionally presented in a table. You can also export the returned JSON to further process it elsewhere.
Where it becomes really useful, however, is in custom reports. We’re just getting started with this functionality, but the idea is that you can write reports using your own JQL and Mixpanel’s UI components. One of the cool things about this is that you can develop these reports locally and deploy them with Git. Pretty neat, right?
To give you a flavour of how useful these two features can be, let me describe a real use case. In our app, we invite users to complete an event a few times a week. Since these events contribute to our data analysis pipeline for each user, the number of times each user completes this event in any given week is an important metric. In fact, our KPI is the proportion of active users who complete this event at least three times in any given week.
Trying to express this in a normal Mixpanel report is really tricky. We had one report showing the average number of times this event was completed, per active user, per week. And we had a report that told us the proportion of active users who’d checked in at least once in any given week. Outside of writing our own JQL though, we had no way to accurately express this KPI.
In JQL, it’s pretty easy:
- Query this event.
- Group by user ID and calendar week (there’s a nice built-in for this), keeping the count of events per user per week.
- Filter out the user/week groups with a count of less than 3.
- Group again, this time just by week, keeping the count of users in each week.
- Query the event we use to represent “active users”.
- Group this by week, keeping the total number of active users per week.
- Join the two datasets on the timestamp for each week.
- Divide the number of users in the first set but the number in the second.
OK, so it took eight steps, but it isn’t super tricky. And now we have a dataset we can feed into a custom report to see how this key metric has changed over time, which is vital if we’re to be effective in improving it.
One disappointment is that custom reports can’t be shown on dashboards at the present time. This massively reduces their discoverability and could mean that you will find yourself flicking backwards and forwards between reports and dashboards. Not the end of the world though.
PS For a good explanation of why the JQL API is a little funky, see Gilbert Wat’s explanation. Spoiler: it’s to do with parallelism.
2) GA custom reports vs. Google Data Studio
I hadn’t heard of Google Data Studio until a message popped up in Google Analytics the other day. My investigations are ongoing, but I thought I’d share some initial observations.
My interest was piqued because I assumed Data Studio would allow us to do some sort of data manipulation or reshaping. That’s not what it’s for. Instead, Data Studio is a tool for producing custom reports from existing data sources. The documentation for is pretty sparse, but there are some 3rd party guides out there. This walk-through covers a lot of the basics.
Since Data Studio doesn’t really have much functionality for transforming data, I found myself mapping some of our events to goals in GA. Once I’d done this, I found I was able to create custom reports in GA more easily than in Data Studio. So here’s my top tip: exhaust your options within GA before looking at Data Studio.
Flippancy aside, it looks like Google Data Studio would be a useful next step for people who’ve mastered Google Analytics and want to make their data more accessible and interactive.
And for those of you, like me, who’ve not yet fully exploited the features and functionality of vanilla Google Analytics, it really is worth digging into. Some of the things I’ve found useful are goals and sequence-filtered segments.
As an aside, I’d be really worried that Google may not give Data Studio the time it really needs to blossom. Google has form for killing off products that are deemed to be underperforming and something like this will require years for a sufficient mass of people to master the steep learning curve. Can a slow burner like Google Data Studio survive the capricious committees that run Google?
3) Hitching Mixpanel to a 3rd party ESP
I mentioned above that its possible to use Mixpanel to trigger communications to users when they trigger certain events. We started using this for interventional emails, but have only deployed about 20% of what we had planned to.
The truth is that Mixpanel’s email capabilities are pretty bad. I don’t want this post to turn into a rant, so I’ll just list my three main aggravations:
- The criteria system in campaigns is less fully featured than the equivalent system in the user explorer. This means you can identify a target group elsewhere in Mixpanel, but then be unable to target them precisely in a campaign. I have no idea why this functionality would be implemented twice in the first place, let alone with subtle differences.
- The tracking of Mixpanel emails, out of the box, is limited to sends and opens. If you want to track clicks, you have to implement this manually for each link. If you want to track other info, such as the sort of email client being used, well… you’re outta luck.
- Finally, the email editor for Mixpanel is just really bad. We’re talking a basic text box for editing HTML emails. Oh, and your changes aren’t persisted reliably unless you go through the next two pages of options (unrelated to the email contents) and click “update” on the final screen. Infuriating!
Luckily, it’s possible to mitigate points 2 and 3 by gaffer-taping Mixpanel to a dedicated ESP, such as SendGrid or Mandrill. You see, Mixpanel allow three types of action to take place when a user qualifies. Email is one. Push notifications are another. The third is webhooks – our saviour!
We’re in the early stages of exploring this, but it looks like it’s possible to:
- Design your email in a dedicated ESP.
- Get Mixpanel to trigger a webhook call whenever a user qualifies.
- Use the webhook to trigger the ESP to send the email to the user.
- Track opens, clicks, etc. using the ESP.
- Send this data back into Mixpanel, using an integration.
Steps three and five in this list is where the magic needs to happen. The calls from Mixpanel to the ESP and from the ESP back to Mixpanel need to be routed through an intermediary that can do a bit of translation. There are plenty of middleware vendors who offer this sort of service. Frankly, though, the thought of yet another service in the mix makes my head spin, so I think we’ll just build it ourselves.
Phew! It’s a bit more complicated than just using one system, but it looks (to us at least) like it’ll be worth it. If it all plays out as we hope, we’ll have all the event-driven goodness of Mixpanel campaigns without having to compromise on the editor or tracking. Wish us luck!
Conclusion
So there you have it. My quasi-ramblings about some advanced techniques I’m in the early stages of exploring. I’ve never posted something quite so speculative before, but I feel like it was probably worth doing.
The two Mixpanel techniques I’ve described show how deep a platform it is. You could use it successfully for years without ever needing to use (or even noticing) JQL, custom reports, or webhooks. But when you do discover them, a certain sort of person will have an “aha!” moment.
In contrast, my brief discussion of Google Data Studio suggests a breadth in Google’s offerings. I’m sure some people will get a lot of value from Data Studio (I’ve not ruled it out for myself), but it’s hard to see how it fits in with everything else, or whether it will still be around in a year. The main benefit it has had in my work so far has been to point out / remind me of features in Google Analytics. Whilst useful to me, I’m not sure that was the intention of the Data Studio product team!
Perhaps the specifics techniques I’ve discussed will be useful to someone, but the real hope is someone out there is inspired to take the ideas and go somewhere wild with them. Perhaps one of these three concepts will give someone pause and look again at the tools they’re using.
Most of all, I’m really curious to know what advanced techniques other people are using? Are you doing anything like I’ve described above? Are you doing something more exotic? I’d love to hear about it, so please let me know in the comments.
Top comments (0)