[SQL 기본] 예제_마케팅 채널 최적화(2)

이번 글에서는 지난번에 이어 마케팅 채널 최적화를 위한 쿼리를 작성하는 예제를 이어서 진행해 보겠습니다. 테이블 명세서에 대한 이해도 생겼으니 아래 테이블 명세서를 확인하며 요청사항을 잘 처리해 봅시다. 이번 예제에서는 우리가 배운 것을 모두 활용할 수 있도록 난이도를 더 올려 보겠습니다.

 

🚨요청사항
마케팅 효율이 좋지 않은 캠페인의 원인을 파악하기 위해 1월 한 달간 소진액이 백만 원이 넘지만 Roas가 200%를 넘지 않는 캠페인을 확인하고자 합니다. 해당하는 캠페인 ID, 채널명, 소재명, 소진액, Roas를 소진액이 큰 순서대로 출력해 주세요.

마케팅 성과 테이블 명세서

 

지난 예제보다 난이도가 급상승한 것 같지만 그래도 우리는 해낼 수 있습니다. 천천히 번역해 봅시다.

 

• SELECT 절에는 우리가 얻고자 하는 값을 정의해야 하고 요청사항에 맞게 정의되어야 하는 값은 다음과 같습니다.
  
  • 값: 캠페인 ID, 채널명, 소재명, 소진액, Roas(?)

• FROM 절에는 해당 데이터가 담긴 테이블을 정의해야 하고 요청사항에 맞게 정의되어야 하는 값은 테이블 명세서에 명시되어 있습니다.
  • 테이블: marketing_performance
• WHERE 절에는 우리가 원하는 데이터를 추출하기 위한 필터링 조건을 정의해야 하고 요청사항에 맞게 정의되어야 하는 값은 다음과 같습니다.
  • 조건: 1월 소진액 백만 원 이상(?), Roas 200% 미만(?)

이번엔 아무리 봐도 번역이 시원하게 이루어지지 않은 느낌입니다. 그런 느낌이 드는 이유는 두 가지 때문인데요. 그 두 가지는 다음과 같습니다.

 

첫째, marketing_performance는 일별로 쌓이는 테이블이기 때문에 1월 소진액을 확인하려면 집계함수가 필요합니다. 일별 소진액을 월별로 집계하려면 SUM() 함수를 활용해 집계해야 합니다. 그렇기 때문에 우리가 원하는 값은 SUM(소진액)이 되어야 합니다.

 

두 번째, marketing_performance테이블에는 Roas라는 컬럼이 없습니다. 하지만 Roas를 계산할 순 있습니다. Roas는 매출과 비용으로 이뤄져 있고 SELECT 절에서는 산술연산이 가능하기 때문에 매출/비용이라는 산술연산을 통해 Roas를 도출할 수 있습니다. Roas는 1월 전체의 Roas를 계산해야 하기 때문에 SUM(발생 매출) / SUM(소진액)이 되어야겠지요.

 

이제 우리는 SELECT 절을 다시 정의할 수 있습니다.

• SELECT 캠페인 ID, 채널명, 소재명, 소진액, Roas(?)
  => SELECT 캠페인 ID, 채널명, 소재명, SUM(소진액), SUM(발생 매출) / SUM(소진액)
• 이를 테이블 명세서와 대응해서 SELECT 절과 FROM 절을 정의하면 다음과 같습니다.

SELECT campaign_id, channel_name, creative_name, SUM(spent), SUM(revenue)/SUM(spent)
FROM marketing_performance

 

그럼 이제 조건들에 대해서 다시 정의해 보겠습니다. 1월 전체의 소진액과 매출을 계산해야 하니 캠페인 집행일자가 2024-01-01 ~ 2024-01-31에 해당하는 데이터를 필터링해야 합니다. 

• WHERE 1월 한 달간 집행
  
  • 다시 한번 BETWEEN A AND B라는 명령어를 바탕으로 날짜에 대한 조건은 BETWEEN '2024-01-01' AND '2024-01-31'으로 정의할 수 있습니다.

SELECT campaign_id, channel_name, creative_name, SUM(spent), SUM(revenue)/SUM(spent)
FROM marketing_performance
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'

 

소진액 백만 원 이상과 Roas 200% 미만 조건은 어떨까요? 해당 조건을 WHERE 절에 정의한다면 다음과 같은 모양이 되겠죠.

SELECT campaign_id, channel_name, creative_name, SUM(spent), SUM(revenue)/SUM(spent)
FROM marketing_performance
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
	AND spent >= 1000000
	AND revenue/spent < 2

 

모양은 그럴싸하지만 만약 이렇게 정의를 하게 되면 우리가 원하는 결과를 얻을 수 없습니다. 그 이유는 쿼리문의 실행 순서가 정해져 있기 때문입니다. 쿼리를 작성할 때는 SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY절의 순서로 작성하지만 실제로 실행할 땐 FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY절의 순서로 실행되기 때문입니다. 

 

그렇기 때문에 위 쿼리대로 실행한다면 SELECT 절에 정의한 집계함수가 실행되기 전에 WHERE절에 정의한 조건에 의해서 데이터에 대한 필터링이 먼저 실행됩니다. marketing_performance 테이블은 일별로 집계되는 테이블이기 때문에 일별 소진액이 백만 원을 넘기는 경우는 극히 드물고 모두 필터링되기 때문에 빈 테이블에 쿼리를 날리는 것과 동일한 결과를 얻게 될 겁니다. 이것은 우리가 우리 원하는 조건 필터링이 아닙니다. 

 

그럼 1월 소진액을 모두 더하는 집계함수가 실행된 이후에 조건절을 실행할 수 있는 방법은 무엇이 있을까요? 그것이 바로 HAVING 절입니다. HAVING 절은 GROUP BY절에 뒤이어 실행되기 때문에 집계함수를 통한 조건 필터링 가능합니다.

 

그럼 먼저 GROUP BY 절을 먼저 정의해 봅시다.

• GROUP BY에는 SELECT 절에서 정의한 컬럼을 모두 정의해줘야 합니다. (집계함수 제외) 

SELECT campaign_id, channel_name, creative_name, SUM(spent), SUM(revenue)/SUM(spent)
FROM marketing_performance
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY campaign_id, channel_name, creative_name

 

이제 HAVING 절을 정의할 차례입니다.

• HAVING 절에는 집계함수를 정의할 수 있습니다. 1월 총 소진액이 백만 원 이상, 1월 총 Roas가 200% 미만이어야 하므로 다음과 같이 정의할 수 있습니다. SUM(spent) >= 1000000 AND SUM(revenue)/SUM(spent) < 2

SELECT campaign_id, channel_name, creative_name, SUM(spent), SUM(revenue)/SUM(spent)
FROM marketing_performance
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY campaign_id, channel_name, creative_name
HAVING SUM(spent) >= 1000000 AND SUM(revenue)/SUM(spent) < 2

 

마지막으로 정의해야 하는 ORDER BY 절입니다. 

• ORDER BY 절에는 출력한 데이터를 정렬하는 명령어입니다. 소진액이 큰 순서대로 출력해야 하니 ORDER BY SUM(소진액) DESC로 정의할 수 있습니다. 

SELECT campaign_id, channel_name, creative_name, SUM(spent), SUM(revenue)/SUM(spent)
FROM marketing_performance
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY campaign_id, channel_name, creative_name
HAVING SUM(spent) >= 1000000 AND SUM(revenue)/SUM(spent) < 2
ORDER BY SUM(spent) DESC

 

지금까지 배운 것들을 모두 활용해 본 예제를 통해 우리가 업무를 통해 접할 수 있는 케이스를 다뤄보았습니다. 다음 글에서는 이와 같은 유사한 예제들을 다뤄보며 SQL 기본에 대한 마지막 이해를 다져보도록 하겠습니다.

 

함께 성장해요.