[Yammer/Mode] A/B 테스트 분석 : 1️⃣ SQL 쿼리 뜯어보기

Yammer 분석 프로젝트

"야머(Yammer)"는 마이크로소프트 산하의 기업용 소셜 네트워크 서비스 회사입니다. Mode에서 가상의 야머사의 데이터를 제공합니다. 이를 활용하여 현업과 유사한 문제 상황을 가정하고 해결하는 프로젝트를 진행하겠습니다.

 

 

A/B test 상황

야머사에서 새로운 포스팅(publisher) 기능을 테스트하기 위해 A/B test를 6월 1일부터 6월 30일까지 진행하였습니다.
야머사에 로그인한 일부 사용자들은 기존 버전(대조군)을 보았고, 다른 사용자들은 새로운 버전(실험군)을 보았습니다.
7월 1일 A/B 테스트 결과를 확인한 결과, 실험군이 대조군 대비 포스팅 개수가 50% 더 높다는 것을 발견했습니다. 이는 게시 활동이 크게 증가한 것입니다.

 

⭐️ 이번 글을 통해 야머사에서 진행한 A/B test가 편향과 오류없이 제대로 진행되었는지 확인하기 위해 SQL 코드를 뜯어보겠습니다!

 

 

A/B test 결과 확인

 

 

 

쿼리 확인

 

⬇️ Yammer Analyst(가상)가 작성한 A/B test 결과 추출 쿼리

-- cte c와 정규분포표 테이블 조인해서 통계적 검정 진행
-- 지표 차이, 지표 향상도, t_stat, p_value 구하기
SELECT c.experiment,
       c.experiment_group,
       c.users,
       c.total_treated_users,
       ROUND(c.users/c.total_treated_users,4) AS treatment_percent,
       c.total,
       ROUND(c.average,4)::FLOAT AS average,
       ROUND(c.average - c.control_average,4) AS rate_difference,
       ROUND((c.average - c.control_average)/c.control_average,4) AS rate_lift,
       ROUND(c.stdev,4) AS stdev,
       ROUND((c.average - c.control_average) /
          SQRT((c.variance/c.users) + (c.control_variance/c.control_users))
        ,4) AS t_stat,
       (1 - COALESCE(nd.value,1))*2 AS p_value
  FROM (
  -- cte c : ..?
SELECT *,
       MAX(CASE WHEN b.experiment_group = 'control_group' THEN b.users ELSE NULL END) OVER () AS control_users,
       MAX(CASE WHEN b.experiment_group = 'control_group' THEN b.average ELSE NULL END) OVER () AS control_average,
       MAX(CASE WHEN b.experiment_group = 'control_group' THEN b.total ELSE NULL END) OVER () AS control_total,
       MAX(CASE WHEN b.experiment_group = 'control_group' THEN b.variance ELSE NULL END) OVER () AS control_variance,
       MAX(CASE WHEN b.experiment_group = 'control_group' THEN b.stdev ELSE NULL END) OVER () AS control_stdev,
       SUM(b.users) OVER () AS total_treated_users
  FROM (
  -- cte b : 대조/실험집단의 목표지표의 평균, 전체 수, 표준편차, 분산 구하기
SELECT a.experiment,
       a.experiment_group,
       COUNT(a.user_id) AS users,
       AVG(a.metric) AS average,
       SUM(a.metric) AS total,
       STDDEV(a.metric) AS stdev,
       VARIANCE(a.metric) AS variance
  FROM (
  -- cte a : 실험군/대조군 유저의 send_message count하기 : 목표 metric
SELECT ex.experiment,
       ex.experiment_group,
       ex.occurred_at AS treatment_start,
       u.user_id,
       u.activated_at,
       COUNT(CASE WHEN e.event_name = 'send_message' THEN e.user_id ELSE NULL END) AS metric
  FROM (
  -- cte ex : 실험 publisher_update 테이블 생성
  SELECT user_id,
               experiment,
               experiment_group,
               occurred_at
          FROM tutorial.yammer_experiments
         WHERE experiment = 'publisher_update'
       ) ex
  -- ex
  JOIN tutorial.yammer_users u
    ON u.user_id = ex.user_id
  JOIN tutorial.yammer_events e
    ON e.user_id = ex.user_id
   AND e.occurred_at >= ex.occurred_at
   AND e.occurred_at < '2014-07-01'
   AND e.event_type = 'engagement'
 GROUP BY 1,2,3,4,5
       ) a
  -- a
 GROUP BY 1,2
       ) b
  -- b
       ) c
  -- c
  LEFT JOIN benn.normal_distribution nd
    ON nd.score = ABS(ROUND((c.average - c.control_average)/SQRT((c.variance/c.users) + (c.control_variance/c.control_users)),3))

 

쿼리 뜯어보기

위 코드는 서브쿼리가 많아서 단계별로 한번 차근차근 뜯어보겠습니다!

▪️ CTE ex :

with ex as (
SELECT user_id,
               experiment,
               experiment_group,
               occurred_at
          FROM tutorial.yammer_experiments
         WHERE experiment = 'publisher_update'
       )

 

팀에서 다수의 실험이 진행될테니 그 중 'publisher_update' 의 데이터를 가져온다

 

▪️ CTE a : 실험 기간 내에 실험/대조집단이 목표지표인 'send_message'를 한 수를 count하기

a as (
SELECT ex.experiment,
       ex.experiment_group,
       ex.occurred_at AS treatment_start,
       u.user_id,
       u.activated_at,
       COUNT(CASE WHEN e.event_name = 'send_message' THEN e.user_id ELSE NULL END) AS metric
  FROM ex
  JOIN tutorial.yammer_users u
    ON u.user_id = ex.user_id
  JOIN tutorial.yammer_events e
    ON e.user_id = ex.user_id
   AND e.occurred_at >= ex.occurred_at
   AND e.occurred_at < '2014-07-01'
   AND e.event_type = 'engagement'
 GROUP BY 1,2,3,4,5
       )

 

- yammer_events(유저 행동 로그 테이블), yammer_users(유저 계정 테이블)을 조인한다.

- 실험 종류 (experiment), 실험/대조 집단 (experiment_group), 실험 시작 기간 (ex.occurred_at), 유저 ID (user_id), 계정 활성화 시간(activated_at)을 기준으로 그룹화한다.

- 유저별로 'send_message'(포스팅)의 행동을 한 수를 count한다. -> 실험의 목표지표

 

조건

- e.occurred_at (포스팅한 시간)이 ex.occurred_at (실험/대조집단에 처치받은 시간) 이후에 발생

- 2014년 7월 1일 전의 행동 로그를 필터링 (ex.occurred_at은 2014년 6월 1일부터 30일까지 진행)

 

▪️ CTE b : 대조집단 / 실험집단의 목표지표 비교하기

b as (
SELECT a.experiment,
       a.experiment_group,
       COUNT(a.user_id) AS users,
       AVG(a.metric) AS average,
       SUM(a.metric) AS total,
       STDDEV(a.metric) AS stdev,
       VARIANCE(a.metric) AS variance
 FROM a
 GROUP BY 1,2
       )

- 실험/대조집단 유저 수 / 유저별로 포스팅(목표지표)을 한 횟수의 평균 / 집단별 전체 포스팅 수 / 집단별 포스팅 표준편차 & 분산

중간 결과 확인

▪️ CTE c : 

c as (
SELECT *,
       MAX(CASE WHEN b.experiment_group = 'control_group' THEN b.users ELSE NULL END) OVER () AS control_users,
       MAX(CASE WHEN b.experiment_group = 'control_group' THEN b.average ELSE NULL END) OVER () AS control_average,
       MAX(CASE WHEN b.experiment_group = 'control_group' THEN b.total ELSE NULL END) OVER () AS control_total,
       MAX(CASE WHEN b.experiment_group = 'control_group' THEN b.variance ELSE NULL END) OVER () AS control_variance,
       MAX(CASE WHEN b.experiment_group = 'control_group' THEN b.stdev ELSE NULL END) OVER () AS control_stdev,
       SUM(b.users) OVER () AS total_treated_users
  FROM b)

- 대조집단의 유저 수, 포스팅 수 평균, 포스팅 전체 수, 포스팅 분산 & 표준편차를 구해준다 ..? (왜 했는지 모르겠음)

- + 전체 실험에 참가한 유저 수를 구해준다

 

▪️ 최종 select 문 : t-통계량으로 통계적 유의성 검증하기

SELECT c.experiment,
       c.experiment_group,
       c.users,
       c.total_treated_users,
       ROUND(c.users/c.total_treated_users,4) AS treatment_percent,
       c.total,
       ROUND(c.average,4)::FLOAT AS average,
       ROUND(c.average - c.control_average,4) AS rate_difference,
       ROUND((c.average - c.control_average)/c.control_average,4) AS rate_lift,
       ROUND(c.stdev,4) AS stdev,
       ROUND((c.average - c.control_average) /
          SQRT((c.variance/c.users) + (c.control_variance/c.control_users))
        ,4) AS t_stat, -- t-test
       (1 - COALESCE(nd.value,1))*2 AS p_value
  FROM c
  LEFT JOIN benn.normal_distribution nd -- t-test의 p-value 확인
    ON nd.score = ABS(ROUND((c.average - c.control_average)/SQRT((c.variance/c.users) + (c.control_variance/c.control_users)),3))

- 대조군 대비 실험군의 지표가 얼마나 변화가 있었는지 확인한다 -> rate_difference, rate_lift

- two-sample t-test (두집단의 평균 비교)를 진행한다. -> 대조집단과 실험집단의 목표지표의 평균 차이가 유의미한지 검증

two-sample t-test 수식 참고

- t-test의 p-value를 계산한다

-> t분포는 z분포와 유사하기 때문에 mode의 benn.normal_distribution (정규분포 테이블)을 가져와서 t-값의 누적확률(nd.value)를 구해준다.
-> 양측검정이기 때문에 (1 - nd.value) 에 2를 곱해줘서 p-value를 구한다.

 

 

결과 해석

 

쿼리를 뜯어보면서 SQL을 활용해서 A/B test의 결과를 도출하고 통계적 검정을 진행하는 방법을 알아볼 수 있었습니다.

그러나 쿼리를 확인해보면 해당 A/B test에서 다수의 오류가 범해진 것을 알 수 있습니다.

다음 포스트에서는 어떤 오류가 범해졌는지 그리고 어떻게 고쳐야 유의미한 A/B test 결과를 도출할 수 있을지 알아보겠습니다!