freeman_7cohort
    Updated 2025-02-01
    with base as (
    select
    date_trunc('month',block_timestamp)as month_date
    ,buyer_address
    ,min(date_trunc('month',block_timestamp)) over(partition by buyer_address) as first_month_interaction
    ,datediff('month'
    ,min(date_trunc('month',block_timestamp)) over(partition by buyer_address)
    ,date_trunc('month',block_timestamp)
    ) as month_diff
    from ethereum.nft.ez_nft_sales
    where platform_name = 'blur'
    )
    ,new_user as (
    select
    first_month_interaction
    ,count(distinct buyer_address) as new_users
    from base
    group by 1
    )

    ,returning_users as (
    select
    first_month_interaction
    ,month_diff
    ,count(distinct buyer_address) as returning_user
    from base
    group by 1,2
    )

    ,retention_rate as (
    select
    n.first_month_interaction
    ,month_diff
    ,new_users
    ,returning_user
    ,round(r.returning_user/n.new_users,4) as retention_rate
    Last run: about 1 month ago
    First month interaction
    New Users
    month 0
    month 1
    month 2
    month 3
    month 4
    month 5
    month 6
    month 7
    month 8
    month 9
    month 10
    month 11
    month 12
    1
    2024-02-01 00:00:00.00014322100%14.65%7.03%5.24%3.51%2.66%2.42%2.03%2.34%1.93%3.70%2.50%0.10%
    2
    2024-03-01 00:00:00.00012028100%12.06%6.82%4.82%3.20%2.72%2.21%2.50%2.05%3.95%2.76%0.12%
    3
    2024-04-01 00:00:00.0009305100%11.48%6.31%4.22%3.27%2.67%2.80%2.40%4.25%2.79%0.17%
    4
    2024-05-01 00:00:00.0007740100%9.64%4.88%3.77%2.65%2.76%2.42%3.53%3.07%0.06%
    5
    2024-06-01 00:00:00.0005663100%10.54%6.73%4.56%3.87%3.44%5.00%3.58%0.19%
    6
    2024-07-01 00:00:00.0003860100%12.49%8.19%6.42%5.16%6.55%5.05%0.18%
    7
    2024-08-01 00:00:00.0004083100%12.61%9.26%7.40%8.84%6.61%0.37%
    8
    2024-09-01 00:00:00.0003212100%13.57%7.81%8.03%6.20%0.40%
    9
    2024-10-01 00:00:00.0003910100%13.12%10.92%8.72%0.28%
    10
    2024-11-01 00:00:00.0003837100%17.72%8.84%0.29%
    11
    2024-12-01 00:00:00.0008847100%14.93%0.60%
    12
    2025-01-01 00:00:00.0005562100%0.81%
    13
    2025-02-01 00:00:00.00065100%
    13
    2KB
    2s