-
Notifications
You must be signed in to change notification settings - Fork 1
/
cohort.sql
46 lines (45 loc) · 1.84 KB
/
cohort.sql
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
37
38
39
40
41
42
43
44
45
46
-- get all data in the specific date range that has event session_start
with sub_data as (
select user_pseudo_id, PARSE_DATE('%Y%m%d', event_date) as event_date,
event_timestamp, event_name
from `project_id.events_*`
WHERE _table_suffix BETWEEN '20230326' AND '20230416'
AND event_name = 'session_start'
),
user_first_week as (
select user_pseudo_id,
DATE_TRUNC(min(event_date), WEEK) AS first_week
from sub_data
group by 1
),
new_user_by_week as (
select first_week,
count(user_pseudo_id) as new_users
from user_first_week
group by 1
),
user_retention_by_week as (
select user_pseudo_id,
DATE_TRUNC(event_date, WEEK) as retention_week
from sub_data
group by 1, 2
),
retained_user_by_week as (
select user_first_week.first_week,
user_retention_by_week.retention_week,
count(user_retention_by_week.user_pseudo_id) as retained_users
from user_retention_by_week
left join user_first_week on user_first_week.user_pseudo_id = user_retention_by_week.user_pseudo_id
group by 1, 2
)
select concat(retained_user_by_week.first_week, " - " , date_add(retained_user_by_week.first_week, interval 6 day)) as week_day,
-- retained_user_by_week.retention_week,
new_user_by_week.new_users,
retained_user_by_week.retained_users,
concat('W', DATE_DIFF(retained_user_by_week.retention_week, retained_user_by_week.first_week, WEEK)) as retention_week_no, --ABS(DATE_DIFF(retained_user_by_week.retention_week, retained_user_by_week.first_week, WEEK))
retained_user_by_week.retained_users/new_user_by_week.new_users as retention_rate
from retained_user_by_week
left join new_user_by_week on retained_user_by_week.first_week = new_user_by_week.first_week
where retained_user_by_week.first_week is not null
order by 1,4
```