[TIL] 데이터분석 데브코스 68일차 - 가상 데이터 기반 AB 테스트 분석 실습/Two-sample T-test/OLAP Cube

실습 데이터

• (상황) Production DB에 저장되는 정보들을 DW로 적재했다고 가정
• 데이터베이스 : dev
• 스키마 : raw_data
• 테이블 :
  • raw_data.user_event : 사용자/날짜/아이템별로 impression이 있는 경우 그 정보를 기록하고, impression으로부터 클릭, 구매, 구매시 금액을 기록
    (※ 실제 환경에서는 이러한 aggregate 정보를 로그 파일 등의 소스로부터 만들어내는 과정이 필요함)
    Primary Key : 사용자, 날짜, 아이템
  • raw_data.user_variant : 사용자가 소속한 AB 테스트 variant를 기록한 파일
  • raw_data.user_metadata : 사용자에 관한 메타정보(성별, 나이 등)가 기록된 파일

 

테이블 (1) user_event

CREATE TABLE raw_data.user_event (
	user_id int,
	datestamp timestamp,
    item_id int,
	clicked int,
	purchased int,
    paidamount int
);

 

 

테이블 (2) user_variant

CREATE TABLE raw_data.user_variant(
	user_id int,
    variant_id varchar(32) -- control vs. test
);

 

 

테이블 (3) user_metadata

CREATE TABLE raw_data.user_metadata (
	user_id int,
    age varchar(16),
    gender varchar(16)
);

 

 

 

 

요약 테이블 : variant_daily_sessions

variant, 사용자, 날짜별로 통계정보를 보여주는 ELT 테이블

CREATE TABLE analytics.variant_daily_sessions AS
SELECT
	variant_id,
    user_id,
    datestamp,
    
    count(distinct item_id) num_of_items, -- 총 impression
    sum(clicked) num_of_clicks, -- 총 click
    sum(purchased) num_of_purchases, -- 총 purchase
    sum(paidamount) revenue -- 총 revenue

FROM raw_data.user_event ue
JOIN raw_data.user_variant uv ON ue.user_id = uv.user_id 
GROUP by 1, 2, 3;

 

 

 

 

 

 

 

 

---

가상 데이터 기반 AB 테스트 분석 실습

1단계. 데이터 로드

Redshift에 연결해서 connection object를 만들자.

import psycopg2
import pandas as pd
import pandas.io.sql as sqlio
import math

# Redshift connection 함수
def get_Redshift_connection(autocommit):
    host = "호스트"
    redshift_user = "사용자 ID"
    redshift_pass = "비밀번호"
    port = 포트번호
    dbname = "dev"
    conn = psycopg2.connect("dbname={dbname} user={user} host={host} password={password} port={port}".format(
        dbname=dbname,
        user=redshift_user,
        password=redshift_pass,
        host=host,
        port=port
    ))
    conn.set_session(autocommit=autocommit)
    return conn

 

conn = get_Redshift_connection(True)
cur = conn.cursor()

 

 

 

2단계. 테이블 생성

1) user_event

sql = """SELECT * FROM raw_data.user_event"""
user_event = sqlio.read_sql_query(sql, conn)

user_event.head()

 

 

 

2) user_variant

sql = """SELECT * FROM raw_data.user_variant"""
user_variant = sqlio.read_sql_query(sql, conn)

user_variant.head()

 

 

 

 

3) user_metadata

sql = """SELECT * FROM raw_data.user_metadata"""
user_metadata = sqlio.read_sql_query(sql, conn)

user_metadata.head()

 

 

 

 

 

4) variant_daily_sessions

"""
CREATE TABLE analytics.variant_daily_sessions AS
SELECT
    variant_id,
    user_id,
    datestamp,
    count(distinct item_id) num_of_items, -- 총 impression
    sum(clicked) num_of_clicks,                -- 총 click
    sum(purchased) num_of_purchases,  -- 총 purchase
    sum(paidamount) revenue                   -- 총 revenue
FROM raw_data.user_event ue
JOIN raw_data.user_variant uv ON ue.user_id = uv.user_id
GROUP by 1, 2, 3
"""

sql = """SELECT * FROM analytics.variant_daily_sessions"""
variant_daily_sessions = sqlio.read_sql_query(sql, conn)

variant_daily_sessions.head()

※ `user_id` 별 `variant_id`는 고정이다.

 

 

 

 

 

 

 

 

Two-Sample T-Test

• 비율, 확률이 아닌 '값'을 갖는 카테고리에 적용하는 테스트
  (ex. Impression, Click, Purchase, Paidamount)
• 값이 커지면 z-score와 비슷해진다.
• 분산(s1, s2) 계산은 Tableau에서 쉽게 계산할 수 있다.
• A와 B별로 위의 평균 값을 보여주고 B쪽 갑스이 경우 t-score값을 바탕으로 컬러 코딩

 

 

 

 

 

방법1) python의 `scipy.stats.ttest_ind()` 함수를 사용

• t-score(사실상 z-score)와 p-value를 계산해서 리턴해준다.

from scipy import stats

# control 그룹에 속한 매출액 정보를 numpy 배열로 받아오기
a = variant_daily_sessions[variant_daily_sessions["variant_id"]=="control"]["revenue"].to_numpy()
# test 그룹에 속한 매출액 정보를 numpy 배열로 받아오기
b = variant_daily_sessions[variant_daily_sessions["variant_id"]=="test"]["revenue"].to_numpy()
print(len(a), len(b))

 

 

# ttest_ind 함수를 사용해서 두 그룹의 값들을 비교
# 이 함수는 t-score (사실상 z-score)와 p value를 계산해서 리턴해줌
t, p = stats.ttest_ind(b, a)
print(t, p)

 

► t-score이 95% 신뢰구간 안에 있기 때문에 매출(`revenue`) 관점에서 a와 b는 동일하다고 볼 수 있다.

 

 

 

위의 과정을 하나의 함수로 정의해보았다.

`compute_z_scrore()`함수의 인자로 들어가는 `field_name`만 바꿔주면 각 컬럼별로 구해볼수 있다.

# 나중에 사용하기 쉽게 함수로 구현
def compute_z_score(df, field_name):
  a = df[df["variant_id"]=="control"][field_name].to_numpy()
  b = df[df["variant_id"]=="test"][field_name].to_numpy()
  t, p = stats.ttest_ind(b, a)

  return t, p
  
print(compute_z_score(variant_daily_sessions, "revenue"))

 

 

 

위의 함수를 Impression, Click, Puchase에 각각 적용해보자

## Impression
print(compute_z_score(vds, 'num_of_items'))

 

## Click
print(compute_z_score(vds, 'num_of_clicks'))

 

## Purchase
print(compute_z_score(vds, 'num_of_purchases'))

 

► 3개의 z-score 모두 신뢰구간 95% 이내에 존재하므로 a와 b가 동일하다고 볼 수 있다.

 

 

 

 

 

 

 

방법2) 직접 계산하기

그룹별로 크기와 평균과 각 원소 제곱의 합을 알면 계산 가능

• 평균(매출액 평균) : `mean_b`, `mean_a`
• 크기(세션의 수) : `n_b`, `n_a`
• 제곱의 평균(매출액 제곱 평균) : `square_b`, `square_a`

import numpy as np

## n_a = 82526
## n_b = 83116

n_a = len(a)
n_b = len(b)

## mean_a = 5.590990
## mean_b = 5.570203

mean_a = np.mean(a)
mean_b = np.mean(b)

## square_a = 219.259967
## square_b = 217.960044

 

 

 

variance(표준편차의 제곱)은 아래와 같이 계산

• `var_b` = `square_b` - `mean_b`*`mean_b`
• `var_a` = `square_a` - `mean_a`*`mean_a`

import math

## var_a = (square_a - mean_a*mean_a)
## var_b = (square_b - mean_b*mean_b)

var_a = np.var(a)
var_b = np.var(b)

print(var_a, var_b)

 

 

 

최종 t-score는 아래와 같이 계산

• `(mean_b - mean_a) / math.sqrt(var_b/n_b + var_a/n_a)`

t_score = (mean_b - mean_a)/math.sqrt(var_a/n_a+var_b/n_b)
print(t_score)

 

 

※ 실제 AB 테스트 결과 분석은 python 코드를 할 수 없다.
시각화된 대시보드 위에 위의 간단한 계산들을 동적으로 하는 방식으로 한다.
이러한 동적 계산이 용이하도록 DBT를 활용해 요약 테이블을 만들어야 한다.

 

 

 

 

 

 

 

 

---

AB 테스트 분석 시각화 대시보드 구현

요구 조건

• AB 테스트 전체 기간에 걸쳐 Key 지표(ex. 매출액)가 비교 가능해야 한다.
• 일별로 Key 지표가 가능해야 한다 ► trend(경향) 파악
• Key 지표의 경우, 통계적으로 유의미한지 아닌지 표시 되어야 한다 ► Color Coding(컬러코딩)
• 트래픽(사용자) 메타 데이터가 있다면 이를 바탕으로 필터링 가능해야 한다.

 

 

 

 

어려운 점

• 선택된 필터에 따라 z-score 계산이 이뤄져야 함
• 먼저 선택된 필터에 맞춰 원본 데이터 수집이 이뤄져야 함
• 미리 모든 가능한 조합을 수집하고 필터 선택에 따라 지표들을 aggregate 해야함
  • 방법 1. 모든 필터 조합에 대해 미리 계산 (Tableau)
  • 방법 2. 동적으로 SQL 실행해서 계산 (Looker)

 

 

 

OLAP Cube

: (Tableau를 사용하는 경우)모든 조합에 대해서 샘플의 수, 매출액의 합, 매출액 제곱의 합을 계산한 데이터

• 장점 : 속도 빠름 (데이터를 매번 읽어올 필요 X)
• 단점 : 필터가 변경될 때마다 데이터 수집 방법도 바뀌어야 함

 

A와 B별로 Impression에 대해 OLAP Cube 생성 예제를 살펴보자.

SELECT
	variant_id,
    'impression' category,
    datestamp,
    age,
    gender,
    
    count(1) n, -- number of sessions (세션의 수)
    sum(num_of_items) sum, -- sum(매출액 합)
    sum(num_of_items*num_of_items) sum2 -- square(매출액 제곱의 합)
    
FROM 이름.analytics_variant_user_daily
GROUP BY 1, 2, 3, 4, 5