월별 매출 분해 (신규/기존/복귀)

문제

쉽게 설명하면

이번 달 매출을 통 세 개에 나눠 담기 — 처음 온 손님 / 지난달에도 온 손님 / 오랜만에 다시 온 손님. 세 통을 합치면 전체와 딱 맞아야 한다.

이 세 칸은 서로 겹치지 않고, 빠지는 경우도 없다. 그래서 합이 딱 맞는다.

이렇게 푼다

1. 사람마다 생애 첫 결제가 몇 월인지를 구해 둔다.
2. 결제 줄마다 그 사람의 직전 결제가 몇 월이었나를 한 칸 위에서 끌어온다.
3. 위 표대로 셋 중 하나를 고른다 — CASE WHEN 첫결제월 = 이번달 THEN '신규' WHEN 직전결제월 = 지난달 THEN '기존' ELSE '복귀' END.
4. 통별로 금액을 합친다.

이것만 피하면 된다

자세히

묻는 것 · FP&A 의 핵심. 배타적 분류를 만들 줄 아는가.

풀이 순서

1. 유저 × 월 단위로 먼저 집계한다. 이걸 안 하면 아래가 전부 꼬인다.
2. MIN(m) OVER (PARTITION BY user_id) → 그 유저의 첫 결제 월.
3. LAG(m) OVER (PARTITION BY user_id ORDER BY m) → 직전 결제 월.
4. 첫 달이면 신규. 아니면 직전 달이 바로 전월인지로 기존/복귀를 가른다.

정답 (MySQL)

WITH mu AS (
  SELECT user_id, DATE_FORMAT(paid_date, '%Y-%m-01') AS m, SUM(amount) AS amt
  FROM payments GROUP BY user_id, m
),
l AS (
  SELECT user_id, m, amt,
         LAG(m) OVER (PARTITION BY user_id ORDER BY m) AS prev_m,
         MIN(m) OVER (PARTITION BY user_id)            AS first_m
  FROM mu
)
SELECT DATE_FORMAT(m, '%Y-%m') AS month,
       SUM(CASE WHEN m = first_m THEN amt ELSE 0 END) AS new_revenue,
       SUM(CASE WHEN m <> first_m AND prev_m = DATE_SUB(m, INTERVAL 1 MONTH)
                THEN amt ELSE 0 END) AS existing_revenue,
       SUM(CASE WHEN m <> first_m AND (prev_m IS NULL OR prev_m < DATE_SUB(m, INTERVAL 1 MONTH))
                THEN amt ELSE 0 END) AS returning_revenue,
       SUM(amt) AS total_revenue
FROM l
GROUP BY month
ORDER BY month;

함정

이렇게 변형된다

이탈(churn) 매출까지 요구하면, 직전 달에는 있었는데 이번 달에 없는 유저를 따로 만들어 음수로 붙인다.