SQL을 이용한 코호트 분석

SQL을 이용한 코호트 분석

SQL을 이용해서 코호트 분석을 해보려고 합니다.
그러기 앞서 코호트가 무엇인지 알아봐야겠죠.

코호트분석

코호트분석 이란?

Cohort analysis 란 시간의 흐름에 따라 growth와 retention을 측정하는 방법이라고 합니다. 나누어진 그룹에 따른 변화된 지표를 측정 합니다. 예를 들자면 제품이나 서비스를 사용한 시간에 따라 고객을 구분 하는 방법이 있을 겁니다.

그룹을 나누는 방법

그룹별로 1월, 2월, 3월... 에 가입한 회원들로 나눌수도 있으며 첫구매를 언제했는지로 나눌수도 있습니다. 시간을 기준으로 나눌 수도 있겠죠. 원하는 날짜의 기간으로 디멘전을 부여하여 값을 정리 할 수 있을 겁니다.

코호트분석의 예시

예컨데 다음과 같은 데이터가 있다고 가정해 보도록 하겠습니다.

매출표

이것을 보면 과연 이 쇼핑몰이 장사를 잘 하고 있고, 어떤 문제점이 있는지 파악 할 수 있을까요?
해당 표를 보고 알 수 있는건 매달 고객이 늘고 있다는점, 그리고 고객당 평균 매출이 비교적 잘 유지 되고 있다는점을 알 수 있습니다.
그럼 여기서 같은 원시 데이터를 가지고 코호트 분석을 하면 어떻게 될까요? 아래 표를 보도록 하겠습니다.

코호트분석

이 표를 보면 마찬가지로 고객이 매달 1천명씩 늘고 있는걸 알 수 있고, 고객들은 시간이 갈수록 구매를 덜 하게 된다는 걸 알 수 있습니다. 거기에 시간이 가면 갈수록, 즉, 늦게 가입한 고객들은 그 감소세가 줄어 들 고 있다는것도 확인 할 수 있습니다. 리텐션, 바이럴 효과, 이탈 등 여러분들이 분석하고 싶은 어떤 지표에도 적용할 수 있습니다.

SQL을 이용한 cohort 분석

SQL을 cohort에 활용 하는것이 생각보다 어렵진 않을 것 같습니다. 우선 데이터를 가지고 와서 모양대로 잘 그려주고, 엑셀이나 구글시트를 이용하면 좀 더 쉽게 이용 할 수 있을것 같습니다. 더 상세한것은 직접 SQL 작성해서 업데이트 하도록 하겠습니다.

이렇게 코호트분석에 대해서 조금 알게 되었습니다.

감사합니다.

by.sTricky