[BigQuery] 현업 문제 해결 쿼리 작성(1) : Funnel 분석

배경

최근 2개월 동안 지원(구직: 유저 타겟 행동)이 지속적으로 줄어 관련팀에서 다각도로 원인을 파악하기 시작하였다.

해당 문제를 Activation 관점에서 제품에 문제가 없는지 확인하고, 제품 내 지원에 영향을 주는 요인이 있는지 확인해보자.

 

서비스 설명

공고리스트 페이지 / 검색 페이지 / 북마크 페이지  👉 세 개의 페이지를 통해 '공고 페이지'에 진입 👉 공고 페이지에서 '지원' 가능

 

 

분석 단계

1. 지원이 실제 감소하는지 주단위(주 별로) 확인하고,
2. 감소하고 있다면, [공고리스트, 검색, 북마크 페이지]에서 지원까지의 퍼널에 문제가 없는지 확인해주세요.

[선택] 각 페이지 별로도 공고페이지 전환율을 확인해주세요.

3. 최종 결론을 글로 작성해주세요. 해당 상황에 대한 인사이트가 아니더라도 팀에 제안할 부분이 있다면 그 또한 작성해주시기 바랍니다.

 

 

문제1

▶︎ 지원이 실제 감소하는지 '주' 단위로 확인

- 주 별 event_name = 'apply' 인 session_id 수 구하기

- date_trunc()로 주 단위로 날짜 변경 후, date으로 감싸 YYYY-MM-DD 형식으로 변경

select date(date_trunc(timestamp, week)) as week,
      count(*) as apply_cnt
from wanted_sample_data.log_data
where event_name = 'apply'
group by 1
order by 1

 

지원이 실제로 감소하고 있는 추세 확인

 

 

문제2. 어떤 페이지의 유입량이 많은지 확인(주 단위)

▶︎ 주별 ‘_view’로 끝나는 event_name 수와 비중(%)을 구하시오. (단, ‘jd_view’는 본 분석에서 제외)

- 윈도우 함수를 사용 : 함수이름(컬럼) OVER (PARTITION BY 구간을 나누는 기준이 되는 컬럼 ORDER BY 정렬 기준 컬럼)

with view_count as (
  select date(date_trunc(timestamp, week)) as week,
        event_name,
        count(session_id) as session_cnt
from wanted_sample_data.log_data
where event_name != 'jd_view' and regexp_contains(event_name, '_view$')
group by 1, 2
)
select *,
        sum(session_cnt) over (partition by week) as total,
        round(session_cnt / sum(session_cnt) over (partition by week), 3) as page_rate
from view_count
order by 1, 2

-- TIP
-- 만약, total_by_week이 0일 가능성이 있다면 '/'을 활용하는대신 safe_divide를 사용하십시오.
-- safe_divide는 분모가 0일경우, null을 출력합니다.
-- safe_divide(session_cnt, SUM(session_cnt) OVER (PARTITION BY week))

공고리스트 페이지의 유입량, 유입률이 타 페이지 대비 월등히 높은 것을 확인할 수 있음

 

🧐 문자열 비교 : REGEXP_CONTAINS vs. LIKE

👉 권장사항: 가능한 경우 REGEXP_CONTAINS 대신 LIKE을 사용!

 

BigQuery에서는 REGEXP_CONTAINS 함수 또는 LIKE 연산자를 사용하여 문자열을 비교할 수 있다.

REGEXP_CONTAINS는 더 많은 기능을 제공하지만 실행 시간이 느리다. 와일드 카드 일치와 같이 REGEXP_CONTAINS에서 제공하는 정규 표현식의 전체 성능이 필요하지 않다면 REGEXP_CONTAINS 대신 LIKE를 사용하는 것이 더 빠르다.

 

문제3

▶︎ 주별 (event_name=‘jd_view’인 session_id) /(event_name=[1-2. 조건]인 session_id)를 구하시오.

- event_name=[1-2. 조건]인 session_id와 event_name=‘jd_view’인 session_id 를 따로 구하고 둘을 session_id 기준으로 조인

- 주별 데이터를 구해야하므로, week(timestamp)도 함께 처리

- countif(조건) 을 활용하여 조건에 맞는 행만 집계

with firstview as (
  select date(date_trunc(timestamp, week)) as week,
          session_id
from wanted_sample_data.log_data
where event_name != 'jd_view' and regexp_contains(event_name, '_view$')
),

jdview as(
  select date(date_trunc(timestamp, week)) as week,
          session_id
  from wanted_sample_data.log_data
  where event_name = 'jd_view'
)

select firstview.week,
      count(firstview.session_id) as firstview_cnt,
      count(jdview.session_id) as jdview_cnt,
      round(countif(jdview.session_id is not null)/count(firstview.session_id), 3) as first_funnel
from firstview
left join jdview on firstview.session_id = jdview.session_id and firstview.week = jdview.week
group by 1
order by 1

 

 

 

문제4

▶︎ 주별 (event_name=‘apply’인 session_id) / (event_name=‘jd_view’인 session_id)를 구하시오.

- 문제 3 풀이와 유사

with 
jdview as(
  select date(date_trunc(timestamp, week)) as week,
          session_id
  from wanted_sample_data.log_data
  where event_name = 'jd_view'
),

apply as (
  select date(date_trunc(timestamp, week)) as week,
          session_id
  from wanted_sample_data.log_data
  where event_name = 'apply'
)

select jdview.week,
      count(jdview.session_id) as jdview_cnt,
      count(apply.session_id) as apply_cnt,
      round(countif(apply.session_id is not null)/count(jdview.session_id), 3) as second_funnel
from jdview
left join apply on jdview.session_id = apply.session_id and jdview.week = apply.week
group by 1
order by 1

 

 

문제5

▶︎ 문제 3, 4에서 구한 값을 구한 값을 week column으로 조인하시오.

- firstview, jdview, apply 테이블을 한번에 조인하고, first_funnel, second_funnel을 계산

WITH
  firstview AS (
  SELECT
    DATE(DATE_TRUNC(timestamp, week)) AS week,
    session_id
  FROM
    wanted_sample_data.log_data
  WHERE
    event_name !='jd_view'
    AND REGEXP_CONTAINS(event_name,'_view$')
  ),

  jdview AS (
  SELECT
    DATE(DATE_TRUNC(timestamp, week)) AS week,
    session_id
  FROM
    wanted_sample_data.log_data
  WHERE
    event_name ='jd_view'
  ),

  apply AS (
  SELECT
    DATE(DATE_TRUNC(timestamp, week)) AS week,
    session_id
  FROM
    wanted_sample_data.log_data
  WHERE
    event_name ='apply' 
  )

  select firstview.week,
          round(countif(jdview.session_id is not null)/count(firstview.session_id), 3) as first_funnel,
          round(countif(apply.session_id is not null)/count(jdview.session_id),3) as second_funnel
  from firstview
  left join jdview on firstview.session_id=jdview.session_id and firstview.week=jdview.week
  left join apply on jdview.session_id=apply.session_id and jdview.week=apply.week
  group by 1
  order by 1
  ;

 

---

결과 해석

1. [공고리스트, 검색, 북마크] 페이지 >> 공고 페이지의 전환율인 first_funnel은 45%~47%로 일정하게 유지되고 있음

2. 공고페이지 >> 지원의 전환율인 second_funnel을 확인해봅시다. 20%~21%로 일정하게 유지되고 있음

따라서, 각 단계가 일정하게 유지되고있으므로 지원 감소에 유저 활동이 영향을 주지 않았다고 판단할 수 있음

 

---

최종 결론

지원 수는 11월 이후 점차 감소하고 있으며,

[공고리스트, 검색, 북마크] 페이지에서 공고페이지로, 공고페이지에서 지원까지 전환율이 일정하게 유지됨이 확인됨

또한 [공고리스트, 검색, 북마크]페이지의 진입 수(유입량)이 일정하게 감소하고있음을 확인할 수 있음(별도 분석, 해당 포스팅에 없음)

즉, 지원 감소의 원인으로 유저의 Activation 이슈가 아니라 Acquisition에 이슈가 있음을 추측할 수 있습니다.