분석 기법 | 퍼널 분석(FUNNEL)과 SQL 쿼리

1. 퍼널 분석( Funnel Analysis)

사용자들이 우리가 설계한 사용자 경험 루트를 따라 잘 도착하는지 확인할 때 사용하는 분석법. 최초 유입부터 최종 목적지까지 단계를 나누어서 살펴 볼 수 있다.

 

- 각 단계를 통과할 때마다 유저 수가 줄어들게 되는데 그림으로 그려보면 아래 그림처럼 점점 좁아지는 형태의 깔대기(Funnel) 모양이라 퍼널 분석 이라고 한다.

- 각 단계를 넘어가는 것을 전환(Conversion)이라 하고, 그 비율은 전환율 (Conversion rate)이라 한다

 

참고 : https://datarian.io/blog/funnel-analysis

 

- 퍼널은 유저들의 주요 행동 기준으로 쪼갤수 있다. 처음에는 전환율에 큰 영향을 줄 만한 굵직한 단위로 나누고 그 이후에 추가 분석이 필요한 부분을 더 세세하게 나눠 볼 수 있다.

 

2. 퍼널 개선 법

• 유입을 늘린다
• 각 단계의 전환율을 올린다
• 퍼널의 단계를 줄인다

1. 유입 늘리기

 

- 퍼널로 유입되는 고객 수 를 늘려 최종 단계에 도착하는 사람 수를 늘린다. 이탈률이 높더라도 유입을 크게 늘리면 자연스럽게 다음 단계로 넘어가는 사람도 증가 한다.

 ex) 유료 광고 예산을 늘리거나, 클릭을 유도할 수있는 광고 소재를 사용 하는 등.

- 특히, 신규 제품 성장은 진입률을 성장이 전환율 성장보다 임팩트가 더 크다. (진입률 자체가 높지 않기 때문) 따라서 일정 수준의 진입률이 담보된 이후에 퍼널 내 전환이 유의미 할 수 있다.

 

2. 전환율 올리기

 

- 전환이 일어나지 않는(=이탈하는) 지점을 찾아 개선해서 다음 단계로 넘어가는 전환율을 높인다.

 ex) 상세페이지의 부족한 설명을 추가하여 가입을 유도한다. 고정형 버튼으로 바꿔 가입을 유도한다. 등

 

 

3. 퍼널의 단계를 줄인다

 

- 퍼널의 단계를 최소화&최적화 시켜 단계별 이탈률을 줄인다. (전환율을 올리고 유입 수를 늘려도 결국은 한 단계를 거칠 때마다 이탈하는 유저가 생길 수 밖에 없음)

- 단, 퍼널 길이보다 중요한건 각 단계들을 유저가 즐기고 몰입할 수 있느냐 이다. 최종 목적지로 가는 여정에서, 유저가 재밌게 느끼고 몰입할 수 있는 단계를 추가한다면 오히려 전환율을 높일 수 있다.

 

3. SQL 쿼리문 (전환 수 & 전환율)

POINT.  필터링된 각 단계별 임시 테이블을 WITH 문으로 만들어 JOIN 한다.

 

- 가상의 사이트 내 고객 행동 데이터를 GA4를 통해 저장하고 SQL로 추출해보자.

- 퍼널은 유입(방문) → 구매 전환(클릭) 두 단계로 이루어져 있음.  아래 컬럼을 출력해 보자. (각 이벤트 : 방문-page_view, 클릭-click 이라고 가정)

• pv : A 페이지를 방문한 세션
• pv_after_click: A페이지 방문 & 구매 버튼을 클릭한 세션
• pv_after_click_rate : A페이지 방문 & 구매 버튼을 클릭한 세션의 전환율

1. 페이지 방문 & 클릭 이벤트가 발생한 각각의 테이블을 with 문으로 서브쿼리 생성 (페이지 방문 후에 구매 버튼을 클릭해야 하는 시간 조건 비교를 위해)

2. 아우터 쿼리에 left join으로 두 테이블 연결 (방문만 한 고객 수도 집계해야하므로 inner join이 아닌, left join )

3. id 가 서로 같고, 이벤트 시간은 방문 <구매(클릭)일 때만 조인 해줘야 함. left join ~ on 파라미터로 2개 조건 연결

WITH pv_t AS (
   SELECT CONCAT(user_id,ga_id) AS id
        ,event_name
        ,page_title
        ,event_times AS pv_time
  FROM ga
  WHERE page_title='A'
    AND event_name='page_view'
), cl_t AS (
   SELECT CONCAT(user_id,ga_id) AS id
        ,event_name
        ,page_title
        ,event_times AS cl_time
  FROM ga
  WHERE page_title='A'
    AND event_name='click'
)
SELECT COUNT(DISTINCT p.id) AS pv
      ,COUNT(DISTINCT cl.id) AS pv_after_cl
      ,COUNT(DISTINCT cl.id)/COUNT(DISTINCT p.id) AS pv_after_click_rate
FROM pv_t p
  LEFT JOIN cl_t cl ON p.id=sc.id AND p.pv_time<=cl.cl_time

 

 

 

퍼널분석 Funnel Analysis