COHORT_MONTH | NEW_USERS | ACTIVE_WITHIN_WEEK | BECAME_REGULAR_USERS | PCT_ACTIVE_WITHIN_WEEK | PCT_BECAME_REGULAR_USERS | |
|---|---|---|---|---|---|---|
1 | 2024-12-01 00:00:00.000 | 41567 | 0 | 11248 | 0 | 27.059927 |
2 | 2025-01-01 00:00:00.000 | 172257 | 0 | 88779 | 0 | 51.538689 |
3 | 2025-02-01 00:00:00.000 | 42538 | 0 | 26567 | 0 | 62.454746 |
4 | 2025-03-01 00:00:00.000 | 89081 | 0 | 77864 | 0 | 87.408089 |
5 | 2025-04-01 00:00:00.000 | 3571 | 0 | 1049 | 0 | 29.375525 |
Haisenberguser onboarding funnel
Updated 2025-04-06
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
›
⌄
-- Step 1: Identify First transactions
WITH first_transactions AS (
SELECT
from_address as sender,
MIN(block_timestamp) AS first_transaction_date
FROM
ink.core.fact_transactions
WHERE
block_timestamp >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '10 months'
GROUP BY
1
),
-- Step 2: Identify regular users (at least 5 transactions in a month)
regular_users AS (
SELECT
from_address as sender,
DATE_TRUNC('month', block_timestamp) AS activity_month,
COUNT(*) AS transaction_count
FROM
ink.core.fact_transactions
WHERE
block_timestamp >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '10 months'
GROUP BY
1, 2
HAVING
COUNT(*) >= 5
),
-- Step 3: Combine stages to create user journey
user_journey AS (
SELECT
DATE_TRUNC('month', ft.first_transaction_date) AS cohort_month,
COUNT(DISTINCT ft.sender) AS new_users,
COUNT(DISTINCT CASE WHEN ru.sender IS NOT NULL THEN ft.sender END) AS became_regular_users,
COUNT(DISTINCT CASE WHEN EXISTS (
Last run: 2 months ago
5
270B
69s