[solvesql] 유입 채널 별 실전반 전환율

쿼리에서 요구하는 것은 퍼널별 전환율을 구하는 것이다. 

이를 위해 view 테이블, scroll 테이블, click 테이블을 만든 후에 left join 하여 집계하였다. 

 

복잡한 쿼리를 위해서는 구조화가 필수임을 느낀다. 

구조화를 잘 한 후 쿼리를 작성하면 복잡한 쿼리도 술술 써지는 것 같기에 .. 

 

source, medium을 기준으로 group by 한 후 이에 대한 각 채널별 세션을 집계하면 된다. 

주의할 것은 

동일 세션 내에서도 이벤트들 간의 선후 관계가 집계 시에 고려되어야 합니다. 동시에 발생한 이벤트의 경우 다음 퍼널로 전환된 것으로 간주해주세요.

조건에 따라 각 이벤트 발생시각의 선후관계를 정의할때 부등호를 잘 체크해줘야 한다. 처음에 < 이렇게 정의했더니 집계의 수가 달라져 통과되지 않았다ㅠㅠ 

 

 

 

with view as (

select user_pseudo_id,

ga_session_id,

source,

medium,

event_timestamp_kst as view_at

from ga

where page_title ='백문이불여일타 SQL 캠프 실전반' and event_name ='page_view'

) , scroll as (

select user_pseudo_id,

ga_session_id,

source,

medium,

event_timestamp_kst as scroll_at

from ga

where page_title = '백문이불여일타 SQL 캠프 실전반' and event_name ='scroll'

) , click as (

select user_pseudo_id ,

ga_session_id ,

source ,

medium ,

event_timestamp_kst as click_at

from ga

where page_title = '백문이불여일타 SQL 캠프 실전반' and event_name = 'SQL_advanced_form_click'

)

select v.source,

v.medium,

count(distinct v.user_pseudo_id , v.ga_session_id) as pv,

count(distinct s.user_pseudo_id, s.ga_session_id) as scroll_after_pv,

count(distinct c.user_pseudo_id, c.ga_session_id) as click_after_scroll,

round(count(distinct s.user_pseudo_id, s.ga_session_id) / count(distinct v.user_pseudo_id , v.ga_session_id),3) as pv_scroll_rate,

round(count(distinct c.user_pseudo_id, c.ga_session_id) / count(distinct v.user_pseudo_id , v.ga_session_id),3) as pv_click_rate ,

round(count(distinct c.user_pseudo_id, c.ga_session_id) / count(distinct s.user_pseudo_id, s.ga_session_id),3) as scroll_click_rate

from view v left join scroll s on v.user_pseudo_id = s.user_pseudo_id

and v.ga_session_id = s.ga_session_id

and v.view_at <= s.scroll_at

left join click c on s.user_pseudo_id = c.user_pseudo_id

and s.ga_session_id = c.ga_session_id

and s.scroll_at <= c.click_at

group by 1,2

order by 3 DESC