BOARDING PASS · What I Do

SQL 광고 성과 리포트 쿼리 만들기: 채널별 비용·전환·ROAS

가상 광고비 표와 주문 표로 채널별 비용·전환·매출·ROAS 를 구하는 SQL 광고 성과 리포트 쿼리를 처음부터 만들어 봅니다. 표마다 먼저 집계하고 WITH·LEFT JOIN 으로 붙이는 순서, SAFE_DIVIDE 로 0 나누기를 피하는 법, JOIN 뒤 합계가 부풀어 오르는 실수도 짚습니다.

DATE

READ9분 읽기

GATESQL

SQL 광고 성과 리포트 는 광고비 표와 주문 표를 채널 단위로 각각 요약한 뒤 하나로 붙이고, 그 위에서 ROAS·CPA 같은 비율을 계산하는 쿼리입니다. 시리즈 마지막 회인 이번 글에서는 지금까지 배운 SELECT·WHERE·GROUP BY·JOIN·WITH 를 한 쿼리 안에서 모두 써 보는데요, 순서만 지키면 새로 나오는 것은 나눗셈 함수 SAFE_DIVIDE 와 반올림 함수 ROUND 두 개뿐입니다. 저도 SQL 을 처음 배우면서 「표 두 개를 붙여서 채널별 ROAS 를 내 주세요」라는 말이 가장 막막하게 들렸는데요, 막상 단계를 나눠 보니 앞 회차에서 한 번씩 써 본 것들의 조합이었습니다. 이번 글에서는 가상 광고비 표와 주문 표로 리포트 쿼리를 처음부터 끝까지 직접 따라 해 보겠습니다. 쿼리는 BigQuery(GoogleSQL) 문법으로 적었습니다. 지난 글: SQL 서브쿼리와 WITH(CTE)로 긴 쿼리 나누기

SQL 기초 시리즈 12/12 · 전체 커리큘럼

오늘 배울 개념

엑셀로 광고 보고서를 만든다고 생각해 보세요. 광고비 시트와 주문 시트가 따로 있으면, 보통은 시트마다 피벗 테이블로 채널별 합계를 먼저 내고, 두 요약표를 채널 이름으로 나란히 붙인 다음, 옆 열에 =매출/광고비 를 적어 ROAS 를 구하죠. SQL 리포트 쿼리도 이 순서를 그대로 따릅니다. 피벗 테이블은 GROUP BY, 요약표를 붙이는 일은 JOIN, 옆 열의 나눗셈은 SELECT 안의 계산 열이고, 단계마다 만든 요약표에는 WITH 로 이름을 붙여 둡니다. 여기서 쓰는 지표는 세 가지입니다. Google Ads 타깃 ROAS 도움말은 광고비 1달러로 매출 5달러를 낸 경우를 5 ÷ 1 × 100% = 500% 로 계산하는데요, 곧 ROAS 는 매출 ÷ 광고비 × 100 입니다. 전환 데이터 도움말은 전환당 비용(CPA)을 총비용 ÷ 전환수 로, 전환율을 전환수 ÷ 클릭 같은 상호작용 수 로 설명합니다. 지표 자체가 낯서시다면 광고 성과 지표 기초 글을 먼저 보셔도 좋습니다.

광고비 표와 주문 표를 각각 GROUP BY 로 채널별 한 줄씩 요약하고, 두 요약표를 channel 열로 LEFT JOIN 한 뒤 SAFE_DIVIDE 로 ROAS 열을 계산하는 SQL 광고 성과 리포트 쿼리의 네 단계 그림 개념 그림: 표마다 먼저 채널별로 요약하고, 요약표끼리 붙인 다음, 마지막에 비율을 계산합니다

따라 해 보기: SQL 광고 성과 리포트 쿼리 만들기

표는 두 개입니다(가상 데이터). ads.ad_spend 는 날짜·채널별 광고비와 클릭 수이고, shop.orders 는 주문마다 어느 채널에서 왔는지가 적힌 주문 표인데요, 5번 주문은 광고가 아닌 자연검색으로 들어왔습니다. 이 예시는 주문마다 채널이 하나로 정해져 있다고 가정했습니다.

ad_spend (광고비)

spend_datechannelclickscost
2026-11-02검색광고12030000
2026-11-02SNS20020000
2026-11-03검색광고10025000
2026-11-03SNS18018000
2026-11-04검색광고8020000
2026-11-04이메일505000

orders (주문)

order_idorder_datechannelamount
12026-11-02검색광고30000
22026-11-02SNS15000
32026-11-03검색광고45000
42026-11-03SNS12000
52026-11-03자연검색20000
62026-11-04검색광고30000
72026-11-04이메일25000
82026-11-04SNS9000

1. 광고비 표를 채널별로 요약하기: 9회 글의 GROUP BY 로 날짜별 여러 줄을 채널마다 한 줄로 줄입니다.

SELECT channel, SUM(clicks) AS clicks, SUM(cost) AS cost
FROM ads.ad_spend
GROUP BY channel
ORDER BY cost DESC;
channelclickscost
검색광고30075000
SNS38038000
이메일505000

검색광고는 30,000 + 25,000 + 20,000 = 75,000원, 클릭은 120 + 100 + 80 = 300회입니다. 세 채널 비용을 더하면 118,000원으로 원래 표의 합계와 같은데요, 이 숫자는 마지막 검산에 다시 씁니다.

2. 주문 표를 채널별로 요약하기: 이번 예시에서는 주문 한 건을 전환 한 건으로 봅니다.

SELECT channel, COUNT(*) AS conversions, SUM(amount) AS revenue
FROM shop.orders
GROUP BY channel
ORDER BY revenue DESC;
channelconversionsrevenue
검색광고3105000
SNS336000
이메일125000
자연검색120000

검색광고는 1·3·6번 주문이라 3건에 105,000원이고, 네 채널 매출을 더하면 186,000원입니다. 광고비 표에는 없던 자연검색이 여기에는 한 줄 있죠.

3. 두 요약표를 WITH 와 LEFT JOIN 으로 붙여 ROAS 구하기: 1·2번 쿼리에 11회 글처럼 이름을 붙여 WITH 로 올리고, 10회 글의 LEFT JOIN 으로 주문 요약을 기준 삼아 광고비를 붙입니다.

WITH cost_by_ch AS (
  SELECT channel, SUM(clicks) AS clicks, SUM(cost) AS cost
  FROM ads.ad_spend
  GROUP BY channel
),
sales_by_ch AS (
  SELECT channel, COUNT(*) AS conversions, SUM(amount) AS revenue
  FROM shop.orders
  GROUP BY channel
)
SELECT s.channel, c.cost, s.conversions, s.revenue,
  ROUND(SAFE_DIVIDE(s.revenue, c.cost) * 100, 1) AS roas_pct
FROM sales_by_ch AS s
LEFT JOIN cost_by_ch AS c ON s.channel = c.channel
ORDER BY s.revenue DESC;
channelcostconversionsrevenueroas_pct
검색광고750003105000140.0
SNS3800033600094.7
이메일5000125000500.0
자연검색NULL120000NULL

검색광고 ROAS 는 105,000 ÷ 75,000 × 100 = 140.0, SNS 는 36,000 ÷ 38,000 × 100 = 94.73… 이라 소수 첫째 자리에서 반올림해 94.7 입니다. 자연검색은 광고비 표에 짝이 없어서, 쿼리 문법 문서의 LEFT JOIN 설명대로 오른쪽 표의 열인 cost 가 NULL 로 채워졌습니다. 새로 나온 함수는 SAFE_DIVIDE 와 ROUND 둘인데요, 수학 함수 문서에 따르면 이 함수는 나눗셈(X / Y)과 같지만 0 으로 나누는 것 같은 오류가 나면 NULL 을 돌려줍니다. 같은 문서는 수학 함수에 NULL 이 들어가면 결과도 NULL 이라고 적고 있어서 자연검색의 ROAS 도 NULL 이 됐고요. INT64 끼리 나눠도 결과는 FLOAT64 라서 140.0 처럼 소수점이 붙고, ROUND(값, 1) 은 소수 첫째 자리까지 남기며 딱 절반인 값은 0 에서 먼 쪽으로 반올림합니다.

4. 같은 요약표로 CPA·전환율 보기: 광고 채널만 보고 싶다면 이번에는 광고비 요약을 기준으로 붙입니다. 아래는 3번의 WITH 두 개를 그대로 둔 채 본 쿼리만 바꾼 것입니다.

-- WITH cost_by_ch, sales_by_ch 부분은 3번과 같습니다
SELECT c.channel, c.clicks, c.cost, s.conversions,
  ROUND(SAFE_DIVIDE(s.conversions, c.clicks) * 100, 2) AS cvr_pct,
  ROUND(SAFE_DIVIDE(c.cost, s.conversions)) AS cpa
FROM cost_by_ch AS c
LEFT JOIN sales_by_ch AS s ON c.channel = s.channel
ORDER BY cpa;
channelclickscostconversionscvr_pctcpa
이메일50500012.05000.0
SNS3803800030.7912667.0
검색광고3007500031.025000.0

SNS 전환율은 3 ÷ 380 × 100 = 0.789… 라 0.79, CPA 는 38,000 ÷ 3 = 12,666.6… 을 정수로 반올림해 12,667 입니다. 기준 표를 광고비 요약으로 바꾸니 자연검색 줄이 빠졌는데요, 어느 표를 왼쪽에 두느냐가 리포트에 어떤 줄이 남는지를 정한다 는 점이 10회에서 배운 LEFT JOIN 의 쓰임 그대로입니다. 결과 값은 모두 예시 표가 작아 손으로 계산해 맞춘 값입니다.

지금까지 배운 것은 이 쿼리 어디에 있을까요?

3번 쿼리 한 편에 시리즈 대부분이 들어 있습니다. 회차별로 어디에 쓰였는지 나란히 놓아 보면 이렇습니다.

회차배운 것3번 쿼리에서 쓴 곳
1·2회테이블 구조, 키로 이어지는 표키처럼 두 표를 잇는 공통 열 channel
4회데이터 타입과 NULLINT64 나눗셈이 FLOAT64, 자연검색의 NULL 광고비
5회쿼리의 논리적 실행 순서WITH 안에서 FROM → GROUP BY → SELECT 순으로 요약
6회SELECT·AS·계산 열roas_pct 같은 계산 열과 별칭
8회ORDER BY매출이 큰 채널부터 정렬
9회GROUP BY·SUM·COUNT채널별 비용·클릭·전환·매출
10회LEFT JOIN·ON주문 요약에 광고비 요약 붙이기
11회WITH(CTE)cost_by_ch, sales_by_ch 두 단계

3회 정규화가 직접 보이지는 않지만, 광고비와 주문을 처음부터 다른 표에 두는 이유가 바로 정규화였죠. 광고비는 날짜·채널마다 한 줄, 주문은 주문마다 한 줄이라 한 줄이 뜻하는 단위가 다른 표 를 붙여야 하는데요, 그래서 붙이기 전에 둘 다 채널 단위로 맞추는 1·2번 단계가 필요했던 겁니다.

한 줄의 단위가 다른 광고비 표와 주문 표를 그대로 JOIN 하면 검색광고 광고비 3줄과 주문 3줄이 3 × 3 = 9줄로 늘어나 광고비 합계가 75000 이 아니라 225000 이 되고, 먼저 채널별로 요약한 뒤 붙이면 1줄 × 1줄이라 75000 이 맞게 나오는 것을 비교한 그림 개념 그림: 단위가 다른 표를 먼저 붙이면 행이 곱해져 합계가 부풀고, 요약한 뒤 붙이면 한 줄끼리 만납니다

실무에서는 어떻게 쓸까요? 주간 광고 보고서 순서

매주 월요일 채널별 광고 성과를 보고하는 담당자라고 가정해 볼게요. 쿼리를 바로 쓰기보다 아래 순서를 따르면 중간에 숫자가 틀어져도 어디서 틀어졌는지 찾기 쉽습니다.

  1. 질문을 지표로 바꿉니다: 「채널별로 돈을 얼마 썼고 얼마 벌었나」를 비용·전환·매출·ROAS 네 열로 적고, 각 지표의 분자와 분모가 어느 표에 있는지 표시합니다.
  2. 표마다 같은 단위로 요약합니다: 보고 단위가 채널이면 두 표 모두 GROUP BY channel 로 먼저 줄입니다. 기간을 거를 때는 WITH 안에서 7회 글의 WHERE spend_date BETWEEN ... 처럼 각 표에 같은 기간 조건을 넣습니다.
  3. 기준 표를 정해 붙입니다: 매출이 난 채널을 모두 보고 싶으면 주문 요약을 왼쪽에, 돈을 쓴 채널만 보고 싶으면 광고비 요약을 왼쪽에 두고 LEFT JOIN 합니다.
  4. 비율은 마지막에 계산합니다: 분모가 0 이거나 NULL 일 수 있는 나눗셈은 모두 SAFE_DIVIDE 로 씁니다.
  5. 검산합니다: 결과의 비용 합이 원래 광고비 표 합계(예시에서는 118,000원)와 같은지, 매출 합이 주문 표 합계(186,000원)와 같은지 봅니다. 숫자가 더 크게 나왔다면 4단계가 아니라 3단계를 의심하면 됩니다.

이 예시에서 주문의 채널은 표에 이미 하나로 적혀 있었는데요, 실제로는 주문을 어느 채널의 성과로 셀지 정하는 기준에 따라 같은 주문도 다른 채널에 들어갈 수 있습니다. 쿼리를 아무리 정확히 써도 그 기준이 바뀌면 ROAS 가 달라진다는 점은 리포트에 함께 적어 두시길 권합니다.

연습 문제

1. (쉬움) 광고비 표만으로 채널별 클릭당 비용(광고비 ÷ 클릭 수)을 구해 보세요. 반올림은 정수로 합니다.

정답 보기
SELECT channel,
  ROUND(SAFE_DIVIDE(SUM(cost), SUM(clicks))) AS cpc
FROM ads.ad_spend
GROUP BY channel
ORDER BY cpc DESC;

검색광고 250.0(75,000 ÷ 300), SNS 100.0(38,000 ÷ 380), 이메일 100.0(5,000 ÷ 50)입니다. SNS 와 이메일은 값이 같아서, cpc 만으로 정렬하면 둘의 순서는 보장되지 않습니다. 순서를 고정하려면 ORDER BY cpc DESC, channel 처럼 열을 하나 더 적습니다.

2. (보통) 3번의 WITH 두 개를 그대로 두고, 광고 채널 전체를 한 줄로 합친 광고비·매출·ROAS 를 구해 보세요.

정답 보기
-- WITH cost_by_ch, sales_by_ch 부분은 3번과 같습니다
SELECT SUM(c.cost) AS cost, SUM(s.revenue) AS revenue,
  ROUND(SAFE_DIVIDE(SUM(s.revenue), SUM(c.cost)) * 100, 1) AS roas_pct
FROM cost_by_ch AS c
INNER JOIN sales_by_ch AS s ON c.channel = s.channel;

118,000원, 166,000원, 140.7 이 나옵니다. INNER JOIN 이라 광고비가 없는 자연검색 20,000원은 빠지고(186,000 − 20,000 = 166,000), 166,000 ÷ 118,000 × 100 = 140.67… 입니다.

3. (보통) 날짜별 광고비와 광고 채널 매출(자연검색 제외), ROAS 를 구해 보세요.

정답 보기
WITH daily_cost AS (
  SELECT spend_date, SUM(cost) AS cost
  FROM ads.ad_spend GROUP BY spend_date
),
daily_sales AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM shop.orders
  WHERE channel != '자연검색'
  GROUP BY order_date
)
SELECT c.spend_date, c.cost, s.revenue,
  ROUND(SAFE_DIVIDE(s.revenue, c.cost) * 100, 1) AS roas_pct
FROM daily_cost AS c
LEFT JOIN daily_sales AS s ON c.spend_date = s.order_date
ORDER BY c.spend_date;
spend_datecostrevenueroas_pct
2026-11-02500004500090.0
2026-11-034300057000132.6
2026-11-042500064000256.0

11월 3일은 3·4번 주문 57,000원을 광고비 43,000원으로 나눠 132.55… 라 132.6 입니다. 보고 단위가 채널에서 날짜로 바뀌었을 뿐, 표마다 요약하고 붙이는 순서는 같죠.

자주 하는 실수

  • 표를 먼저 JOIN 하고 나중에 SUM 합니다: ads.ad_spend 와 shop.orders 를 channel 로 바로 붙인 뒤 GROUP BY 하면, 검색광고는 광고비 3줄과 주문 3줄이 만나 9줄이 되고 광고비 합계가 75,000원이 아니라 225,000원으로 나옵니다. 쿼리 문법 문서는 INNER JOIN 을 두 표의 모든 조합(곱집합)에서 조건에 맞지 않는 행을 버리는 것으로 설명하는데요, 조건에 맞는 짝이 여럿이면 그만큼 행이 늘어나기 때문입니다. 위 그림처럼 요약한 뒤 붙이기 를 지키고, 검산으로 확인하세요.
  • / 로 나눴다가 0 에서 쿼리가 멈춥니다: 연산자 문서에 따르면 0 으로 나누는 연산은 오류를 내고, 문서도 이때 SAFE_DIVIDE 나 IEEE_DIVIDE 를 쓰라고 안내합니다. 어느 날 전환이 0건인 채널이 생기면 CPA 계산(광고비 ÷ 전환수)에서 쿼리 전체가 실패하니, 리포트 쿼리의 나눗셈은 처음부터 SAFE_DIVIDE 로 적어 두는 편이 안전합니다. 다른 DBMS 에서는 다르게 피하는데요, PostgreSQL 조건 함수 문서는 CASE WHEN 으로 0 나누기 실패를 피하는 예를 들고, 같은 문서의 NULLIF(분모, 0) 으로 0 을 NULL 로 바꿔 나누는 방법도 쓸 수 있습니다.
  • 채널별 ROAS 를 AVG 로 평균 냅니다: 3번 결과의 140.0, 94.7, 500.0 을 평균하면 244.9 가 나오지만, 연습 문제 2번에서 구한 광고 채널 전체 ROAS 는 140.7 입니다. 광고비 5,000원짜리 이메일과 75,000원짜리 검색광고가 같은 무게로 섞였기 때문인데요, 전체 비율은 분자와 분모를 각각 더한 뒤 다시 나눠야 합니다.

채널별 ROAS 140.0, 94.7, 500.0 을 단순 평균하면 244.9 가 되지만 매출 합 166000 을 광고비 합 118000 으로 나누면 140.7 이 되는 차이를 저울 그림으로 비교한 SQL ROAS 계산 그림 개념 그림: 비율의 평균은 광고비 규모를 무시하므로, 합계끼리 다시 나눈 값과 다릅니다

마치며

SQL 광고 성과 리포트 쿼리는 표마다 같은 단위로 먼저 요약하고, 기준 표를 정해 LEFT JOIN 으로 붙인 뒤, 마지막에 SAFE_DIVIDE 로 비율을 계산하는 세 단계였습니다. 숫자가 이상하면 원래 표의 합계와 맞춰 보는 검산부터 해 보시길 권합니다. 0회 「데이터베이스란?」부터 12회까지 시리즈를 따라오시느라 고생 많으셨습니다. 저도 공부하면서 쓴 글이라, 실제 데이터에 적용해 보며 달라지는 점이 생기면 다시 알려 드리겠습니다. 회차별 글은 전체 커리큘럼을 참고해 주세요. 이번 글로 SQL 기초 시리즈는 끝나고 다음 회차는 없는데요, 헷갈리는 문법이 생기면 해당 회차로 돌아가 예시 표를 다시 따라 해 보시면 됩니다.

자주 묻는 질문

SQL 로 ROAS 계산은 어떻게 하나요?
매출 합계를 광고비 합계로 나누고 100 을 곱하면 됩니다. BigQuery 에서는 ROUND(SAFE_DIVIDE(매출, 광고비) * 100, 1) 처럼 쓰는데요, 광고비 표와 주문 표를 바로 JOIN 하지 말고 각각 채널별로 먼저 합계를 낸 뒤 붙여야 합계가 부풀지 않습니다.
SAFE_DIVIDE 와 / 나눗셈은 무엇이 다른가요?
계산 결과는 같지만, / 는 0 으로 나누면 오류를 내 쿼리가 멈추는 반면 SAFE_DIVIDE 는 그 자리에 NULL 을 돌려줍니다. 입력에 NULL 이 있으면 둘 다 NULL 이 됩니다.
JOIN 했더니 광고비 합계가 원래보다 커졌어요. 왜 그런가요?
한 줄이 뜻하는 단위가 다른 두 표를 그대로 붙이면, 조건에 맞는 짝이 여럿인 행이 그 수만큼 늘어나기 때문입니다. 두 표를 채널이나 날짜처럼 같은 단위로 먼저 GROUP BY 한 뒤 JOIN 하면 한 줄끼리 만나 합계가 맞습니다.

관련 글 더 보기