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.000 | 14322 | 100% | 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.000 | 12028 | 100% | 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.000 | 9305 | 100% | 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.000 | 7740 | 100% | 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.000 | 5663 | 100% | 10.54% | 6.73% | 4.56% | 3.87% | 3.44% | 5.00% | 3.58% | 0.19% | ||||
6 | 2024-07-01 00:00:00.000 | 3860 | 100% | 12.49% | 8.19% | 6.42% | 5.16% | 6.55% | 5.05% | 0.18% | |||||
7 | 2024-08-01 00:00:00.000 | 4083 | 100% | 12.61% | 9.26% | 7.40% | 8.84% | 6.61% | 0.37% | ||||||
8 | 2024-09-01 00:00:00.000 | 3212 | 100% | 13.57% | 7.81% | 8.03% | 6.20% | 0.40% | |||||||
9 | 2024-10-01 00:00:00.000 | 3910 | 100% | 13.12% | 10.92% | 8.72% | 0.28% | ||||||||
10 | 2024-11-01 00:00:00.000 | 3837 | 100% | 17.72% | 8.84% | 0.29% | |||||||||
11 | 2024-12-01 00:00:00.000 | 8847 | 100% | 14.93% | 0.60% | ||||||||||
12 | 2025-01-01 00:00:00.000 | 5562 | 100% | 0.81% | |||||||||||
13 | 2025-02-01 00:00:00.000 | 65 | 100% |
freeman_7cohort
Updated 2025-02-01
999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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
13
2KB
2s