[BigQuery] 현업 문제 해결 쿼리 작성(4) : 성과 확인(A/B test)

성과 확인

target : 서비스에 행한 변화가 유저의 행동에 어떤 변화를 주었는가

👉 서비스에 적용한 변화가 기업이 원하는 목표(지표)를 달성하게 하였는가

 

• 적용한 변화 : 공고 카드에 '스킬셋' 노출
• 어떤 유저들이 변화의 대상인가 : 공고 페이지에 들어오는 모든 유저
• 예상되는 행동 변화 : 공고 카드를 더 많이 클릭할 것

 

문제1. 배포 전 대비 배포 후 성과를 평가해보자 👉 일별 전환율을 확인

• with를 써서, jdlist 에 진입한 유저와 jd에 진입한 유저를 날짜별로 구분해서 구한다

WITH
  jdlist AS(
  SELECT
    DATE(timestamp) AS date,
    session_id
  FROM
    wanted_sample_data.project_log
  WHERE
    event_name = 'jdlist_view' ),
  jd AS(
  SELECT
    DATE(timestamp) AS date,
    session_id
  FROM
    wanted_sample_data.project_log
  WHERE
    event_name = 'jd_view' )

 

• 두 테이블(jdlist와 jd)을 session_id를 기준으로 조인
• 조인 결과를 date를 기준으로 groupby 하여, jdlist에 진입한 유저 수와, jd에 진입한 유저수를 카운트하고
• 두 결과를 나누어 전환율을 구한다

SELECT
  jdlist.date,
  COUNT(jdlist.session_id) AS jdlist_cnt,
  COUNTIF(jd.session_id IS NOT NULL) AS jd_cnt,
  ROUND(COUNTIF(jd.session_id IS NOT NULL)/COUNT(jdlist.session_id), 3) AS jd_rate # 전환율
FROM
  jdlist
LEFT JOIN
  jd
ON
  jdlist.session_id = jd.session_id
GROUP BY
  1
ORDER BY
  1

 

• 결과를 보고, Looker Studio로 이동해서 시각화하여 확인해보자

 

• 날짜별 jd_rate(전환율)을 보면 특정 일자를 기준으로 높아지고 낮아짐을 볼 수 있다.
  • 2/9일부터 설 연휴가 시작되고, 12일까지 이어졌고, 2월 13일부터 16일까지 지원유도 마케팅이 시행되었다고 한다면,
  • 적용한 변화가 유의미하게 유저 행동에 변화를 주었다고 판단하기 어렵다 → A/B test가 필요한 이유!

---

A/B 테스트

• 배포가 지표에 영향을 주었을지 확인하기 위해서는 '상관관계'에서 그치는 것이 아닌 '인과관계'를 증명해야 한다.
• A/B 테스트란 기존에 있던 서비스와 새로 적용하고 싶은 서비스를, 임의로 나눈 두 집단에게 각각 다른 서비스를 노출하여 어떠한 그룹이 더 높은 성과를 만족하는지 정량적으로 확인하여 평가하는 것이다.
  • 단, 모수가 매우 적으면 실험이 그만큼 길어지고, 실험 기간동안 얻을 수 있는 임팩트가 감소하므로 유의한다
  • Spillover Effect가 염려될 경우도 유의 👉 실험군의 행동이 대조군에게 영향을 미치는 구조는 명확한 인과관계 파악을 어렵게 한다.

 

 

문제2. A/B 테스트로 성과를 확인해보자

• 확인해야 할 지표(Success Metric)는 공고리스트 페이지에서 공고페이지로의 전환율이다
• 즉, 공고페이지 진입수 / 공고리스트 페이지 진입수

1. 공고리스트 진입수와 공고페이지 진입수를 각각 구하되 날짜/세션아이디/어느 실험군에 속하는지를 추출한다
2. where절 조건에 어느 페이지를 보았는지 여부와 더불어, 실험이 진행된 날짜 이후를 지정해준다

#공고리스트 진입수
with jdlist as(
  select date(timestamp) as date,
       session_id,
       json_extract_scalar(user_property, '$.experiment') as experiment
  from wanted_sample_data.experiment_log
  where event_name = 'jdlist_view' 
        and date(timestamp) >= '2024-02-06'
  ),

#공고페이지 진입수
jd as(
  select date(timestamp) as date,
       session_id,
       json_extract_scalar(user_property, '$.experiment') as experiment
  from wanted_sample_data.experiment_log
  where event_name = 'jd_view' 
        and date(timestamp) >= '2024-02-06'
  )

jd 테이블 결과 예시

 

3. 두 테이블을 session_id를 기준으로 조인한다

4. 날짜를 기준으로 groupby 하여 실험군 A, B에 대한 페이지 진입수와 전환율을 각각 구한다.

👉 이때 실험군이 나누어져 있으므로 countif()를 쓰도록 한다.

select jdlist.date,
       countif(jdlist.experiment='A') as a_jdlist_cnt,
       countif(jdlist.experiment='A' and jd.session_id is not null) as a_jd_cnt,
       round(countif(jdlist.experiment='A' and jd.session_id is not null)/countif(jdlist.experiment='A'), 3) as a_jd_rate,
       countif(jdlist.experiment='B') as b_jdlist_cnt,
       countif(jdlist.experiment='B' and jd.session_id is not null) as b_jd_cnt,
       round(countif(jdlist.experiment='B' and jd.session_id is not null)/countif(jdlist.experiment='A'), 3) as b_jd_rate
from jdlist
left join jd on jdlist.session_id = jd.session_id
group by 1

 

 

▶︎ 결과를 보면, 실험군인 B그룹의 전환율이 올랐음을 확인할 수 있다!

 

 

▶︎ Looker Studio에서 두 전환율을 비교해봐도 변화가 유저 행동에 유의미한 영향을 주었음을 확인할 수 있다

---

부록. 문제2를 윈도우 함수를 사용해서 풀어보기

• LEAD 함수를 사용하여 각 session_id 별로 다음 이벤트를 가져올 수 있다
• 다음 쿼리를 먼저 확인한다.

select session_id,
       event_name,
       #다음 이벤트 가져오기
       LEAD(event_name, 1) OVER (partition by session_id order by timestamp) as next_event,
       json_extract_scalar(user_property, '$.experiment') as experiment,
       date(timestamp) as date
from wanted_sample_data.experiment_log
where date(timestamp) >= '2024-02-06'

 

 

• 위 쿼리 결과에 QUALIFY 를 활용하여 모든 계산이 끝난 후에 조건을 걸 수 있다 >> jdlist를 본 사람만 필터링
• 👉 having 절이 group by 가 활용되었을 때만 사용가능한 것처럼, qualify도 윈도우 함수가 쓰였을 경우에만 사용할 수 있음!
• 여기서는 LEAD OVER가 쓰였기 때문에 QUALIFY 사용가능

select session_id,
       event_name,
       #다음 이벤트 가져오기
       LEAD(event_name, 1) OVER (partition by session_id order by timestamp) as next_event,
       json_extract_scalar(user_property, '$.experiment') as experiment,
       date(timestamp) as date
from wanted_sample_data.experiment_log
where date(timestamp) >= '2024-02-06'
QUALIFY event_name = 'jdlist_view'

 

 

• 이제, Experiment 별로 전환율을 계산한다

with base as(
      select session_id,
            event_name,
            #다음 이벤트 가져오기
            LEAD(event_name, 1) OVER (partition by session_id order by timestamp) as next_event,
            json_extract_scalar(user_property, '$.experiment') as experiment,
            date(timestamp) as date
      from wanted_sample_data.experiment_log
      where date(timestamp) >= '2024-02-06'
      QUALIFY event_name = 'jdlist_view'
)

select date,
       countif(experiment='A') as jdlist_A,
       countif(experiment='A' and next_event is not null) as jd_A,
       round(countif(experiment='A' and next_event is not null)/countif(experiment='A'), 3) as jd_rate_A,
       countif(experiment='B') as jdlist_B,
       countif(experiment='B' and next_event is not null) as jd_B,
       round(countif(experiment='B' and next_event is not null)/countif(experiment='B'), 3) as jd_rate_B
from base
group by 1
order by 1

 

📢 결과 해석

• 동일하게 A/B테스트를 하였을 때 외부 요인(설 연휴, 마케팅)을 A,B안 모두 동일하게 받게되므로 온전한 프로젝트 성과를 측정할 수 있었다.
• B안이 A안보다 전환율이 높은 것을 확인할 수 있으며, 개발 상 이슈가 없다면 B안을 배포하는 것을 제안할 수 있다.(단, 해당 전환율 차이가 유의미한 차이라고 가정)