1
ROI = (LTV - CAC) ÷ CAC × 100%
2

3
其中：
4
- LTV = ARPU × 用户生命周期（月）
5
- CAC = 渠道投入 ÷ 新增付费用户数

1
K = 邀请率 × 邀请接受率
2

3
- K > 1：病毒式增长
4
- K = 1：稳定增长
5
- K < 1：需要持续获客投入

1
NPS = 推荐者比例（%） - 贬损者比例（%）
2

3
评分标准：
4
- 9-10 分：推荐者
5
- 7-8 分：中立者
6
- 0-6 分：贬损者
7

8
健康值参考：
9
- NPS > 50：优秀
10
- NPS 30-50：良好
11
- NPS 0-30：需改进

1
-- 日活跃用户数
2
SELECT
3
    DATE(event_time) as date,
4
    COUNT(DISTINCT user_id) as dau
5
FROM events
6
WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
7
GROUP BY DATE(event_time)
8
ORDER BY date;
9

10
-- DAU/MAU 比率（用户粘性指标）
11
SELECT
12
    DATE(event_time) as date,
13
    COUNT(DISTINCT user_id) as dau,
14
    (SELECT COUNT(DISTINCT user_id)
15
     FROM events
16
     WHERE event_time >= DATE_TRUNC('month', CURRENT_DATE)) as mau,
17
    COUNT(DISTINCT user_id) * 1.0 /
18
    (SELECT COUNT(DISTINCT user_id)
19
     FROM events
20
     WHERE event_time >= DATE_TRUNC('month', CURRENT_DATE)) as stickiness
21
FROM events
22
WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
23
GROUP BY DATE(event_time);

1
-- N 日留存率
2
WITH first_usage AS (
3
    SELECT user_id, MIN(DATE(event_time)) as first_date
4
    FROM events
5
    GROUP BY user_id
6
),
7
daily_users AS (
8
    SELECT DISTINCT user_id, DATE(event_time) as event_date
9
    FROM events
10
)
11
SELECT
12
    f.first_date,
13
    COUNT(DISTINCT f.user_id) as new_users,
14
    COUNT(DISTINCT CASE WHEN d.event_date = f.first_date + 1 THEN f.user_id END) as d1_users,
15
    COUNT(DISTINCT CASE WHEN d.event_date = f.first_date + 7 THEN f.user_id END) as d7_users,
16
    COUNT(DISTINCT CASE WHEN d.event_date = f.first_date + 30 THEN f.user_id END) as d30_users
17
FROM first_usage f
18
LEFT JOIN daily_users d ON f.user_id = d.user_id
19
GROUP BY f.first_date
20
ORDER BY f.first_date;