flipside

    Kaia: Cohort Retention
    i_danKaia: Cohort Retention
    Updated 2025-02-26
    WITH cohorts AS (
    SELECT
    from_address
    , MIN(DATEADD(MONTH,
    CASE
    WHEN MONTH(block_timestamp) <= 6 THEN 0
    ELSE 6
    END,
    DATE_TRUNC('YEAR', block_timestamp)
    )) AS cohort_semester
    FROM kaia.core.fact_transactions
    GROUP BY 1
    ),
    semester_activity AS (
    SELECT
    from_address
    , DATEADD(MONTH,
    CASE
    WHEN MONTH(block_timestamp) <= 6 THEN 0
    ELSE 6
    END,
    DATE_TRUNC('YEAR', block_timestamp)
    ) AS activity_semester
    FROM kaia.core.fact_transactions
    ),
    cohort_retention AS (
    SELECT
    c.cohort_semester
    , w.activity_semester
    , COUNT(DISTINCT c.from_address) AS retained_users
    FROM cohorts c
    JOIN semester_activity w
    ON c.from_address = w.from_address
    GROUP BY 1, 2
    )
    SELECT
    Last run: about 1 month ago
    COHORT_SEMESTER
    COHORTSEMESTER
    Cohort Semester
    ACTIVITY_SEMESTER
    ACTIVITYSEMESTER
    Activity Semester
    RETAINED_USERS
    1
    		2019-01-01 00:00:00.0001H1 20192019-01-01 00:00:00.0001H1 2019495
    2
    		2019-01-01 00:00:00.0001H1 20192019-07-01 00:00:00.0002H2 2019285
    3
    		2019-01-01 00:00:00.0001H1 20192020-01-01 00:00:00.0003H1 202034
    4
    		2019-01-01 00:00:00.0001H1 20192020-07-01 00:00:00.0004H2 202053
    5
    		2019-01-01 00:00:00.0001H1 20192021-01-01 00:00:00.0005H1 202160
    6
    		2019-01-01 00:00:00.0001H1 20192021-07-01 00:00:00.0006H2 202138
    7
    		2019-01-01 00:00:00.0001H1 20192022-01-01 00:00:00.0007H1 202233
    8
    		2019-01-01 00:00:00.0001H1 20192022-07-01 00:00:00.0008H2 202219
    9
    		2019-01-01 00:00:00.0001H1 20192023-01-01 00:00:00.0009H1 202332
    10
    		2019-01-01 00:00:00.0001H1 20192023-07-01 00:00:00.00010H2 20239
    11
    		2019-01-01 00:00:00.0001H1 20192024-01-01 00:00:00.00011H1 20247
    12
    		2019-01-01 00:00:00.0001H1 20192024-07-01 00:00:00.00012H2 202410
    13
    		2019-01-01 00:00:00.0001H1 20192025-01-01 00:00:00.00013H1 20252
    14
    		2019-07-01 00:00:00.0002H2 20192019-07-01 00:00:00.0001H2 20194820836
    15
    		2019-07-01 00:00:00.0002H2 20192020-01-01 00:00:00.0002H1 2020126398
    16
    		2019-07-01 00:00:00.0002H2 20192020-07-01 00:00:00.0003H2 202027185
    17
    		2019-07-01 00:00:00.0002H2 20192021-01-01 00:00:00.0004H1 20211025
    18
    		2019-07-01 00:00:00.0002H2 20192021-07-01 00:00:00.0005H2 2021492
    19
    		2019-07-01 00:00:00.0002H2 20192022-01-01 00:00:00.0006H1 2022359
    20
    		2019-07-01 00:00:00.0002H2 20192022-07-01 00:00:00.0007H2 2022231
    91
    7KB
    218s