How to filter Mixpanel reports by day of the week

PUBLISHED 25 Feb 2024 - UPDATED 26 Feb 2024

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:

Mixpanel Insights report showing All Events as a table

Click on Breakdown and add a Breakdown by Time:

Adding a Breakdown by Time to a Mixpanel Insights report

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

Mixpanel Insights report breaking down data by date

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

Changing a Mixpanel time breakdown to Day of the Week

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

Mixpanel Insights report broken down by day of the 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:

Time filtering options in Mixpanel

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

Creating a custom event property in Mixpanel

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

Formula for day of week in Mixpanel

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

Typecasting the Time property in Mixpanel

  1. Click on Number

Typecasting a property to Number in Mixpanel

  1. 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:

Day of the WeekValue
Sunday0
Monday1
Tuesday2
Wednesday3
Thursday4
Friday5
Saturday6

(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:

Mixpanel Insights report broken down by Day of Week custom property

Filtering by 'Day of Week' custom property

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

Day of Week filter in Mixpanel Insights report

This will exclude any Saturday data from your Insights report:

Mixpanel Insights report with Day of Week filter applied

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:

Mixpanel Insights report with custom event formula showing days of week as names

Subscribe
Notify of
guest
3 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Robin
Robin
3 months ago

well done

Kathleen
Kathleen
23 days ago

Pro-tip: you can create a day of week or hour of day Filter by creating a Breakdown on that unit and dragging it up into filters. It'll then let you select based on that unit what you want to include/exclude.

James Clark
Hi! I'm James Clark and I'm a freelance web analyst from the UK. I'm here to help with your analytics, ad operations, and SEO issues.
3
0
What do you think? Leave a commentx
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram