SEGMENT | MONTH_NUM | ACTIVE_ADDRESSES | INITIAL_ADDRESSES | RETENTION_PERCENTAGE | |
|---|---|---|---|---|---|
1 | 🌟 High Value | 1 | 28901 | 28901 | 100 |
2 | 🌟 High Value | 2 | 17702 | 28901 | 61.3 |
3 | 🌟 High Value | 3 | 8031 | 28901 | 27.8 |
4 | 🌟 High Value | 4 | 4339 | 28901 | 15 |
5 | 🌟 High Value | 5 | 1091 | 28901 | 3.8 |
6 | ❇️ Medium Value | 1 | 58570 | 58570 | 100 |
7 | ❇️ Medium Value | 2 | 28467 | 58570 | 48.6 |
8 | ❇️ Medium Value | 3 | 11783 | 58570 | 20.1 |
9 | ❇️ Medium Value | 4 | 6308 | 58570 | 10.8 |
10 | ❇️ Medium Value | 5 | 2632 | 58570 | 4.5 |
11 | ⚠️ Low Value | 1 | 9807194 | 9807194 | 100 |
12 | ⚠️ Low Value | 2 | 948632 | 9807194 | 9.7 |
13 | ⚠️ Low Value | 3 | 838832 | 9807194 | 8.6 |
14 | ⚠️ Low Value | 4 | 616644 | 9807194 | 6.3 |
15 | ⚠️ Low Value | 5 | 245333 | 9807194 | 2.5 |
bobby_daniel02_Sei User Segment Monthly
Updated 10 days ago
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
29
30
31
32
33
34
35
36
›
⌄
WITH user_initial_activity AS (
SELECT
FROM_ADDRESS,
COUNT(*) AS tx_count,
COUNT(DISTINCT DATE_TRUNC('day', block_timestamp)) AS active_days,
SUM(VALUE) as total_value,
MIN(block_timestamp) AS first_activity
FROM sei.core_evm.fact_transactions
WHERE block_timestamp >= DATEADD(MONTH, -{{Month_interval}}, CURRENT_DATE())
GROUP BY FROM_ADDRESS
),
-- Implement engagement-level classifications for user segmentation
user_segments AS (
SELECT
FROM_ADDRESS AS address,
CASE
-- Premium Segment: Power users with strong activity retention
WHEN (tx_count >= 5 AND active_days >= 2 AND total_value >= 500) THEN '🌟 High Value'
-- Standard Segment: Users with balanced activity
WHEN (tx_count >= 5 AND active_days >= 2 AND total_value >= 50) THEN '❇️ Medium Value'
-- Inactive Segment: Little to no on-chain activity
ELSE '⚠️ Low Value'
END AS segment,
first_activity
FROM user_initial_activity
WHERE first_activity >= DATEADD(MONTH, -{{Month_interval}}, CURRENT_DATE())
),
-- Monitor individual user engagement on a monthly basis
monthly_activity AS (
SELECT
us.address,
us.segment,
Last run: 10 days ago
15
611B
58s