Andrew S.

Commented on Filter Insights Report for Specific Days: Fridays...·Posted inMixpanel Bot

Note, though, that it's then hard to sort that by actual month (January = 01 vs "April" is before "January"). So you may want to use IFS to do something like 01: January

Commented on Filter Insights Report for Specific Days: Fridays...·Posted inMixpanel Bot

Andrew S.

IFS(REGEX_EXTRACT(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"string","value":"$time"}"""@,"^\\d{4}-(\\d{2})-",1)="01","January",REGEX_EXTRACT(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"string","value":"$time"}"""@,"^\\d{4}-(\\d{2})-",1)="02","February",REGEX_EXTRACT(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"string","value":"$time"}"""@,"^\\d{4}-(\\d{2})-",1)="03","March",REGEX_EXTRACT(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"string","value":"$time"}"""@,"^\\d{4}-(\\d{2})-",1)="04","April",REGEX_EXTRACT(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"string","value":"$time"}"""@,"^\\d{4}-(\\d{2})-",1)="05","May",REGEX_EXTRACT(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"string","value":"$time"}"""@,"^\\d{4}-(\\d{2})-",1)="06","June",REGEX_EXTRACT(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"string","value":"$time"}"""@,"^\\d{4}-(\\d{2})-",1)="07","July",REGEX_EXTRACT(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"string","value":"$time"}"""@,"^\\d{4}-(\\d{2})-",1)="08","August",REGEX_EXTRACT(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"string","value":"$time"}"""@,"^\\d{4}-(\\d{2})-",1)="09","September",REGEX_EXTRACT(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"string","value":"$time"}"""@,"^\\d{4}-(\\d{2})-",1)="10","October",REGEX_EXTRACT(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"string","value":"$time"}"""@,"^\\d{4}-(\\d{2})-",1)="11","November",REGEX_EXTRACT(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"string","value":"$time"}"""@,"^\\d{4}-(\\d{2})-",1)="12","December")

Commented on Filter Insights Report for Specific Days: Fridays...·Posted inMixpanel Bot

Andrew S.

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.

Commented on Filter Insights Report for Specific Days: Fridays...·Posted inMixpanel Bot

Andrew S.

REGEX_EXTRACT(@"""{"label":"Time","propertyDefaultType":"datetime","resourceType":"event","type":"string","value":"$time"}"""@,"^\\d{4}-(\\d{2})-",1)

Commented on Filter Insights Report for Specific Days: Fridays...·Posted inMixpanel Bot

Andrew S.

Actually, I just thought of something!

Commented on Filter Insights Report for Specific Days: Fridays...·Posted inMixpanel Bot

Andrew S.

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) + 1

So 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.

Commented on Filter Insights Report for Specific Days: Fridays...·Posted inMixpanel Bot

Andrew S.

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)

Commented on Filter Insights Report for Specific Days: Fridays...·Posted inMixpanel Bot

Andrew S.

(FLOOR(Time/86400)+4)%7 is the magic number formula

Commented on Filter Insights Report for Specific Days: Fridays...·Posted inMixpanel Bot

Andrew S.

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).

Commented on Filter Insights Report for Specific Days: Fridays...·Posted inMixpanel Bot

Andrew S.

You can say on day of week to only be Friday’s

Awards & Badges

View All