Hi,
I have a question in setting up a cohort analysis chart so that I can visualize our subscriber retention. I have been able to run the numbers in sql but setting it up in Looker so I can pivot my data around the signup months and periods have been a bit challenging. My current dataset consists of our subscriber data that has a user_id, valid_from and valid_to date which lists when a subscriber has the subscription starting and ending.
I want to be able to breakout when a user starts into cohorts of either months/weeks and then see how many of those in the cohort are still a subscriber x amount of periods that has passed. For example, 100 people started in April, 80 are still subscribers in May, 60 in June and so forth.
I’ve set up my view like this so far:
dimension_group: cohort {
sql: ${valid_from_date} ;;
type: time
timeframes: [raw, date, week, month, quarter, year]
}
dimension_group: cohort_durations {
type: duration
intervals: [day, week, month, quarter, year]
sql_start: ${valid_from_date} ;;
sql_end: CASE
WHEN ${valid_to_date} > current_date() THEN current_date()
ELSE ${valid_to_date}
END;;
}
measure: distinct_users {
type: count_distinct
sql: ${user_id};;
}
However, when I pull in the cohort, and pivot by the cohort_durations with the measure, the numbers don’t come out the way I would expect them to. Wondering if anybody has any thoughts or suggestions on how to improve on this?
The setup is pretty similar to what is described in this
https://community.looker.com/technical-tips-tricks-1021/advanced-lookml-cohort-and-retention-analysi... but slightly differs in that the data isn’t for repeating visits.
LinkedIn
Twitter
