광고비 ROAS 엑셀 보고서, 광고센터 RAW 한 장에서 자동으로 집계하기

광고센터에서 내려받은 원본 RAW는 ROAS가 U열에 가서야 나올 만큼 열이 깁니다. 데모로 옮기면서 보고에 쓰는 열 열두 개만 남겼습니다.

날짜, 광고 상품명, 그룹명, 상태, 노출수, 클릭수가 앞에 섭니다. 그 뒤로 클릭률, 클릭당비용, 광고비, 14일 구매 전환수와 전환액, 14일 ROAS가 이어집니다.

제가 데모로 옮긴 커머스 광고 엑셀은 이 RAW가 1만 행을 넘었습니다. 같은 상품이 광고그룹마다 따로 한 줄씩 찍히고, 날마다 또 한 줄이 붙습니다.

보고서에 필요한 건 "어느 상품에 얼마 썼고 얼마 벌었나" 몇 줄입니다. 그 몇 줄을 얻으려고 1만 행을 매주 손으로 묶고 있다면 같은 숫자를 여러 번 만드는 셈입니다.

실무 엑셀 두 개를 가짜 데이터로 옮긴 데모를 놓고, RAW 한 장에서 보고서까지 이어지는 구조를 뜯어 보겠습니다. 하나는 쇼핑몰 광고, 하나는 모바일 게임 마케팅 지표입니다.

RAW 한 장의 생김새

커머스 데모의 RAW 시트 이름은 '광고(상품별)'입니다. 원본 1만여 행 가운데 분포가 비슷한 대표 45행만 남겼습니다.

행 하나는 상품 하나가 광고그룹 하나에서 낸 성과입니다. 광고그룹은 다섯 개이고, 일자별 시트는 3월 1일부터 11일까지 11일 치를 담습니다.

여기서 눈여겨볼 열은 마지막 14일 ROAS입니다. 광고센터가 이미 계산해 준 값이라 그대로 더하거나 평균 내고 싶어집니다.

그런데 이 열을 피벗에서 그대로 쓰면 숫자가 틀어집니다. 그 이야기는 조금 뒤에 다시 합니다.

보관용 RAW와 계산용 시트

구조의 출발은 RAW를 고치지 않는 것입니다. 광고센터 CSV는 파워쿼리로 불러와 열 이름과 형식만 맞춥니다.

사람 손은 RAW 시트에 닿지 않습니다. 정렬을 바꾸거나 행을 지우는 작업은 전부 그 뒤 시트에서 합니다.

이렇게 나누면 "보고서 숫자가 원본과 맞느냐"는 질문에 답하기 쉬워집니다. 원본이 그대로 남아 있으니 다시 세어 볼 기준이 있습니다.

RAW 위에는 피벗 시트 세 장이 올라갑니다. 상품별 피벗, 일자별 추이, 광고그룹별 피벗입니다.

세 장 모두 같은 RAW를 바라봅니다. 상품별 시트에 숫자를 따로 붙여 넣는 일이 없으니 시트끼리 숫자가 어긋날 틈이 줄어듭니다.

피벗을 받치는 집계식 한 줄

상품별 피벗의 광고비 칸은 SUMIFS 한 줄입니다. RAW의 광고비 열을, 상품명 열이 이 행의 상품명과 같은 것만 골라 더합니다.

전환수와 전환액 칸도 더하는 열만 바뀝니다. 함수 자체는 어렵지 않으니 설명은 여기까지만 하겠습니다.

제가 더 신경 쓴 건 범위입니다. 커머스 데모의 SUMIFS는 RAW의 열 전체를 참조합니다.

다음 주 RAW가 행 수가 늘어서 와도 범위를 고칠 일이 없습니다. 파워쿼리를 새로 고치면 피벗 숫자가 따라옵니다.

게임 데모는 반대로 2행부터 마지막 행까지 범위를 고정해 두었습니다. 원본 시트 구조를 그대로 옮긴 결과입니다.

이 방식은 행이 늘면 범위도 같이 늘려야 합니다. 손으로 늘리다 새 행이 빠진 채 보고가 나가는 일이 흔해서, 새로 만든다면 열 전체 참조나 표 참조를 권합니다.

ROAS를 평균 내면 생기는 일

피벗의 ROAS는 합친 전환액을 합친 광고비로 다시 나눠서 구합니다. RAW의 ROAS 열을 평균 내면 숫자가 틀어집니다.

데모 RAW의 '핀턱 하프 집업 니트'가 좋은 예입니다. 이 상품은 광고그룹 두 곳에서 따로 집계됩니다.

행	광고비 / 전환액	ROAS
고급설정 그룹	5,238 / 32,610	623%
쉬운설정 그룹	2,221 / 0	0%
두 행 ROAS 평균		311%
합계로 다시 계산	7,459 / 32,610	437%

평균은 광고비 5천 원짜리 행과 2천 원짜리 행에 같은 무게를 줍니다. 그래서 실제 효율보다 100%포인트 넘게 낮게 나왔습니다.

데모는 이 원칙을 모든 파생 지표에 걸었습니다. ROAS, 클릭률, 클릭당비용은 언제나 원본 RAW의 합계에서 다시 계산합니다.

상품별 피벗의 총합계 ROAS도 합친 전환액을 합친 광고비로 나눈 식입니다. 시트마다 ROAS가 한 자리씩 다르게 찍히는 문제는 대개 이 지점에서 생깁니다.

손익분기 100%, 목표 300%

숫자를 다 계산해도 보고받는 쪽은 상품을 하나하나 읽지 않습니다. 데모의 상품별 피벗도 광고비 상위 20개만 보여 줍니다. 그래서 기준선을 서식으로 걸어 두었습니다.

ROAS 100% 미만은 빨간 배경입니다. 광고비보다 전환액이 적다는 뜻이라 대시보드 KPI 카드에는 '손익분기 미달'이라는 꼬리표가 붙습니다.

300% 이상은 초록 굵은 글씨로 '목표 달성'을 표시합니다. 그 사이는 서식 없이 둡니다.

전환액이 0인 행은 일부러 빨강에서 뺐습니다. 데모 RAW 45행 가운데 상품 42개를 묶으면 36개가 전환액 0이라, 전부 빨갛게 칠하면 화면이 빨강으로 덮입니다.

이런 행은 ROAS 칸을 회색 줄표로 두고, 광고비 열의 색조 히트맵으로 지출 크기를 따로 보게 했습니다. 광고 상태가 '노출불가'인 행도 회색 글자입니다.

대신 슬라이서로 광고그룹을 하나만 고르면 이 구분이 드러납니다. 위 그림 오른쪽 카드가 그 장면입니다.

'고급설정_3단계_A' 그룹은 상품 6행에 광고비 7,416이 나갔는데 전환액이 0입니다. 이 그룹만 고르면 ROAS 카드가 0%와 함께 손익분기 미달을 띄웁니다.

구간	표시
300% 이상	초록 굵게, 목표 달성
100% 이상 300% 미만	서식 없음
100% 미만	빨간 배경, 손익분기 미달
전환액 0	회색, 광고비 히트맵으로 따로 확인

기준값 300%는 데모에 넣은 예시입니다. 마진 구조가 다르면 손익분기 ROAS도 달라지니, 자기 상품 원가율로 계산한 숫자를 넣으세요.

게임 지표에 옮겨 본 같은 뼈대

두 번째 데모는 가상의 모바일 게임 마케팅 지표입니다. RAW 시트 한 행이 하루이고 DAU·신규 설치·결제자·ARPPU·ARPU·D1·D7·D30 리텐션이 들어 있습니다.

원본은 16개월 치였고 데모는 최근 6개월로 줄였습니다. 달마다 5일씩 표본을 골라 30행만 남겼습니다.

월별 피벗은 날짜가 "2025-06"으로 시작하는 행만 골라 평균이나 합계를 냅니다. DAU와 신규 설치는 평균, 매출은 합계입니다.

그 위에 응용지표 시트가 있습니다. 원본의 '계산관련' 시트에 정의된 12개 공식 가운데 핵심을 RAW에서 일별로 다시 계산합니다.

RN7은 신규 설치에 D7 리텐션을 곱해 7일 뒤 남는 신규 사용자를 셉니다. LTV는 ARPU에 리텐션으로 근사한 평균 잔존일을 곱합니다.

이 데모의 월별 ROAS에는 한계가 있습니다. 이 RAW에는 광고비 열이 없고, 월 값은 일별 ROAS의 평균입니다.

앞에서 평균 내면 틀린다고 한 바로 그 방식입니다. 광고비 열을 RAW에 받을 수 있는 환경이라면 커머스 데모처럼 합계끼리 나누는 쪽으로 바꾸는 게 맞습니다.

100% 경계는 같습니다. 데모 데이터에서 9월부터 지표가 꺾이고, 10월 월평균 ROAS가 96.5%로 내려가면서 빨강으로 바뀝니다.

실습으로 갈지, 대시보드로 갈지

광고 매체가 둘 이상이거나 상품 수가 많을수록 이 구조의 이득이 커집니다. RAW만 매체별로 쌓고 피벗과 서식은 한 번 만들어 두면 됩니다.

엑셀로 직접 만들어 보고 싶다면 파워쿼리·피벗 대시보드 교육이 이 흐름을 4시간 실습으로 다룹니다. 회사 RAW의 열 구조를 알려 주면 같은 모양의 가짜 데이터로 실습 파일을 만듭니다.

매체 API에서 바로 받아 오거나, 여러 사람이 같은 화면을 봐야 한다면 웹 대시보드로 옮기는 쪽이 낫습니다. 그런 경우는 대시보드 제작 상담에서 RAW 샘플 한 장으로 범위를 같이 잡을 수 있습니다.

탭을 옮기는 순서

커머스 광고 ROI 데모를 열면 대시보드 시트부터 보입니다. 아래 순서로 탭을 옮겨 보세요.

먼저 '광고(상품별)' 탭에서 14일 ROAS 셀을 하나 눌러 보세요. 수식바에 전환액을 광고비로 나누는 식이 뜹니다.

다음은 '상품별피벗' 탭입니다. 광고비 셀을 누르면 RAW 열 전체를 참조하는 SUMIFS가 보이고, 맨 아래 총합계 ROAS는 합계끼리 나눈 식입니다.

마지막으로 대시보드로 돌아와 광고그룹 슬라이서에서 '고급설정_3단계_A'만 고르세요. ROAS 카드가 손익분기 미달로 바뀌는 걸 볼 수 있습니다.

게임 쪽은 포트폴리오의 게임 마케팅 지표 데모에서 '기간(월)' 슬라이서로 2025-10만 고르세요. ROAS 카드가 96.5%와 함께 손익분기 미만 ⚠로 바뀝니다.

둘러본 뒤에는 지금 쓰는 보고서 파일을 열고 아래 질문에 답해 보세요.

점검 질문	아니라면 생기는 일
내려받은 RAW를 손대지 않고 따로 보관하는가	숫자가 원본과 맞는지 다시 셀 기준이 사라집니다
ROAS·CTR·CPC를 원본 합계에서 다시 계산하는가	시트마다 ROAS가 다르게 찍힙니다
손익분기와 목표 기준이 서식으로 걸려 있는가	효율 낮은 소재를 사람이 눈으로 찾아야 합니다
새 RAW를 붙이면 보고서가 저절로 바뀌는가	범위를 늘리다 새 행이 빠진 채 보고가 나갑니다

넷 중 둘 이상이 '아니오'라면 피벗을 다듬기 전에 RAW 시트를 따로 떼는 일부터 시작하세요.