채널별 결제 전환과 LTV

난이도 보통 · 권장 9분 · 출력 컬럼 channel, total_users, paying_users, paying_ratio, ltv

문제

채널별로 아래 네 가지를 구하세요. total_users 그 채널의 전체 유저 수 paying_users 그중 결제 이력이 1건 이상 있는 유저 수 paying_ratio paying_users / total_users × 100 ltv 결제 유저 1인당 누적 결제액 평균 (분모는 전체 유저가 아니라 paying_users 입니다) · 비중과 LTV는 소수점 둘째 자리까지 반올림합니다. · LTV 내림차순으로 정렬하세요.

정렬 ltv DESC

쉽게 설명하면

돈 쓴 손님만 모아서 1인당 평균 얼마 썼나. 안 쓴 손님은 평균에서 뺀다.

	분자	분모
결제 비중	결제한 사람	전체 유저
LTV	총 결제액	결제한 사람

같은 문제 안에서 분모가 두 번 바뀐다. 여기서 헷갈린다.

이렇게 푼다

사람마다 총 결제액을 구한다.
유저 명단 왼쪽에 두고 붙인다 (전체 유저 수를 세야 하므로).
전체 유저 수 / 결제한 사람 수를 각각 센다.
LTV = 총 결제액 ÷ 결제한 사람 수.

이것만 피하면 된다

LTV 의 분모는 결제한 사람이다. 전체 유저로 나누면 그건 ARPU 라는 다른 지표다. 문제를 다시 읽어라 — 어느 쪽을 묻는지가 갈린다.
* 1.0 또는 100.0 을 꼭 넣어라.

자세히

묻는 것 · 분모가 다른 지표 두 개를 한 쿼리에서 만들 줄 아는가.

풀이 순서

유저별 누적 결제액을 먼저 만든다.
users 에 LEFT JOIN.
COUNT(*) 는 전체 유저, COUNT(결제컬럼) 은 결제 유저. NULL 을 세지 않는 성질을 이용한다.
AVG 도 NULL 을 무시하므로 결제 유저 기준 평균이 자동으로 나온다.

정답 (MySQL)

WITH pu AS (
  SELECT user_id, SUM(amount) AS amt FROM payments GROUP BY user_id
)
SELECT u.channel,
       COUNT(*)                                       AS total_users,
       COUNT(pu.user_id)                              AS paying_users,
       ROUND(100.0 * COUNT(pu.user_id) / COUNT(*), 2) AS paying_ratio,
       ROUND(AVG(pu.amt), 2)                          AS ltv
FROM users u
LEFT JOIN pu ON pu.user_id = u.user_id
GROUP BY u.channel
ORDER BY ltv DESC;

함정

이 문제의 핵심은 COUNT(*) 와 COUNT(컬럼) 의 차이다. 분모 두 개를 CASE 없이 만들 수 있다.
LTV 분모를 전체 유저로 착각하기 쉽다. 문제를 다시 읽을 것.
AVG(COALESCE(amt,0)) 로 쓰면 결제 안 한 유저가 0 으로 들어가 LTV 가 내려간다. 의도가 다르다.

이렇게 변형된다

“ARPU vs ARPPU” 가 정확히 이 차이다. ARPU 는 전체 유저, ARPPU 는 결제 유저 기준.