Level 3 - Question 44

Question

Retention: Build an event property that assigns a number to each day by dividing timestamp by milliseconds in a day.

Additional Context

Build an event property that returns day number. Use the calculate method and divide timestamp by the number of milliseconds there are in a day. =[timestamp]//8670000000. Name the property and save it.

Take Note:

Scuba converts every timestamp to Epoch milliseconds, which is the number of milliseconds since Jan 1, 1970. If you wanted to determine something like how many days has a user had since their first day, it makes sense to assign a numeric value to each day and then subtract the difference between the current day and their first day. In this case, we’re taking the value of timestamp and dividing it by the epoch milliseconds there are in a day, and the result will assign a number to each day. As I'm writing this it is 19,108 days since Jan 1, 1970, so today's day number is 19,108, if my first day was day 19,000, by subtracting the 2 I can learn that I have been active for 108 days. This is the core component used in Retention in Scuba analytics, and it can also assign numbers to days, weeks, months, and years.

Solution

Note: The numbers and result do not need to be an exact match as the data changes over time. The query construction is what is most important. See images below.

*Click to enlarge*