COHORT_MONTH | NEW_USERS | ACTIVE_WITHIN_WEEK | BECAME_REGULAR_USERS | PCT_ACTIVE_WITHIN_WEEK | PCT_BECAME_REGULAR_USERS | |
|---|---|---|---|---|---|---|
1 | 2024-05-01 00:00:00.000 | 1607322 | 655557 | 257006 | 40.785667 | 15.989702 |
2 | 2024-06-01 00:00:00.000 | 3488076 | 831457 | 118825 | 23.837124 | 3.406606 |
3 | 2024-07-01 00:00:00.000 | 2026853 | 1020655 | 122354 | 50.356637 | 6.036649 |
4 | 2024-08-01 00:00:00.000 | 2159615 | 844705 | 65397 | 39.113685 | 3.028179 |
5 | 2024-09-01 00:00:00.000 | 2790747 | 1282553 | 152162 | 45.957337 | 5.452375 |
6 | 2024-10-01 00:00:00.000 | 6431814 | 2962356 | 1281431 | 46.057862 | 19.923322 |
7 | 2024-11-01 00:00:00.000 | 4759940 | 2946239 | 884208 | 61.896558 | 18.576032 |
8 | 2024-12-01 00:00:00.000 | 6325239 | 3342893 | 1035546 | 52.850066 | 16.37165 |
9 | 2025-01-01 00:00:00.000 | 10534391 | 4965402 | 1166606 | 47.135159 | 11.074261 |
10 | 2025-02-01 00:00:00.000 | 10387122 | 3773554 | 465152 | 36.329158 | 4.478161 |
11 | 2025-03-01 00:00:00.000 | 7693178 | 2782340 | 620001 | 36.166328 | 8.059101 |
Haisenberguser onboarding funnel
Updated 2025-03-29
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
›
⌄
-- Query to Analyze Aptos User Onboarding Funnel
-- Step 1: Identify first transactions
WITH first_transactions AS (
SELECT
sender,
MIN(block_timestamp) AS first_transaction_date
FROM
aptos.core.fact_transactions
WHERE
block_timestamp >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '10 months'
GROUP BY
sender
),
-- Step 2: Identify regular users (at least 5 transactions in a month)
regular_users AS (
SELECT
sender,
DATE_TRUNC('month', block_timestamp) AS activity_month,
COUNT(*) AS transaction_count
FROM
aptos.core.fact_transactions
WHERE
block_timestamp >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '10 months'
GROUP BY
sender, DATE_TRUNC('month', block_timestamp)
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,
Last run: 2 months ago
11
776B
235s