코호트 및 퍼널 쿼리
리텐션 관련 질문에 답합니다
코호트 및 퍼널 쿼리은(는) CoddyKit의 무료 Digital Marketing Academy 강의입니다. 이것은 4개 중 4번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 Digital Marketing Academy 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. Digital Marketing Academy 강의에는 총 4개의 강의가 포함되어 있습니다.
코호트란 무엇인가요?
코호트는 일반적으로 가입 월처럼 동일한 시작 이벤트를 공유하는 사용자 그룹입니다. 각 코호트를 시간에 따라 추적하면 실제 리텐션을 확인할 수 있습니다.
전체 집계 지표만으로는 이탈을 파악하기 어렵지만, 코호트를 사용하면 신규 사용자가 실제로 계속 남아 있는지 알 수 있습니다.
SELECT user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users;코호트 정의하기
첫 단계는 각 사용자에게 코호트를 지정하는 것입니다. DATE_TRUNC는 가입 날짜를 월 단위로 내림하여 사용자를 깔끔하게 그룹화할 수 있게 합니다.
이 결과를 이후 모든 분석에서 재사용할 수 있는 기본 구성 요소로 저장하세요.
SELECT DATE_TRUNC('month', signup_date) AS cohort_month,
COUNT(*) AS cohort_size
FROM users
GROUP BY 1
ORDER BY 1;활동 기간
리텐션은 가입 후 몇 개월이 지난 시점에 활동했는지를 측정합니다. 주문이 발생한 월과 코호트 월 사이의 차이로 기간을 계산합니다.
기간 0은 가입한 월이고, 기간 1은 다음 달이며, 이후에도 같은 방식으로 이어집니다.
SELECT o.user_id,
(DATE_PART('year', o.order_date) - DATE_PART('year', u.signup_date)) * 12
+ (DATE_PART('month', o.order_date) - DATE_PART('month', u.signup_date)) AS period
FROM orders o
JOIN users u ON u.user_id = o.user_id;리텐션 표 만들기
코호트 월과 기간을 결합한 다음 각 셀에서 활동한 고유 사용자를 셉니다. 그 결과가 전형적인 리텐션 삼각형입니다.
각 행은 하나의 코호트이고, 각 열은 가입 후 경과한 개월 수입니다.
SELECT DATE_TRUNC('month', u.signup_date) AS cohort,
DATE_PART('month', AGE(o.order_date, u.signup_date)) AS period,
COUNT(DISTINCT o.user_id) AS active
FROM users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY 1, 2;리텐션율
규모가 서로 다른 코호트의 원시 사용자 수는 비교하기 어렵습니다. 각 기간의 활동 사용자 수를 코호트의 최초 사용자 수로 나누세요.
윈도 함수로 기간 0의 규모를 모든 행에 가져오면 비율을 계산할 수 있습니다.
SELECT cohort, period,
active * 1.0 / FIRST_VALUE(active) OVER (
PARTITION BY cohort ORDER BY period
) AS retention
FROM cohort_activity;퍼널이란 무엇인가요?
퍼널은 방문, 가입, 장바구니 추가, 구매처럼 순서가 정해진 단계를 거치는 사용자를 추적합니다. 단계 사이의 감소 폭을 보면 사용자가 어느 지점에서 이탈하는지 알 수 있습니다.
퍼널 분석은 막연한 "낮은 전환율"이라는 불만을 구체적으로 이탈이 발생하는 단계에 대한 문제로 바꿔 줍니다.
SELECT step, COUNT(DISTINCT user_id) AS users
FROM events
WHERE step IN ('visit', 'signup', 'cart', 'purchase')
GROUP BY step;각 단계 세기
조건부 집계를 사용하면 한 번의 처리로 각 단계의 사용자 수를 셀 수 있습니다. FILTER는 각 단계에 도달한 고유 사용자를 집계합니다.
쿼리 하나와 행 하나로 전체 퍼널을 한눈에 확인할 수 있습니다.
SELECT
COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'signup') AS signups,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events;단계별 전환율
중요한 수치는 연속된 단계 사이의 비율입니다. 각 단계를 이전 단계로 나누면 가장 큰 이탈이 발생하는 지점을 알 수 있습니다.
정수 나눗셈으로 비율이 0이 되지 않도록 소수로 형변환하세요.
SELECT signups * 1.0 / NULLIF(visits, 0) AS visit_to_signup,
buyers * 1.0 / NULLIF(signups, 0) AS signup_to_buy
FROM funnel_counts;타임스탬프를 사용한 순서형 퍼널
엄격한 퍼널에서는 단계가 순서대로 발생해야 합니다. 다음 단계의 타임스탬프가 이전 단계보다 나중인 경우에만 각 단계를 다음 단계와 JOIN하세요.
이렇게 하면 일치하는 가입보다 먼저 발생한 구매가 집계되는 것을 막을 수 있습니다.
SELECT COUNT(DISTINCT v.user_id) AS visited,
COUNT(DISTINCT p.user_id) AS purchased
FROM events v
LEFT JOIN events p
ON p.user_id = v.user_id
AND p.step = 'purchase'
AND p.event_time > v.event_time
WHERE v.step = 'visit';채널별 퍼널
퍼널을 획득 채널별로 나누면 단순히 클릭만 하는 유입원이 아니라 실제로 전환하는 사용자를 보내는 출처를 확인할 수 있습니다.
조건부 집계 결과를 채널별로 그룹화하여 출처별 품질을 비교하세요.
SELECT channel,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events
GROUP BY channel;인사이트에서 실행으로
코호트는 출시가 거듭될수록 리텐션이 개선되는지 알려 주고, 퍼널은 어떤 단계를 먼저 개선해야 하는지 알려 줍니다.
두 분석을 함께 사용하면 마케팅이 무슨 일이 일어났는지 보고하는 데서 벗어나 그 원인과 다음 투자 지점을 진단하는 방향으로 전환됩니다.
빠른 확인
퍼널에서 방문, 가입, 구매 단계의 사용자 수를 세고 있습니다. 연속된 단계의 비율은 무엇을 보여 주나요?
복습
코호트는 시작 월을 기준으로 사용자를 그룹화하고 DATE_TRUNC와 AGE를 사용하여 기간별 리텐션을 추적합니다. 퍼널은 순서가 정해진 각 단계에서 고유 사용자를 세고 연속된 단계의 비율을 비교합니다.
이제 리텐션과 전환을 SQL만으로 진단할 수 있는 고급 도구를 갖추었습니다.
자주 묻는 질문
“코호트 및 퍼널 쿼리” 강의는 무료인가요?
네 — “코호트 및 퍼널 쿼리” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 Digital Marketing Academy 강의 전체를 잠금 해제할 수 있습니다. Digital Marketing Academy 강의에는 총 4개의 강의가 포함되어 있습니다.
“코호트 및 퍼널 쿼리”에서 뭘 배우나요?
리텐션 관련 질문에 답합니다 브라우저에서 직접 실행하는 실습 코드로 Digital Marketing Academy을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.
Digital Marketing Academy을(를) 시작하는 데 경험이 필요한가요?
사전 경험은 필요하지 않습니다. CoddyKit의 Digital Marketing Academy은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 4번째 강의입니다.
“코호트 및 퍼널 쿼리” 강의는 얼마나 걸리나요?
대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.
이 Digital Marketing Academy 강의에서 코드를 작성하고 실행할 수 있나요?
네. 모든 Digital Marketing Academy 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.