How to filter Mixpanel reports by day of the week

It's easy to break a Mixpanel Insights report down by day of the week, but the only way to filter it is by creating a custom event property that converts Unix time into day of the week. Here's how.

On the Mixpanel Community Slack workspace recently, a user asked how to exclude Saturdays from an Insights report. They had set up alerts on their report but didn't want to receive alerts on Saturdays. Excluding all Saturday data from the report would prevent that happening.

It's a good question, so how do we filter out data for a particular day from a Mixpanel Insights report?

Breaking down a report by day of the week

First, breaking down an Insights report by day of the week is really straightforward. Here's a report that just shows 'All Events - Total Events' for the past 30 days as a table:

Click on Breakdown and add a Breakdown by Time:

By default, this will break your report down by date:

So click on by Day and select Day of the Week (then click Apply):

Now you can see number of events by day of week:

It feels like it should be easy to go from here to filtering out a particular day. But when you add Time as a filter, you don't get the option to filter by Day or Day of the Week:

OK, you could select 'Not On' and exclude a particular date. With enough patience, you could exclude a whole run of Saturdays. But that's not very efficient, or very scalable.

Creating a 'Day of Week' custom property

Instead here's a better approach. First you'll need a custom event property to calculate day of the week.

1. Alongside Breakdown, click +
2. Click + Create to create a custom property
3. Select Custom Event Property

4. Give your custom event property a name, e.g. 'Day of Week'
5. Type in the following formula: (FLOOR(Time/86400)+4)%7

6. We need Time to be a number. In your formula, click on the Time property and Typecast

7. Click on Number

8. Click Apply

Top tip: instead of typing in the formula then type-casting 'Time', you can copy and paste the following directly into the Formula box. It may look strange, but it works!

(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7

However you add it, the formula will convert Mixpanel's Unix time into day of the week, represented by the following values:

(Credit for converting Unix time to day of the week goes to this Stack Overflow question.)

In other words, your report will look something like this:

Filtering by 'Day of Week' custom property

Now you can add a filter where Day of Week Not Equal 6:

This will exclude any Saturday data from your Insights report:

You could of course reverse this operation and use the custom property to only show Saturday data (or data from any day/s of the week you choose).

Finally, don't forget to name and Save your Insights report!

One caveat, the custom property assumes you are working in UTC and doesn't take into account the timezone you have chosen for your Mixpanel project. You'll need to tweak the formula for different timezones by adding or subtracting the correct number of seconds from the '86,400' value (86,400 being the number of seconds in a day).

Converting day numbers to day names

After I shared this article with the Mixpanel Community Slack workspace, another user Andrew Sussman of Preferabli) suggested an improvement. Why not use a formula that returns the names of the days rather than the numbers of the days (0-6)?

Create a new Mixpanel custom event property and paste in the following formula:

IFS((FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7=0,"Sunday",(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7=1,"Monday",(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7=2,"Tuesday",(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7=3,"Wednesday",(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7=4,"Thursday",(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7=5,"Friday",(FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@/86400)+4)%7=6,"Saturday")

As you can see, this formula uses IFS to run multiple checks and return the corresponding day name. Thanks, Andrew!

And here's what it looks like in action alongside our original custom event property: