MONTH | ENGAGEMENT_TIER | USER_COUNT | AVG_PROGRAMS_USED | |
|---|---|---|---|---|
1 | 2020-03-01 00:00:00.000 | Tier 0: Tester, Never Came Back | 98 | 0 |
2 | 2020-03-01 00:00:00.000 | Tier 1: Novice (2-7 days) | 22 | 0.05 |
3 | 2020-03-01 00:00:00.000 | Tier 2: Biginner (8-30 days) | 25 | 2.28 |
4 | 2020-03-01 00:00:00.000 | Tier 3: Occational User (31-60 days) | 4 | 0.5 |
5 | 2020-03-01 00:00:00.000 | Tier 4: Elite User (61-180 days) | 6 | 16.5 |
6 | 2020-03-01 00:00:00.000 | Tier 5: OG User (180+ days) | 34 | 6.12 |
7 | 2020-10-01 00:00:00.000 | Tier 0: Tester, Never Came Back | 382520 | 2.49 |
8 | 2020-10-01 00:00:00.000 | Tier 1: Novice (2-7 days) | 936 | 1.23 |
9 | 2020-10-01 00:00:00.000 | Tier 2: Biginner (8-30 days) | 459 | 5.32 |
10 | 2020-10-01 00:00:00.000 | Tier 3: Occational User (31-60 days) | 158 | 14.25 |
11 | 2020-10-01 00:00:00.000 | Tier 4: Elite User (61-180 days) | 212 | 31.86 |
12 | 2020-10-01 00:00:00.000 | Tier 5: OG User (180+ days) | 113 | 93.58 |
13 | 2020-11-01 00:00:00.000 | Tier 0: Tester, Never Came Back | 404006 | 2.22 |
14 | 2020-11-01 00:00:00.000 | Tier 1: Novice (2-7 days) | 1986 | 0.51 |
15 | 2020-11-01 00:00:00.000 | Tier 2: Biginner (8-30 days) | 500 | 2.76 |
16 | 2020-11-01 00:00:00.000 | Tier 3: Occational User (31-60 days) | 126 | 13.17 |
17 | 2020-11-01 00:00:00.000 | Tier 4: Elite User (61-180 days) | 141 | 28.23 |
18 | 2020-11-01 00:00:00.000 | Tier 5: OG User (180+ days) | 128 | 58.69 |
19 | 2020-12-01 00:00:00.000 | Tier 0: Tester, Never Came Back | 6002823 | 0.26 |
20 | 2020-12-01 00:00:00.000 | Tier 1: Novice (2-7 days) | 186105 | 0.05 |
i_danSolana Retention By Active days
Updated 2025-04-28
99
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
›
⌄
WITH tiered_users AS (
SELECT
DATE_TRUNC('month', first_tx_date) AS first_active_month
, signer
, num_days_active
, ARRAY_SIZE(programs_used) AS programs
, CASE
WHEN num_days_active = 1 THEN 'Tier 0: Tester, Never Came Back'
WHEN num_days_active BETWEEN 2 AND 7 THEN 'Tier 1: Novice (2-7 days)'
WHEN num_days_active BETWEEN 8 AND 30 THEN 'Tier 2: Biginner (8-30 days)'
WHEN num_days_active BETWEEN 31 AND 60 THEN 'Tier 3: Occational User (31-60 days)'
WHEN num_days_active BETWEEN 61 AND 180 THEN 'Tier 4: Elite User (61-180 days)'
WHEN num_days_active > 180 THEN 'Tier 5: OG User (180+ days)'
ELSE 'Unknown'
END AS engagement_tier
FROM solana.core.ez_signers
)
SELECT
first_active_month AS month
, engagement_tier
, COUNT(DISTINCT signer) AS user_count
, ROUND(AVG(programs), 2) AS AVG_Programs_used
FROM tiered_users
GROUP BY 1, 2
ORDER BY 1, 2;
Last run: about 1 month ago
...
327
23KB
207s