데이터 분석가의 SQL 데이터 변환 가이드 비즈니스 핵심 지표(pCVR, CPO) 산출과 WITH절 활용법

데이터 분석을 공부하다 보니, 자연스럽게 비즈니스 핵심 지표를 맞이 하게 된다. 기본적인 비즈니스 성과 및 지표 용어를 이해하고 있어야, 분석의 깊이를 더 할 수 있다고 생각한다. 그래서 용어의 간단 정리 및 SQL 데이터 변환을 위해 사용되는 함수를 알아보자.

[🚀 데이터 분석가들이 자주 묻는 SQL & 비즈니스 지표 Q&A]

Q1. SQL 나눗셈 연산 시 ‘Division by zero’ 에러를 방지하려면?

• A: 표준 SQL에서는 NULLIF(분모, 0)를 사용하여 0을 NULL로 변환하고, BigQuery에서는 SAFE_DIVIDE(분자, 분모) 함수를 사용해 에러 대신 NULL을 반환하도록 처리해야 쿼리가 중단되지 않습니다.

Q2. 쿼리 내에서 새로 만든 컬럼(별칭)을 바로 다음 계산에 사용하고 싶다면?

• A: **WITH절(CTE)**을 사용해 임시 테이블을 먼저 생성해야 합니다. SQL은 동일한 SELECT 문 내에서 정의된 별칭을 즉시 인식하지 못하므로, WITH절로 중간 단계 데이터를 저장한 후 최종 SELECT에서 참조하는 것이 정석입니다.

Q3. 개별 행 데이터와 전체 합계(SUM)를 한 줄에서 계산할 때 발생하는 오류 해결법은?

• A: **스칼라 서브쿼리(Scalar Subquery)**를 활용하세요. (SELECT SUM(column) FROM table) 형태로 합계 값을 단일 스칼라 값으로 먼저 뽑아내면, SQL이 혼란 없이 행 단위 계산(예: 전체 대비 비중)을 수행할 수 있습니다.

SQL 데이터 변환 및 비즈니스 핵심 지표

SQL 데이터 변환(Data transformation)은 기존 컬럼으로부터 새로운 컬럼을 도출할 수 있게 해주는 기본적인 데이터 조작 작업입니다.

즉, 데이터 변환 작업을 거치면, 결과가 표시되는 테이블 마지막 끝에 새 컬럼이 추가된 형태가 된다는 의미다.

SELECT *,

표현식 AS 새_컬럼_이름

FROM 테 이 블 _ 이 름;

표현식이라고 되어 있는 부분은 컬럼명과 집계 연산이 될 수도 있고 다양한 형식으로 표현될 수 있다. 어쨌든 그것을 AS 새 컬럼 이름으로 만드는 것이 데이터 변환이라고 한다. 이것은 하나 뿐만 아니라 그 이상도 만들 수 있다.

아래에서는 데이터 변환으로 인해 새롭게 추가되는 컬럼명을 구하기 위한 비즈니스 핵심 지표 및 공식에 대해서 알아보려고 한다.

데이터 변환을 통해 자주 생성되는 비즈니스 핵심 지표

데이터 분석 공부를 위해 여전히 datacamp를 사용하고 있다. 단순히 코드나 쿼리만 있을 줄 알았는데, 비즈니스 핵심 지표를 알려주는 부분이 있어 이 부분은 향후 사용될 가능성이 크기에 한번 정리해 봤다.

SQL 데이터 변환을 하면 아래와 같은 비즈니스 지표를 추가로 만들어야 기회가 생긴다.

1) 비즈니스 지표

마케팅 비용을 들여 데려온 손님들이 실제로 물건을 샀는지를 성적표로 매긴 것

pCVR = 100 * 유료 주문 수 / 유료 방문 수

%를 확인할 때에는 단순히 숫자만 보면 안된다. 비율 확인 시에는 어느 정도 분모의 크기가 커야 신뢰성에 힘이 실린다.

2명 중에 1명이면 50%, 1000명 중 10명이면 1%이지만, 1000명의 분모 사이즈의 비율이 훨씬 신뢰성을 가지는 것은 나만 느끼는 것은 아닐 것이다.

유료 주문 수 하나를 얻기 위해 필요한 평균 광고 비용

CPO = 총 광고 비용 / 총 유료 주문 수

예를 들자면, 광고비 $1000을 들여서 유료 주문 수 50건을 만들어 냈다. 그렇다면 CPO는 $20이라는 얘기이다.

하나의 주문을 받기 위해서는 평균적으로 $20의 광고 비용이 든다는 얘기이다. CPO가 낮을수록 더 효율적으로 고객을 획득하고 있다는 뜻이다

CPO를 알아야 하는 이유 : 광고 예산 설정 및 수익성 관리 핵심 지표

• 수익성 판단: 제품 가격이 $100인데 CPO가 $20이면 주문당 $80의 마진이 생깁니다. 하지만 CPO가 $150이면 손해
• 광고 예산 최적화: 목표 매출 $100,000를 달성하려면 예상 주문 수와 CPO를 곱해서 필요한 광고비를 계산 가능
• 채널 비교: Instagram CPO가 $20인데 TikTok CPO가 $50이면 어디에 예산을 더 투입해야 할지 명확해짐
• 트렌드 모니터링: 우리 데이터처럼 CPO가 증가하는 추세($20 → $33)를 발견하면 전략을 재검토해야 한다는 신호

내가 낸 돈보다 얼마나 더 벌었는 지를 알아보는 지표, 기준은 1이고, 값이 1.5라면 투자금의 1.5배를 회수한 것(50% 수익)이고, 0.8이라면 80%만 회수한 것(20% 손실)

Return Multiple = Sell Price(판매가) / Avg Buy Price(평균 매수가)

%를 쓰지 않아서, 직관적이다. 수익 배수는 내 투자의 덩어리(원금)가 얼마나 커졌는 지를 보여주는 지표이다.

내 원금 대비 이 순이익이 차지하는 비중이 얼마인가? 를 구하는 것

Price Change Ratio = ((판 값 – 산 값) / 산 값) * 100

예를 들어 주식을 구매했고, 원금이 100,000원이라고 치자.

광고 기여도(Ad Contribution) : 광고비를 쓰고 나서 진짜 내 주머니에 남은 순수익

총 매출에서 광고비와 이행 비용(택배비, 포장비, 인건비 등)을 제외한 순수익이다. 단순히 매출만 보면 장사가 잘 되는 것처럼 보여질 수 있다.

하지만, 광고 기여도로 계산을 하면 실제로 내 주머니에 얼마가 남는 지 알게 되기 때문에 매출보다 더 중요하다.

Ad Contribution = Revenue(매출) – Ad spend(광고비) – (총 유료 주문 수 * 건당 부가 비용)

• 진짜 수익성 판단: 매출(Revenue)은 ‘허상’일 수 있지만, 광고 기여도는 ‘현실’
• 광고비 최적화: 광고 기여도가 높은 날의 특징을 분석해서 그날의 광고 전략을 강화

광고 기여율 (Contribution Rate) : 광고 기여도가 전체 매출에서 차지하는 비중을 백분율(%)

광고 기여도는 그 날의 매출 금액에 따라 결정되는 경우가 많다. 매출이 아주 큰 날과 아주 적은 날의 경우 비교하기가 어렵다.

기여율은 이렇게 규모가 다른 날들을 공정하게 비교하기 위함이다.

Contribution Rate = (Ad Contribution / Revenue) * 100

얼핏 보면, B일이 매출과 광고 기여도가 A일 보다 더 크기 때문에 B일 효율적으로 돈을 번 것 같지만, 광고 수익률을 따졌을 때는 A일의 광고 전략이 훨씬 효율적임을 알 수 있다.

• 수익성 가이드라인 : 예를 들어 광고 기여율 15% 잡고, 그 기여율이 그 이하로 떨어졌을 때 광고를 중단하거나, 전략을 수정하는 의사 결정 판단의 기준이 된다.

2) 마진과 광고 기여도의 차이

광고 기여도를 공부한 후 약간의 의문이 들었다. 광고 기여도도 어쨌든 광고를 집행한 이후의 순수익을 나타낸다.

그리고 우리가 기본적으로 알고 있는 마진은 매출에서 원가 등 제반 비용을 빼고 나서 남는 순수익을 의미한다. 둘 다 순수익이라 명확하게 할 필요가 있었다

이 한 문장이 그 둘의 차이를 명확히 보여준다.

“마진은 ‘무엇을’ 팔지 결정하고, 광고 기여도는 ‘어떻게’ 팔지 결정합니다.”

• 마진 : 제품 본연의 가치
• 광고 기여도 : 마케팅의 실효성

마진은 광고와 상관없이 그 물건을 가져와서 파는 행위 자체에서 발생하는 이익이다. 매출에서 매수 원가, 플랫폼 수수료 등 이런 것들을 빼고 나는 순수익이다.

분석 관점은 제품의 수익성으로, 이 제품을 계속 팔아도 될까? 라는 점이 포인트이다.

고기를 10만원 주고 사왔는데, 이것 저것 다 빼고 남는 돈이 너무 적거나, 오히려 손해를 보는 제품은 비즈니스 입장에서 팔면 안되는 제품이다.

반면, 광고 기여도의 분석 관점은 채널/캠페인의 효율로서, 이 광고를 계속 해도 될까를 고민하게 만든다. 즉 광고 기여도는 광고를 태웠을 때 남는 최종 수익(순수익)을 의미한다.

마진이 10000원이 남는데, 광고비 20000원을 사용하는 것은 말이 안된다는 것이다. 그럼 이 광고 전략은 수정되거나 중단해야 한다는 결론에 따른다.

마진 비율 : 내가 번 돈(마진)이 내가 쓴 돈(투자 원금)에 비해 몇 %나 되는지

Margin ratio = (Margin(순이익) / Total cost(총 비용)) * 100

얼핏 보면, 마진이 $20인 와인을 좋은 것 같다. 하지만 맥주의 경우 단 돈 $10를 가지고 $5를 벌어다 주는 50% 마진 비율을 가지고 있다.

즉, 내 돈(자본)을 훨씬 효율적으로 사용한다는 의미다. 자본 사용의 효율성을 보여지는 지표인 것이다.

• 투자 우선수위의 결정 : 마진 비율이 높은 상품군에 예산을 더 투입해야 회사가 빠르게 성장

그리고, 또 한 가지 우리는 위에서 수익 배수를 배운 적이 있다. 내가 낸 돈보다 얼마나 더 벌었는 지를 알아보는 지표이다.

그러면 내 자본을 얼마나 효율적으로 썼는지를 보여주는 마진 비율과 비슷한 것이 아닐까 하는 생각이 들었다. (수익 배수 vs 마진 비율)

수익 배수는 전체적인 덩어리의 변화를 보여주고, 마진 비율은 투자 대비 순수한 덤이 얼마인지를 보여줍니다

즉, 수익 배수가 1.2배라면 마진 비율은 20%인 셈이죠. 같은 현상을 배수로 부르느냐 백분율로 부르느냐 의 차이일 뿐이다.

3) 전체, 매출, 마진 대비 백분율

• 전체 대비 백분율(Percent of total) : (개별항목 값 / 전체항목 값) * 100
• 사용하는 이유 : 서로 다른 규모의 데이터를 비중이라는 공통 척도로 정규화하여 비교

• 매출 전체 대비 백분율 : (개별항목 매출 / 총 항목 매출) * 100
• 사용하는 이유 : 시장 점유율 및 회사 외형 성장을 어떤 상품이 견인하는지 보여주는 지표

• 마진 전체 대비 백분율 : (개별항목 마진 / 총 항목 마진) * 100
• 사용하는 이유 : 회사의 실질적인 수익(내실)에 누가 가장 많이 기여하는지 보여주는 지표

[비즈니스 핵심 지표 사용하는 이유]

• 유료 전환율(pCVR) : 단순히 방문자만 많은 지, 아니면 실제 구매로 이어지는 알짜 손님인지를 판별하여 광고 타겟팅의 정확도를 측정
• 주문당 비용(CPO) : 주문 1건을 만드는 데 드는 비용이 제품 마진보다 높지는 않은 지 확인하여, 수익성 있는 광고 예산을 설정
• 수익 배수 (Return Multiple) : 내 돈이 몇 배가 되었나 라는 덩어리의 개념으로 투자 성과를 빠르게 파악
• 가격 변동률 : 서로 다른 금액의 투자 종목들을 동일한 선상(%)에서 비교하여 어떤 투자가 더 알찼는지 평가
• 광고 기여도 (Ad Contribution) : 매출(Revenue)이라는 착시 현상에 속지 않고, 캠페인이 실제로 회사의 통장 잔고에 기여하고 있는지 냉정하게 판단
• 마진 비율 (Margin Ratio, %) : 적은 자본으로 얼마나 많은 이익을 남기는지 가성비를 측정하여, 어떤 상품에 리소스를 집중할지 결정
• 전체 대비 백분율 : 매출이나 마진의 핵심 동력(Core Driver)이 어디인지 찾아내어 숲 전체의 구조를 이해

비율을 구할 때에는 데이터 값에 반드시 음수가 존재하는 지 확인해야 한다. 음수를 가지고 있다면, 전체 그 항목의 합계가 줄게 되어 양수 마진이 전체에서 가지는 비율이 커져 버려서 100%를 넘는 경우도 생긴다.

비율 계산 시 음수가 있을 경우에는 그 데이터 값은 가감하게 버려라

SQL 데이터 변환 위한 함수

1) NULLIF & SAFE_DIVIDE

비즈니스 핵심 지표를 보시면 비율을 구하는 것이 많다. 비율을 구한다는 것은 연산에서 나눔을 의미하는 데 분모가 비워져 있는 경우에는 그 비율을 구할 수 없다.

이 경우에는 단 하나만 이런 경우가 발생해도 전체 데이터 측면에서 봐서 오류라서 멀쩡히 데이터 값이 있는 것도 오류로 간주된다

SELECT *,

100 * paid_orders / NULLIF(paid_sessions, 0) AS pcvr,

ad_spend / NULLIF(paid_orders, 0) AS cpo

FROM campaign_performance

ORDER BY campaign_day;

분모와 연관되는 부분만 보시라. NULLIF(paid_sessions, 0)의 의미는 paid_sessions 컬럼의 데이터 값이 비워져 있다면, Null로 표시해라는 의미이고, 그렇지 않은 것은 원래 데이터 값을 그대로 이용해라는 의미이다.

이렇게 하면 분모가 비워지는 경우가 없어 오류가 발생하지 않고 데이터 값이 구해진다. 하지만 Bigquery에서는 SAFE_DIVIDE라는 특별한 함수가 사용된다.

SELECT *,

SAFE_DIVIDE(100 * paid_orders, paid_sessions) AS pcvr,

SAFE_DIVIDE(ad_spend, paid_orders) AS cpo

FROM campaign_performance

ORDER BY campaign_day;

SAFE_DIVIDE(100 * paid_orders, paid_sessions)는 SAFE_DIVIDE(x,y)는 기본적으로 x를 y로 나누라는 의미이다.

만약 y 예시에서는 paid_sessions 컬럼 속 데이터가 비워져 있다면 그것을 Null로 인식하고 결과를 Null로 표시하라는 의미이다. 하지만, 데이터가 있는 것이라면 정상적으로 나눗셈을 진행하라는 뜻이다.

Nullif와 Safe_divide 함수를 사용하면, 나눗셈으로 인한 오류를 막을 수 있다.

2) WITH절

WITH절을 설명하기 위해 일단 광고 기여도, 기여율 부분으로 돌아가보자

ad_contribution (광고 기여도)

= revenue(매출) − ad_spend(광고비) −(paid_orders×avg_fulfillment_cost)(이행비)

contribution_rate(광고 기여율) =100 × ad_contribution/revenue

광고 기여율을 구하기 위해서는 SQL 데이터 변환 작업으로 먼저 광고 기여도(ad_contribution)를 구해야 한다. 그러고 나서 기여율을 구하면 나온다.

하지만, 한꺼번에 아래와 같이 하나의 코드에서 실행하는 적용이 안된다

SELECT *,

revenue – ad_spend – (paid_orders * avg_fulfillment_cost) AS ad_contribution

100 × (ad_contribution/revenue) AS contribution_rate

FROM campaign_performance

두번째 줄에서 광고 기여도(ad_contribution)를 구하고, 그 다음 세번째 줄에서 그 광고 기여도를 넣고 광고 기여율을 구한다.

하지만 이렇게 하면 오류가 나온다. ad_contribution의 데이터 값이 아직 저장되지 않았기에 세번째 줄에서는 ad_contribution 데이터를 가져올 수 없고 따라서 오류가 발생한다

이 때 사용하는 것이 WITH절이다. 하는 일은 원래 쿼리를 감싸고 그 결과에 이름을 부여한다

WITH campaign_performance_extended AS (

SELECT *,

revenue – ad_spend – (paid_orders * avg_fulfillment_cost) AS ad_contribution

FROM campaign_performance

)

SELECT *,

SAFE_DIVIDE(100 * ad_contribution, revenue) AS contribution_rate

FROM campaign_performance_extended;

WITH절은 문제였던 ad_contribution 값을 campaign_performance_extended 임시 테이블에 저장을 해 버리는 역할을 한다

그리고 다음 줄에서 광고 기여율(contribution_rate)를 구할 때 campaign_performance_extended 임시 테이블에 저장된 ad_contribution을 가져올 수 있게 되는 것이다.

다시 정리해 보자

• WITH 절은 쿼리의 결과로 이름이 지정된 임시 테이블을 생성
• 임시 테이블에는 원래 컬럼들과 새로 계산한 컬럼들이 모두 포함
• 최종 SELECT는 원래 테이블이 아닌 임시 테이블에서 데이터를 가져옴
• WITH 단계의 컬럼 별칭은 최종 SELECT에서 이름으로 직접 사용할 수 있음

3) 스칼라 서브쿼리(Scalar Subquery)

스칼라 서브쿼리는 다른 쿼리 안에 중첩된 쿼리로, 단일 값 하나만 반환한다. 집계 함수를 행 단위 계산에서 사용할 때 유용하다.

스칼라라는 이름은 테이블이나 여러 행이 아닌 단일 값(스칼라)을 반환하기 때문이다. 아래 쿼리를 보자.

SELECT *,

SAFE_DIVIDE(100 * revenue, SUM(revenue)) AS revenue_pot,

FROM category_sales;

매출 전체 비중을 구하고, 그 값을 revenue_pot에 넣어라 한다. 그게 문제가 없는 쿼리라 생각할 수 있다.

SELECT *, 는 개별 행을 전체 다 보여달라는 것이고, SUM(revenue)는 모든 행의 합계이니 이것은 단일 행이라 생각할 수 있다. SQL은 이 부분을 혼란스러워 해서 오류가 생기는 것이다

스칼라 서브쿼리는 여기에선 SUM(revenue)를 먼저 계산해서 하나의 값을 가져와 버린다. 그러면 SQL은 개별 행과 어떤 특정 값(더 이상 SUM(revenue)가 아니니 오류 없이 작업을 실행할 수 있다는 것이다.

SELECT *,

SAFE_DIVIDE(100 * revenue, (SELECT SUM(revenue) FROM category_sales)) AS revenue_pot,

FROM category_sales;

빨간색으로 되어 있는 부분이 스칼라 서브쿼리 부분이다. 쿼리 안에 쿼리로 생각해 보면 된다. 서브쿼리가 먼저 계산되어 특정 값이 나온다. 더 이상 SUM은 없는 것이다.

4) Round() 함수

가독성을 위해 소수점 자릿수를 조절하려면 ROUND() 함수를 사용한다. 아래의 예시는 소수점 2자리까지 표시하는 것이다.

SELECT *,

ROUND(100 * col_a / col_b, 2) AS percentage

FROM table_name;

마무리

오늘은 SQL 데이터 변환을 통해 새로 만들어 질 수 있는 비즈니스 핵심 지표에 대해서 알아보는 시간을 가져 보았다.

데이터 분석이 특히 비즈니스에 사용되는 경우가 많기 때문에 비즈니스 지표를 이해할 수 있는 것이 향후 대시보드에서 스토리를 얘기할 때 더 유용하다고 생각해서 이 내용을 정리해 보았다.

또한 데이터 변환을 위해서 생길 수 있는 오류를 Nullif, Safe_divide, WITH절, 스칼라 서브쿼리 등도 알아보는 시간을 가져보았다.

나는 단순히 코드, 키워드를 익히는 것이 데이터 분석가가 되는 첫번째 요소라 생각하지 않는다. 데이터가 말해주는 것을 이해하고 해석할 수 있는 능력이 더 중요하다고 생각한다. 그러기 위해서는 비즈니스 세계에서 자주 사용되는 핵심 지표는 꼭 이해하고 있어야 한다고 생각한다.