Hi everybody!
TL;DR: This guide explains how we solved our use case of needing dynamic cohorts based on stickiness. The solution involves combining derived properties and distinct values of property option for counting events in a cohort definition.
At my workplace, we use stickiness to classify user engagement levels (e.g., a casual user is someone who uses the product for one week in a month, and a power user is someone who uses the product for three or more weeks in a month). We've been struggling to incorporate stickiness-based engagement into our dynamic cohorts, which are essential for our analytics.
Recently, we found a good enough solution to our use case by combining derived properties and distinct values of property options for counting events in a cohort definition.
1) To create a derived property that returns a numeric order of a week in a year, we used the Amplitude property `Server Upload Time` and transformed the timestamp to a week number using the following formula:
`Week Number` = DATE_TIME_FORMATTER(LONG_TO_TIME(`Server Upload Time`), "yyyy-MM-dd'T'HH:mm:ss.SSS", "w")
2) We then created a user cohort using the following criteria:
...did perform `App Launched`
with distinct value of property `Week Number` >= 3
any time during last 30 days
This cohort contains all users who performed App Launched in at least three distinct weeks during the last 30 days. Although this approach will pick-up some edge cases when week is split between two periods we still find it insightful. Simiar can be done on a daily level with already existing Amplitude property `Event Day of Week`.
I hope that this guide can be helpful to others looking to incorporate stickiness-based engagement into their dynamic cohorts.
