2nd Day (Bucket) Retention
As an analyst, you give high priority to retention. CQL allows you to easily create retention rate trends. Below is an example of a trend over 2nd day retention.
Syntax:
SELECT right(cohort_name,5) as Month_Day,
cohort_id,
cohort_size,
bucket_id,
(COUNT(DISTINCT user_id)/cohort_size)*100 AS Percent
FROM cooladata
WHERE date_range(doc.date_range)
CLUSTER COHORT BY 1 DAYS EACH
STARTS WITH event_name = "user_login"
FOLLOWED BY event_name = "level_up"
BUCKET BY 1 DAYS ALL
HAVING (cohort_id < 60 AND bucket_id=1)
GROUP BY 1,2,3,4
ORDER BY 2
Tips/Best Practices:
Use bucket_id=6 to look at 7-day retention, and bucket_id=29 to look at 30-day retention.