Community Tip: Typecasting Date Properties
Date properties are a valuable part of every Mixpanel implementation. Common examples include dates for account creation, last login, free trial start dates, and more. If you’re not already sending dates, learn about date properties here.
Out of the box, our web application’s interface provides targeting of different cohorts of users using these dates. Mixpanel allows you to use the following operators on dates:
“property x” was [more than / less than] [1 – 30] [days / weeks / months] ago
This is useful for a variety of situations, such as the example above in the Segmentation Report – looking at users who purchased an item but signed up within the past week. That being said, there are some circumstances where a singular date filter won’t cut it. What happens if we want to know who made a purchase in exactly the second week, or those who bought products between two and five hours after registration. This could be set up with a double filter, but this can clutter reports a bit:
This is still a rolling window relative to today’s date, making it impossible to target a specific range. What if I want to figure out how many users created from 6/7-6/16 made a purchase in the past 30 days? We can’t do this with the “Created” property as a date – but we can if we cast this property as an integer:
You may look at this and wonder what those funky numbers are all about – these are Unix epoch timestamps, referring to the seconds elapsed since 1/1/1970 at 12:00am UTC. It’s easy to calculate an epoch timestamp from a human-readable date with a bit of simple math:
- Human readable time -> Seconds
- 1 hour = 3600 seconds
- 1 day = 86400 seconds
- 1 week = 604800 seconds
- 1 month (30.44 days) = 2629743 seconds
- 1 year (365.24 days) = 31556926 seconds
There are, of course, a litany of online tools that will do conversions like this for you – http://www.epochconverter.com/ is particularly good as it shows the epoch conversion in both UTC and your own timezone.
But if we want to find the value for 6/7/2014 @ 12:00am PST by hand:
- 6/7/2014 – 1/1/1970 = 44 years, 5 months, 6 days = 16,228 days
16,228 * 86400 = 1402099200
Epoch timestamps are always in UTC, so we now need to adjust for the UTC-PST difference (-7 hours):
- 1402099200 + (7 * 3600) = 1402124400 = 6/7/2014 @ 12:00am PST
Using epoch timestamps (the numeric description of a date) rather than “date” types unlocks a whole new dimension of power for your reports. Try using this method to focus in on users who purchased something after registering on a certain date; send a coupon to everyone who invited a friend to your site on Black Friday; email everyone who attempted to log in during a few specific minutes of downtime – the possibilities are really endless here!