Cohort Analysis

Cohort는 전향성 추적조사를 의미

• 특정 요인에 노출된 집단과 노출되지 않은 집단을 추적하고 연구 대상 질병의 발생률을 비교하여 요인과 질병 발생 관계를 조사하는 연구 방법, 요인 대조 연구(factor - control study)라고도 불림

기준에 따라 그룹을 세분화하여 분석을 하는 방법

• A라는 광고 채널을 통해 유입된 사용자 그룹
• 관련 사용앱이 10~15개인 사용자 그룹

Cohort Analysis 분석 방향

• 예를 들어 1월 1일에 처음 방문했던 사람들이 1월 2일과 1월 3일에 재방문하여 활동을 지속하는가를 관찰 => 그리고 다시 1월 5일에 처음 방문했던 사람들에 대해서도 관찰하여 비교 => 하지만 이렇게 기간만을 비교하면 의미가 크지않기 때문에 이에 대한 액션 혹은 개선 방안을 고민하는 것 자체가 어려워짐
• 그래서 보통 어떠한 마케팅 액션별로 반응하는 방문자를 비교하는 것이 올바른 코호트 분석 방법임

SQL을 사용한 Cohort

# User 테이블
CREATE TABLE users (id INT(11) PRIMARY KEY NOT NULL, 
			 name VARCHAR(40) NOT NULL, 
			 date DATETIME NOT NULL);

# Event 테이블
CREATE TABLE events (id INT(11) PRIMARY KEY NOT NULL, 
		     type VARCHAR(15), 
			 user_id INT(11) NOT NULL, 
			 date DATETIME NOT NULL, 
			 FOREIGN KEY (user_id) REFERENCES users(id));

SELECT results.months,
       results.cohort,
       results.actives AS active_users,
       user_totals.total AS total_users,
       results.actives/user_totals.total*100 AS percent_active
FROM
  ( SELECT ROUND(DATEDIFF(events.date, users.date)/30.4) AS months,
           DATE_FORMAT(events.date, '%Y/%m') AS MONTH,
           DATE_FORMAT(users.date, '%Y/%m') AS cohort,
           COUNT(DISTINCT users.id) AS actives
   FROM users
   JOIN events ON events.user_id = users.id
   GROUP BY cohort, months ) AS results
JOIN
  ( SELECT DATE_FORMAT(date, "%Y/%m") AS cohort, 
		   count(id) AS total
   FROM users
   GROUP BY cohort ) AS user_totals ON user_totals.cohort = results.cohort
WHERE results.MONTH < DATE_FORMAT(NOW(), '%Y/%m');

Ref

[Ref0] http://mherman.org/blog/2013/07/19/cohort-analysis-data-sourcing-with-sql/#.VX-5OUbzmhE
[Ref1] http://andrewchen.co/retention-is-king/
[Ref2] https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
[Ref3] https://mariadb.com/kb/en/mariadb/time-zones/