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

cql last

Tips/Best Practices:

  • Use bucket_id=6 to look at 7-day retention, and bucket_id=29 to look at 30-day retention.