WEEK | INTENT_USERS | NEW_USERS | RETURNING_USERS | |
|---|---|---|---|---|
1 | 2025-02-24 00:00:00.000 | 205 | 78 | 163 |
2 | 2025-02-17 00:00:00.000 | 234 | 102 | 166 |
3 | 2025-02-10 00:00:00.000 | 263 | 143 | 185 |
4 | 2025-02-03 00:00:00.000 | 239 | 142 | 161 |
5 | 2025-01-27 00:00:00.000 | 186 | 82 | 140 |
6 | 2025-01-20 00:00:00.000 | 255 | 142 | 179 |
7 | 2025-01-13 00:00:00.000 | 263 | 160 | 168 |
8 | 2025-01-06 00:00:00.000 | 150 | 69 | 100 |
9 | 2024-12-30 00:00:00.000 | 172 | 103 | 106 |
10 | 2024-12-23 00:00:00.000 | 194 | 135 | 121 |
11 | 2024-12-16 00:00:00.000 | 226 | 169 | 118 |
12 | 2024-12-09 00:00:00.000 | 167 | 125 | 82 |
13 | 2024-12-02 00:00:00.000 | 99 | 68 | 53 |
14 | 2024-11-25 00:00:00.000 | 61 | 41 | 35 |
15 | 2024-11-18 00:00:00.000 | 69 | 53 | 35 |
16 | 2024-11-11 00:00:00.000 | 43 | 32 | 24 |
17 | 2024-11-04 00:00:00.000 | 30 | 30 | 16 |
m3jiRetention Analysis: Returning vs. New Owners
Updated 2025-03-01
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
WITH first_seen AS (
SELECT
DISTINCT OWNER_ID,
MIN(BLOCK_TIMESTAMP) AS first_intent_date
FROM
near.defi.fact_intents
GROUP BY
OWNER_ID
)
SELECT
DATE_TRUNC('week', fi.BLOCK_TIMESTAMP) AS week,
COUNT(DISTINCT fi.OWNER_ID) AS intent_users,
COUNT(DISTINCT CASE WHEN fi.BLOCK_TIMESTAMP = fs.first_intent_date THEN fi.OWNER_ID ELSE NULL END) AS new_users,
COUNT(DISTINCT CASE WHEN fi.BLOCK_TIMESTAMP > fs.first_intent_date THEN fi.OWNER_ID ELSE NULL END) AS returning_users
FROM
near.defi.fact_intents fi
JOIN
first_seen fs ON fi.OWNER_ID = fs.OWNER_ID
GROUP BY
week
ORDER BY
week DESC;
Last run: 13 days agoAuto-refreshes every 24 hours
17
662B
1s