Filter Insights Report for Specific Days: Fridays Last 3 Months
can I set criteria in an insights report to only look at specific days. For example, I only want to look at Fridays over the last 3 months
can I set criteria in an insights report to only look at specific days. For example, I only want to look at Fridays over the last 3 months
Hi Bradley A., Please stand by as I search the Mixpanel knowledge base for an AI-powered answer. I normally take about 15 seconds or so. In case AI cannot answer your question, feel free to reach out to Mixpanel support at mixpanel.com/get-support. If you have follow-up questions for this thread, please tag me again with your question: @kapa.ai <follow-up question> 👋
Feel free to ask a follow-up question once I've answered by mentioning me with @kapa.ai
Based on the knowledge sources provided, I don't see a direct feature in Mixpanel Insights reports that allows you to filter data to show only specific days of the week (like only Fridays) over a date range.However, there are a few approaches you might consider: 1. Custom Bucketing: Insights offers Custom Bucketing for high-cardinality segments, but this is designed for organizing data into uniform or varied size buckets rather than filtering by specific days of the week.2. Inline Filters: You could potentially use inline filters to filter events by a property if you have a property that captures the day of the week. For example, if you're tracking a "day_of_week" property with your events, you could filter to only show events where that property equals "Friday."3. JQL (Advanced): For more complex queries, Mixpanel's JQL allows custom data manipulation, though the knowledge sources don't provide specific examples for day-of-week filtering.The knowledge sources don't contain enough information about a built-in "day of week" filter in Insights reports. If you need this specific functionality, you may want to check Mixpanel's documentation directly or contact their support team for the most current capabilities.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.
Time > Day of Week in the filter
Thanks, yeah I do that anyway but wanted to focus on specific days only
You can say on day of week to only be Friday’s
Thanks Andrew, maybe I’m being really stupid but I haven’t found that option. Would you mind sharing a screenshot? Thanks again!
No problem. First, create a custom event property. I called my "DayofWeekWords". It leverages the number calculation to get a day of week (0=Sunday, 1=Monday, etc.) and converts it to words.
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")Then apply that custom property as a filter to the event or the whole insight (your choice).
Aaah cool thank you!
(FLOOR(Time/86400)+4)%7 is the magic number formula
if you just create a custom property with that, you can do the same thing but have to recast it as a number and enter 0-6 for the day of the week (0=Sunday, 6=Saturday)
So this is, to my knowledge, not possible bc Mixpanel does not appear to have true "date" functions for custom properties. You can "guestimate" it with match, but it's not truly accurate.
((FLOOR(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"number","value":"$time"}"""@ / 2629743) - 660) % 12) + 1So that takes the Time property, casts it as a number, divides it by 2629743 which is an estimate for the number of seconds in a month. That number is offset by the number of months from 1/1/1970 (the unix epoch timestamp reference) and then divides that by 12, and the "floor" grabs the main integer part of the division. I add "1" to it so it starts at 1-12 instead of 0-11. The issue is that there is no standard "month" (28, 29, 30, 31 days) so it's an estimate by using the average number of days. So like I said, not perfect but it gets you closer to an answer. And that offset of 660 was based on doing 2025 dates, you would want to adjust that if you were starting with a different year. Also, a similar "estimate" could be done for day of the year (1-365[6]). ((FLOOR(Time / 86400) - 20089) % 365) + 1. That offset of 20089 is looking at 55 years since 1970 (55*365) plus 14 leap days in that period of time.
Actually, I just thought of something!
REGEX_EXTRACT(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"string","value":"$time"}"""@,"^\\d{4}-(\\d{2})-",1)That casts the Time property as a string, which then normally looks like 2025-01-01T00:03:50, so I use Regex_Extract to grab the month out of that. If you want it in words, you can do a length IFS with conversion to words.