티스토리 뷰

코호트(Cohort) 분석은 소비자를 그룹으로 구분지어서, 시간의 흐름에 따라서 각 그룹별로 성과 및 여러 액션들을 측정하는 분석 방법입니다. 예를 들어 코호트 분석을 이용할 경우, 다음과 같은 성과를 측정할 수 있습니다.


  • 가입자의 잔존율(Retention) 수치

  • 특정 고객 그룹(예: 국가, 나이, 도시, 성별 등)이 제품을 얼마나 많이 사용하는지 측정

  • 특정 월에 가입한 사용자들을 분류해서, 각 사용자들의 성과를 트래킹


코호트 분석은 주로 온라인 서비스에서 많이 활용되며, 최근 스타업에 유용한 분석 기법으로 자주 소개되고 있습니다. 이번 포스팅에서는 아파치 타조를 이용한 코호트 분석 방법을 소개하겠습니다.


1. 데이터 준비


필자는 TPC-H 벤치마크 데이터셋의 orders 테이블을 대상으로 코호트 분석을 진행할 것입니다. 표1은 orders 테이블의 스키마를 정리한 내용입니다.


칼럼

내용

칼럼 타입

o_orderkey

주문번호

integer

o_custkey

고객번호

integer

o_orderstatus

주문상태

char(1)

o_totalprice

주문금액

decimal(15,2)

o_orderdate

주문일자

date

o_orderpriority

주문 우선순위

char(15)

o_clerk

점원 이름

char(15)

o_shippriority

배송 우선순위

integer

o_comment

코멘트

varchar(79)

[표1] orders 테이블 스키마


우선 타조에 코호트 분석을 위한 데이터 베이스를 생성합니다.

default> create database cohort;

OK

default> \c cohort;

You are now connected to database "cohort" as user "hadoop".



그리고 타조에서 예제1과 같이 외부 테이블을 생성합니다.


CREATE EXTERNAL TABLE orders (o_orderkey int, o_custkey int,

o_orderstatus text,  o_totalprice float, o_orderdate text, o_orderpriority text,

o_clerk text, o_shippriority int, o_comment text)

USING CSV WITH ('text.delimiter'=',')

LOCATION 'hdfs://localhost:9010/user/hadoop/orders';

[예제1] 타조용 orders 테이블 생성 구문


orders 테이블을 조회하면, 데이터가 출력되는 것을 확인할 수 있습니다.


cohort> select * from orders limit 3;

o_orderkey,  o_custkey,  o_orderstatus,  o_totalprice,  o_orderdate,  o_orderpriority,  o_clerk,  o_shippriority,  o_comment

-------------------------------

1,  36901,  O,  173665.47,  1996-01-02,  5-LOW,  Clerk#000000951,  0,  nstructions sleep furiously among

2,  78002,  O,  46929.18,  1996-12-01,  1-URGENT,  Clerk#000000880,  0,   foxes. pending accounts at the pending, silent asymptot

3,  123314,  F,  193846.25,  1993-10-14,  5-LOW,  Clerk#000000955,  0,  sly final accounts boost. carefully regular ideas cajole carefully. depos

(3 rows, 0.051 sec, 164.0 MiB selected)


2. 코호트 정의


필자는 특정 월의 첫번째 구매자를 그룹(코호트)으로 묶고, 각 그룹의 이후의 월 단위 재구매 패턴을 비교 분석할 것입니다. 예제2는 1992년 1월부터 6월 사이의 주문 고객을 코호트 그룹으로 묶는 질의문입니다.


CREATE TABLE cohort AS

SELECT o_custkey, -- 고객번호

 min(o_orderdate) as cohort_date, -- 최초 주문일

 min(substr(o_orderdate, 0, 8)) cohort_id -- 코호트 그룹ID

FROM orders

WHERE o_orderdate BETWEEN '1992-01-01' AND '1992-06-30'

GROUP BY o_custkey

ORDER BY o_custkey;

[예제2] 코호트 그룹 정의용 질의문


예제2를 실행한 후, cohort 테이블을 조회하면 다음과 같이 데이터가 출력됩니다.


cohort> select * from cohort limit 3;

o_custkey,  cohort_date,  cohort_id

-------------------------------

1,  1992-04-19,  1992-04

2,  1992-04-05,  1992-04

4,  1992-04-26,  1992-04

(65315 rows, 0.053 sec, 2.4 MiB selected)



3. 재구매 패턴 분석하기


이제 코호트 그룹의 고객들의 재구매 패턴을 분석해보겠습니다. 예제3은 코호트 그룹과 orders 테이블을 조인한 후, 코호트 그룹의 주문 총액과 평균 주문액을 계산하는 질의문입니다.


-- cohort, 주문 월, 주문자 수, 주문 건수, 주문 총액, 평균 주문액

CREATE TABLE cohort_analysis AS

SELECT c.cohort_id,

 substr(o_orderdate,0,8) as order_month,

 count(distinct(o.o_custkey)) as buyer_cnt,

 count(o.o_orderkey) as order_cnt,

 round(sum(o.o_totalprice)) as amount,

 round(avg(o.o_totalprice)) as avg_amount

FROM   orders o

JOIN cohort c ON o.o_custkey = c.o_custkey

WHERE  o.o_orderdate between '1992-01-01' and '1992-06-30'

GROUP BY c.cohort_id, substr(o_orderdate,0,8)

ORDER BY c.cohort_id, substr(o_orderdate,0,8) ASC

[예제3] 재구매 패턴 분석용 질의문


만약 cohort 테이블을 생성하지 않았다면, 예제4와 같이 서브쿼리 형태로 재구매 패턴을 분석할 수도 있습니다.


CREATE TABLE cohort_analysis AS

SELECT c.cohort_id,

 substr(o_orderdate,0,8) as order_month,

 count(distinct(o.o_custkey)) as buyer_cnt,

 count(o.o_orderkey) as order_cnt,

 round(sum(o.o_totalprice)) as amount,

 round(avg(o.o_totalprice)) as avg_amount

FROM   orders o JOIN (

   SELECT o_custkey,

min(o_orderdate) as cohort_date, min(substr(o_orderdate,0,8)) as cohort_id

   FROM   orders

   WHERE o_orderdate between '1992-01-01' and '1992-06-30'

   GROUP BY o_custkey

) c ON o.o_custkey = c.o_custkey

WHERE  o.o_orderdate between '1992-01-01' and '1992-06-30'

GROUP BY c.cohort_id, substr(o_orderdate,0,8)

ORDER BY c.cohort_id, substr(o_orderdate,0,8) ASC

[예제4] 서브쿼리를 이용한 재구매 패턴 분석용 질의문


분석 질의문을 실행한 후, cohort_analysis 테이블을 조회하면 다음과 같이 데이터가 출력됩니다.


cohort> select * from cohort_analysis;

cohort_id,  buyer_cnt,  order_cnt,  amount,  avg_amount

cohort_id,  order_month,  buyer_cnt,  order_cnt,  amount,  avg_amount

-------------------------------

1992-01,  1992-01,  17409,  19330,  2924475520,  151292

1992-01,  1992-02,  3117,  3486,  527538064,  151330

1992-01,  1992-03,  3342,  3802,  570538560,  150063

1992-01,  1992-04,  3231,  3621,  541002048,  149407

(중략)

1992-01,  1992-05,  3379,  3797,  567691136,  149510

1992-05,  1992-06,  1338,  1482,  222910456,  150412

1992-06,  1992-06,  6227,  6816,  1031019040,  151265

(21 rows, 0.047 sec, 916 B selected)


마지막으로 재구매 패턴의 시각화를 진행하겠습니다. 우선 cohort_analysis 테이블 데이터를 이용하여, 엑셀에서 피벗 테이블을 생성합니다. 그림1은 행 레이블은 cohort 그룹ID, 열 레이블은 주문 월, 값 항목은 평균 주문액 합계로 설정한 피벗 테이블입니다.


스크린샷 2014-12-07 오후 9.32.11.png

[그림1] cohort_analysis 데이터의 피벗 테이블


위 테이블은 각 코호트 그룹의 첫 구매 이후의 평균 주문액 합계를 나타냅니다. 필자는 데이터를 쉽게 이해하도록, 그림2와 같이 테이블을 수정했습니다. 열 레이블은 첫 구매월부터 5달 후까지로 설정하고, 각 칼럼값을 해당 월로 이동시켰습니다.


스크린샷 2014-12-07 오후 9.44.20.png

[그림2] 재구매 분석 결과 테이블


마지막으로 위 테이블을 이용하여, 그림3과 같은 꺾은선 차트를 생성했습니다.


스크린샷 2014-12-07 오후 9.44.27.png

[그림3] 재구매 분석 차트


차트를 보면, 4월과 5월 첫 구매자 그룹은 재구매가 감소하지만, 2월과 3월 첫 구매자 그룹은 3달 후부터는 재구매가 증가하는 것을 확인할 수 있습니다.

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
댓글
댓글쓰기 폼