[MySQL] 전환율 구하기

 

먼저 기본 테이블로 USERS, EVENTS가 있다고 가정한다.

• USERS

user_id	group	signup_date
1	A	2024-09-01
2	B	2024-09-01
3	A	2024-09-02
4	B	2024-09-02
5	C	2024-09-02
6	A	2024-09-03
7	B	2024-09-03
8	A	2024-09-03
9	B	2024-09-03
10	C	2024-09-03
11	C	2024-09-03
12	C	2024-09-03

 

• EVENTS

event_id	user_id	event_type	event_value	event_date
1	1	view	null	2024-09-05
2	1	purchase	100.00	2024-09-05
3	4	view	null	2024-09-05
4	2	view	null	2024-09-05
5	3	purchase	150.00	2024-09-06
6	4	purchase	200.00	2024-09-07
7	1	view	null	2024-09-06
8	2	purchase	120.00	2024-09-08
9	5	view	null	2024-09-03
10	6	view	null	2024-09-03
11	7	view	null	2024-09-03
12	8	view	null	2024-09-04
13	9	view	null	2024-09-03
14	10	view	null	2024-09-04
15	11	view	null	2024-09-03
16	12	view	null	2024-09-04
17	7	purchase	250.00	2024-09-04
18	10	purchase	150.00	2024-09-06
19	5	purchase	300.00	2024-09-07
20	12	purchase	100.00	2024-09-04
21	1	purchase	200.00	2024-09-07
22	4	purchase	250.00	2024-09-08

 

전환하기까지 걸린 날짜 확인

with user_first_view as (
	select user_id, min(event_date) as first_view
	from abtest.events
	where event_type = 'view'
	group by user_id
)
select e.user_id, first_view, min(event_date) as after_first_purchase, datediff(min(event_date), first_view) as days
from abtest.events e
left join user_first_view v on e.user_id = v.user_id
where event_type = 'purchase' and event_date >= first_view
group by (e.user_id)

 

user_id	first_view	after_first_purchase	days
1	2024-09-05	2024-09-05	0
2	2024-09-05	2024-09-08	3
4	2024-09-05	2024-09-07	2
5	2024-09-03	2024-09-07	4
7	2024-09-03	2024-09-04	1
10	2024-09-04	2024-09-06	2
12	2024-09-04	2024-09-04	0

 

그룹별 전환율

with user_first_view as (
	select user_id, min(event_date) as first_view
	from abtest.events
	where event_type = 'view'
	group by user_id
)
select `group`, round(count(distinct case when event_type = 'purchase' then e.user_id else null end)/count(distinct e.user_id),2) as conversion_rate
from abtest.events e
left join user_first_view v on e.user_id = v.user_id
left join abtest.users u on e.user_id = u.user_id
where event_date >= first_view
group by `group`

 

group	conversion_rate
A	0.33
B	0.75
C	0.75