5. SQL로 리포트 자동화하기 – 실무형 통합 분석 쿼리 예제

데이터 기반 의사결정을 요구받는 요즘, 많은 마케터와 비즈니스 실무자는 데이터팀 없이도 스스로 보고서를 생성할 수 있어야 한다. 하지만 단순한 SELECT 쿼리로는 원하는 리포트를 만들기 어렵고, 매번 엑셀에 복사해서 붙여넣는 수동 작업은 시간이 많이 든다. 이럴 때 필요한 것이 SQL로 리포트를 자동화하는 방법이다. 이번 글에서는 앞에서 배운 SQL 기능들을 실제 업무 환경에서 ‘월별 고객 리포트’를 자동 생성하는 프로젝트 예제로 통합해보고, 비개발자도 쉽게 따라할 수 있도록 단계별로 설명한다.

 

프로젝트 목표

“고객별로 월 단위 주문 데이터를 집계하고,

• 누적 매출
• 주문 순위
• 전월 대비 매출 증감
• 구매 여부 태그

등을 포함한 리포트를 자동으로 생성한다.”

 

 

1. 사용 테이블 구조

users

user_id	user_name	signup_date
1	김철수	2025-06-01
2	이영희	2025-06-15
3	박민수	2025-07-01
4	최유리	2025-07-20

orders

order_id	user_id	amount	order_date
101	1	120000	2025-07-05
102	2	90000	2025-07-10
103	1	150000	2025-08-01
104	3	80000	2025-08-05
105	1	50000	2025-08-10
106	4	200000	2025-08-12
107	2	130000	2025-08-20

 

2. 리포트 컬럼 정의

컬럼명	설명
user_id	고객 고유번호
user_name	고객 이름
report_month	리포트 기준 월 (YYYY-MM)
monthly_amount	해당 월 총 구매금액
cumulative_amount	누적 구매금액
order_rank	월별 고객별 구매금액 순위
prev_month_amount	전월 구매금액
growth_vs_prev_month	전월 대비 증감액
customer_status	신규/기존 고객 여부 (구매 기준)

 

3. SQL 리포트 자동화 쿼리

위에서 예시로 든 테이블과 컬럼 정보를 통해 다음과 같이 자동화 쿼리를 생성해볼 수 있다. 

 

WITH monthly_data AS (
  SELECT 
    u.user_id,
    u.user_name,
    strftime('%Y-%m', o.order_date) AS report_month,
    SUM(o.amount) AS monthly_amount
  FROM orders o
  JOIN users u ON o.user_id = u.user_id
  GROUP BY u.user_id, report_month
),
cumulative_data AS (
  SELECT 
    user_id,
    report_month,
    monthly_amount,
    SUM(monthly_amount) OVER (
      PARTITION BY user_id
      ORDER BY report_month
    ) AS cumulative_amount,
    RANK() OVER (
      PARTITION BY report_month
      ORDER BY monthly_amount DESC
    ) AS order_rank,
    LAG(monthly_amount, 1) OVER (
      PARTITION BY user_id
      ORDER BY report_month
    ) AS prev_month_amount
  FROM monthly_data
),
final_report AS (
  SELECT 
    user_id,
    (SELECT user_name FROM users WHERE users.user_id = c.user_id) AS user_name,
    report_month,
    monthly_amount,
    cumulative_amount,
    order_rank,
    prev_month_amount,
    (monthly_amount - IFNULL(prev_month_amount, 0)) AS growth_vs_prev_month,
    CASE
      WHEN prev_month_amount IS NULL THEN '신규 고객'
      ELSE '기존 고객'
    END AS customer_status
  FROM cumulative_data c
)
SELECT *
FROM final_report
ORDER BY report_month, order_rank;

 

4. 결과 예시

아래 결과표는 SQL 쿼리 실행 결과로 생성된 리포트에 대한 예시이다.

user_id	user_name	report_month	monthly_amount	cumulative_amount	order_rank	prev_month_amount	growth_vs_prev_month	customer_status
1	김철수	2025-07	120000	120000	1	NULL	120000	신규 고객
2	이영희	2025-07	90000	90000	2	NULL	90000	신규 고객
1	김철수	2025-08	200000	320000	2	120000	80000	기존 고객
2	이영희	2025-08	130000	220000	3	90000	40000	기존 고객
3	박민수	2025-08	80000	80000	5	NULL	80000	신규 고객
4	최유리	2025-08	200000	200000	1	NULL	200000	신규 고객

이는 고객별로 월간 구매 데이터를 자동으로 집계해주기 때문에, 마케팅, 운영, CRM 등 다양한 부서에서 실질적인 인사이트를 빠르게 얻을 수 있다. 먼저, 고객의 월별 총 매출을 확인할 수 있다. 고객마다 어떤 달에 얼마를 구매했는지 숫자로 바로 확인 가능하며, 특정 기간 동안의 매출 집중 구간도 쉽게 파악할 수 있다. 또한, 고객별 누적 매출을 통해 어떤 고객이 장기적으로 높은 구매력을 보이고 있는지를 확인할 수 있다. 이 정보는 VIP 고객 선정, 우선 지원 대상 선정 등 전략적인 의사결정에 도움이 된다.

 

윈도우 함수를 통해 계산된 '월내 순위'를 보면, 각 달마다 어떤 고객이 매출 상위권에 있는지를 알 수 있다. 예를 들어 8월에 가장 많은 금액을 결제한 고객이 누구인지 즉시 확인할 수 있으며, 이를 기반으로 상위 고객 대상 보상 정책을 설계할 수 있다.

 

전월 매출과 비교한 증감액을 통해 고객의 구매 행동 변화도 알 수 있다. 예를 들어, A 고객은 8월에 20만 원을 결제했지만 전월에는 10만 원만 결제했다면, 이 고객은 상승 추세에 있는 고객으로 분류할 수 있다. 반대로 매출이 줄어든 고객은 이탈 위험군일 수 있으므로 별도의 관리가 필요하다.

 

마지막으로, 고객이 신규 고객인지, 기존 고객인지 자동으로 분류되기 때문에, 신규 유입 트렌드도 한눈에 파악 가능하다. 신규 고객 수가 많은 달은 유입 마케팅이 효과를 본 것으로 판단할 수 있고, 기존 고객이 재구매하는 패턴을 통해 리텐션 전략도 분석할 수 있다.

 

즉, 자동화 한 쿼리 구문 하나로 다음과 같은 정보들을 알 수 있다:

• 고객별 월별 매출 금액
• 현재까지의 누적 구매 금액
• 각 월의 고객 매출 순위
• 전월 대비 매출 증감액
• 신규 고객 vs 기존 고객 구분
• 리텐션 강화 또는 이탈 방지 대상 파악
• 월별 구매 트렌드 및 캠페인 효과 분석

이 모든 정보를 단 하나의 SQL 쿼리로 자동 생성할 수 있다는 점이 가장 큰 장점이다. 매번 엑셀을 수정하거나 복잡한 계산을 할 필요 없이, 쿼리 한 줄로 실시간 리포트를 운영할 수 있다.

 

 

5. 실무 활용 예시

• 📈 마케팅팀: 구매 증가 고객에게 할인 쿠폰 발송
• 🧾 영업팀: 누적 매출 기준 고객 리포트 제출
• 🕵️ 운영팀: 전월보다 매출이 감소한 고객 탐지
• 👥 CRM팀: 신규 고객과 기존 고객 세분화 마케팅 실행

 

---

결론

이제 여러분은 SQL을 단순히 데이터를 조회하는 도구로만 보지 않고, 리포트를 자동화하고 실시간 분석까지 가능한 비즈니스 도구로 활용할 수 있게 되었다. 이 글에서 다룬 SQL 구조는 실무에서도 그대로 사용할 수 있으며, 매일 반복되는 리포트를 수작업 없이 생성해줄 수 있다. SQL을 조금 더 다듬으면 이를 Python, Excel, 또는 BI 도구와 연동해 완전 자동 리포트 시스템도 구축할 수 있다.